SSRS (SQL Server Reporting Services) Best Practices
Data Sources
1.
Use shared data sources even if there
is only dataset
2.
In case of providing fixed
credentials, use read-only user accounts
3.
Do not create report using SSRS
Report Builder
Data Sets
1.
Try to keep report fields in single
dataset. If the parameters are being populated from dataset then that dataset
must be kept separate.
2.
Do not hard code anything either in
the query or calculated dataset fields. Use parameters instead of hardcoding
values
3.
Instead of filtering data in the
dataset filters, filter the same in query
4.
Do not pull unnecessary fields
5.
Do not call queries that updates
data in the data source objects e.g. Database Table
6.
Avoid usage of shared datasets
7.
Avoid usage of VB code i.e. report
code
8.
If report is based on database then
use stored procedure or views
Parameters
1.
Use appropriate data types
2.
Use report code or dataset queries
to populate dynamic and custom parameters
3.
Provide default values to parameters
4.
Configure the default value of
parameters in deployed report from configuration option without actually
editing the RDL
5.
If users are not supposed to update
parameter values then use internal parameters instead of keeping the parameters
hidden
Report code
1.
Use modular approach (Create
separate function even for small requirement)
2.
Do not hard code
3.
Commenting is must for every
function
Expressions
1.
Do not use ReportItems!Fields as it
might get changed and also it is difficult to maintain over the time
2.
Use ReportItems!Fields only for cell
formatting for that particular cell
Report Area
1.
If report shows same static values
at multiple places, then create parameter of the same and use it everywhere
2.
Remove unwanted white space from the
report
Performance and optimizations
1.
Do not pull unnecessary data
2.
If possible then perform
aggregations at the query level and only rendering related customizations at
the report level
3.
If the source of the report does not
change frequently, then use SSRS caching feature and also set the processing
options accordingly
4.
If you use sub reports/ drill down
reports then in child reports, do not add logic of populating parameters which
are consuming values of parent report
5.
Try to use SSRS parameter sniffing
SSRS Coding Standard
Naming conventions
1.
Data sources
a.
Follow [server].[database/ cube]
naming convention
2.
Data sets
a.
Provide meaningful names to datasets
b.
Prefix the names with 'ds'
c.
Give proper names to dataset fields
3.
Parameters
a.
Provide meaningful names to datasets
In the prompt field, put proper phrase or text
that you want to show in parameter
