SSRS REPORT

• SSIS package Extract, Transform, Loaded the data into OLAP databases. Data can be various formats like CSV, Excel.
• SSAS is about analysis. Analysis means calculations like sum, count or some complicated formula which does forecasting, analysis calculation. In SSAS we create a cube for precalculation which is to allow queries to return data quickly.
• SSRS is a reporting service that helps to fetch data from cube also fetch the data from SQL Server.

SSRS has two templates:-

• Report Server Project Wizard creates a new report server project using the report wizard.
• Report Server Project creates an empty report template.

In this article, we create a report and fetch the data from the analysis cube. Here I have already created a cube.

• Firstly we go to Solution Explorer and right-click it → Goto Add project → Select Report Server Project Wizard → Click on OK. When we clicking on ok new wizard is open that is Report Wizard.

REPORT WIZARD

• SSRS connects different types of data sources like XML, SQL Server, etc.

DATA SOURCES

• Here we select Microsoft SQL Server Analysis Services because we fetch the data from cube then click on Edit.

SELECT DATA SOURCE

• On the Edit option, we enter the server name as well cube name.

CONNECTION

• As per the selected data source, It generates a connection string to obtain data for the report.

REPORT WIZARD

• We specify the MDX query to get data for the report by using Query Builder.
• We write query an analysis cube use MDX. MDX is a Multidimensional Expressions query language for OLAP.

DESIGN THE QUERY

• Here we fetch the required data and click on OK.

QUERY DESIGNER

• As per fetching data here creates an MDX query.

DESIGN THE QUERY

• Tabular report display data in tabular format which is a row and column.
• Matrix report display summarized data. In matrix number of columns can change dynamically depending on input data.
• We select Tabular report and click on Next.

REPORT TYPE

The report can have three sections:-

• Page (Header):- It displays Report title, Page number, Time, etc.
• Group:- It displays different types of the same data which are repeating in a single project like country or state-wise report.
• Details:- It shows actual data of the report.

We drag and drop the field from the Available field to the Display field and click Next.

DESIGN THE TABLE

• Provide a report name and click to Finish.

COMPLETE THE WIZARD

• REPORT. RDL is created under the SSRS folder. RDL is a Report Definition Language nothing but XML file.

REPORT

• Here we rename of report title as customer Report and run the report.

REPORT

• Report Data provides data to a report. Data Source tells where the server is located. Data Set tells what kind of query we executed.

REPORT DATA

PARAMETER

• Now we report search by an Address. So we need to add parameter on the project.
• There are two ways of adding a parameter. Add a Parameter or Query Designer. Here we add a query.

QUERY

• Here we set the query over the Address table.

QUERY DESIGNER

• When we click on the OK parameter is created in the report data.

PARAMETER

• Here we select Mumbai and click on View Report then as per address report is displayed.

REPORT AS PER ADDRESS

Drop Down By An Another Way

• Here we learn how to create a drop-down list. For creating a drop-down list we uncheck the multiple values from created parameters.

REPORT DATA

• We create a new Dataset. And we select the data source and create a query by using a query designer.

DATASET PROPERTIES

• As well in the parameter, we select a value for this parameter.

REPORT PARAMETER PROPERTIES

• If we run the report address shown in a drop-down list.

DROP DOWN LIST

Start with MSBI video as mentioned below to learn topic practically in-depth: