Initial knowledge of SQL Server 2005 Reporting Services Part 2

Original: [translation] initial knowledge of SQL Server 2005 Reporting Services Part 2

in the Part 1 article we have a preliminary understanding of SQL Server Reporting Services 2005(SSRS). We built two reports, respectively, through the Report Wizard and Report Designer. In Part 2 we will drill down into more feature information as an SSRS developer.

Here's what we're going to look at:

L the use of an expression. These expressions allow you to dynamically control all aspects of a report from control properties to data binding.

L common functions for calculations and operations used in reports.

L custom code in the report.

We also create a matrix control that creates a subreport. Finally, we'll see how the report is drilled and sorted dynamically.

Start

in order to run the sample, you need to install and configure the SQL Server 2005, SQL Server 2005 Reporting Services, and Visual Studio 2005, if you are not sure how to do so, you can go back to Part 1 go and learn. Then download the source code file. These files contain the Visual Studio Project and the SQL script that created the Reportdemo database . If you have all of these, you can create a database directly.

now create a business intelligence project first. Select Add New item in the project to add the shared data source reportdb.rds and report file firstreportman.rdl to the project. Then change the relevant configuration information in the data source to ensure that you can connect to your database. We start with firstreport.rdl, so we'll open it in the designer first.

An expression

An expression is a short statement written in code to set the value of a property. An expression can set any data property that is used in a report: parameters, global variables, words in datasets, and embedded functions.

SSRS2005 has made great strides in creating expressions. Now provides a very intuitive expression editor. If you scroll through the Properties window, you will find that the properties of most controls can be set by an expression (the expression options are everywhere). You can also see a lot of buttons with FX written in the Pop-up Properties dialog box . These buttons allow you to open the Expression Editor.

Such an expression feature will SSRS2005 has become a very useful tool. This makes it possible to dynamically set the data from presentation to presentation. Rather than just writing dead in there. SSRS Sets the properties with an expression.

Take a look at your report and now we want to highlight the specific the customer information from state. We can design an expression like this:=iff (fields! State.value = "CA", "Bold", "Normal")

If we apply this expression to the table's detail columns, when we run the report, all the the columns of the California are highlighted. If you need this data, it will be convenient. However, if you need a report for each highlighted column, that's ridiculous. So we have to make dynamic settings. When the report runs, it highlights different content depending on the parameters passed in.

First, add a parameter first. Named hightlightstate, set the following values:

California	Ca
Florida	FL
Louisiana	LA
Illinois	IL
Texas	TX

Click OK.

Notes:

When you see a pre-assignment to a parameter, there is a query option. You can use this option by defining a dataset in Report Designer.

The next step is to create an expression. Click the table control in the report to see a column and a row of tables on the left and top of the table, and click the front icon to set the property for the positive row. Click An expression in fontweight in properties, enter =iif (fields! State.value = parameters! Highlightstate.value, "Bold", "Normal")

The function iif is an expression that is often used in SSRS. The format is:

IIF (Boolean statement, true result, false result)

If the Boolean expression returns true then the function returns ture result, and if the Boolean expression returns false, the function returns false result. The Boolean expression asks: is the parameter passed in now equal to the current state code ? If true then the text will appear bold or normal.

Go to the Preview tab to see the effect.

Common Functions

Now let's go back to the expression editor. There are three columns in the lower half of the editor. The first column shows the list of categories used to create the expression, and the last is the common function, which expands the node to select the time and date category. Double-click any element in the second column so that it is added to the Edit text box, and if you place the cursor over the first bracket, you will see the IntelliSense information:

now let's use The year function shows only the age of a person's birth. Select the properties of the dateOfBirth column, and on the General tab, in the value information, click the Expression button to enter the expression editor. To change an expression to:

=year (fields! Dateofbirth.value)

That's all right, now preview it.

It is also important to note that there are aggregation functions. These functions can help you work with data in a dataset. For example, your report is about the price of some goods, so you can create a total price with the Sum function. For example, if you have a report on the purchase date and purchase quantity for each transaction, you can get the average price through the AVG function.

Global Variables

Return to the expression editor again. This time, let's click Global in the first column, and the second column shows the global variables that are available for use in the report.

This time let's use global variables to make a report that counts pages. First, add a footer to the report, drag four text boxes into the footer, set the Font property to 8pt

=globals! TotalPages

TD width= "199" valign= "Top" style= "WIDTH:149.25PT; padding:0cm; " >

page

of

Preview the report to see "' Page 1 of 5 '. "Such a message.

There are also some notable global parameters:

L excutiontime– This will show the time required to run the report

L reporturl– This ensures that your reports come from the right data source

L userid– The User ID that displays the report run

You can use these global parameters to perform basic diagnostic operations on a report. You can add these parameters to the footer and display or hide them using parameters. This allows the report user to see a crisp report after publishing, and you can see more information.

