Using CardioLog's SDK, you can tap into the CardioLog database and create your own reports. The creation of a custom report using the CardioLog's SDK requires T-SQL programming skills.
In order to create a custom report, the following steps are required:

    1. Setting the report properties
    2. Setting the report preferences
    3. Creating an SQL stored procedure to populate the report
    4. Registering the report in the CardioLog UI

Contents

Setting the Report Properties

The properties for a custom report should be defined in the following file: [CardioLog Installation Directory]\CardioLog\Data\SpecialReportsCustom.xml

Report – Required fields

<specialReports>
   <specialReport id="[report_id]" title="[report_title]" type="[Table | Chart | Meter]" cmdText="sql_stored_procedure" />
</specialReports>
  • id - report id must be over 9999
  • cmdText - the stored procedure used by the report
  • type - report type

 

Example: 

<specialReport id="10000" title="My Custom Report" type="Table" cmdText="stp_my_sql_procedure" />

Report – Optional fields

<param name="[stored_procedure_param]" rulePref="[CustomParam[num] | entityInfo | entityType]" prefType="[Int | Url | String ]" defaultVal="[number]" />

  • <param> - This field maps custom parameters in Report Preferences to the actual SQL stored procedure parameters.
  • name - mapping to the stored procedure parameter

rulePref:

  • CustomParam - a reference to a <customparam> in Report Preferences
  • entityInfo - a reference to a <entityUrl> in Report Preferences
  • entityType - used by reports which have an item-type filter

 

Example:

<specialReport id="10002" title="Top Users By Url Table" type="Table" cmdText="stp_ visits_top_users_by_url_table" >

<param name="@url" rulePref="entityInfo" prefType="Url" defaultVal="http://" />

</specialReport>

<specialReport id="10003" title="Top Content Contributors Table" type="Table" cmdText="stp_ portal_top_content_contributors_table" >

<param name="@entityType" rulePref="CustomParam0" prefType="Int" defaultVal="-1" />

</specialReport>


Setting the Report Preferences

The appearance of the report is formatted using an Xml string.

Table Report

<prefs>
   <showhelp>false</showhelp>
   <showpreferences>false</showpreferences>
   <timeframe>36000000000</timeframe>
   <timeinterval>36000000000</timeinterval>
   <maxrows>10</maxrows>
   <mincount>1</mincount>
   <reporttype>10001</reporttype>
   <columns>
      <column id="User" title="User" type="text" width="35%" />
      <column id="views" title="Views" width="15%" type="number" sorted="true" />
      <column id="visits" title="Visits" width="15%" type="number" />
      <column id="searches" title="Searches" width="15%" type="number" />
      <column id="avgVisitDuration" title="Visit Duration" width="20%"
       type="number" />
   </columns>
   <ctitle>Unique Users</ctitle>
</prefs>

Required Preferences Fields

<timeframe>[milliseconds]</timeframe>

  • <timeframe>The date period for the report, in milliseconds.
  • This hour: 600000000
  • Last hour: -600000000
  • This day: 36000000000
  • Last day: -36000000000
  • This month: 6048000000000
  • Last month: -6048000000000
  • This quarter: 25920000000000
  • Last quarter: -25920000000000
  • This year: 77760000000000
  • Last year: -77760000000000
  • Custom timeframe: 0

Example: <timeframe>36000000000</timeframe>


<timeframe_START> - A dynamic time period for the report.

<timeframe_START>[number]d</timeframe_START>

<timeframe_END>today</timeframe_END>

<timeframe_TITLE>Last [number] days</timeframe_TITLE>

Note: These tags are used whenever timeframe is set to 0. All three tags are required

Example:

<timeframe_START>30d</timeframe_START>

<timeframe_END>today</timeframe_END>

<timeframe_TITLE>Last 30 days</timeframe_TITLE>


<maxrows> - The number of rows displayed by the report.

<maxrows>[number]</maxrows>

Example:<maxrows>10</maxrows>


<mincount> - Show rows with a minimum results count.

<mincount>[number]</mincount>

Example:<mincount>5</mincount>

 

<reporttype> - This is a reference to the report properties, as defined in SpecialReportsCustom.xml. See Setting the Report Properties.

<reporttype>[number]</reporttype>

Example:<reporttype>10000</reporttype>


<columns> - Definition of report columns.

<columns>

<column id="[column id]" title="[column name]" width="[px | %]" type="[text | SPPage | number | hidden | user]" sorted="[true | false]" />

</columns>

  • id - corresponds to the column name returned by the SQL query
  • title - display name for the column
  • sorted - results are sorted by the specified column

Example:

<columns>

