If you have missed my first two parts of this series of posts below are the web links.
Reporting With SSRS Part 1 -With Sample Report
Reporting With SSRS Part 2-Deploy to Report Manager and Create Subscription
After a blessed month of ramzan and few really busy days at office have passed i am able to deliver this third part of SSRS blog series.
In this part I would like to briefly explain you about Expressions,one of the powerful features of SSRS .Using few examples we will go through this feature which will get you started with this.
Start with designing a simple report which will point to AdventureWorksDW.Create a simple report using the below query.
select FirstName, MiddleName, LastName, NameStyle, BirthDate, MaritalStatus, Suffix, Gender from dbo.DimCustomer
If you are not aware of creating reports, please go through Part-1 and Part-2 of this series.
Expressions are used to evaluate various conditions,calculate values and also used to control the report design.They can also used to configure report properties and appearence.Expressions can use built-in feilds like “Page Number”,”Report Name” etc (You can see the complete list of built-in fields in Built-in Fields folder in report data pane), report parameters ,built in operators and functions.
For example if you want to hide a column in a report you can use an expressions which will evaluate to either true or false and show or hide particular column.We can use the report we just created to do this.
To do this,Right click the parameters folder in report data tab and select “Add Parameter”.In the popup box which will be similar to the one displayed below, name the parameter as “ShowBirthDate” and optionally give the desciption.Click on “Available Values” and specify below two values using “Add” button as shown below.
You will see a parameter in the report when you click on the preview tab.As of now this is not being used anywhere and if you run the report you will not notice any change in the report with either of the values selected for this parameter.We will now use this parameter to show or hide “Birth Date” column of the report using an Expression.
Right click on the table header for the column “Birth Date” as shown below.
We will see the below text box which will prompt you to select appropriate display option.Use “Show or hide based on expression” and click on the “Fx” expression button,this will pop out a expressions window where you can specify an expression and can evaluate it to be true ot false according to the value selected in the parameter.
We need to set an expression to hide the column .We will use “iif” function which will evaluate to be true if the “ShowBirthDate” paramete is selected to No and will hide the column else will show the column.Below is the screen which has a iif function with the complete expression which will do the job.You can also take a look at all the other stuff which you can use to build your expression.
Click ok and preview the report and select Yes in the report parameter value.You will be able to see the column but if you select No the column will dissapear.
When Paramete value is set to Yes:
When Paramete value is set to No:
you can chage report formatting,caluculate fields,pass parameters to the child report based on expression etc.
This is just a start to and introduciton to expressions,I am listing few web links where you can get more information on the topic.
Regards
Syed Asrar Ali
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
