Creating custom reports

This document describes how to add your own custom reports in EPiServer Commerce. The built in reports are done with Microsoft SQL Server Reporting Services using local reports. You can create both local reports as well reports that are stored on a reporting services server. If you use the remote option you will be able to take advantage of the subscription services to have these reports distributed automatically at desired frequency levels.

Classes referred to here are available in the following namespaces:

• Mediachase.Web.Console.Reporting
• Mediachase.Web.Console.Reporting.DataSources

Adding custom reports

Follow the steps below to create a custom report.

1. Create a new Web Application.

2. Remove the default.aspx page and web.config file. You can leave it if you want to test the project outside of Ccommerce Manager.

3. Copy the Apps/Reporting/Config folder and its content into the new project.

4. If you are using the SQL Reporting services, also copy Apps/Reporting/Reports into the new project.

5. Copy the Apps/Reporting/Reporting.config file into the new project (all references hereafter are to files in the new project). The project should look like the image below in the solution explorer.

6. Edit the Config/Naviation/@.xml file to include a new report. Below is a sample adding two parent links New Local Reports and New Server Reports with a report for each one.

Example: adding report links

<Link id="NewLocalReports" text="New Local Reports" iconUrl="~/Apps/Reporting/images/report-group.png" iconCss="treeIcon">
    <Link id="ApplicationLog" text="Application Log" iconUrl="~/Apps/Reporting/images/report.png" iconCss="treeIcon" command="ApplicationLogReportCommand" />
</Link>
<Link id="NewRemoteReports" text="New Server Reports" iconUrl="~/Apps/Reporting/images/report-group.png" iconCss="treeIcon">
    <Link id="ServerReport" text="New Report on Server" iconUrl="~/Apps/Reporting/images/report.png" iconCss="treeIcon" command="NewServerReportCommand" />
</Link>

7. Next, edit the elements under the section Navigation/Commands/Add element. Below is a sample of two commands for the two new report links added. Note that the second command passes a querystring to the ChangeView method. This allows the view to have parameters if needed.

Example: adding report links to navigation

<Command id="ApplicationLogReportCommand">
    <CommandType>ClientAction</CommandType>
    <ClientScript>CSManagementClient.ChangeView('Reporting', 'viewApplicationLog')</ClientScript>
</Command>
<Command id="NewServerReportCommand">
    <CommandType>ClientAction</CommandType>
    <ClientScript>CSManagementClient.ChangeView('Reporting', 'viewNewServerReport', 'Report=testreport')</ClientScript>
</Command>

8. Review the Reporting.config file:

• Add a new view element by copying an existing one.
• Make sure the View ID is the same as the unique view name set in command that changes the view.
• The name attribute will be used as the header title in Commerce Manager when the report is being viewed.
• For the controlUrl, specify the name and path of a new .ascx file to be created. It should be in this form: "Reporting/Your ascx name"

Below is a sample of adding two new views to go with the two new links created earlier.

Example: adding report views to the configuration file

<View id="viewApplicationLog" name="Application Log" controlUrl="Reporting/LocalApplicationLog.ascx"></View>
<View id="viewNewServerReport" name="New Server Report" controlUrl="Reporting/RemoteReportViewer.ascx"></View>

9. Create a new ascx control using the name from the controlUrl in the previous step. Place it in the root of the new project. This ascx will define the appearance and functionality of the report when displayed.

If you are using SQL Reporting services, you can add the ASP.NET ReportViewer control to the ascx. If you use this control, you can add a LocalReport element with a ReportPath specified as: "Apps\Reporting\Reports[Your report name]", or you can point the report viewer control to the server if you have a server instance set up.

Creating a local report

Follow the steps below to create a local report.

1. Add your the.rdlc file of your report to the Reports folder.

2. Add the ReportViewer Control to the ascx file.

