Analysis services switch to multidimensional mode

Once I came to customer to implement OLAP solution I found that I am not able to deploy my OLAP XMLA database script since customer Analysis services installed on server was switched in tabular mode and my OLAP database was designed for multidimensional mode. This post is not focused on difference between tabular and multidimensional mode. But in short. Analysis services supports tree modes. Multidimensional, Pivot for Sharepoint and Pivot mode. Mode is set trough installation process. Here I come with quick “how to” switch from tabular to multidimensional mode. Hopefully it saves time to you when you come to the same situation.

In short:

  1. Edit config msmdsrv file in SSAS installation directory
  2. Restart SSAS service

Go to SSAS installation directory, in my case it was situated on C:\Program Files\Microsoft SQL server\MSAS14.MSSQLServer\OLAP\Config\ and find msmdsrv configuration settings file. Open it in notepad or another texts editor.

SSAS config file path
Picture 1 – SSAS config file path

Find DeploymentMode tag and change it to 0. 2 is for Tabular mode,0 for multidimensional mode, 1 for Pivot for Sharepoint.

SSAS config file path
Picture 2 – SSAS config file path

After you finish editing configuration file restart SSAS service. You can do it through Windows->Services or using SQL Management Studio as shown on picture bellow.

SSAS config file path
Picture 3 – SSAS config file path

Parametrized group Expand/Collapse Tablix Report

In one of previous post I wrote about expand, collapse groups and how useful feature for Tablix component it is. User can expand data of each individual group member to its detail. But in case user would like to expand all group members, the solution becomes inconvenient for fast work with reporting. User must go and click on each member of group to expand data. In case that child groups are set to have the same toggle functionality, collapsed by default, it could become a nightmare for user to work with such a report. Fortunately, you can use parameters to handle Expand/Collapse state of groups. In this post I will show you approach I use in my projects.

Steps in short:

  1. Define report parameter with member correspondent to groups in Tablix
  2. Define report variables connected to the parameter and returning true or false for visibility of each groups
  3. Set group visibility property based on expression
  4. Set toggle item default state based on expression

Create report parameter first. Let’s name it Level, as group level user would like to expand. Go to Parameters Pane, right click above Parameters item and click Add new parameter. In Report Parameter Properties dialog set name and Integer as Parameter Type in general tab. Then go to Available Values tab and set Specify values checkbox – type values user can select. I set values that mean group levels – for our report: Region, Province, Name with values from 0 to 2. The Integer values mean level of group hierarchy. You will see their usage later in this post.

Report data, Parameters
Picture 1 – Report data, Parameters
Report Parameter Properties
Picture 2 Report Parameter Properties

As default values type 0 to choose Region group level by default. It means that our report will be Collapsed by default.

Report Parameters, Specify values
Picture 3 – Report Parameters, Specify values

Right click in Report Builder design surface near to the Tablix component and select Report Properties from popup. Then go to Variables tab and Add 3 variables corresponding to parameter values I set in previous step: Region, Province, Name. I would prefer to use variables that it can be easily manageable on one place, but solution could be implemented directly through expressions. Furthermore, solution with variables could help performance of the report rendering too (see in next posts). To Value items set to them expression which returns true or false value, based on level returned from selected parameter Level. This means when user selects parameter value, these variables calculate visibility for correspondent group.

Report Properties Variables
Picture 4 – Report Properties Variables

Here are expressions you put to variable values:







On picture bellow there are defined each variables.

Report Properties, Variables
Picture 5 – Report Properties, Variables

To define expression for each variable, click on expression icon on the right side of input box for Variable and define expression.

Report Properies, Variables, Expression
Picture 6 – Report Properies, Variables, Expression

Now we set visibility for each group. Because EnglishCountryRegionName name group is default group, it will be visible when user runs report by default. We set Visibility setting for this group to Show. Go to the next group, StateProvinceName group, click to its group area, and set Region variable value to the group visibility property. You can get it by two ways:

Right click on the group StateProvinceName name in the Grouping pane, Visibility section and Show or hide based on expression.

Or you can do it directly in the Properties Pane. When you click on the group in Grouping pane, Hidden property on Properties Pane and set expression value Variables!Region.Value.

Group Properties, Visibility
Picture 7 – Group Properties, Visibility

I set Region variable value to Show or hide based on expression of StateProvinceName group.

Show or Hide based on expression
Picture 8 – Show or Hide based on expression

Let’s go to the Level parameter and choose the Region value. On the picture bellow you can see that all groups that are under the Region group in the hierarchy, are expanded. Now you can do the same steps for LastName group.

