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 sub-report 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-report 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")
         )
         

Alternating Row Colours – Across dynamic columns
In your report

1. 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")
      

Page Footer

1. 1. 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.

Top Grouping
It is not actually top n grouping but it is a way of setting on the pie chart, any value less than say 5% gets bucketed into an “other” group.

This is done by:

• Selecting the chart so that the “Chart Data” appear
• Press F4 to get the properties
• Expand the “Custom Attributes” option
• Set CollectedStyle to “Single Slice”
• Set your Collected Threshold to a % level that below which you wish to ignore, say 5%
• Set your Collected Label to “Other” or “Below 5%”

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