<column id="title" title="User" width="30%" type="text" />

<column id="views" title="Views" sorted="true" width="15%"

type="number" />

<column id="visits" title="Visits" width="15%" type="number" />

<column id="searches" title="Searches" width="15%" type="number" />

<column id="duration" title="Visit Duration" width="15%" type="number" />

</columns>


<ctitle> - The report title.

<ctitle>[report_name]</ctitle>

Example:<ctitle>Unique Users</ctitle>

Optional Preferences Fields

<aduser>Active Directory users filter.

<aduser>userid [,userid]</aduser>

<adusername>username [,username]</adusername>

Example:

<aduser>123267,123171</aduser>

<adusername>Myron Childs, Ronald Clapp</adusername>


<adgroup> - Active Directory groups filter.

<adgroup>[groupid [,groupid]]</adgroup>

<adgroupname>[group_name]</adgroupname>

Example:

<adgroup>16202</adgroup>

<adgroupname>MY-COMPANY\Marketing</adgroupname>


<categories> - User categories filter.

<categories>

<category id='[category_id]' value='[valueid [,valueid]'>[category_name, [category_name]]</category>

</categories>

Example:

<categories>

<category id='1' value='10,6'>Development, PostSales</category>

</categories>


<customparam> - Custom report filters.

<customparam0 title="[filter_name]" type="select"

selectValues="[name1]:[value1]|[name2]:[value2]">

[default_value]

</customparam0>

Example:

<customparam0 title="$Type$" type="select"

selectValues="All:-1|Blogs:101|Documents:9|Lists:2|List Items:6|Personal Space:10|Publishing Sites:102|Search Centers:105|Sites:1|Record Centers:104|Report Centers:103|Web Page:107|Web Part Pages:11|Wikis:100">-1</customparam0>


<entityUrl> - Report filtering by URL. Use this option for reporting on pages which are not included in the portal tree (Object Explorer).

<entityUrl>[url]</entityUrl>

Example: <entityUrl>http://myportal/_layouts</entityUrl>

 

<entityId> - Report filtering by a specific item from the portal tree (Object Explorer).

<entityId>0:[item_id] [,0:[item_id]]</entityId>