Custom Functions

SSRS2005 has more than one function in it, which is enough to handle most situations, but sometimes you need to be more flexible in your control. So here you can write your own custom expressions in vb.net or C # .

Now let's add one in.

Open the Layout tab of Report Designer, select Report Properties from the Report menu, and jump directly to the Code tab. We will write a function that returns different colors depending on the data passed in. This function is then used in the table details.

Copy the following code in:

Public Function GetColor (ByVal status as String) as String

IF status = "All" 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 to close the dialog box.

Notes:

all the colors are in the attribute. Color drop -down list.

We now have a function that is now packaged into an expression. Right-click the Customer Status column to open the Properties window. Locate the Background Color property, and select the expression from the drop-down list. Add the following code to create an expression:

=code. GetColor (fields! Customerstatus.value)

when the report runs, the function is processed and the parameter information is passed in. The function determines which color to display according to the parameters. SSRS can determine which color to assign to the corresponding cell.

Note that in order to use this function we need to write:=code.<myfunction>.

Preview the report.

Sub-report

A subreport is a report that is embedded in another report. Subreports can use parameters and use their own datasets. It is worth noting that the SSRS Neutron report is actually another report. In fact, in SSRS , you can run a subreport separately.

To create a subreport, you simply drag a subreport control from the Toolbox into the report and set it in which report to render. If the subreport needs parameters, you have to tell the main report to pass that parameter to the subreport. It's actually very simple.

Now add a report to a project Mainreport.rdl, and then create a dataset that uses a shared data source. Use the following query:

SELECT CustomerID, FirstName, LastName from Customer

go to the Layout tab and drag a table control in. Select the first column to add the customer's last name, and the second column is the name. The name of the third column is set to Address. Preview the report to determine that it can run.

Create a sub-report again Mysubreport.rdl. Also create a dataset that uses a shared data source. Use the following query statement:

SELECT Address, City, state, ZipCode

From Customer

WHERE (CustomerID = @CustomerID)

go to the Layout tab and use the text box control to display address information. You can automatically create a TextBox control by simply dragging the dataset field in. You can also notice that when you do this, the data in the text box automatically uses the first () function. This function will use the first row of data returned by the dataset, and the others will be ignored.

Now run the report test with the book "".

now go back to MAINREPORT.RDL the main report. To embed an operation, drag a subreport control into the Address column. Right-click the subreport Selection property to set the subreport to Mysubreport.

Next go to the Parameters tab, which is where you connect the subreport to the main report. Here you set which parameter to pass the corresponding parameter to the subreport.

in the parameter name, select CustomerID, select =fields! in the parameter value Customerid.value, which allows thesubreport to know which information to display for that customer.

Click OK to preview the main report.

Matrix

Below we will use matrix controls to create a matrix-style report.

First, add a MATRIXREPORT.RDL report. Add a DataSet with a shared data source, and the query statement is Select * from Customer.

Navigate to the Report Designer Layout tab, drag a matrix control in, and drag, the Customerstatus will be drag in the column to FirstName and LastName drag into the data area. After a little beautification, it should be the following effect:

Run like this:

Add drillthrough

drillthrough in SSRS allows your report to expand and contract like a tree.

First, create a ADVANCEDTABLE.RDL report. To add a dataset with a shared data source, the query statement is a Select * from Customer query.

go to the Layout tab and drag a table control in. First we have to add a grouping to the table control. Select the table control, right-click the small front icon, and choose Insert Group. The Grouping and Sorting Properties dialog box will pop up and the expression in group mode is selected =fields! State.value, so that the report is grouped in state . Now Drag the State field to the first Column group column. Then drag FirstName,LastName and City into the detail area. Beautify it for a preview.

we have now implemented the grouping, but we are not able to drill. Select the table control, select the small icon in front of the detail column, and right-click to select Properties. Expand the Visibility section, set the Hidden property to True, and set the ToggleItem property to State. Hidden determines the state of this column when the report is run. If set to True then the start is indented and hidden. Set the ToggleItem property to stateso that when the report runs, it will have a small "+" number that can be expanded to the tree structure.

Preview report found only The states column is displayed, but it can be expanded and shrunk.

Add Dynamic Sort

It's actually easy to sort the table controls dynamically. Open the FIRSTREPORTMAN.RDL report first. Go to the Layout tab of Report Designer. Right-click the FirstName text box in the header to select Properties. Go to the Interactive Sort tab. Select the Add an interactive sort operation to the Word text box check box, and then set the sort expression to =fields! Firstname.value, the sort expression is the value that is displayed in this column. Similar settings are also made for other columns.

Now you can preview the report to make an interactive sort of each column.

Summary

I hope these SQL Server Reporting Services 2005 Advanced Topics to help you better communicate with your users. In Part 3 , we will look at a standard control and its various functions.

Initial knowledge of SQL Server 2005 Reporting Services Part 2