Click here to Skip to main content
15,867,756 members
Articles / Web Development / ASP.NET

Create a custom report for displaying SSRS report usage details in SharePoint 2013

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
19 Jul 2016CPOL4 min read 25K   1   7
This article describes how to create a usage report for SSRS reports that are hosted in the SharePoint Site.

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

  1. Configure SSRS service Application
  2. Generate Query to retrieve usage data from the database
  3. 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

Image 1

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

  1. Navigate to the SharePoint Server where you configured SSRS database
  2. 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

Image 2

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.

Image 3

  • 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.

Image 4

  • 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

Image 5

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader
India India
I am Senthil Gopal, a SharePoint Lead based out of Chennai, India.
I have around 9+ years of experience in Microsoft technologies such as C#,Asp.net, SharePoint 2010 and 2013.
This is a Social Group

4 members

Comments and Discussions

 
QuestionFormatting Pin
Nelek19-Jul-16 1:27
protectorNelek19-Jul-16 1:27 
AnswerRe: Formatting Pin
senthill19-Jul-16 2:04
professionalsenthill19-Jul-16 2:04 
GeneralRe: Formatting Pin
Vincent Maverick Durano19-Jul-16 2:19
professionalVincent Maverick Durano19-Jul-16 2:19 
GeneralRe: Formatting Pin
Nelek19-Jul-16 2:55
protectorNelek19-Jul-16 2:55 
GeneralRe: Formatting Pin
senthill19-Jul-16 5:02
professionalsenthill19-Jul-16 5:02 
GeneralRe: Formatting Pin
Vincent Maverick Durano19-Jul-16 5:15
professionalVincent Maverick Durano19-Jul-16 5:15 
GeneralRe: Formatting Pin
senthill19-Jul-16 5:24
professionalsenthill19-Jul-16 5:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.