Skip to main content

SQL Server Reporting Services best practices


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

Comments

Post a Comment

Popular posts from this blog

DataZen Syllabus

INTRODUCTION TO DATAZEN PRODUCT ELEMENTS ARCHITECTURE DATAZEN ENTERPRISE SERVER INTRODUCTION SERVER ARCHITECTURE INSTALLATION SECURITY CONTROL PANEL WEB VIEWER SERVER ADMINISTRATION CREATING AND PUBLISHING DASHBOARDS CONNECTING TO DATASOURCES DESIGNER CONFIGURING NAVIGATOR CONFIGURING VISUALIZATION  PUBLISHING DASHBOARD WORKING WITH MAP  WORKING WITH DRILL THROUGH DASHBOARDS

MS BI Syllabus

Microsoft Business Intelligence Course Syllabus SSRS – SQL Server Reporting Services  Getting Started 1. Understanding Reporting (Authoring,Management,Delivery) 2. Installing Reporting (Native Mode, SharePoint Integration mode) 3. Building your first report  Authoring Reports 1. Developing Basic Reports (RDL,wizard,designer,datasource,dataset,formatting) 2. Working with expressions (expression to calculate value, Agg functions, exp for objects) 3. Organizing Data (Data Regions, Table, Matrix, Chart, List) 4. Advance Report (Parameter, drill down, drill through, links, 5. Report Model (Data Source, Data Source View, Model , Report Builder 3.0)  Managing Report ( Report Manager) 1. Managing Content (deploying report, folders, linked reports, datasources, value etc) 2. Managing Security (Item Level , Site navigation, localhost – sql) 3. Managing Server Config (Config Manager, Report Manager, Report Server DB)  Delivering Report 1. Accessing Report (Viewing...

PowerBI Interview Questions and Answers

Power BI Interview Questions – General Questions 1). What is self-service business intelligence? Ans: Self-Service Business Intelligence (SSBI) is an approach to data analytics that enables business users to filter, segment, and, analyse their data, without the in-depth technical knowledge in statistical analysis, business intelligence (BI). SSBI has made it easier for end users to access their data and create various visuals to get better business insights. Anybody who has basic understanding of the data can create reports to build intuitive and shareable dashboards. 2). What are the parts of Microsoft self-service business intelligence solution? Ans: Microsoft has two parts for Self-Service BI  Excel BI Toolkit – It allows users to create interactive report by importing data from different sources and model data according to report requirement.  Power BI – It is the online solution that enables you to share the interactive reports and queries that you have created using ...