Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Microsoft Reporting Services 2005 Features and Enhancements

By Kamran Ali,

With the latest release of SQL Server 2005 comes the new and revamped Microsoft Reporting Services 2005. In this article I will give an overview of enhancements, which have been included in this new release, particularly those which are related to Report Programming.

Report Development Environment

From this release onwards Microsoft Reporting Services reports are created using the environment named SQL Server Business Intelligence Development Studio, which is a subset of Microsoft Visual Studio 20005. This is installed as a Program group when you install SQL Server 2005 Reporting Services.

Click on this program group and open up the Business Intelligence Development Studio. Click on File-->New and you are presented with the "New Project" dialog box. Here you can select the template which you want to work on. In SQL Server 2005 you can not only design reports using the Business Intelligence Development Studio but also DTS Packages (now called Integration Services), as well as Analysis Services Projects. We will select the "Report Server Project" icon. Name the Project and click OK

Right click the Reports icon in the Solution Explorer and right click the "Add new item..." menu. The "Add new item..." dialog box for is displayed. Select Report and name the report, then click Add.

I will use one of my already prepared report stored procedures for this report. This stored procedure takes in two parameters:

    period_start_date (type datetime)
and
    human_resource_id (type int)
Now lets see what new features have been added to Reporting Services 2005

Multi-Value Parameters

Nothing much has changed since SQL Server 2000 Reporting Services as far as adding a dataset is concerned so I will skip that section and will jump right ahead to the interesting part.
Once the report designer has been loaded, right click in the top left corner of the designer and click "Report Parameters...". The Report Parameters dialog box is shown.
Right away you notice that now you can have Multi-valued parameters. This is a huge improvement from previous versions of Reporting Services where you could not define a parameter as multi-valued

I will set my second parameter ,human_resource_id, as multi-valued and will pick it up from another dataset. Also notice that the period_start_date parameter is set to DateTime datatype. This is nothing new as compared to previous versions since we could always set a parameter's type on this page. However, this will have a very pleasant looking effect once you switch to Preview mode.
Click layout and drag some fields from the dataset. Now click preview and notice how there is a Calendar icon next to the period_start_date parameter field!

Also note how the multi-value functionality has been implemented

Expressions

The Expression Editor has been completely rewritten to better serve the needs of developers. Intellisense has been added to the Expression Editor as well a tree view of all the common functions which can be used while writing report expressions. The functions themselves are nicely categorized to make it easy for the developer to pick the one he/she wants. Also note that Expressions now support a whole range of Operators as well

Selecting an item in the middle pane shows a brief help in the right-hand pane describing what the functionality of the selected item is.Also, note the red wavy line underneath the expression: the expression editor now parses the code on the fly and visually notifies you of code errors.

One thing I missed, though, was the fact that when I typed Code. , I did not see the name of my custom function in the Intellisense list for some reason. It would be great if this functionality is added to the expression editor.

End-User Interactive Sorting

Another goodie which comes with the new version of the Reporting Services is the ability to give your end-users the ability to sort on the report data. This is easily implemented by following the steps:

  • Add a table to your report designer ( End-User sorting can only be added to 'data regions' i.e. report items which display repeated rows of data. Examples are table, list or matrix)
  • Add fields from the dataset to the details section
  • Type in a name for the first column in the table
  • Right click the text box for the header and click "Properties"
  • Click on the "Interactive Sort" Tab
  • Check the "Add an interactive sort action to this textbox" checkbox. The ""Sort Expression" dropdown becomes enabled
  • Select the field you want to sort on when the user clicks the Sort button on the report. Click OK

Now when you preview the report you will see two small arrows next to the column name which you selected for sorting. Click on the arrows and the report data will be sorted

Printing Enhancements

Remember we always wondered how to change the layout of the report from Landscape to Portrait? Earlier one had to go in to Report Properties and flip the Page Width and Page Height with one another on the Layout Tab. You can still do that, however, there is a nice little icon called (you guessed it!)Page Setup in the Preview mode available with this release of Reporting Services

Conclusion

These features may be small additions to the tool but they go a long way in increasing developer productivity. I hope that Microsoft will keep adding more features to Reporting Services in the future. It would be really wonderful if Microsoft can 'port-back' these features to Reporting Service 2000. This will be a huge benefit for all those report developers who realize that certain for-granted features (like Intellisense) are missing from the SQL Server 2000 Reporting Services tool but cannot upgrade to SQL Server 2005 for various reasons.

Happy Reporting!

Total article views: 19651 | Views in the last 30 days: 7
 
Related Articles
FORUM

Splitting A Parameter From Report Services

Splitting A Parameter From Report Services

BLOG

Reporting Services Switch Function

Recently a client requested to create a Reporting Services expression that emulated the CASE WHEN st...

FORUM

Adding Size in Reporting Services Parameter

Adding Size in Reporting Services Parameter

FORUM

MultiValue Parameter Width in SQL 2008/2005 Reporting Service

MultiValue Parameter Width in SQL 2008/2005 Reporting Service

FORUM

Reporting Services 2008 overwrites parameter datasets

Reporting Services 2008 overwrites parameter datasets (MDX) when a query that uses the parameter is ...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones