This post originated from an RSS feed registered with .NET Buzz
by Peter van Ooijen.
Original Post: Calculated fields in SQL reporting services
Feed Title: Peter's Gekko
Feed URL: /error.htm?aspxerrorpath=/blogs/peter.van.ooijen/rss.aspx
Feed Description: My weblog cotains tips tricks and opinions on ASP.NET, tablet PC's and tech in general.
New York, Madrid, London.... SQL reporting services. All respect for the British flegma with which they respond to terror. They just go on with what they were doing. Having said that (what more can you say..?) I would like to add my micro-pennies by sharing some of my recent good experiences building reports with SQL reporting services. Installing did have some small quirks but since then it has been a joy to use. We had a monster of a Quick Report (a report tool which came with earlier versions of Delphi) which was repeated in three flavors and needed fiddling with the database to gather all information desired. The SQL reporting services version is one plain report using some straightforward sub-reports. Especially calculated fields made all desired options simple to implement.
A report is based on one SELECT query. The columns returned are available as fields in the report designer. You can add calculated fields to that list. Their expressions yielding the result are in VBscript where you refer to other fields, report globals, parameters as well as VBscipt functions.
It concatenates two other fields and inserts a string constant. You can also refer to other calculated fields in the expression. A more sophisticated expression looks like this
= LEN(TRIM(Fields!MyCalculatedField.Value))
It takes the value of the first calculated field and applies the Vbscript functions TRIM and LEN on it. Reporting services will make sure the fields are calculated in the right order, the second calculated field will contain the right length.
The core of a typical report is a table. By default the detail rows in there are sorted as they are returned by the report's SQL statement. You can change the sorting on a properties tab of the table. This uses the same expression builder as for building a calculated field. Sort on one of the fields or an expression Also sorting on a calculated field works very well. As the sort order is just a script expression you can even pass in the name of the field to sort on at report render time:
=Fields(Parameters!sort.Value).Value
The report has a couple of calculated fields whose expressions are desired sort orders. One of the parameters to the report is the name of the calculated field to sort on. Originally we had three reports, each with a different ORDER BY in the SQL. Now we have one report where the desired sort order is just a parameter. Thanks to the calculated fields.
It does not stop here. Instead of VBscript you can also let the report load your own assemblies with .NET code and use that. That's a new story. I do hope at a better occasion.
Previous Topic
