Whether you’re new to reporting or have some experience it’s sometimes not easy knowing where to start when creating reports. Your plan on what to report can change throughout its development because the report specification needs more detail or extra thought and ideas are valid additions as information is discovered about the report.
Here are my simple guidelines that might help you on your way.
Depending on your data sources try to get most of your figures correct before it reaches the reporting stage. It sounds obvious I know, but fixing calculations or adding business logic within reporting services isn’t a good practice.
If your data source is connecting from an analysis services cube, most of your number crunching should be done at the cube stage – even column order for dates etc.
Setting up your report with the best possible data will save you so much time – so do think about what figures you are producing before you start to create.
Reporting services offers a wide range of charts, tables and gauges. To bring them all together and get them to work does take some thought and it’s not always about using as many functions as possible, as this can lead to a reporting nightmare. Don’t try and fit every thing on one page, a few reports on different pages could be more effective.
One function that seems to be tricky to get right when I was learning how to build reports is Parameters.
Even though this is the first thing a user sees, I try to leave these till last.
- Use default values – Try not to have a blank report shown when launched by a user.
- Parameter order – Decide a logical parameter order e.g. Year, Quarter, Month.
- Guided Parameters – Using MDX you can eliminate the chances of a user slice on a blank area of data.
- Removing Parameters – Remember to remove the parameter dataset, the dependency e.g. @parameterName in other parameters datasets and from the report menu.
- Parameters ‘All’ MDX – Removing the ‘All’ member can stop a user from displaying large levels of data that could span hundreds of pages.
- Passing Parameters MDX – Remember when passing a parameter to a jump to report, this will need to be the fully qualified name [dimension level].[Hierarch level].[member level].
Reporting ‘Face’ (a.k.a. Layout)
Nothing is worse then seeing a lonely report or a cluttered report; a report that is over powered with colour or a dull one.
I think if you view a report and do not understand what you’re looking at within 10 seconds then its not a well presented report.
- Headers and footers should be used to contain information that needs to be displayed on each page whether you’re just viewing or printing. So be sure to set up a template that always contains: logos (hyperlinked to url), page number & number of pages, date time, run time and possibly username.
- Tables, Matrixes or Tablixes need to have consistent font, font sizes, colours and borders.
- Format is also important if you haven’t already formatted your figures in your database or cube then makes sure this happens at the report stage. Reasonable decimal places and correct currency signs.
- Readable Charts, gauges, sparklines are all visual representations of data therefore should be easier to read then a table. Think about what you would like to display and the best choice of chart type to represent your data e.g. show percentages on pie charts.
- Fonts – Avoid using serifs and keep fonts consistent throughout the report.
- Characters – if you are faced with a column that shows all capitals try using this expression: StrConv(Name Of Field, VbStrConv.ProperCase).
- Tables – Avoid doing too many calculations (remember to hide your reference columns).
- Charts –The right charts for the right data! Just because it looks good doesn’t mean you should use it!