SSRS - Conditional Formatting By Switch Function

In this tutorial, we will learn How to use Switch function features in your chart report. 

Switch is useful when there are three or more conditions to test because it's simpler to read than nested IIF statements. It's similar to a Case statement in T-SQL. A Switch will return the first expression that it finds true, so the order of the conditions is very important. Switch works like case statement and we can write multiple conditions. Switch evaluate them in sequencer manner and take the first expression that evaluated to true.

As with most things in SSRS you can do something in more than 1 way but today I wanted to introduce you to the Switch Function. 

The SWITCH function allows you to have multiple conditions when evaluating an expression. It evaluates each condition within the function. 

I would recommend you read the expression you have written very carefully because your logic is flawed and that is why sometimes, your SWITCH statement is not working as you believe it should. 

SSRS - Scale Break features in Chart

In this tutorial, we will learn How to use Scale Break features in your chart report. A scale break is a stripe drawn across the plotting area of a chart to denote a break in continuity between the high and low values on a value axis (usually the vertical, or y-axis). Use a scale break to display two distinct ranges in the same chart area. 

       You cannot specify where to place a scale break on your chart. The chart uses its own calculations based on the values in your dataset to determine whether there is sufficient separation between data ranges to draw a scale break on the value axis (y-axis) at run time.

We will show you a lesser well known feature within SSRS that only takes 1 click to implement. 

We can enable this feature by right clicking on the vertical axis and selecting the (Vertical Axis Properties). Then after that just click the check box for (Enable Scale Breaks.)

To change the style of the scale break

1. Open the Properties pane.
2. On the design surface, right-click on the y-axis of the chart. The properties for the y-axis object (named Chart Axis by default) are displayed in the Properties pane.
3. In the Scale section, expand the ScaleBreakStyle property.
4. Change the values for ScaleBreakStyle properties, such as BreakLineType and Spacing.

The MaxNumberOfBreaks I think is self-explanatory. When you click on (Enable Scale Breaks) SSRS will automatically determine how, when and where to place the scale breaks. So if you only want 1 you can change that setting here.

Another property you can change is the ‘CollapsibleSpaceThreshold.’ You can change this setting to help control how aggressive the scale break is. This can be handy if you have a scale break that appears in the report and it probably shouldn’t or maybe it should. You can
adjust this up or down to help eliminate this issue.

SSRS - How to Add Bookmarks Actions in Report

SSRS bookmarks are essentially customized navigational links in the report. The bookmark can be any string, but it must be unique in the report. If the bookmark is not unique, a link to the bookmark finds the first matching bookmark.

For example, if your report displays a table grouped by color, you would add a bookmark based on the group expression to the group header. Then you would add a table with a single text box at the beginning of the report that displayed the color values, and set the bookmark link on that text box. When you click the color, the report jumps to the page that displays the group header row for that color.

In this tutorial, we are going to learn how to Add bookmarks and bookmark links to a report. Add bookmarks and bookmark links to a report when you want to provide a customized table of contents or to provide customized internal navigation links in the report. Typically, you add bookmarks to locations in the report to which you want to direct users, such as to each table or chart or to the unique group values displayed in a table or matrix. We can create your own strings to use as bookmarks, or, for groups, you can set the bookmark to the group expression.

To add a bookmark link - In report design view, select the text box, image, chart, or other report item to which you want to add a bookmark. The properties for the selected item appear in the Properties pane. For example, you could type BikePhoto as the bookmark for an image in your report.

1. In Design view, right-click the text box, image, chart, to which you want to add a link and then click Properties.
2. In The Properties dialog box for that report item, click Action.
3. Select Go to bookmark. An additional section appears in the dialog box for this option.
4. In the Select bookmark box, type or select a bookmark or an expression that evaluates to a bookmark. Using the previous example, type BikePhoto to create a link to the image in your report.
5. Click OK.
6. (Optional) The text is not automatically formatted like a link. For text, it is helpful to change the color and effect of the text to indicate that the text is a link. For example, change the color to blue and the effect to underline in the Font section in the Home tab of the Ribbon.
7. To test the link, click Run to preview the report, and then click the report item that you set this link on

SSRS - How to Create Recursive Report

In Reporting Services paginated reports, a recursive hierarchy group organises data from a single report dataset that includes multiple hierarchical levels, such as the report-to structure for manager-employee relationships in an organisational hierarchy.

In this tutorial, we are going to learn how to use SSRS to report on recursive data. If you have a self referencing table or even a Parent-Child hierarchy setup in an SSAS multidimensional cube you could find these techniques useful. In our example below we want to display the data within a multi-level hierarchy. We will be working with HR data and we want to see the CEO at the top of hierarchy and then see who reports to who after that.

To create a recursive hierarchy group - There are very simple steps to create a recursive hierarchical report as given below: 

1. In Design view, add a table, and drag the dataset fields to display. Typically, the field that you want to show as a hierarchy is in the first column.
2. Right-click anywhere in the table to select it. The Grouping pane displays the details group for the selected table. In the Row Groups pane, right-click Details, and then click Edit Group. The Group Properties dialog box opens.
3. In Group expressions, click Add. A new row appears in the grid.
4. In the Group on list, type or select the field to group.
5. Click Advanced.
6. In the Recursive Parent list, enter or select the field to group on.
7. Click OK.
8. Run the report. The report displays the recursive hierarchy group, although there is no indent to show the hierarchy

For demo of this report, you can visit our YouTube channel as well -