Custom Reports

Contents

• Setting the Report Properties
• Report – Required fields
• Report – Optional fields
• Setting the Report Preferences
• Table Report
• Required Preferences fields
• Optional Preferences Fields
• Creating the SQL Stored Procedure
• Table Report
• Registering a Report in the CardioLog UI
• Creating a Report Sub-Menu
• Inserting a Report into the CardioLog Database

 

---

 

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

 

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

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>

This field maps custom parameters in Report Preferences to the actual SQL stored procedure parameters.

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

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

Required Preferences fields

<timeframe>

The date period for the report.

<timeframe>[milliseconds]</timeframe>

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>

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 the “Setting the report properties” section – 2.1.

<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:

 

Report Properties:

 

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 Report Center and Analysis Center. This is done by right clicking the Central Area and selecting a report.

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:

 

Note: DirectoryId should be > 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

 

controlId: should be > 9999

prefs: this column holds the report preferences Xml

Table Report Example:

 

Creating the Report Rule

The report rule holds additional properties for the report.

 

RuleId: should be > 9999

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

Prefs: number=same as <reporttype> value in the Report Preferences.

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

Table Report Example:

 

Linking Report-Rule and Directory-Report