September 20, 2013

Editing DMS Reports to be Multi-Site Friendly

By: Craig Taylor
September 20, 2013

Making DMS Reports Mult-Site Friendly
My client is using a single instance of Sitecore with multiple sites.  While reveiwing Sitecore's built-in reports for DMS, I noticed something that was going to make it difficult to produce reports that could be broken down by site.

Specifically, when looking at the "Latest Visits" report and when drilling down to the "Visit Detail (Session)" report, I noticed that I can't differentiate between the separate sites.  I would only see the visits as "/home" or "/contact-us."  It is impossible to know which site on the instance the visitor visited.

The Solution

In order to see which site was visited, we need to edit the default report provided by Sitecore.  In this example, we'll look at the "Visit Detail (Session)" report.  This can be accomplished in two steps:

  1. Edit the SQL Query for the report
  2. Edit the Report

Edit the SQL Query for the report

The SQL query for the "Visit Detail" report is located at \sitecore\System\Settings\Analytics\Reports SQL Queries\Visit Pages.  We need to update this query to include the site that was visited.  Luckily, DMS logs this information and stores it in the "Visits" table, in the "MultiSite" column.  We can add this "MultiSite" column to our query and once updated, the (SQL) query should look like this:
SELECT [Duration], [Url], [PageId], [PageId] as [PageIdObject], [Visits].[MultiSite] as [Site]
FROM [Pages] INNER JOIN [Visits] ON [Pages].[VisitorId] = [Visits].[VisitorId] AND [Pages].[VisitId] = [Visits].[VisitId]
WHERE 
  [Visits].[VisitId] = @VisitId
ORDER BY [DateTime], [VisitPageIndex]
I've given my "MultiSite" column an alias of "Site" for clarity.

Note: Even if you're not using Oracle, you might as well update the Oracle script as well in case a database switch is made later down the road:
SELECT "DURATION", "URL", "PAGEID", "PAGEID" as "PAGEIDOBJECT", "VISITS"."MULTISITE" as "SITE"
FROM "PAGES" INNER JOIN "VISITS" ON "PAGES"."VISITORID" = "VISITS"."VISITORID" AND "PAGES"."VISITID" = "VISITS"."VISITID"
WHERE 
  "VISITS"."VISITID" = :VisitId
ORDER BY "DATETIME", "VISITPAGEINDEX"

Edit the Report

Now that the report query has been updated to include which site was visited, we need to update the report to display this information.  While viewing the report, you can click the "Design" button in order to open the Stimulsoft Reports Designer application to edit the report.  I'm sure this might work well, but I wasn't able to get it to work correctly.  I instead went straight to the ".mrt" file in order to edit the report.  The mrt file for the "Visit Detail" report is located at \Website\sitecore\shell\Applications\Analytics\VisitDetail.mrt.  Open it up in your favorite text editor and you will see that it's just XML.

I searched in this file for where the other columns of the report query were being used.  I found the list of columns that were added and I added an additional column for my 'site' column:
<value>Site,System.String</value>
I also incremented the 'count' value for the columns.  The complete section looks like this:
<VisitPages Ref="5" type="Stimulsoft.Report.Dictionary.StiSqlSource" isKey="true">
        <Alias>VisitPages</Alias>
        <Columns isList="true" count="8">
          <value>Url,System.String</value>
          <value>Duration,System.Int32</value>
          <value>Hour,Hour,System.Int32,VisitPages.Duration_x002F_3600</value>
          <value>Minutes,Minutes,System.Int32,VisitPages.Duration_x002F_60</value>
          <value>PageId,System.Guid</value>
          <value>PageIdObject,System.Object</value>
          <value>DurationTimespan,DurationTimespan,System.TimeSpan,new_x0020_TimeSpan_x0028_0_x002C__x0020_0_x002C__x0020_0_x002C__x0020_0_x002C_VisitPages.Duration_x0029_</value>
          <value>Site,System.String</value>
        </Columns>
        <CommandTimeout>0</CommandTimeout>
        <Dictionary isRef="1" />
        <Name>VisitPages</Name>
        <NameInSource>DataConnection</NameInSource>
        <Parameters isList="true" count="1">
          <value>_x0040_VisitId,,14,0</value>
        </Parameters>
        <SqlCommand />
      </VisitPages>

This allows the report to make use of the field, but we now need to add the field to the report.  I then searched for where the text was being built for displaying the page visited.  I found this and added the site name in:
<Text>{string.IsNullOrEmpty(VisitPages.Url)|| VisitPages.Url == "/" ? VisitPages.Site + "/default.aspx" : VisitPages.Site + VisitPages.Url}</Text>

The complete section looks like this:
            <Text21 Ref="58" type="Text" isKey="true">
              <Brush>Transparent</Brush>
              <ClientRectangle>4.83,0,13.97,0.4</ClientRectangle>
              <ComponentStyle>GeneralText</ComponentStyle>
              <Conditions isList="true" count="0" />
              <Font>Arial,10</Font>
              <Margins>0,0,0,0</Margins>
              <Name>Text21</Name>
              <Page isRef="11" />
              <Parent isRef="55" />
              <Text>{string.IsNullOrEmpty(VisitPages.Url)|| VisitPages.Url == "/" ? VisitPages.Site + "/default.aspx" : VisitPages.Site + VisitPages.Url}</Text>
              <TextBrush>Black</TextBrush>
              <TextOptions>HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,</TextOptions>
              <Type>Expression</Type>
            </Text21>

The Results

Now, when drilling down from the "Latest Visits" report to the "Visit Detail" report, we can see that the (redacted) site name is now present on our report:
DMS Visit Detail Multisite Report