I have recently been sharpening up on my SSRS skills and I have decided to take time to share what I feel are some valuable tips and tricks.
This is a large list but can serve as a great resource to refresh your memory before starting any SSRS project.
General Design Tips:
1. Always use views or stored procedures for data sets. This can allow reports to continue to run properly even if a field is added to and underlying data source. Additional fields can also be added into a view easily without having to make changes to underlying tables as well.
2. Use calculations within the report when you don’t have access to create data structures in the database (i.e. stored procedures).
3. Make sure to set the "NoRows" properties for all data regions. This will return a message to the user if no data is returned when executing the report.
4. If you plan to use an existing dataset when creating a new report, rather than starting with a blank report, copy an existing report, and delete the data regions from it. This can help preserve the appearance of the other reports (especially if standard colors, layouts, and logos are used) and minimize the effort when starting a new report.
5. Properties that can be formatted with four values (2,2,2,2) read from left to right, then top to bottom (Left, Right, Top, Bottom).
6. Even if the report only has one dataset, it is good practice to always define the dataset when referencing fields in case more datasets are added at a later time. This will save some rework.
7. Always name all objects and items within the report. This makes debugging easier. Naming objects as you create them will save a lot of time later in the testing process.
Advanced Report Authoring:
1. Lists can become very powerful by simply inserting other data regions (i.e. graphs, tables, matrices, and other lists) into the list itself. The list can then be grouped by a dimension to dynamically display the data. However, keep in mind that lists can only use data regions from the same dataset.
2. In a matrix, if you want some data columns to appear before the column groupings use an integer value in the "GroupsBeforeRowHeaders" property.
3. Create separate datasets for filters, these should be two column tables (one row for the value, one row for the label). A parameter must be created to use the data from the dataset.
4. Parameters can be used to dynamically control formats, they can be used within the dataset queries to control the data returned, and also within filters (two column datasets as mentioned in the previous tip).
5. A filter returns the full dataset then filters the data, a parameter in a query limits the dataset that is returned. Filters can be applied to single data regions and still allow other regions to use the full dataset. It can be important to note when and how the data is filtered when trying to fix / prevent performance issues.
6. If a report action (i.e. text which hides and / or displays a sub report) for an item is defined try to remember to underline it. This is always a good idea because it can inform the user that an action is available.
7. To display sub reports correctly you must provide a value for all parameters of the sub report for it to render without error.
8. Recursive relationships (employee to manager) have a whole set of Recursive logic that can be applied in order to create calculations. If you want a parent row’s value aggregated with the lower levels, simply use an aggregation with the Recursive keyword.
Working with Report Models:
1. A report model contains a hierarchy of objects whose purpose is to represent the data structures and relationships in business terms. It is used to hide the complexities of data structures to the end users.
2. The data source for a report model project can use the SQL Server provider only. A report model can only use one data source.
3. Report Models can also be created from Management Studio and Report Manager.
4. Just like in SSAS, named queries and named calculations can be used within the data source view for a Report Model.
5. Fact tables must have a logical key in the Report Model in order to include it as an entity, this can be a composite key of all foreign keys in the table.
6. You should plan to go through several cycles of fine-tuning your report models as users get more experience working with Report Builder (an iterative development approach).