<entityName>[item_name [,[item_name]]</entityName>

<entityTree>0[,0]</entityTree>

Example:

<entityId>0:34d2534b-8bee-4b6c-968d-efbc7841a1a4</entityId>

<entityName>My Portal</entityName>

<entityTree>0</entityTree>


<showhelp> - Show/hide help for the report.

<showhelp>[false | true]</showhelp>

Example:<showhelp>false</showhelp>

Creating the SQL Stored Procedure

The stored procedure should query the main SQL Usage Events table, named – CardioLog.dbo.tab_event_log. This table can be joined with other tables (from within the CardioLog database or from External databases) for richer reports.

For more details about the CardioLog database tables, see the CardioLog Database Structure document.

Table Report

The guidelines for creating a stored procedure for a table report are as follows:

  1. The stored procedure should be created in the CardioLog database.
  2. The stored procedure should return a table with columns as defined in the <columns> report preferences field.
  3. The stored procedure should include a list of required parameters (see next section).
  4. The stored procedure should include a list of optional parameters defined in the report properties.

 

Example:

Stored Procedure:

Use [CardioLog]

GO

CREATE procedure [dbo].[stp_top_documents_table]

--required parameters

@startTime datetime,

@endTime datetime,

@groupIds varchar(2000),

@userIds varchar(2000),

@categoryIds varchar(2000),

@eventType smallint,

@spLocation varchar(500),

@aggregated tinyint,

@maxRows int,

@minCount int,

@trafficSourceNames nvarchar(255) = null,
@trafficSourceConditions nvarchar(255) = null,
@trafficSourceValues nvarchar(500) = null,
@goalIds varchar(255) = null,
@countryIds varchar(255) = null,

--optional parameter

@entityType smallint

AS

SELECT

url, title, views

FROM

--code…

Report Properties:

<specialReport id="10000" title="Top Documents Table" type="Table" cmdText="stp_top_blogs">
   <param name="@entityType" rulePref="entityType" prefType="Int" defaultVal="9" />
</specialReport>

Report Preferences:

<prefs>

<showhelp>false</showhelp>

<timeframe>36000000000</timeframe>

<maxrows>10</maxrows>

<mincount>1</mincount>

<reporttype>10000</reporttype>

<columns>

<column id="title" title="Title" width="80%" type="SPPage" />

<column id="url" title="Url" width="0" type="hidden" />

<column id="views" title="Views" sorted="true" width="20%"

type="number" />

</columns>

<ctitle>Top Documents</ctitle>

</prefs>

Required stored procedure parameters

The following parameters are required. They are populated automatically by the reporting engine:

  • @startTime datetime,
  • @endTime datetime,
  • @groupIds varchar(2000),
  • @userIds varchar(2000),
  • @categoryIds varchar(2000),
  • @eventType smallint,
  • @spLocation varchar(500), or @url varchar(500)
  • @aggregated tinyint,
  • @maxRows int,
  • @minCount int,

Registering a Report in the CardioLog UI

In the CardioLog UI, reports can be added to dashboards in both the Report Center and Analysis Center. This is done by right clicking the Central Area and selecting a report.Your browser may not support display of this image.

To make a new report available for selection in the UI, the following is required:

    1. Optional: create a submenu (directory) for the report
    2. Inserting the Report Into tab_catalog_controls
    3. Creating the Report Rule
    4. Linking the Directory-Report and Report-Rule

Creating a Report Sub-Menu

The creation of a report sub-menu is done with an SQL batch.

Example:

Use [CardioLog]

GO

SET IDENTITY_INSERT [dbo].[tab_catalog_dir] ON

INSERT [dbo].[tab_catalog_dir]

([DirectoryId], [Name], [ParentId], [order])

VALUES

(2500, N'Top Documents', 2040, 100)

SET IDENTITY_INSERT [dbo].[tab_catalog_dir] OFF

GO

Note: DirectoryId value should be greater than 2499

In this example, the parent directory (2040) is “Page Views”.

Inserting a Report into the CardioLog Database

Inserting the Report Into tab_catalog_controls

Use [CardioLog]

GO

INSERT [dbo].[tab_catalog_controls]

([controlId],

[Type],

[viewerType],

[title],

[description],

[prefs],

[reportPrefs])

VALUES

([number],

N'[report | graph | meter]',

N'[Report | SimpleChart | Meter]',

N'[Table | Chart | Meter]',

N'[report_description]',

N'[report_prefrences]',

N'<prefs/>')

  • controlId - value should be greater than 9999
  • prefs - column holds the XML report preferences file


Table Report Example:

Use [CardioLog]

GO

INSERT [dbo].[tab_catalog_controls]

([controlId],

[Type],

[viewerType],

[title],

[description],

[prefs],

[reportPrefs])

VALUES

(10000,

N'report',

N'Report',

N'Table',

N'This is my custom report.', N'<prefs><showhelp>false</showhelp><timeframe>36000000000</timeframe><maxrows>10</maxrows><mincount>1</mincount><reporttype>10000</reporttype><columns><column id="title" title="Title" width="80%" type="SPPage" /><column id="url" title="Url" width="0" type="hidden" /><column id="views" title="Views" sorted="true" width="20%"type="number" /></columns><ctitle>Top Documents</ctitle></prefs>',

N'<prefs/>')

 

Creating the Report Rule

The report rule holds additional properties for the report.

Use [CardioLog]

GO

INSERT [dbo].[tab_rule]

([RuleId],

[deleted],

[RuleType],

[UserLogMethod],

[EntityLogMethod],

[LastAnalysisTime],

[UserInfo],

[EntityInfo],

[Prefs],

[Title],

[IsIndexed],

[eventType])

VALUES

([number],

0,

5,

-1,

[2 | 3],

getdate(),

N'',

N'***DYN_PARAM***',

N'<prefs><reportType>[number]</reportType></prefs>',

N'[title]',

0,

[0 | 1 | 2])

RuleId value should be greater than 9999

EntityInfo Use value: 2 = specific (homepage), 3 = aggregated (all pages)

Prefs - number is the same as the <reporttype> value in the Report Preferences.

eventType 0 = view, 1 = duration, 2 = search


Table Report Example:

Use [CardioLog]

GO

INSERT [dbo].[tab_rule]

([RuleId],

[deleted],

[RuleType],

[UserLogMethod],

[EntityLogMethod],

[LastAnalysisTime],

[UserInfo],

[EntityInfo],

[Prefs],

[Title],

[IsIndexed],

[eventType])

VALUES

(2162,

0,

5,

-1,

3,

getdate(),

N'',

N'***DYN_PARAM***',

N'<prefs><reportType>2162</reportType></prefs>',

N'Top Documents - Table - Aggregated',

0,

0)


Linking Report-Rule and Directory-Report

 

INSERT [dbo].[tab_catalog_controls_rules]

([controlId], [ruleId], [order])

VALUES

(10000, 10000, 1)

INSERT [dbo].[tab_catalog_controls_dirs]

([controlId], [directoryId], [order], [hide])

VALUES

(10000, 2500, 10, 0)

  • No labels