Поделиться через

How to create Self-service BI reports having Calendar controls using Report Builder 3.0

  • Статья

Introduction

In this blog, we are going to learn how to create reports on the fly using report builder 3.0 and also how to add parameters to the reports created using this method.

Important Note: Please note that in this approach we haven’t had to modify the underlying MDX query at all.

 

Assumptions

  • The cube is well formed based on MS guidelines. The Data model conforms to standards and there are no glitches
  • The attribute relationships and Hierarchies have been made properly.
  • This demo has been made using the ADVENTUREWORKSDW database on a cube built for INTERNET SALES related measures groups

Some MDX Terms explained:

  • SET – A set is an accumulation of members of the same kind
  • MEMBER – A member represents a particular value in the cube
  • STRTOSET – Used when we want to represent multiple members from a dimension/measure. The members could be from the same column/attribute or, multiple attributes.
  • When we use STRTOSET in the FROM clause like the following, we mean to ask the engine to use the current set of members coming in from a MULTI-value parameter.

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Order
Date].[Date Key].[Date Key].ALLMEMBERS * [Dim SalesTerritory].[Sales Territory Key].[Sales Territory Key].ALLMEMBERS
* [DimCustomer].[Customer Key].[Customer Key].ALLMEMBERS )

 } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS FROM

 (

 SELECT

 (

 STRTOSET(@DimSalesTerritorySalesTerritoryKey,
CONSTRAINED) ) ON
COLUMNS

 FROM ( SELECT ( STRTOMEMBER(@FromOrderDateFullDateAlternateKey, CONSTRAINED) :

 STRTOMEMBER(@ToOrderDateFullDateAlternateKey, CONSTRAINED) ) ON COLUMNS

 FROM [Adventure Works DW]

 )

 ) CELL PROPERTIES VALUE,

 BACK_COLOR,

 FORE_COLOR,

 FORMATTED_VALUE,

 FORMAT_STRING, FONT_NAME,
FONT_SIZE, FONT_FLAGS

 

  • STRTOMEMBER – This function converts a STRING
    value into a valid member type.

 

Steps to create the report (with screenshots)

Click on New Report from the menu on the left hand side.

 

 

Now, Select Tablix (Table or, Matrix Wizard).

 

Create a new data source or, use a shared Datasource.

 Select the following option for using a connection to SSAS.

Then build the connection string.

Post building the connection
string, drag and drop your Measures and Dimension attributes into the right
side pane.

It will look something like the following.

Now, add filters to filter your result row-set on the top pane.

Add more filters if needed.

Select the check box located at the right corner of the parameter row.

Arrange fields by dragging and dropping fields from the list of fields.

Now, you may want to look for options to decide the function that will be used for the Values field.

 

 

Select the LAYOUT.

It should look something like the following once all selections have been made.

In case you want to use a CALENDAR control on the DATE filters, here is what you can do.

Go ahead and modify the type of the Report Parameters which are automatically added when you add a DATE RANGE filter in the design window.

 

 

Now, set the AVAILABLE VALUES section to NONE

As well as, the Default VALUE section.

 

 

Do, the same for both the FROMDATE & TODATE parameters.

Now, come back to the DATASET and open PROPERTIES.

You will see that by default the Query Parameters are mapped to Report Parameters of the same name.

Now, let’s modify the value by providing an expression here as follows in both FROM DATE and TO DATE:

="[Order Date].[Full Date Alternate Key].&[" + Format(Parameters!FromOrderDateFullDateAlternateKey.Value, "yyyy-MM-dd") + "T00:00:00]"

Note:

  • We are using this expression to convert the date format supplied by the calendar control which will look like ‘2/13/2005’ to the
    stored value in the FULLDATEALTERNATEKEY column/attribute of the DATE dimension
    of the cube i.e. ‘2005-02-13T00:00:00’.
  • Here "[Order Date].[Full Date Alternate Key].&[" + has been added to fully qualify the member name
  • FORMAT function has been used to format the date coming from the calendar control into the member format from the cube
  • And "T00:00:00]" has been concatenated to the expression to convert the value so far from string to date

Repeat the above for TO DATE as well.

Now, your DATASET Properties -> Parameters should look like this:

 

Now, your report viewer report is ready to run.

 

 

Please click on the RUN button on the top LEFT. Choose the dates in the Calendar controls.

Note: Throws the error about the CONSTRAINED flag if you happen to select a date beyond the available limit in the DATE dimension.

An error has occurred during report processing.
(rsProcessingAborted)

Query execution failed for dataset 'DimSalesTerritorySalesTerritoryKey'.
(rsErrorExecutingCommand)

Query (1, 588) The restrictions imposed by the CONSTRAINED
flag in the STRTOMEMBER function were violated.

To avoid hitting this issue we can use the following expression in the DATASET -> PROPERTIES -> PARAMETERS.

=IIF((Parameters!FromOrderDateFullDateAlternateKey.Value)<= Today(),("[Order Date].[Full Date Alternate Key].&[" +
Format(Parameters!FromOrderDateFullDateAlternateKey.Value, "yyyy-MM-dd") + "T00:00:00]"),"[Order Date].[Full Date Alternate
Key].&[2008-03-21T00:00:00]")

Here the highlighted value is the last valid date key value in the Date dimension.

Moving on….

After selecting a value(s) for the 3rd parameter, once we click on View Report, we can see the following.

 

 

How to handle non-date parameters (Sales Territory Key in my case)

For parameters other than Dates, we need to provide AVAILABLE value as follows (when you add a filter, this is added by default):

This is because when the Query parameter is equated with the report parameter, it is by default equated with this value.

The query looks something like this. Notice the STRTOSET.

SELECT NON
EMPTY { [Measures].[Sales Amount] } ON COLUMNS,

 NON EMPTY { ([Order Date].[Date Key].[Date Key].ALLMEMBERS *

 [Dim Sales Territory].[Sales Territory
Key].[Sales Territory Key].ALLMEMBERS *

 [Dim Customer].[Customer Key].[Customer Key].ALLMEMBERS )

 }

 DIMENSION PROPERTIES MEMBER_CAPTION,
MEMBER_UNIQUE_NAME ON
ROWS FROM

 (

 SELECT

 (

 STRTOSET(@DimSalesTerritorySalesTerritoryKey, CONSTRAINED)
) ON COLUMNS

 FROM ( SELECT ( STRTOMEMBER(@FromOrderDateFullDateAlternateKey,
CONSTRAINED) :

 STRTOMEMBER(@ToOrderDateFullDateAlternateKey,
CONSTRAINED) ) ON
COLUMNS

 FROM
[Adventure Works DW]

 )

 ) CELL PROPERTIES VALUE,

 BACK_COLOR,

 FORE_COLOR,

 FORMATTED_VALUE,

 FORMAT_STRING,
FONT_NAME, FONT_SIZE,
FONT_FLAGS

 

 

Another thing to note here is that the PARAMETERS should be in the correct SEQUENCE everywhere.

We can use the following method (right click) to move a filter UP/DOWN to make it in the correct sequence.

Also, the sequence should be the same everywhere i.e. in the Parameters tab of the DATASET properties as well as the report itself.

Conclusion

Report builder is a very efficient tool for realizing self-service BI reporting. For using this tool to generate reports based on multi-dimensional cubes on the fly, one does not need to possess knowledge of MDX/DAX or, any other multi-dimensional query language. This tool can be downloaded freely from the following location.

https://www.microsoft.com/en-in/download/details.aspx?id=6116