But there is one thing which should be resolved. Look at picture bellow at Expand/Collapse mark which is set to +. But the state of the group is expanded. Follow next steps to fix it.

Sales Report
Picture 9 – Sales Report

On the picture bellow there is highlighted to which toggle items put bellow mentioned expressions in the Tablix. Each toggle item has its own InitialToggleState since it is standard report item.

Toggle Items in Report Tablix
Picture 10 – Toggle Items in Report Tablix

For the StateProvinceName group, there was set EnglishCountryRegionName Textbox as toggle item. (it was described in post Expand collapse groups) Click on EnglishCountryRegionName textbox, Go to Properties Pane, Visibility section and set to InitialToggleState expression =CBool(1+CInt(Variables!Region.Value)).

InitialToggleState, Expression
Picture 11 – InitialToggleState, Expression

After confirmation you should see the same in Properties Pane of EnglishCountryRegionName textbox, as on picture bellow.

Picture 12 – InitialToggleState

Now you should see fixed Expand/Collapse mark on picture bellow.

Sales Report, Expand
Picture 13 – Sales Report, Expand

Do the same steps for LastName group on StateProvinceName textbox with following expression =CBool(1+CInt(Variables!Province.Value)). On LastName textbox we do not need to set expression, since it is not toggle item of any groups.

Let’s try report now, change Level parameter values. See pictures bellow how report layout changes.

Sales Report
Picture 14 – Sales Report
Sales Report
Picture 15 – Sales Report
Sales Report
Picture 16 – Sales Report


Expand collapse groups

Expand, collapse functionality is nice feature of Reporting Services. In this post we use this functionality in connection with Tablix component. Be aware that more SSRS components support this functionality and it is good approach how to make reports and dashboards more user-friendly. It’s also a way how to make top to bottom analysis, since user can get data from totals to details by few clicks.

For practice you can use any of your report where you have Tablix or Matrix component with few groups. Or you go to one of my previous posts where you can download sample grouped report.

Lets have report or similar report as on picture bellow. So we have groups and detail data and our task is to prepare user view per Region Name by default with posibility to go to data on lower group level. In short we have to do 2 steps:

  1. Create toggle item in Tablix, which allow user to do Expand/Collapse action
  2. Hide groups at lower level of group hierarchy to see only Region Name when runing report by default

Lets have report or similar report as on picture bellow.

Sales report
Picture 1 Sales report

To set toggle item, click on group from which you would like to Expand/Collapse data. As you see on picture bellow, we would like to toggle EnglishCountryRegionName, so we have to go to StateProvinceName group property and there set toggle item. It is important that toggle item is textbox outside of the group we would like to Expand/Collapse (otherwise SSRS displays an error when rendering).

Tablix Toggle item
Picture 2 Tablix Toggle item

Right click on StateProvinceName group in Grouping pane and Properties from popup menu.

 Grouping Pane
Picture 3 Grouping Pane

Go to Visibility tab in Group Properties dialog and here we check Display can be toggled by this report item.

Group Visibility
Picture 4 Group Visibility

On picture bellow there are shown possible options you can set when managing Expand/Collapse functionality.

  1. For Expand/Collapse lets define toggle item. From select box lists EnlishCountryRegionName, it is text box we will use for Expand/Collapse functionality.
  2. Show – if we would like to have group expanded by default let’s leave it checked
  3. Hide – hides group by default.
  4. Show or hide based on an expression – I will explain in more detail in next posts.
Group Properties - Group visibility
Picture 5 Group Properties – Group visibility

For our scenario, Group Property dialog should look as on picture bellow. Check Hide, check Display can be toggled by this report item and select Report Item.

Group Properties
Picture 6 Group Properties

Group visibility and toggle item can be set also through Properties

Pane as shown on picture below. Click on Grouping Pane on StateProvinceName group. In Properties Pane set properties in Visibility section, Hidden and ToggleItem.

Properties Pane
Picture 7 Properties Pane

TIP: toggle item sign has “+” character by default. If you would like to change it go to your report item, which you set as toggle. In our case it is textbox EnglishCountryRegionName in the Tablix. In Properties

Pane you can change the default initial state sign trough InitialToggleState property.

Properties Pane Visibility
Picture 8 Properties Pane Visibility

Finally, you should get your Tablix design looks like on the report bellow. Data are collapsed at Region name level. User can go the detail by expand toggle item situated on the left side of the report.

Sales report Collapsed
Picture 9 Sales report Collapsed

After clicking on Australia item you expand all data hidden under Australia region group.

Sales report Expanded
Picture 10 Sales report Expanded

In next post we extend our solution by parametrized Collapse/Expand group.