Few of my colleagues and friends have been asking me for a while to write something on the differences between SQL Server Reporting Services versions 2005 and 2008. Especially when they were preparing for interviews / attending interviews, this was one of the most common questions they were asked by the interviewers. However when they browsed on the net, they could not get a comprehensive list of differences between SSRS 2005 and SSRS 2008 not even in a single place. Hence I came up with this idea of presenting some of the major differences between SSRS 2005 & 2008 so that it is helpful for the people who are curious to know about the differences between SSRS 2005 & 2008 and also for those people who are preparing for an interview on SQL Server Reporting Services.

Below table shows few of the differences between 2005 and 2008 versions of SQL Server Reporting Services in the order of priority (major to minor differences from top to bottom).

Comparison of SSRS 2005 and SSRS 2008

SQL Server 2005 Reporting Services	SQL Server 2008 Reporting Services
Multiple service architecture (IIS Service & Windows Service)	Single service architecture (Windows Service)
Depends on IIS	Comes with Built-In Web Server and hence does not depend on IIS
Complex deployment due to the need for separate configuration for IIS	Deployment is simple due to inbuilt web server and Reporting Services Configuration Manager
Charts, Dials etc. had to be separately installed and integrated	Comes packaged with Charts, Dials, Indicators, Sparklines etc.
Limited exporting options	More exporting options including Microsoft Word etc
You can only format entire text in a textbox with one single formatting style (bold, italicize, colors etc.)	Allows you to format pieces of text or words or letters within a single textbox with different formatting styles like bold, italicize, colors etc. Example: a Disclaimer Textbox, Header Textbox etc.

Apart from this there are quite a few other differences or improvements in SQL Server 2008 Reporting Services. For more details, please see the Product Information Page for SQL Server 2008 Reporting Services.

This post is specially for Suneel who had asked for this quite long time back :-)

If there are any other major differences which are worth mentioning then do leave a comment, so that the post can be updated and other readers can benefit from it.

As always for any comments / suggestions / opinions, please leave a comment below.

Related Articles:

• Features of SQL Server Reporting Services (SSRS)
• Shortcuts for SQL Server Client Applications
• Controlling Exporting Options in Reporting Services (SSRS)
• How to Display Report Execution Time in Reporting Services (SSRS) 
• Other SSRS Related Articles on MyTechnoBook

Often in Reporting Applications, faster rendering of reports is very essential. Especially in case of Operational Reporting Systems with large number of concurrent users, Reporting Rendering SLA is defined during the requirement phase of the project and is a very critical component of the Requirements / Reporting Application.

In these kind of solutions its good to display the total time a report takes for rendering from the moment the View Report button is clicked in the report manager. This feature can be used as a means of communicating the exact report rendering time to the users and also to identify the reports / scenarios which are exceeding / missing the rendering SLA.

Now let us see how to display the execution time in an SSRS report.

Since SSRS uses functions based on .Net framework we can use the following expression to get the report rendering time in seconds:

System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds

Similary to display the time in terms of minutes and hours we can just replace Seconds in the above expression with Minutes and Hours respectively.

Though execution time in terms of Hours is rarely / never used.

To make the display more user friendly or more presentable we can use the following expression:

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"

Here is a sample report displaying the execution time in terms of Hours, Minutes and Seconds as shown below.

As shown in the above screenshot the report takes 1 Min 3 Seconds or 63 Seconds to render.

Now let us verify how accurate this number is by querying the SSRS report tables which hold the history / log of a report execution.

I ran the following query in the ReportServer database: 

SELECT TOP 1 ReportID, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing 

, TimeRendering, TimeDataRetrieval + TimeProcessing + TimeRendering AS TotalRenderingTime
FROM dbo.ExecutionLog
WHERE ReportID = '90ABF38F-E280-4B75-B807-343AFB5FE696'
ORDER BY TimeStart DESC

And here are the results of the above query:

From the above screenshot we can see that the total time is 63172 milliseconds or ~63 Seconds or 1 Min & 3 Seconds, which is same as the Execution Time displayed in the previous screenshot.

Please let me know your comments / opinions about this article by leaving a comment below.