Example: adding ReportViewer

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="LocalApplicationLog.ascx.cs" Inherits="ReportingDeveloperGuide.LocalApplicationLog" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<div>
    <h2>Local Report</h2><br />
    <rsweb:ReportViewer SizeToReportContent="True" AsyncRendering="false" ID="MyReportViewer" runat="server" Width="100%" Height="90%"
        HyperlinkTarget="_blank" style="margin-top: 0px">
        <LocalReport EnableHyperlinks="false">
        </LocalReport>
    </rsweb:ReportViewer>
</div>

3. Create the code behind of the control to get the data set needed for the local report, and then add the data source to the control.

Example: getting the report data

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.WebForms;

namespace ReportingDeveloperGuide
{
    public partial class LocalApplicationLog : System.Web.UI.UserControl
    {
        /// <summary>
        /// Handles the Load event of the Page control.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                BindReport();
            }
        }

        /// <summary>
        /// Binds the report.
        /// </summary>
        private void BindReport()
        {
            string qry = @"SELECT TOP 100 [LogId]
                              ,[Source]
                              ,[Operation]
                              ,[ObjectKey]
                              ,[ObjectType]
                              ,[Username]
                              ,[Created]
                              ,[Succeeded]
                              ,[IPAddress]
                              ,[Notes]
                              ,[ApplicationId]
                          FROM [ooi_main].[dbo].[ApplicationLog]
                          ORDER BY Created desc";


            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["EcfSqlConnection"].ConnectionString);
            SqlCommand cmd = new SqlCommand(qry, connection);

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);

            MyReportViewer.LocalReport.DataSources.Clear();
            MyReportViewer.LocalReport.DataSources.Add(new ReportDataSource("Logging_ApplicationLog", ds.Tables[0]));
            MyReportViewer.LocalReport.ReportPath = this.MapPath("Reports\\rpt_ApplicationLog.rdlc");
            MyReportViewer.DataBind();
        }

    }
}

Connecting a reporting services server

Follow the steps below to connect to a reporting services server.

1. Add the ReportViewer control to the ascx file.

Example: adding ReportViewer

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="RemoteReportViewer.ascx.cs" Inherits="ReportingDeveloperGuide.RemoteReportViewer" %>
        <%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
        <div>
            <h2>Server Report</h2><br />
            <rsweb:ReportViewer AsyncRendering="false" ID="MyReportViewer" runat="server" Width="100%" ShowParameterPrompts="true" ProcessingMode="Remote">
            <ServerReport DisplayName="MySavedReport" ReportPath="" ReportServerUrl="" />
            </rsweb:ReportViewer>
        </div>

2. Create the code behind of the control to set the report path and report server URL.

Example: defining report path and server URL

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

    namespace ReportingDeveloperGuide
    {
        public partial class RemoteReportViewer : System.Web.UI.UserControl
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!this.IsPostBack)
                {
                    BindReport();
                }
            }

            private void BindReport()
            {
                this.MyReportViewer.ServerReport.ReportServerCredentials = new ReportViewerCredentials(ConfigurationManager.AppSettings["ReportServerUsername"],
                    ConfigurationManager.AppSettings["ReportServerPassword"], "");
                this.MyReportViewer.ServerReport.ReportServerUrl = new Uri(ConfigurationManager.AppSettings["ReportServerUrl"]);
                this.MyReportViewer.ServerReport.ReportPath = "/Reports/" + Request.QueryString["Report"];
                this.MyReportViewer.ServerReport.Refresh();
            }
        }
    }

Deploying and testing

Do the following to deploy and test the reports:

• Publish the new project into a new folder. Make sure to use the publishing option "Only files needed to run this application".
• Copy the new project dll from the publish folder's bin folder into the ConsoleManager bin folder.
• Copy the remaining files and folders (excluding the bin folder) into the ConsoleManager\Apps\Reporting folder.
• Reset the IIS.
• Reload Commerce Manager and navigate to Reporting.

Viewing reports

Local report

Server report

Refer to the article Reporting options in EPiServer Commerce for more information how you can create custom SQL reports to display data from your EPiServer Commerce website.