Tricks you should know when you use Reporting Services for enterprise-scale projects.
Reporting Service tips and tricks
Reporting Services is a great product that can render everything that you can imagine. However sometimes it requires some tricks to get the job done. The following are lessons learned in my last large reporting services project. (The text is a quick and dirty summary – maybe i will take some time in the future to fix errors add some pictures and improve the language.)
Lesson 1 – Create Modules
Not a new story in computer science but very important if you want to create a number of complex reports that should look similiar. Make sure you create reusable, parametrizable subreport modules. This allows you to change things in a single place where you would otherwise have to change it in many places. (Be careful with parameters that change the layout of subreport. This could break the pagination.)
Lesson 2 – Use Metadata
If you have many textboxes with different formatting you can add formatting information to your dataset and use expression to apply the formatting to the textboxes.
Lesson 3 – Use a tablix to create page breaks
This is one of the most important lessons! If you need to control the page breaks for the pdf renderer. the best thing is to use a tablix with a dataset that contains a list of page names. (The same dataset can be used to create the table of contents btw.)
Create a simple list where you output the page names. Then insert multiple rows per page-rowgroup and insert one subreport into each row. Now the trick is to set the row (subreport) visible only if the page should be rendered with this subreport-page-template. In the rowgroup properties you also specify to create a pagebreak between each rowgroup of the tablix.
Lesson 4 – Use the language property of the report or a textbox to set number formats.
There are many blogs in the internet suggesting to format the Number fields using complex Formatting Rules or to do it in SQL Server and return a string instead of an actual decimal number. Don`t do that! (not only it is more work, you can also not reuse the report when you export it to excel.) it is so much easier with the simple formats like =”N1″ + Language = de-de.
Btw. if you want to format a number with a tousand-dot and a decimal comma. You should set the language to de-de. The austrian de-at does not create the dot separator. Instead you get some whitespace between digits with the Austrian German format. (Btw Windows language settings are not considered by Reporting Services renderers.)
Lesson 5 – If you want to render something always the same distance from the bottom on a few pages and you cannot do it in the footer…
(because the footer sizes is fixed) use rectangles and set the reports property to use the whitespace in the rectangle instead of expanding the rectangle. The rectangle will always have the same size as long as the content does not grow beyond the space in the rectangle container. anything that is below the rectangle will always remain in the same location.
Lesson 6 – A rectangle with a subreport with no data rows will disappear and will not consume any space.
If you want to avoid this add a little hidden textbox or a line into the subreport.
Lesson 7 – The page header and footer can display data from a dataset indirectly
The trick is to create a hidden textbox in the body of the report and create an expression in the report header or footer that references this textbox (as a dataset cannot be references directly in the header or footer.)
Lesson 8 – Use bitmaps for better image quality.
Unfortunately reporting services does not support vector graphics so to get better image quality use Bitmaps! Jpg, png often show compression artifacts.