Note: This demonstration is tested in SSRS 2008. The implementation should be almost the same for SSRS 2005 as well.

Today I was working on an SSRS report for one of our clients and came across a specific formatting requirement around formatting Minutes as HH:MM. Browsed a little bit but could not get it working. Then sat for a while & tried few tricks by playing around with the expressions and finally got it working :-)

Here is how I solved the problem.

The data available in the database is numeric and represents Total Minutes. When I initially wrote some expressions to derive Hours and Minutes from the Total Minutes I was able to get the data something like this:

10 Minutes -> 0:10

65 Minutes -> 1:5 etc

However the need was to display the data something like below:

10 Minutes -> 00:10

65 Minutes -> 01:05 etc

Now to get this formatting, first split the Minutes into Hours & Remaining Minutes as follows:

Hours part of TotalMinutes = Floor(Fields!Minutes.Value/60)

Remaining Minutes part of TotalMinutes = Fields!Minutes.Value Mod 60

Now to display the data in the intended format put the following expression in the textbox of the detailed row of the Table/Matrix of SSRS:

=Format(Floor(Fields!Minutes.Value/60),"00") + ":" + Format((Fields!Minutes.Value Mod 60),"00")

Below is a sample report with formatted minutes.

 

Hope you will find this useful. If yes feel free to leave a comment below.

Note:  This article/demonstration is based on SSRS 2008. The options should be almost the same in SSRS 2005 as well.

Reports are one of the most important components in any BI projects. SQL Server Reporting Services (SSRS) is one of the various reporting tools available in the market. SSRS comes bundled with SQL Server and is free when you buy SQL Server License.

One of the most common need in any reporting tools is to be able to export/save the report in a different format. Reporting Services offers various exporting options like Excel, PDF etc. Often times few reports are suitable for exporting to a particular format and does not fit perfectly in other formats. Example: A huge report with some 50 fields is most suitable to be exported to Excel and will not fit into other formats like PDF/Word etc.

Following are the formats supported by SQL Server 2008 Reporting Services:

• XML file with report data
• CSV (comma delimited)
• Acrobat (PDF) file
• MHTML (web archive)
• Excel
• TIFF file
• Word

Often business users like formats like Excel, PDF etc and do not want the other exporting formats to be available/displayed in the pull down menu for choosing an exporting format.

We can control the list of exporting options available for the users using the "rsreportserver.config" file. Here is how we do it.

Let us say we need to remove the "Word" option from the dropdown for exporting option.

Initially all the exporting options would be available in the Report Manager as shown below.

 

Now go to the SQL Server/SSRS Installation directory and locate "rsreportserver.config" file. In my case it is located in the path: "D:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer".

Open the "rsreportserver.config" file with notepad and locate "<render></render>" tag. This tag contains sub tags called as "<extension></extension>" for various exporting options. The "<extension></extension>" tag for exporting to Word for example is as follows:

<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/> 

Now comment this statement/tag as follows:

<!-- <Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/> -->

Now refresh the Report in the Report Manager/Server using the browser refresh button and see that the option for exporting to Word disappears from the list as shown below.

 

Now the business can be relaxed to see that they only have limited set of exporting options which they are interested in. This will make the presentation better and the users happy :-)

Note: This demo is based on SQL Server 2008 Reporting Services. In other versions of SQL Server the options might slightly vary.

SQL Server Reporting Services (SSRS) is a great BI tool offering lot of powerful features. SSRS 2008 has lot more features and is much more powerful than SSRS 2005. Irrespective of whether its SSRS 2005 or SSRS 2008, there are many features common between the two versions. Functions are one such powerful options/capabilities in SSRS.

In this article I will present detailed steps for the use of Functions in SSRS.

Often in reporting/BI projects using SSRS we use lot of calculations/expressions in many fields in the reports. There are scenarios where in same formula/calculations is used across many fields in the report. Functions come in handy especially in this kind of scenarios.

Let us take a look at this sample report with simple calculations for finding Sum, Difference, Product & Percentage.

Create an SSRS report with the following query in the dataset:

