If you are looking for a tool which can perform the designing, generation and deployment of reports used to deliver a variety of interactive and printed reports using SQL Server Database. Then SSRS can be taken into consideration. This cheat sheet will guide you with the basic concepts which is required to get started with it. It is a handy reference sheet for the beginners and the one having knowledge about the BI tools.
SSRS is a feature included in the SQL server product. It is a server-based reporting platform used to create and manage a wide variety of reports and deliver them in a range of formats.
We can create basic reports containing tables and graphs, or more complex data reports using charts, maps and sparklines. The reports can draw the data from the SQL Server database, relational database such as Oracle and other types of multi-dimensional or XML based data source such as SQL Server Analysis service, Teradata or parallel data warehouse. It is used to deliver a variety of interactive and printed reports.
It is a full-featured application that is used to design, develop, test and deploy the reports. The main components of SSRS are:
It is a tool used to develop reports. It has some enhancements to the user-interface for designing, developing and testing reports included with SQL server.
The BIDS paradigm is based on the concept of projects and solution. A project is a container of one type of objects such as SSRS reports, web pages, SSIS packages etc. which can be used to organize things in a meaningful way. Whereas, a solution is a container of one or more projects, it is a complete application that can be made with number of projects such as report project, SSIS project, web project etc.
It is a Visual Studio based Microsoft application configured to use for MSBI line of products such as SSIS, SSRS and SSAS
It is an XML file that corresponds to XML grammar
It is produced by the Visual Studio report definition that is (.rdlc) files in XML format to be used with ReportViewer control
Data sources holds the details of the database server
A dataset stores the specific query that is used to fetch the data for a report. There are two types of Datasets:
It is a dataset published on a remote server and can be used by multiple reports
These datasets are defined in and used by a single report
It is a reporting tool which should have fast user interface Primary Component. The following diagram shows the architecture of SSRS
Architecture of SSRS
The Architecture of SSRS mainly consists of the following components and tools:
|Return first day of the current week||=DateAdd(“d”,-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)|
|Returns first day of the current month||=DateAdd(“d”,-(Day(today)-1), Today)|
=DateSerial( year(today()), month(today()), 1)
|Return first day of current year||=DateAdd(“d”,-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)|
|Last day of current month:||=dateadd(“m”,1,dateserial(year(Today),month(Today),0))|
|Last day of previous month:||=dateadd(“m”,0,dateserial(year(Today),month(Today),0))|
|Last day of next month:||=dateadd(“m”,2,dateserial(year(Today),month(Today),0))|
|Return period over period||For week over week|
For month over month
For year over year
|Return current month name||=MonthName(Month(Today()))|
|Convert text to proper case||=StrConv(Fields!FieldName.Value, VbStrConv.ProperCase)|
|To replace null with another value||=iif(Fields!FieldName.Value = nothing, “No Value”,Fields! FieldName.Value)|
|To alternate row color||=iif(RowNumber(Nothing) Mod 2 = 0, “Silver”, “White”)|
|Handling division by zero||=iif(Fields!DenominatorField.Value = 0, 0, Fields!NumeratorField.Value/|
iif(Fields!DenominatorField.Value = 0, 1, Fields! DenominatorField.Value))
With this, we come to an end of SSRS Cheat sheet. To get in-depth knowledge, check out our interactive, live-online SSRS certification Training here, that comes with 24*7 support to guide you throughout your learning period. Intellipaat’s SSRS online training will give you complete understanding of designing, developing, testing and tuning of Business Intelligence Reports.Previous Next
Download Interview Questions asked by top MNCs in 2019?