Lesson 8: Miscellaneous options in SSRS (Tutorial)

1. Adding a new concatenated column

Below we can see a table and we want to concatenate Title with Genre to get Full Movie Title with genre.

First we will add a new column to the extreme right end “Movie Full Name”.

Right click the cell where we want to perform concatenation and then go to Expression.

Type the expression as shown in below image. Click Ok. 

NOTE: If you field is not string data type (for instance integer) you might have to use function to convert it to string.

Preview the report.

2. Adding a new calculated column

Choose Data Set. Right Click and choose Add Calculated field.

The DataSet Properties window opens up. Enter a Calculated field Name “MovieWithGenre” and click on the Expression button (fx).

Next add the below expression in the expression window. Click Ok.

The Calculated column appears in the dataset.

Drag and drop the calculated column in a new column in the table in the report designer.

Preview the report.

Using IIF in expression

Add a new column to the table in the report designer.

In the expressions window enter the below code. Click Ok.

Preview the report.

Adding Alternate color to table rows

Select entire row. Go to Properties, Find Background property and click on ‘Expression...’

In ‘Expression’ window type: 

=IIF((RowNumber(nothing) MOD 2)=1,"Gainsboro","Transparent")

Preview the report.

Sorting Columns in SSRS

Right click on the column header and choose Textbox Properties.

Choose Interactive Sort from the Textbox properties dialog and Check the checkbox for Enable interactive sort on this Textbox, Choose Title field from the Sort by drop down.

Preview the report.

Adding custom paging in SSRS

From Main Menu, select Report, and the click on Add page footer option.

Let us add four textboxes from the Report Item toolbox onto the designer as shown under.

Choose the 2nd textbox and click on the Expression.

From the expression window that opens, let us write the expression.

Similarly for the 4th Textbox, let us write the expression as = Globals!TotalPages. Click Ok.

The report looks like below.

Preview the report.

Related topics:

Lesson 1: CREATE A SIMPLE SSRS REPORT (TUTORIAL)
Lesson 2: CREATE A SSRS REPORT WHICH EXCEPTS INPUT PARAMETER (TUTORIAL)
Lesson 3: CREATE DRILL DOWN VIEW IN SSRS (TUTORIAL)
Lesson 4: NAVIGATING BETWEEN SSRS REPORTS (TUTORIAL)
Lesson 5: WORKING WITH SUB REPORT IN SSRS (TUTORIAL)
Lesson 6: WORKING WITH EXPRESSIONS IN SSRS (TUTORIAL)
Lesson 7: WORKING WITH GRAPHS AND INDICATOR IN SSRS (TUTORIAL)

Lesson 7: Working with graphs and Indicator in SSRS (Tutorial)

Adding a bar graph to report

Add a duplicate copy of column “Price”. Rename the header as “Price Analysis”

Right click on the new row “Price”. Select Insert and the select the option “Data Bar”.

In the “Select Data Bar Type” window, select the “Stacked Bar”. Click Ok.

Right click on the Data Bar and from the context menu, choose Chart Properties.

In the General section of the Chart Properties window, choose Gray Scale from the Color Pallet drop down.

And in the Fill section, choose Gradient in the Fill Style and provide some suitable gradients. Click Ok

Preview the report.

Right click on the Data Bar and from the context menu,choose Horizontal Axis Properties.

From the properties dialog check the Reverse Direction checkbox of the Scale options. Click OK.

Preview the Report.

Adding an indicator to report

Right click on the new row “Price”. Select Insert and the select the option “Indicator”.

The Indicator Type properties window appears. From there let us choose the "3 Arrows (colored)" Directional Indicator type. Click Ok.

Right click on Indicator, select Indicator Properties.

We can change the value and states of the Indicator from the Values and States option available in the Indicator Properties. We can even add more indicator states. The default is shown below. Click Ok.

Preview the report.

Related topics:

Lesson 1: CREATE A SIMPLE SSRS REPORT (TUTORIAL)
Lesson 2: CREATE A SSRS REPORT WHICH EXCEPTS INPUT PARAMETER (TUTORIAL)
Lesson 3: CREATE DRILL DOWN VIEW IN SSRS (TUTORIAL)
Lesson 4: NAVIGATING BETWEEN SSRS REPORTS (TUTORIAL)
Lesson 5: WORKING WITH SUB REPORT IN SSRS (TUTORIAL)
Lesson 6: WORKING WITH EXPRESSIONS IN SSRS (TUTORIAL)
Lesson 8: MISCELLANEOUS OPTIONS IN SSRS (TUTORIAL)