satish: SSRS,SSIS Design misc..

Convert Seconds to Days Hour Minutes Seconds in SSRS Report

Convert Seconds to Days Hour Minutes Seconds in SSRS Report

Method 1: Go to report property. Open Code section and paste the below code:
Public Function SecondsToText(ByVal intTotalSeconds) As String
    Dim hours As String =INT(intTotalSeconds/3600)
    If Len(hours) < 2 Then
        hours = RIGHT(("0" & hours), 2)
    End If
    Dim mins As String = RIGHT("0" & INT((intTotalSeconds MOD 3600)/60), 2)
    Dim secs AS String = RIGHT("0" & ((intTotalSeconds MOD 3600) MOD 60), 2)

    SecondsToText= hours & ":" & mins & ":" & secs

End Function

Method 2: Go to report property. Open Code section and paste the below code:

Function SecondsToText(Seconds) As String
Dim bAddComma As Boolean
Dim Result As String
Dim sTemp As String
Dim days as String
Dim hours as String
Dim minutes as String

If Seconds <= 0 Or Not IsNumeric(Seconds) Then
     SecondsToText = "0 seconds"
     Exit Function
End If
Seconds = Fix(Seconds)
If Seconds >= 86400 Then
days = Fix(Seconds / 86400)
Else
days = 0
End If
If Seconds - (days * 86400) >= 3600 Then
hours = Fix((Seconds - (days * 86400)) / 3600)
Else
hours = 0
End If
If Seconds - (hours * 3600) - (days * 86400) >= 60 Then
minutes = Fix((Seconds - (hours * 3600) - (days * 86400)) / 60)
Else
minutes = 0
End If
Seconds = Seconds - (minutes * 60) - (hours * 3600) - _
   (days * 86400)
If Seconds > 0 Then Result = Seconds & " second" & AutoS(Seconds)
If minutes > 0 Then
    bAddComma = Result <> ""

    sTemp = minutes & " minute" & AutoS(minutes)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
If hours > 0 Then
    bAddComma = Result <> ""

    sTemp = hours & " hour" & AutoS(hours)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
If days > 0 Then
    bAddComma = Result <> ""
    sTemp = days & " day" & AutoS(days)
    If bAddComma Then sTemp = sTemp & ", "
    Result = sTemp & Result
End If
SecondsToText = Result
End Function

Function AutoS(Number)
    If Number = 1 Then AutoS = "" Else AutoS = "s"
End Function

Then in field paste the below code:

=code.SecondsToText(fields!TimeInSeconds.value)

Method 3:

=DATEADD("s", SUM(Fields!TimeinSeconds.Value), CDate("00:00")).ToString("HH:mm:ss")

Wednesday, July 25, 2012

SSRS Error Code - rsReportServerDatabase Unavailable

When we try to connect to some of the SSRS report server, the foolowing data connection error is displayed. It is because of improper Server and ReportDatabase configuretion. To overcome this perform the below steps:

1. Navigate to Programs --> MS SQL Server XXXX --> Configuration Tools --> Report Service Configuration Manager.

2. Click on Database menu.

3. Check whether SQL Server Name and proper ReportServer database is configured. If not click on Change Database button and configure your server name and ReportServer database. The connection will work now.

Wednesday, June 27, 2012

Call SSRS Report in ASP.NET Web Page

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 a new 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, June 3, 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:

Tuesday, May 29, 2012

Adding Serial Number to SSRS Report via RowNumber() function

Add a new column as 'S.No.' in SSRS report. Open Expression window of the column and enter below text:

=RowNumber("<Datasetname>")

E.g., If my dataset name is "MyData"

then the command will be:
=RowNumber("MyData")

