- 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.
- SSRS connects different types of data sources like XML, SQL Server, etc.
- Here we select Microsoft SQL Server Analysis Services because we fetch the data from cube then click on Edit.
- On the Edit option, we enter the server name as well cube name.
- As per the selected data source, It generates a connection string to obtain data for the report.
- 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.
- Here we fetch the required data and click on OK.
- As per fetching data here creates an MDX 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.
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.
- Provide a report name and click to Finish.
- REPORT. RDL is created under the SSRS folder. RDL is a Report Definition Language nothing but XML file.
- Here we rename of report title as customer Report and run the 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.
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.
- Here we set the query over the Address table.
- When we click on the OK parameter is created in the report data.
- Here we select Mumbai and click on View Report then as per address report is displayed.
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.
- We create a new Dataset. And we select the data source and create a query by using a query designer.
- As well in the parameter, we select a value for this parameter.
- If we run the report address shown in a drop-down list.
Start with MSBI video as mentioned below to learn topic practically in-depth:
