Old pumpkin: How to Create SSAs-based reporting services and use parameters (parameters)

Generally, SSRS is used to create reports based on relational data sources. This is basically the end of various technical books. Few reports based on SSAs data sources are introduced. As a result of the project's needs, I have come into contact with this content. Here I will introduce how to establish SSAs-based reports in reporting services based on some of my experiences in the project, and how to use parameters.

1. Create a data source

First, create a new SSRs project, and then create a new shared data sources. In the type drop-down box, select Microsoft SQL Server Analysis Services ",

Click "Edit ..." Click to select cube.

 

Step 1: We have established a connection to the data source. Next, we need to create a specific report. Let's assume that in the following case, a report is created to calculate the sales volume of each product, which is filtered by the country, region/province, and city parameters.

2. Create a blank report

After creating a new report, we can see the following interface. There are three tabs in total. The data page is used to create the dataset required for the report. The layout page is used to design the report format and display the specific data. The preview page is used to preview the report.

Since the selected data source is cube, we can see the selected cube on the left and Its metrics and dimension information.

3. Create a dataset

Next, we start to create the dataset required for the report. Select "new dataset…" in the dataset drop-down box ..." Create a new dataset.

In the following window, press OK to create a blank dataset.

Due to the complexity of the cube, the system provides a convenient drag function to select a dataset. By analyzing the requirements, we learned the product name attributes in the product dimension, the sales in the measurement value, and the country, region, and city attributes in the geographic information dimension. For the former, we can easily select the desired attributes from the list on the left and drag them into the blank space on the right.

The name and sales of the subject data products required for our reports have been basically established. Click the "refresh" button in the tool box to see two additional attributes in the dataset on the left of the screen: Product Name and sales.

The newly added items are the dataset members we will use on the layout page.

4. Set Parameters

First, determine which attribute under which dimension is the parameter. In this example, we have three parameters: country, province, and city. We need to select them from the left dimension list and drag them to the filter list in the upper-right corner. Please note that during the drag process, the drag should be strictly in sequence, and the final parameters column should be selected.

What we need to do is to switch from the dataset page to the layout page, and then switch back to the dataset page. Therefore, in the dataset drop-down box, we are surprised to find three more datasets.

Click these three newly generated datasets, and we will find the following MDX statement:

　Resellergeographycountryregion:

 

　Resellergeographystateprovince:

　Resellergeographystateprovince:

 

We noticed that

The difference in mdx is the difference after the from statement except for the different attributes under the dimension. The MDX of country is followed by the data directly obtained from the Cube, while the from Statement of region contains a select strtoset (@ resellergeographycountryregion, constrained) on columns, which is the implementation of cascade relations between parameters, the country's region is determined based on the country selected, and the same is true for province. This also explains why the parameters must be dragged in order, so that the automatically generated code can determine the cascade order in order. Of course, it is no problem to manually modify MDX, as long as you know its structure.

After the above operations, three parameters with linkage relationships have been implemented. Next, we can simply design the report format.

5. design the report format

Switch to the layout page, drag a table control, drag the product name and sales value to the corresponding cell from the dataset list on the left, and then slightly modify the table style.

 

6. Preview

Finally, switch to the preview page to see the effect. Select parameters from the three drop-down lists and click View Report. The corresponding report data is displayed.

 

The above briefly introduces how to use SSRs to create SSAs-based reports and how to use report parameters. Pay attention to the following issues in actual application:

1. Drag parameters in the required order.

2. After dragging parameters, you can only create or update a dataset by switching to the tab page.

3. Once the parameter is changed, the "multi value" of the configuration parameter on the layout page will be selected. If you want to add or delete the parameter, remember to go to the parameter configuration interface, reset the multiple options of each parameter (of course, if you need to select multiple options, you don't have to worry about it)

4. Edit the report in bids. The XML file in the background is actually modified. However, in some cases, the front-end display and the XML storage in the back-end are not synchronized. Therefore, if a report preview error occurs, do not change it urgently, try shutting down the report file and then re-opening it. At this time, it is likely to witness the miracle of the moment ......