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!