Session 9: SSRS Expressions
Next, lets move on to creating expressions in SQL server reports.
Expressions can be used for a number of things. The best part is that it operates on a cell by cell basis.
Hence, you can write expressions to manipulate the data, or change the properties of cell etc. Even if you are dealing with graphs and maps, many of the properties, you can control using expressions (like color of graph, some values etc).
Basically you would want to write expressions, if you need to manipulate properties/data at runtime
Lets look at a very simple example.
Step 1: Create a report server project and connect to Adventure Works Database.
Step 2: Add the SalesOrderDetail table with a couple of columns to the Report.
Step 3: In the toolbox, add the table control to the report.
Step 4: Add 3 columns to the report - SalesOrderID, SalesOrderDetailID, OrderQty, LineTotal.
Step 5: Now lets write some expressions. We will display anything in LineTotal cell as Green, if the number is above 2000, else we will display in Red.
Right click on the cell , and select text-box properties.
Step 6: Next to the colr dropdown, there is an Fx button.
Write the below expression.
Step 7 : Click OK and then on the Preview button.
You should be getting the final output as displayed below.
Step 8: Now lets write some expressions to manipulate some data.
Right click on the OrderQuantity Cell and click on expressions.
Enter the below expression and click OK.
Step 9: Click on the preview tab, you should get the below output.
I have demonstrated a very simple use of expressions. However , you can write very complex expressions depending on your business needs. For instance, you can write expressions, to calculate the standard deviation of so and so , and manipulate results accordingly.
Many time, you need to write expressions to deal with Dates and times
For information on: http://msdn.microsoft.com/en-us/library/ms157328.aspx
Thanks for reading my post, and let me know of your feedback
0 comments
Post a Comment