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:
human_resource_id (type int)
Now lets see what new features have been added to Reporting Services 2005
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
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
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
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.