Microsoft is working hard on pushing a SharePoint server and integrating every reporting aspect in it, from the new PivotTable to PerformancePoint server. Yet, in the same sentence, they claim that SharePoint is for medium to enterprise organizations. What if you don’t work for an enterprise size organization and you don’t install software you don’t have the licenses for?
Well, I don’t work for an enterprise size company and we really don’t have a use for the entire SharePoint server features we ate just looking for a tool to share our report, which leaves me with original reporting application that bundles with SQL Server – SSRS (SQS Server Reporting Services).
Building reports in SSRS for SSAS has never been a straightforward task, especially if you want to integrate parameters and conditional execution. It seems that with every version, the connection between SSRS and SSAS is getting stronger, yet even with report builder 3, building a report that slices the information using dimension members or sets is still a confusing task.
To successfully build an SSAS report using parameters, you should forget the work flow used for building T-SQL reports, the process must be broken into several building blocks and at the end “manually” connect between them. I have broken down the development process to the following building blocks:
Data Source – the connection to the SSAS server
Data Set –
Filed list- a list of fields returned by the query used to bind the results of the query to the report form.
Parameters – parameters are usually used to slice the results of the query. There are two major types: filters using dimension members that are presented as a checkbox tree on the report form and regular parameters such as date ranges that will be presented as text fields or datetime pickers.
Query – the actual query running the query. Due to the limitation of the query builder, you will probably have to replace it using your own MDX code.
Report designing – the actual report page displaying the results.
Creating a Data Source is very similar to a creating a SQL Data Source, I strongly suggest you create a shared connection that will be reused by all the reports in the same project. If you want to use the active directory security feature to limit the viewed data or the actual report, change the connection string on the production server to use the current user credential. While developing, either use your own user or set an admin privilege user. Also using the query designer (will be explained later) takes a considerable amount of time creating connection to the DB server and retrieving the data, so while developing I work with my development SSAS server located in the same network as my development desktop.
Report designing is also pretty straight forward and very similar to creating a T-SQL report. When you have reached the final stage of designing the report, and you find that you need to add a new calculated field, it is easier to add it to grid than to go through all the steps for adding a new field in the query designer.
The trick in the process is creating a dynamic Data Set. I usually launch the process with the query designer, using it to create all three groups. Afterwards I manually manipulate them to add the missing features and join them back together. I will create a simple report presenting the revenue of my demo publisher per month, then I will manipulate the Dataset turn it into a dynamic report that will allow the user to select the time span, certain publishers and specific countries.
Right click the Data Set folder and click add DataSet. Select the DataSource and click the query designer button (see image1). In the query designer popup window, build your query by dragging and dropping from the left side menu (presenting the SSAS DB objects). The queries designer is limited compared to what you can do with an MDX query. We will take care of that in one of the next steps; just make sure the fields you want to display in the report are selected. When you are done, click the OK button. Saving the query will create the field list that we will use later in the report. In the image below I have selected one measure revenue and two dimension member’s month and publisher from two different dimensions. The result you see is the revenue of our Demo account. Since I aim to build a dynamic report, I will use the query builder to add the dimension based parameters by dragging the dimension to the filter section and setting them as parameters using the parameter check box. Marking the parameter check box is creating the parameter and a hidden Dataset that will retrieve the values for the parameter check list form the dimension in the SSAS. At this point we are missing the date slicing parameters, you can add a manual parameter at this step, yet the query designer does not recognize a parameter defined outside the query designer. I will add parameters manually in one of the next steps.
After clicking OK, you will go back to the Dataset Properties window. In the Field tab you will see the list of the fileds we have selected in our query design (see image 2).
In the parameter tab, you will see two new parameters (see image 3) bound to our query. The parameters get their names from a dimension structure. I always change them to something more useful and readable. Click OK to close the window and save the definition.
My next step will be to add two new parameters: FromDate and ToDate. These are two regular date-picker parameters. Close the Dataset properties window and right click the parameters folder on the reports data window (left side menu). Select ‘add new’ and set the required properties. Set the name, type and default value and don’t forget the ‘never refresh’ in the advance tab. At the end, we can see a list of four parameters (see image 4).
Now, back in the Dataset properties window, we will bind them to our query. In the parameter tab, click ‘add’. Select the parameter from the dropdown menu and set a corresponding name (see image 5).
Now we are at the final step, where we manually change our query and combine the new parameters. We cannot use the query designer for the next step it will ignore our manual settings a return the configuration to the state. I have to write my own MDX query (yes, there are IT guys that know SQL Server at a DBA level, build reports in SSRS, Cubes in SSAS and knows how to write MDX queries). In order to accomplish that in the Query tab, I will edit the query by clicking the FX button (marked in red in image 6). Explaining the DMX syntax is out of the scope of this post. I simply added the following code to include the usage the date parameters:
Visual Basic
FROM ( SELECT ( STRTOMEMBER( "[TimeH].[YQMDH].[Date].&[" + Format(@FromDate, "yyyy-MM-dd") + "T00:00:00]" , CONSTRAINED) : STRTOMEMBER( "[TimeH].[YQMDH].[Date].&[" + Format(@ToDate, "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED) ) ON COLUMNS FROM …
1
FROM ( SELECT ( STRTOMEMBER( "[TimeH].[YQMDH].[Date].&[" + Format(@FromDate, "yyyy-MM-dd") + "T00:00:00]" , CONSTRAINED) : STRTOMEMBER( "[TimeH].[YQMDH].[Date].&[" + Format(@ToDate, "yyyy-MM-dd") + "T00:00:00]", CONSTRAINED) ) ON COLUMNS FROM …
After changing the query and adding parameters, you cannot update the field list. The Dataset properties window can’t cope with the manual changes, so if you are asked to update the list. Choose the cancel button; you can always do it manually later.
Now you are ready to build your report. Select the table component, then drag and drop the fields from the field list. When you execute the report, the new parameter will appear at the top of the report.
These are the basic try adding fields into the field list, adding custom set and members. Ping me if you like to hear more.
Guy R
Коментарі