Using Multi-Value Parameter in SSRS 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,'')
                + @Delimiter
   WHILE LEN(@sTemp) > 0
   BEGIN
      INSERT INTO @Table
      SELECT SubString(@sTemp,1,
             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.

Monday, December 19, 2011

SQL Query to get Bank Statement As on Date

DECLARE @Transactions TABLE(

[Date] DateTime,

Remarks VARCHAR(100),

CRAmt DECIMAL(18,3),

DRAmt DECIMAL(18,3),

Balance DECIMAL(18,3),

[BalanceType] CHAR(2))

INSERT INTO @Transactions VALUES(GETDATE()-312,'Remark 1',100, NULL,NULL,NULL)

INSERT INTO @Transactions VALUES(GETDATE()-212,'Remark 2',NULL, 50,NULL,NULL)

INSERT INTO @Transactions VALUES(GETDATE()-12,'Remark 3', 200, NULL,NULL,NULL)

SELECT * FROM @Transactions

DECLARE @DrTotal AS DECIMAL(18,3) = 0

DECLARE @CrTotal AS DECIMAL(18,3) = 0

DECLARE @Balance AS DECIMAL(18,3) = 0

UPDATE @Transactions

SET @DrTotal = @DrTotal + ISNULL(DRAmt,0),

@CrTotal = @CrTotal + ISNULL(CRAmt,0),

@Balance = @Balance + ISNULL(DRAmt,0) - ISNULL(CRAmt,0),

Balance = ABS(@Balance),

[BalanceType] = CASE WHEN @Balance > 0 THEN 'Dr' ELSE 'Cr' END

SELECT [Date],Remarks, CRAmt , DRAmt , Balance , [BalanceType] FROM @Transactions

UNION ALL

SELECT GETDATE() [Date],'Total' Remarks

, @CrTotal CRAmt , @DrTotal DRAmt

, ABS(@Balance) Balance , CASE WHEN @Balance > 0 THEN 'Dr' ELSE 'Cr' END [BalanceType]

Thursday, May 5, 2011

SSRS Report Life Cycle

1. Report authoring: This stage involves creation of reports that are published using the Report Definition language. RDL is an XML based industry standard for defining reports.
2. Report management: This involves managing the published reports as a part of the webservice. The reports are checked for consistency and performance. They can be executed whenever demanded or can be scheduled and executed.
In short Report Management includes:
- Organizing reports and data sources,
- Scheduling report execution and delivery, and
- Tracking reporting history.
3. Report delivery: Reports can be delivered to the consumers either on their demand or based on an event. Then they can view them is a web-based format.
4. Report security: It is important to protect reports as well as the report resources. Therefore, Reporting Services implement a flexible, role-based security model.

Configuring Reporting Services to Use SSIS Package Data

Configuring Reporting Services to Use SSIS Package Data

Reporting Services includes a data processing extension that retrieves data from a SQL Server Integration Services (SSIS) package. The SSIS data processing extension is not supported. This data processing extension is a non-production feature that is off by default. Using the SSIS data processing extension on a production server is not recommended at this time. If you choose to enable the feature and use it on your report server, be aware that at run time the package will be processed under the security identity of the Report Server Windows service account or the Report Server Web service account. This has implications on how you secure the data sources that the package accesses. Specifically, you will need to grant the service accounts permission to read the data and perform any operation that you define in the package. Use this feature only if you take the following precautions:

· Make sure the content of the package is controlled (for example, you can use the password protection features in SSIS or set permissions on the file). For more information, see Setting the Protection Level of Packages.

· Design the package so that it can only perform those operations that are necessary for getting the data you want.

· When configuring the report server service accounts, always use least-privilege user accounts or service accounts.

Before you can use the SSIS data processing extension, you must modify configuration files to enable the extension on your report server and in Report Designer. Follow the instructions in this topic to edit the configuration files.

Enabling the Integration Services Data Processing Extension for Report Designer

To enable the data processing extension, you must remove comments from two sections in the RSReportDesigner.config file.

1. Install Integration Services on the same computer on which you installed Reporting Services.

2. Using Windows Explorer, locate <drive>:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.

3. Using a text editor, open the RSReportDesigner.config file.

4. Remove the comment marks (delete  characters from the beginning and end of the line) for this entry in the <Data> section:

5. In the <Designer> section, remove the comments from this entry:

6. Save the file.

After you modify the configuration file, the SSIS data processing extension becomes available as a data source type that you can select in the Data Source dialog box in Report Designer.

Enabling the Integration Services Data Processing Extension on the Report Server

Before you can process a report that retrieves package data, you must remove comments from RSReportServer.config file.

1. Open the in RSReportServer.config file, located by default at <drive>:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer.

2. Remove the comment marks (delete  characters from the beginning and end of the line) for this entry:

3. Save the file.

After you edit RSReportServer.config, the SSIS data processing extension can be used on the report server. To verify that the data processing extension is available, open a data source property page in either Report Manager or Management Studio and view the list of data processing extensions in Data Source Type. If you modified the configuration file correctly, SSIS appears in the list of data source types.

Difference between SSRS 2005 and SSRS 2008

SSRS 2005 vs SSRS 2008

Wednesday, April 27, 2011

To Provide Alternate Colors to Rows in SSRS Report

To Provide Alternate Colors to Rows in SSRS Report perform th efollowing steps:

Select the row of the table, go to the row properties and in expression enter the following code:

=iif(RowNumber(nothing) mod 2, "silver", "white")

Friday, March 4, 2011

How to display all Labels in X-axis

To display all labels of x-axis, go to Interval property of X-Axis label and give value as '1'.

Tuesday, February 1, 2011

Setting No. of Rows Per page in SSRS

This can be achieved at the group level. If you do not have group, then add one group but uncheck the 'Include Group Header' and 'Include Group Footer'. Also, check 'Page Break at Start'.

But when ask to group by what, use:

=Ceiling(RowNumber(Nothing)/20)

Monday, January 10, 2011

Repeating Row Header in SSRS 2008 Page

1. Perform the following steps to repeat headers in SSRS 2008:

2. Set the prperties as shown in below image:

3. Set Fixed Data as True to view header while scrolling.

Tuesday, November 30, 2010

Change Background color in SSRS - Switch Syntax

Switch Syntax to change color in SSRS report

=Switch(Fields!Performance.Value >= 80, "Green", Fields!Performance.Value >= 60, "Amber", Fields!Performance.Value < 60, "Red")

satish

Tuesday, 25 June 2013

SSRS,SSIS Design misc..