SELECT 1 AS ColumnA, 2 AS ColumnB
UNION ALL
SELECT 3 AS ColumnA, 4 AS ColumnB
UNION ALL
SELECT 5 AS ColumnA, 6 AS ColumnB
UNION ALL
SELECT 7 AS ColumnA, 8 AS ColumnB
UNION ALL
SELECT 9 AS ColumnA, 10 AS ColumnB
UNION ALL
SELECT 11 AS ColumnA, 12 AS ColumnB
UNION ALL
SELECT 13 AS ColumnA, 14 AS ColumnB
UNION ALL
SELECT 15 AS ColumnA, 16 AS ColumnB
UNION ALL
SELECT 17 AS ColumnA, 18 AS ColumnB
UNION ALL
SELECT 19 AS ColumnA, 20 AS ColumnB

Now lets drop a Table object on to the report designer with the following six fields as shown in the below screenshot.

• Column A
• Column B
• Sum of A & B
• Difference of A & B
• A Multiplied by B
• A as a % of B
  

Now go to "Report > Report Properties". Report Properties dialog box will open and in this window click on "Code" in the left pane. Enter the following code with four different functions for calculating Addition, Difference, Product & Percentage in the Code window as shown in the below screenshot.

' Function for Addition
Function GetSum(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarSum AS Integer 
VarSum = A + B
Return VarSum
End Function

' Function for Difference
Function GetDiff(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarDiff AS Integer 
IF (A>=B) THEN
VarDiff = A - B
ELSE
VarDiff = B - A
END IF
Return VarDiff
End Function

' Function for Multiplication/Product
Function GetProduct(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarProduct AS Integer 
VarProduct = A * B
Return VarProduct
End Function

' Function for Percentage
Function GetPercent(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarPercent AS Integer 
VarPercent = (A/B)*100
Return VarPercent
End Function

Enter the following expressions in the detailed row of the report for the six fields as shown in the below screenshot.

Expr1: "=Fields!ColumnA.Value"
Expr2: "=Fields!ColumnB.Value"
Expr3: "=Code.GetSum(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr4: "=Code.GetDiff(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr5: "=Code.GetProduct(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr6: "=CStr(Code.GetPercent(Fields!ColumnA.Value,Fields!ColumnB.Value)) + " %"" 

Now go to the Preview tab and check out the results as shown in the below screenshot.

And the results are amazing. Having functions for scenarios where the same calculation is used in many places in a report is really helpful and makes the code more modular & neat.

Note: This has been tested & presented as per SSRS 2008.

If you find this post helpful then feel free to leave a comment below :-)

SQL Reporting Services (SSRS) is a great tool offering lots of features and ability to customize the report appearance apart from addressing complex business reporting needs. Above all SSRS is one of the components which comes free when you buy SQL Server :-)

Often in Reporting and Dashboarding projects customization/look and feel becomes one of the important aspects of the project. SQL Server Reporting Services offers various options to customize the look and feel of the report to make the report more Jazzy and Visually Appealing.

One of the most powerful components of SSRS is the ability to control various elements/properties of a report with the use of expressions. One of the properties which is usually set in tabular reports especially in case of really long reports with hundreds or thousands of rows is to set different colors to alternate rows.

To set different color (two different colors) to alternate rows, follow these steps:

• Select the Data Row of the table in the report
• Go to Properties Window
• Under the BackgroundColor property, select expression from the drop down
• Enter the expression "=IIf(RowNumber(Nothing) Mod 2 = 0, "Color1", "Color2")" in the Expression Window.
• In the above expression replace Color1 & Color2 with either the Color Code/Color Name as appropriate.

Below is a snapshot of a sample SSRS report with two different colors for alternate rows.

 















This customization is very useful in case of long running reports with hundreds/thousands of records having many columns where in tracking the data in one single row accross all the fields becomes a little difficult/confusing if we dont use seperate colors for alternate rows.

I was working on one of the requirements for a client where in the application owned by the client had a set of values in report column. The requirement was to make each of the values in that column a URL, on clicking this URL the value gets passed as a parameter to another report and the report is rendered without having to manually select/enter the parameter.

I had implemented a similar requirement in SSRS 2005 for one of the previous clients. In this current requirement which I am talking about the implementation was in SSRS 2008, which from this requirement standpoint does not make any difference.

I knew that we need to concatenate the value of the parameter to the URL of the report and this will do the job. So I took the report URL from the Report Manager and tried to concatenate the parameter but ended up in failure.

To resolve this I tried searching the web and found one article (http://www.mssqltips.com/tip.asp?tip=1336) which explained about various options/features which can be implemented through the report URL in SSRS.

While going through this post I found one very interesting thing and that is we need to concatenate the parameter value to the Report Server URL of the Report and not the Report Manager URL of the Report.

Many times we end up implementing the things in a straight forward fashion and hence dont come accross this kind of peculiar situations. However sometimes when we try doing the same thing differently, we end up meeting with this kind of peculiar findings which are worth treasuring in ones knowledge base.

Trying to implement something in different ways is worth it since we either end up finding an alternate/better/worst option which can be used to justify the best among the solutions identified or end up finding this kind of scenarios which have only one way to implement.

Happy Reporting :-)

We often find ourself searching the web for options to configure/customize the Connection Strings. I have found a very interesting and useful website which lists/explains various options for configuring/customizing the Connection Strings for various Data Sources.

No need to worry about which source database you are dealing with, almost all the popular data sources have been listed on this site. You name the data source and you will find it on this site. SQL Server, Oracle, DB2, Excel, Access, SharePoint and many more.

Snapshot of sample OLE DB Connection String for AdventureWorks database:

Here is the site which I am talking about:
http://www.connectionstrings.com/

Hope most of you will find this site useful.

Microsoft SQL Server (2005, 2008) is one of the most widely used platforms in most of the organizations. It is one of the most powerful and cost effective tools available in the market. It comes packaged with an ETL, Reporting & Analysis (cubes) tools. SQL Server Integration Services (SSIS) is the ETL component, SQL Server Reporting Services (SSRS) is the Reporting Component & SQL Server Analysis Services (SSAS) is the analysis (cubes) component of SQL Server.

This article explains some of the major features/advantages of SQL Server Reporting Services Reports. Apart from the various other features, the following are the major features of SQL Server Reporting Services:

1. Supports heterogeneous data sources

Different types of data sources can be used to build reports in Reporting Services. The data sources include Oracle, SQL Server, DB2, any OLEDB based source, ODBC, Flat file, excel and many more different data sources.

2. Supports ad hoc reporting

Reporting Services comes with another major component called "Report Builder". This component helps the business users build ad hoc reports as per their requirement during their analysis. These reports need a predefined model which can be designed in such a way that it can cater to almost any kind of requirement raised by the business users by including appropriate measures, dimensions and any other required data. This is a very effective tool since most of the business users are more comfortable with excel pivot table reports having drag and drop functionality, Report Builder also provides similar feature.

3. Seamless integration with SharePoint

SharePoint being one of the most powerful tool which can be used to share documents, sites etc and comes with a bunch of very effective features is used in almost every organization. Reporting Services Reports can be easily integrated with SharePoint (WSS/ MOSS).

4. Ease of integration with PerformancePoint

PerformancePoint Server is emerging as a very powerful performance management tool. PerformancePoint Server can be used to Monitor, Analyze and Plan the business. Reporting Services Reports can be easily integrated with PerformancePoint Dashboards.

5. Supports various popular exporting options

Many business users are comfortable using reports in various popular formats like Excel, PDF etc. Reporting Services offers various exporting options like Excel, PDF etc.

6. Scheduled & Emailing of Reports

Reporting Services supports various scheduling and emailing features. Reports can be emailed to the business users on a scheduled basis and business users can easily it over the web. Reports can be scheduled to sent to a printer & printed automatically.

7. Inbuilt User Interface – Report Manager

Reporting Services comes with an attractive inbuilt user interface which can be used to perform various operations apart from browsing and viewing reports. Various administration activities like managing permissions, scheduling etc can be performed using Report Manager.

8. Easily customizable using .NET Code

Reporting Services reports can be easily customized using .NET code to make them in sync with the existing website/portal.

9. Various security features available

Various security options are available in Reporting Services. These security options can be managed using Report Manager.

10. Maintenance of Reports is simpler

Maintenance of reports is simpler and can be performed using the Report Manager.