SSRS Tips and Tricks

December 11th, 2014 Leave a comment Go to comments

This is my brain dump of points to remember when developing SQL Server Reporting Services reports.

The environment being used when writing these is SQL Server 2012

Subreports

  1. Paging
    When creating a report with sub reports paging should be added to the top level report. The paging inside the sub report looks fine on screen but is ignored when exporting to PDF.
  2. No Data
    When there is no data in a subreport the report will not show. If you have a report where you have paging between sub-reports you will end up with a page break, but then no report, which means you get an empty page in your report. The way to force you report to render is to create an empty dataset, with

    select 1

    as the SQL. This will always execute and force the sub-reprot to always render

Tablix

  1. Repeating Headers
    Repeating the header rows requires going into “Advanced Mode”. Advanced mode can be found by clicking on the black triangle on the far right of the “Row Groups” and “Row Columns” header bar.
    Once in Advanced mode you get extra “Static” elements. In row groups click on the static element and it will highlight the item in the area it relates to in the report. Once selected look at the properties window and set,

    • Repeat on new page: True
    • Keep Together: True

    The main issue is that you must have a continuous block of the same settings, meaning you can’t have 3 header rows and choose to repeat the 1st and 3rd but not the 2nd, It will throw a compile time error.

  2. Alternating Row Colours
    1. Select the Row of your tablix
    2. Choose the Background Property –> Expression
    3. Enter the following formula
      =IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "No Color")
    4. Groups: Alternating Row Colours Header
      This is for the main outer grouping

      1. Select the Row of your tablix
      2. Choose the Background Property –> Expression
      3. Enter the following formula
        =IIF(RunningValue(Fields!Category.Value,CountDistinct,Nothing) Mod 2, "WhiteSmoke", "White")
      4. Groups: Alternating Row Colours – Group Footer
        Try the following in your footer section of your group

        =IIF(
        	RunningValue(Fields!Item.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0
        	,IIF(ROWNUMBER(NOTHING) MOD 2=0,"WhiteSmoke","White")
        	,IIF(ROWNUMBER(NOTHING) MOD 2=1,"White","WhiteSmoke")
        )
        
  3. Alternating Row Colours – Across dynamic columns
    In your report

    1. a the top level of grouping that you wish to alternate the column colour from add a new column
    2. Name the column txtRowCount
    3. Add the following expression
      =RunningValue(Fields!GroupingFieldName.Value,countDistinct,Nothing)
      

      where GroupingFieldName is a value that this particular level of grouping is being grouped by, e.g. if grouping by company then Fields!CompanyName would most likely be the value

    4. On the colums that require alternating colour add the following formula for the background colour attribute
      =IIF(VAL(ReportItems!txtRowCount.Value) MOD 2,"WhiteSmoke","White")
      
    5. Page Footer

      1. Page Name
        The PageName property is useful when building a report made of multiple sub-reports.
        The footer can have

        =Globals!PageName

        and each section of the report can have a different PageName.
        The PageName attribute is available on

        • Rectangle
        • Tablix

        In the case of the a main report running sub-reports you would wrap your sub-report in a rectangle and then set the PageName property on th rectangle

      Expressions

      When trying to access an element in the page use the following:

      ReportItems!textbox1.Value
      

      Pie Charts

      Add Percentage – Data Labels

      • Right Click on the pie chart and tick, “Show Data Labels”.
      • Right click on one of the labels and choose “Series Label Properties
      • In the label field choose #PERCENT, choose yes when prompted

      This will work if you have a single data item. If you have multiple items you need to click on the chart and have the “Chart Data” popup. For Each value:

      • Ensure the properties window is visible (F4), and click on a data element in the chart data
      • Expand the “Label” element and set Visible = True and set the value as “#PERCENT” or the value you wish to display, by setting an expression.
      • Edit the font size and or the format as required.
    6. Data Labels Outside of Pie Chart Click on the Pie Chart image, and in the properties window expand custom attributes. Set the PieLabelStyle to “Outside”

      Bar Charts

      Labels Outside
      Same as above

      • Click on the bar chart until just the bars inside the chart is selected. (The Chart Data popup will appear)
      • In properties expand “Custom Attributes”
      • Set the “BarLabelStyle” to “Outside”

      NOTE: Padding on the chart. I had an issue where the Outside label was rendering too close to the bar. After an hour of playing I found that the fix was to changing to “Bar, Plain” gave the best padding
      Sorting the Chart Data
      Sorting is done from the “Category Groups” of the “Chart Data” Popup. Right Click on the category Group, and choose category group properties. Switch to the sort tab and play with your sort orders

  1. Julio Caldas
    January 23rd, 2014 at 08:00 | #1

    Your trick about always render a subreport with no data save my life!!!

    Thanks a lot!

  2. Phil C
    November 18th, 2015 at 05:36 | #2

    Thanks very much for the tip on using Globals!PageName in the footer.
    I had an issue with changing a string in the report header and this worked perfectly!

  1. No trackbacks yet.