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

   XML

   <%@ 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>
     <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

   C#

   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

The following steps show how to connect to a reporting services server.

1. Add the ReportViewer control to the ascx file.

   Example: adding ReportViewer

   XML

   <%@ 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>
     <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

   C#

   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();
               }
           }
       }

Note: For the server report, you can reuse the same control and just pass the name of the report to be viewed by the report viewer control. In this way, you can add links, and the same control can be used for each link.

Deploying and testing

Do the following to deploy and test the reports:

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

Viewing reports

Local report

Server report

See Reporting options in Episerver Commerce for information about creating custom SQL reports to display data from your Episerver Commerce website.

Last updated: Oct 12, 2015