Mahadevan Business Intelligence: SSRS

Showing posts with label SSRS. Show all posts

Wednesday, 27 June 2012

Call SSRS Report in ASP .NET with Parameters

The below steps help to call an SSRS report in Web Page designed using ASP.NET. The practice the below code we should install AJAX toolkit.

1. Create anew ASP wep page project.
2. Add a new web form.
3. Add th ebelow scripts in the WEB for designer:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SSRS2.aspx.cs" Inherits="SSRS_WEB.WebForm2" %>
<%@ Register assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="Microsoft.Reporting.WebForms" tagprefix="rsweb" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
       <tr>
           <td>Start Date: </td>
           <td><asp:TextBox Width="180" runat="server" ID="StartDatePr"/></td>
           <td><asp:CalendarExtender runat="server" TargetControlID="StartDatePr"/></td>
           <td>End Date: </td>
           <td><asp:TextBox Width="180" runat="server" ID="EndDatePr"/></td>
           <td><asp:CalendarExtender ID="CalendarExtender1" runat="server" TargetControlID="EndDatePr"/></td>
           <td><asp:Button Text="Show Report" ID="btnSubmit" runat="server" onclick="btnSubmit_Click" /></td>
       </tr>
   </table>
    </div>
    <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <rsweb:ReportViewer ID="MyReportViewer" runat="server" Font-Names="Verdana"
        Font-Size="8pt" InteractiveDeviceInfos="(Collection)" ProcessingMode="Remote"
        WaitMessageFont-Names="Verdana" WaitMessageFont-Size="14pt" Height="800px"
        Width="1000px">
        <ServerReport ReportServerUrl="" />
    </rsweb:ReportViewer>
    </form>
</body>
</html>

4. Add below code in respective .CS file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Reporting.Common;
using Microsoft.Reporting.WebForms;
namespace SSRS_WEB
{
    public partial class WebForm2 : System.Web.UI.Page
    {
       protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnSubmit_Click(object sender, EventArgs e)

        {
            //First
            MyReportViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
            MyReportViewer.ServerReport.ReportServerUrl = new Uri("http://localhost/reportserver_Sathya"); // Report Server URL
            MyReportViewer.ServerReport.ReportPath = "/SQLSSRS/Dashboard"; // Report Name
            MyReportViewer.ShowParameterPrompts = false;
            MyReportViewer.ShowPrintButton = false;
            ReportParameter[] parameters = new ReportParameter[2];
            parameters[0] = new ReportParameter("StartDate", StartDatePr.Text);
            parameters[1] = new ReportParameter("EndDate", EndDatePr.Text);
            MyReportViewer.ServerReport.SetParameters(parameters);
            MyReportViewer.ServerReport.Refresh();

        }
    }
}

On executing the page, you can see SSRS reoport called in Web page.

Sunday, 10 June 2012

Applying Multi-Value Parameter in SSRS Reports via Stored Procedure

It has been found that in SSRS 2005-2012, when we use direct query as data set we are able to apply multi-value parameter and the report works when we select multiple values in parameter.

E.g., Let the direct query be as follows:

SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
WHERE Country in (@Country)

When we use the above query in dataset a parameter Country will be created in Parameter list, and we can configure parameter as shown below, the report work well when we select multiple values in parameter.

When the same query is executed as stored procedure, the report will fail when we select multiple values in parameter. To overcome this issue we need to create the given function in our database:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo]. [MultiParameter]
(
   @DelimittedString [varchar](max),
   @Delimiter [varchar](1)
)
RETURNS @Table Table (Value [varchar](100))
BEGIN
   DECLARE @sTemp [varchar](max)
   SET @sTemp = ISNULL(@DelimittedString,'')
         &nb sp;      + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
         &nb sp;   CharIndex(@Delimiter,@sTemp)-1)

      SET @sTemp = RIGHT (@sTemp,
        LEN(@sTemp)-CharIndex(@Delimiter,@sTemp))
   END
   RETURN
END
GO

And we need to modify the stored procedure as like below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Mahadevan
-- Create date: May 27, 2012
-- Description: To list of Customer by Countrywise
-- =============================================
CREATE PROCEDURE [dbo].[Rpt_Customer]
@Country NVARCHAR (MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
    SELECT CustomerName, AccountNo, SalesAmount FROM dbo.Sales
WHERE Country IN (Select * from [dbo].[MultiParameter] (@Country, ',') )

END
GO

Now configure the report parameter to select multi- values, the report will work well.

Tuesday, 5 June 2012

How to get customized color in SSRS chart

While we design SSRS chart, we have to use available colors from SSRS Color pallette or color templates by selecting them from color, background color, etc. Instead we can apply our own defined colors to our chart by following below steps:

Step 1: Create new SSRS report.

Step 2: Go to Report properties of the SSRS report. Open Code section and copy-paste the below code in the Code area:

Private colorPalette As String() = { "Blue", "Red", "Teal", "Gold", "Green","#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0

Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String

If mapping.ContainsKey(groupingValue) Then

Return mapping(groupingValue)

End If

Dim c As String = colorPalette(count Mod colorPalette.Length)

count = count + 1

mapping.Add(groupingValue, c)

Return c

End Function

Step 3: Now create a chart (e.g., pie chart); Open Series properties, in Fill expression copy-paste below command:

=code.GetColor(Fields!SalesMonth.Value)

Step 4 (Option): You can use this color in your grid report as a background, by copy-paste the below code in Columns Background properties:

=code.GetColor(Fields!SalesMonth.Value)

The output will be simillar to one below: