Documentation Center

Changing the Query Used by an Analytics Report

You can customize that query that is used to populate an analytics report with data.

Before you begin

You must have Use application, Use analytics, and Develop app permissions and have a thorough understanding of XML and SDL LiveContent Reach skin development. See "Customizing Skins" in the SDL LiveContent Reach User Guide for more information. Additionally, if your modifications extend beyond changing a report title, report grouping, or chart type, you should be knowledgeable about HTML, CSS, JavaScript, XQuery, and XSL technologies.

About this task

When a report is activated, SDL LiveContent Reach runs a query to gather the analytic data into an XML model. The query that is used is defined by the <query> element in the configuration, which defines the XQuery file to load from the skin, and the $function variable to pass to the XQuery file.

<query file="your_queries.xql" function="MostViewedMedia"/> 
This query is expected to return an XML model, and it must conform to the following:
  • Line Chart: each series represents a line and each point is a date within that data set. The point ( name attribute and x attribute) should be a date, and the y attribute should be the value of the point.

    The following is a typical structure returned by the query:
    <result> 
    <series name="Name of Series"> 
    <point name="" x="" y=""/> 
    ... 
    </series> 
    ... 
    </result>
  • Pie and Bar Chart: there is only one series and each point represents a slice of data. In a bar or pie chart, the name attribute should be the label for the data point, and y attribute should be the value of the point.
    The following is a typical structure returned by the query:
    <result>
    <series name="Name of Series">
    <point name="Label 1" y=""/>
    ...
    </series>
    </result>

Procedure

  1. Open the configuration, and identify the XQuery file noted in the file attribute.

    LiveContent_home/db/LiveContent/ui/skins/your_skin/xql/your_queries.xql

  2. Edit the appropriate XQuery statements to suit your needs.
    Example:
    LiveContent_home/db/LiveContent
    /ui/skins/your_skin/xql/your_queries.xql
    
    (: =================================================
          
          All Site Traffic Custom
                -- count of visitors who performed a @significant event
       ================================================= :)
    if($query_func = "AllSiteTrafficCustom") then (
          let $result := util:eval(concat("/Audit[child::event[@significant='1' ", 
    						$filter-params, "]]"))
          return
            (: result for LineChart :)    
            <result>
               <series>
               {
               for $d in $dateRanges
                 let $startDay := LiveContent-Analytics:get-start-date($d)
                 let $endDay := LiveContent-Analytics:get-end-date($d)
                 let $events := LiveContent-Analytics:get-events-in-date-range
                     ($result, $startDay, $endDay)
                      return
                      <point name="{$endDay}" x="{$endDay}" y="{count($events)}"/>
               }
               </series>
            </result>
    ) else (......)
    
  3. Save the query.
  4. Upload the new skin customization into the database.
    • You can upload the resources using the web UI, as described by Adding a Skin and Resources.
    • You can also update the skins via the command line using the loaddb tool. Copy the updated skin resources from the source code repository to LiveContent_home/db/LiveContent/ui/skins/<your_skin>/.
      • On Windows, to load the most recent changes into the database run this command:

        loaddb.bat UPGRADE

      • On Linux, run this command:

        loaddb.sh UPGRADE