Introduction
We are going to generate usage report for SSRS report is deployed in SharePoint site. The source of the report is Reporting Service database which is created while configuring SSRS service application in SharePoint server.
Steps to generate the report
- Configure SSRS service Application
- Generate Query to retrieve usage data from the database
- Displaying the data in Grid view using Asp.net and C#.net code
Overview of the Report
Below diagram depicts how we are going to represent the data on the sharepoint page using grid view control in Asp.net
Step 1: Configure SSRS Service Application
Please refer the following link for details steps to configure the SSRS service application
Configure SSRS Report in SharePoint Server
Step 2: Generate Query to retrieve usage data of SSRS Report
Please follows below steps to generate query
- Navigate to the SharePoint Server where you configured SSRS database
- Open SQL management studio and connect Reporting Service database “ReportingService_SharePoint”, the name of the database might be different for your case, remember the database name which you gave when you configure the service application
In the reporting service database, we used only two tables to get execution log details
1. Catalog table
This table contains metadata of rdl file/report which we uploaded in the SharePoint document library.
- ItemId – guid of the item
- Path – location of the file in document library
- Name – Name of the rdl file which we gave while uploading the file to document library
2. ExecutionLogStorage table
This table contains execution details of each RDL report in the SharePoint site.
- Parameter – The parameter which we passed while accessing the report
- Format – Type of the file ( RPL / HTML .. etc)
- TimeStart – execution start time
- TimeEnd – execution end time
- TimeDataRetrival – retrieval time
- TimeProcessing – processing time
3. Expand View folder to get execution log of SSRS report. By default, there are 3 views are present in the database as shown in below diagram
ExecutionLog3 query
SELECT [InstanceName] ,[ItemPath],[UserName],[ExecutionId],[RequestType] ,[Format],[Parameters],[ItemAction]
,[TimeStart],[TimeEnd],[TimeDataRetrieval],[TimeProcessing],[TimeRendering],[Source],[Status]
,[ByteCount],[RowCount],[AdditionalInfo]
FROM [ReportingService].[dbo].[ExecutionLog3]
Where
InstanceName – application id
ItemPath – RDL file path. The format is /{Guid}/{libraryName}/{filename}
/{5901b745-d657-41cd-9969-7fddc3c7b670}/Display Message/RPT_Display_Message.rdl
UserName – Name of the user who access the report
Custom query to retrieve data from exutionlogview 3
As per our needs, we can customize the query to filter the execution log details. In the below query, I used to retrieve data based on the report name, user name and accessed from and to date.
SELECT [userName],ItemPath,[TimeStart] as Date, [TimeDataRetrieval],[TimeProcessing],[TimeRendering],[Status],[ByteCount],[RowCount]
FROM ExecutionLog3
where ItemPath like '{0}' and CONVERT(DATE,TimeStart) between '{2}' and '{3}' and userName='{1}'order by TimeStart desc", reportName, loginName, fromDate, toDate
Display Data in Grid view
In this section, we are going to see how to represent the execution log details in UI using grid view control.
Form Code
<table width="100%" style="padding: 5px;">
<tr>
<td style="width: 10px">From</td>
<td style="width: 10px">
<spuc:datetimecontrol runat="server" dateonly="true" id="ru_datefrom" />
</td>
<td style="width: 10px">To</td>
<td style="width: 10px">
<spuc:datetimecontrol dateonly="true" runat="server" id="ru_dateto" />
</td>
<td style="width: 25px">
<asp:dropdownlist id="ru_userddl" runat="server"></asp:dropdownlist>
</td>
<td style="width: 25px">
<asp:dropdownlist id="ru_reportddl" runat="server"></asp:dropdownlist>
</td>
<td>
<asp:button id="rubtnapply" runat="server" text="Apply" onclick="rubtnapply_Click" />
</td>
</tr>
<tr>
<td colspan="9">
<asp:datagrid id="ReportUsageGrid" allowsorting="true" width="100%" cssclass="Grid" runat="server" autogeneratecolumns="False" gridlines="Vertical" cellpadding="3"
borderstyle="None" borderwidth="1px" bordercolor="black" onsortcommand="ReportUsageGrid_SortCommand">
<alternatingitemstyle backcolor="Gainsboro"></alternatingitemstyle>
<headerstyle font-bold="True" forecolor="white" backcolor="#00B3E3"></headerstyle>
<columns>
<asp:templatecolumn headertext="Report Name">
<itemtemplate>
<asp:label ID="ReportName" runat="server" Text='<%#GetReportName(DataBinder.Eval(Container.DataItem, "ItemPath").ToString())%>'></asp:label>
</itemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="File Name" sortexpression="ItemPath">
<itemtemplate>
<asp:label ID="ItemPath" runat="server" Text='<%#FormatItemPath(DataBinder.Eval(Container.DataItem, "ItemPath").ToString())%>'></asp:label>
</itemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="User Name" sortexpression="UserName">
<itemtemplate>
<asp:label ID="username" runat="server" Text='<%#FormatUserName(DataBinder.Eval(Container.DataItem, "UserName").ToString())%>'></asp:label>
</itemtemplate>
</asp:templatecolumn>
<asp:templatecolumn headertext="Execution Count" sortexpression="ExecutionCount">
<itemtemplate>
<a onclick="ReportUsageReportDialog('<%#FormatReportParameter(DataBinder.Eval(Container.DataItem, " username").tostring(),databinder.eval(container.dataitem, "ItemPath" ).tostring())%>')" href="javascript:void(0);" id="ExecutionCount"><%#Eval("ExecutionCount") %></a>
</itemtemplate>
</asp:templatecolumn>
</columns>
<pagerstyle horizontalalign="Center" forecolor="white" backcolor="#999999" mode="NumericPages"></pagerstyle>
</asp:datagrid>
</td>
</tr>
</table>
When we click on the execution count link, it will be opened a popup to display details of the report with retrieval time, rendering time and status of the report. I used the following script to open details form in SharePoint modal popup window.
<script type="text/javascript">
function ReportUsageReportDialog(parameter) {
var options = {
url: "/_layouts/15/ReportUsageDetails.aspx?" + parameter,
title: "Report Usage Details",
dialogReturnValueCallback: function (dialogResult) {
if (dialogResult == 1) {
SP.SOD.execute('sp.ui.dialog.js', 'SP.UI.ModalDialog.showModalDialog', AddAlertoptions);
}
},
allowMaximize: false
};
SP.SOD.execute('sp.ui.dialog.js', 'SP.UI.ModalDialog.showModalDialog', options);
}
</script>
Code behind
GetReportName
This method will extract report name from itempath column value
public string GetReportName(string RDLfileName)
{
DataTable reportMaster = (DataTable)ViewState["ReportMaster"];
if (reportMaster == null)
{
LoadReportMaster();
reportMaster = (DataTable)ViewState["ReportMaster"];
}
RDLfileName = RDLfileName.Contains("/") ? RDLfileName.Split('/')[RDLfileName.Split('/').Length - 1].ToString() : RDLfileName;
DataView view = new DataView(reportMaster);
view.RowFilter = "LinkFilename2='" + RDLfileName + "'"; ;
DataTable results = view.ToTable();
if (results.Rows.Count > 0)
{
return results.Rows[0]["Title"] != null ? results.Rows[0]["Title"].ToString().Trim() : string.Empty;
}
else
{
return string.Empty;
}
}
FormatItemPath
This method will form report url using itempath column value
protected string FormatItemPath(string itempath)
{
return itempath.Contains("/") ? itempath.Split('/')[itempath.Split('/').Length - 1].ToString().Trim() : itempath.Trim();
}
BindReportUserNameDDL
This method bind username dropdown list value.
void BindReportUserNameDDL()
{
SPUser user = null;
try
{
record = (DataTable)ViewState["ReportUsageRecords"];
ru_userddl.Items.Clear();
if (!ru_userddl.Items.Contains(new ListItem("All")))
{
ru_userddl.Items.Add(new ListItem("All"));
}
ru_userddl.SelectedValue = "All";
foreach (DataRow row in record.Rows)
{
SPContext.Current.Web.AllowUnsafeUpdates = true;
try
{
user = SPContext.Current.Web.EnsureUser(row["UserName"].ToString());
}
catch()
{ }
if (user != null)
{
SPContext.Current.Web.AllowUnsafeUpdates = false;
if (!ru_userddl.Items.Contains(new ListItem(user.Name, user.LoginName.Contains("|") ? user.LoginName.Split('|')[1] : user.LoginName)))
{
ru_userddl.Items.Add(new ListItem(user.Name, user.LoginName.Contains("|") ? user.LoginName.Split('|')[1] : user.LoginName));
}
}
else
{
if (!ru_userddl.Items.Contains(new ListItem(row["UserName"].ToString(), row["UserName"].ToString())))
{
ru_userddl.Items.Add(new ListItem(row["UserName"].ToString(), row["UserName"].ToString()));
}
}
}
}
catch (Exception ex)
{
throw;
}
}
SPContext.Current.Web.EnsureUser – this will consume more time to get user details by using user name from SharePoint site. If user doesn’t exist, the error message will be captured in the catch block and will continue to pick for another users.
To avoid performance issue, we can create a table UserMaster to store user information such as user key, User Name, Email Id and login name.
The logic would be, when the user accessing the report first time, will check whether the user already in user table. If not exist add user in the table and return user name. In case if the user already exist, return the name of the user by pass the login name id from exution log table.
void LoadUsers(DropDownList ddl)
{
try
{
ddl.Items.Clear();
if (ViewState["UserMaster"] == null)
{
record = new DataTable();
query = string.Format(@"SELECT [UserKey],[UserName],[EmailId],[LoginName]
FROM [dbo].[UserMaster]
where isArchived=0 order by [UserName] asc");
record = objBLL.GetData(query);
ViewState["UserMaster"] = record;
}
else
{
record = (DataTable)ViewState["UserMaster"];
}
ddl.DataTextField = "UserName";
ddl.DataValueField = "LoginName";
ddl.DataSource = record;
ddl.DataBind();
if (!ddl.Items.Contains(new ListItem("All")))
{
ddl.Items.Add(new ListItem("All"));
}
ddl.SelectedValue = "All";
ViewState["UserMaster"] = record;
}
catch (Exception ex)
{
}
}
FormatUserName
This method returns the name of the user name if we pass login name as a parameter.
protected string FormatUserName(string loginName)
{
try
{
if (ViewState["UserMaster"] == null)
{
record = new DataTable();
query = string.Format(@"SELECT [UserKey],[UserName],[EmailId],[LoginName],[GlobalUser]
FROM [dbo].[UserMaster]
where isArchived=0");
record = objBLL.GetData(query);
ViewState["UserMaster"] = record;
}
else
{
record = (DataTable)ViewState["UserMaster"];
}
row = record.Select("loginname='" + loginName.ToLower() + "'");
if ((row != null && row.Length > 0))
{
username = row[0]["UserName"].ToString();
}
}
catch (Exception) { }
return username;
}
Bind Grid
This method bind grid, the parameter should be from date, to date and username and report name.
private void BindReportUsageGrid(DateTime objFromDate, DateTime objToDate, string UserName, string ReportName)
{
try
{
query = string.Format(@"SELECT ItemPath, UserName, COUNT(ItemPath) as ExecutionCount FROM ExecutionLog3
where (ItemPath like '%{0}%') and CONVERT(DATE,TimeStart) between '{1}' and '{2}' and UserName like '%{3}%' group by ItemPath, UserName
order by ExecutionCount desc
", ReportName, objFromDate.ToString("yyyy-MM-dd"), objToDate.ToString("yyyy-MM-dd"), UserName);
dt = SPtable.GetData(query);
ReportUsageGrid.DataSource = dt;
ReportUsageGrid.DataBind();
ViewState["ReportUsageRecords"] = dt;
}
catch (Exception ex)
{
string str = ex.Message;
}
}
Conclusion
I hope, this article help you to create customized usage report for SSRS report which executed in SharePoint site. If you have any queries or comments, please let me know.
History
19th July 2016 : inital version created.
