If you have a SSAS tabular cube with a large number of measures in a particular measure group, you may want to group specific measures into display sub-folders. Display sub-folders help keep a cube more organized and reduce the amount of scrolling end-users need to do to locate the measures they want to add to a pivot table in Excel. As far as I am aware, there is no option to create a sub-folder from the SQL Server Data Tools GUI (without installing a 3rd party tool such as BIDS Helper). Fortunately, you can create display sub-folders without the need to install a 3rd party tool by following the steps listed below. Thanks to OpenSourceSQL.com for the original blog post on this topic.
- Right click the Model.bim file for your solution and choose View Code. You’ll be presented with the XML behind the model.
- Find the <CalculationProperty> section related to the measure you want to add to a sub folder. The actual tag where the measure is named is <CalculationReference>.
- The XML tag that adds a sub folder is <DisplayFolder>…</DisplayFolder>. Add this inside the <CalculationProperty> tag and enter the desired folder name.
- When you connect to the cube in Excel and add a pivot table, the measure should now be displayed in the appropriate sub folder.
