We will investigate:
- Use of expressions that allow you to dynamically control nearly every aspect of reporting from control properties to data binding
- Common functions that allow run calculations and manipulate data within the report itself
- Custom code, used to build your own library of functions within the report.
We'll also set up a Matrix control and create a sub-report. Lastly, we will add some flair to the report by adding dynamic drills and sorting.
Getting started
In order to follow my examples, you will need to have SQL Server 2005, SQL Server 2005 Reporting Services, and Visual Studio 2005 correctly installed and configured. If you need some help with this, please refer back to the links in Part 1 of this series. Next, download the code file for this article (see the Code Download link in the speech bubble to the right of the article title). The code file contains a sample Visual Studio project and a SQL Script for creating the ReportDemo database. If you've not done so already, go ahead and create the database using theReportingDemoDatabaseScript.sql script or, alternatively, by restoring the provided ReportingDemo.bak file.
Now start a new Business Intelligence project in Visual Studio 2005 (or using BIDS, which installs with SQL Server 2005). Select Project | Add Existing Item function to add to the project the shared datasource (ReportDb.rds) and the sample report, FirstReportMan.rdl. Next, open up the shared datasource and set the server, database, login and password to point to your copy of the database. We will start with the FirstReportMan.rdl report, so double-click on it in the solution explorer to load it into the designer.
Expressions
An expression is a short statement, written in code, which sets the value of a property. An expression can set the value of a property based on just about any data available in the report: parameters, report global values, fields in a dataset, and built-in functions.
SSRS 2005 has made some drastic improvements in how expressions are created. It now supplies an intuitive expression editor. If you scroll through the Properties window, you will notice that most of the properties of controls on the report can be hard-coded or can be set through expressions. (That's the <expression> option you see everywhere.) You will also see buttons labeled fx in the pop-up Properties dialog. These buttons will access the expression editor.
The expression feature makes SSRS 2005 into an incredibly versatile tool, because it allows most aspects of the report, from the data it shows to how it shows it, to behave dynamically. Rather than using a hard-coded value for a particular property, the SSRS resolves the expression to set the value for the property.
Looking at our report, let's say we want it to highlight all the customers from a particular State. We could use the expression:
=iif(Fields!State.Value = "CA", "Bold", "Normal")
If we apply this expression to the detail row in the table and run the report it will bold the text for each row where the value of the State field is California. That's great if the data from California is all that you are interested in. However, it would be ridiculous to have one report for each state, so let's make this expression even more dynamic by adding a report parameter that will determine which State is highlighted when the report is run.
First, let's add the new parameter to the report. Open the FirstReportMan.rdl report and navigate to the Layout tab. From the Report menu select Report Parameters. When the dialog window opens, click the Add button at the bottom of the screen. Call the new parameter 'HighlightState' and set the available values as follows:
| California | CA |
| Florida | FL |
| Louisiana | LA |
| Illinois | IL |
| Texas | TX |
Now click on OK.
Note:
You'll see that when setting up a list of pre-defined values for a parameter that there is a 'From Query' option. You can use this option by defining a dataset on the data tab of the report designer. For example, if we had a State table in the database we could define a dataset to retrieve the data for the parameter dropdown from that table. Once you choose the 'From Query' option, you select the dataset, the value field (this is the field from the dataset that contains the value passed to the report) and the label field (the field from the dataset that contains the value displayed to the user when they are prompted for parameters).
You'll see that when setting up a list of pre-defined values for a parameter that there is a 'From Query' option. You can use this option by defining a dataset on the data tab of the report designer. For example, if we had a State table in the database we could define a dataset to retrieve the data for the parameter dropdown from that table. Once you choose the 'From Query' option, you select the dataset, the value field (this is the field from the dataset that contains the value passed to the report) and the label field (the field from the dataset that contains the value displayed to the user when they are prompted for parameters).
The next step is to set up the expression. Click on the table control on the report. When the table control is selected, an extra control tab appears at the top and left of the table. Click on the little 'row maker' icon, just to the left of the detail row. This allows you to set properties for the entire row. In the properties window select FontWeight and choose <expression> from the drop-down list to open the expression editor. Typing =iif( should give you Intellisense. You can use the expression builder to create the following line of code:
=iif(Fields!State.Value = Parameters!HighlightState.Value, "Bold", "Normal")
The iif is a flow control statement commonly used in SSRS expressions. Its format is:
iif (boolean statement, true result, false result)
If the boolean statement is true, then the true result is returned, and if not, the false result is returned. In this case, the boolean statement is asking: 'Does the state code equal the state code that was passed to the report? If it does, return the word 'bold'. If it does not, return the word 'normal''. The value returned is then used to set the property for that instance of the row.
Now switch to the Preview tab and select a State to highlight. When the report runs, all the rows with customers from the selected State should be displayed in bold.
Let's navigate back to the expression editor. In the bottom half of the screen there are three columns. The first column lists different categories for building expressions. The last option is Common Functions. Expand theCommon Functions node. Click on the Date & Time category. In the next column, double-click on one of the functions. You will see that it is added to the expression box at the top of the dialog box. Next, simply put the cursor in the expression box after the function. If you then type in the opening left bracket you should instantly see Intellisense for the function you selected.
Let's use the Year function in the report to display only the year in which someone was born, instead of their full birth date. Right-click on the field that displays the date of birth and select Properties. On the General tab, find theValue field and click the fx button to bring up the expression editor. Change expression to read.
=Year(Fields!DateOfBirth.Value)
There we have it. It's a good idea to preview the report now.
Also of note here are the aggregate functions. These functions assist you in working with all of the data in a dataset. For example, let's say your report is an order form with a list of items and their prices. You can use theSum function on the Price field to create a total price on the report. In a different example, if you had a report that listed all the purchase dates and the total amount of each purchase, you could use the AVG function to get the average purchase price for that customer.
Let's go back again to the expression editor. This time, click on Globals in the first column of the expression editor. In the second column, you will see the list of the global variables that are provided to the report.
In this example, we are going to use the global variables to create a page-numbering scheme for our report. First, add a footer to the FirstReportMan.rdl report. You enable this by going to the Layout tab and selecting the Report Footer option from the Report main menu. Now that you have a footer, drag four text boxes into the footer section. Set their font properties to 8 pt, bold. Set the text for each text box as follows:
| Page |
| =Globals!PageNumber |
| of |
| =Globals!TotalPages |
Switch to the Preview tab and run the report. You will now have a footer on each page that looks something like 'Page 1 of 5'.
Some other globals worth noting are:
- ExecutionTime – this tells you how long it took to run the report
- ReportServerUrl – this allows you to verify that you are getting your report from the correct source
- UserID - this shows under which User ID the report was run
You can use these global variables to build some basic diagnostics into your reporting. You could also add these variables to the report footer and use a parameter to hide or show them. That way, even in production, your users can see a tidy, clean report, but you can see the same report with all the extra information on it.
There are about 100 common functions in SSRS 2005, and they can handle most of what you will need to do. Occasionally, however, you will still need to set an expression with more control than the common functions can manage. So, to make SSRS even more flexible, you can write custom VB.NET or C# functions and use them in expressions.
Now we are going to add a custom function to the FirstReportMan.rdl report.
Open the report and navigate to the Layout tab. From the Report menu select Report Properties and then jump to the Code tab. We are going to write a custom function that returns a different color, depending on the value that is passed to it. We will then use that function to set the background color for the status field in the report detail table.
Copy the following code into the code window:
Public Function GetColor(ByVal status as String) as String
IF status = "100" Then
Return "White"
End IF
IF status = "101" Then
Return "Yellow"
End IF
IF status = "102" Then
Return "Tomato"
End IF
End Function
Click OK and close the window.
Note:The full list of available colors is found in the Color dropdown list in the properties window.
Now that we have a function that returns color names we need to wire up that function to an expression. Click on the Customer Status cell and open the Properties window. Find the Background Color property and chooseExpression from the dropdown. Add the following line of code to create an expression:
=code.GetColor(Fields!CustomerStatus.Value)
When the report runs and this expression is resolved, SSRS will call your GetColor function and pass the value of the customer status for that instance of the row to the function. The function takes over and decides which color should be returned. SSRS then uses that value as the value for the background property for that cell.
Please note that custom functions must be called using =code.<myfunction>.
Now navigate to the Preview tab and run the report..
A subreport is a report that is embedded into another report. Subreports can take parameters and execute their own datasets. A key aspect to note is that a subreport in SSRS is actually just another report (unlike some reporting tools, where a subreport is a special construct). In fact, in SSRS you can execute a subreport on its own.
To add a subreport to a report, you simply drag a subreport control onto the report and tell it which report to display. If the subreport requires parameters, you have to tell the main report which value to pass to the subreport. It's actually very simple.
Let's add a new report to the project and call it MainReport.rdl. Create a new dataset using the shared datasource and the query:
SELECT CustomerID, FirstName, LastName FROM Customer
Switch to the Layout tab. Drag a table on the report detail area. Set up the first column to display the customer's first name (by dragging that column from the Datasets tab into the Detail row) and set up the second column to display the customer's last name. Label the third column 'Address'. Preview the report, just to be sure it works.
Create another report, and call this one MySubReport.rdl. This time, create a dataset that uses the shared data source, and use the following query text:
SELECT Address, City, State, ZipCode
FROM Customer
WHERE (CustomerID = @CustomerID)
In the Layout tab, use text boxes to create the address layout, as shown in figure 5. You can simply drag the text boxes onto the screen by clicking on the field in datasets tab and dragging it onto design surface. You will also note that when you do this, the expression used to set the value property for the textbox uses the First() function. This function will use the value from the first row returned by the dataset. All other rows returned are ignored.
Now preview the report and use '100' for the CustomerID parameter.
Let's jump back to the MainReport.rdl. To embed the subreport, drag a SubReport control into the detail cell for the column you labeled 'Address'. Right-click on the SubReport control and select Properties. In the Properties dialog choose MySubReport from the subreport dropdown.
Next, switch to the Parameters tab. This is where you connect your subreport to the main report. You do this by indicating which value from the main report is to be passed to the subreport to fulfill its parameter requirements.
In the Parameter Name column choose CustomerID and in the Parameter Value column choose=Fields!CustomerID.Value. This will wire up the subreport to whichever customer is being displayed in the row of the table.
Click OK to close the dialog, and then preview the main report.
It's this question that drives us, Neo. What is the Matrix? OK, I'll stop right here. You can insert your own slam to the second two movies. However, there is a really cool control in SSRS called the Matrix control. The Matrix is used to create pivot table style reports. We are going to set up a simple Matrix control to show us which customers, in which State, have which status.
First, start a new report called MatrixReport.rdl. Add our standard dataset where you use the shared data source, and then add the Select * from Customer query.
Navigate to the Layout tab and drag a new Matrix control onto the page. Where it says Rows, drag the State field from the dataset. Where it says Columns, drag the CustomerStatus field from the dataset. Drag first name and last name into the data area on the Matrix control. After a little formatting, it should resemble this:
When the report runs, it looks like this:
The drill down functionality in SSRS allows you to have areas of your report that can expand and collapse, much like a tree view.
First, start a new report called AdvancedTable.rdl. Add our standard dataset, where you use the shared data source and the Select * from Customer query.
Navigate to the Layout tab and drag a new table control onto the page. The first thing we need to do is to add a group to the table. Click the table to highlight it, then right-click on the little box with three horizontal lines in it that appear at the left side of the table at the beginning of detail row. Select Insert Group. When the Group Dialog appears use =Fields!State.Value as the expression, so that the report now groups on State. Now drag the State field into the first column on the Group row. Next, drag the First Name, Last Name and City fields into the detail row. Add some formatting, and preview the report.
We've now performed a grouping, but we still have to enable the drilldown. Click on the table to highlight it. Right-click on the nub that appears, just to the left of the detail row. Select Properties. When the Properties window opens, expand the visibility section. Set the Hidden property to True and set the ToggleItem property to State. TheHidden property determines the state of the row when the report is first run. If we set it to True then the data is collapsed and hidden. By setting the ToggleItem property to Statee, when the report is run a little + sign appears next to it when the report is run and it works like a tree view.
Switch to the Preview tab and run the report. Now only the States appear, but you can see the customer in each State by using the tree-style +/- controls.
It is incredibly easy to add sorting to your table. Let's re-open the FirstReportMan.rdl report. Navigate to the Layouttab of the report designer. Click in the cell labeled First Name in the header of the table. Right-click and selectProperties. Switch to the Interactive Sort tab. Check the checkbox for 'Add an interactive sort action to this textbox'. Next, set the Sort expression to the value that is displayed in the column – in this case=Fields!FirstName.Value. Now repeat this process for the rest of the report.
Preview the report, and you should have interactive sorting on each column.
0 comments:
Post a Comment
Your comments: