How to create an SSAs-based reporting service report and use parameters (parameters)

Generally, SSRs This topic is used to create reports based on relational data sources. For SSAs Data source reports are rarely introduced. Due to the needs of the project, I have come into contact with this content. Here I will introduce how Reporting Services Built on SSAs And how to use Parameters .

1. Create a data source

First, we create a newSSRsProject, and then create a newShared data sources, InTypeSelect"Microsoft SQL Server Analysis Services",

Click"Edit ..."Button, selectCube.

 

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 tags in total,DataPage is used to create the dataset required by the report,LayoutPage is used to design the report format and display data,PreviewPage is the preview report.

Because the selected data source isCube,So we can see the selectedCubeIt also includes metrics and dimension-related information.

3. Create a dataset

Next, we start to create the dataset required for the report. First inDatasetSelect"New dataset ..."Create a new dataset.

The following window is displayed, and press OK"To create a blank dataset.

BecauseCubeThe 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"Refresh"You can see that there are two more attributes in the dataset on the left of the screen: Product Name and sales.

The two newly added items areLayoutThe dataset members that will be used on the 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 order, and the lastParametersColumn Selection.

At this time, what we need to do is a very neural step, from DatasetPage SwitchLayoutPage, and then switch backDatasetPageDatasetIn the drop-down box, we are surprised to find three more datasets.

Click these three newly generated datasets, and we will find the followingMdxStatement:

Resellergeographycountryregion:

 

Resellergeographystateprovince:

Resellergeographystateprovince:

 

We noticed that the three mdxExcept for different attributes under the dimensionFromStatement.CountryOfMdxDirectlyCubeAndRegionOfFromStatement containsSelect strtoset (@ resellergeographycountryregion, constrained) On ColumnsThis is the implementation of the cascade relationship between parameters.CountryTo confirm the display of the countryRegion,ProvinceIt is similar. This also explains why we need to drag parameters in order as mentioned above, so that the automatically generatedCodeIn order. Of course, manually modifyMdxNo problem, 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

SwitchLayoutPage, dragTableControl, then drag the product name and sales value into the corresponding cell from the dataset list on the left, and then slightly modify the table style.

 

6. Preview

Finally switchPreviewPage, we can see the effect. Select parameters from the three drop-down lists, and then click"View Report"The corresponding report data is displayed.

 

The above briefly introduces how to useSSRsCreateSSAsAnd the use of 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 switchTabPage to create or update a dataset

3. Parameter 1 but changed,LayoutParameters"Multi value"So if you want to add or delete parameters, remember to reset the multiple options of each parameter on the parameter configuration page (of course, if you need multiple choices, you don't have to worry about it)

4InBidsIn fact, the report is modified in the backgroundXMLFile. However, sometimes the front-end displays and the back-end displaysXMLThe storage will not be synchronized. Therefore, if a report preview error occurs, do not rush to change it. Try shutting down the report file and then re-open it, it is probably a time to witness a miracle.......