How to hide and organize SSAS measures in folders with MDX code ? And allow the possibility to group them for Analysis Services end-users? Indeed, browsing an SSAS cube from Microsoft Excel or Power BI for Ad-Hoc Reporting is very convenient.<\/p>\n\n\n\n
However, if the list of measures calculated in MDX is several tens or hundreds of items, they must be classified. Ideally, they should be classified by functional groups. And by sub-groups organised by the time axis.<\/p>\n\n\n\n
First, all measures calculated in YTD<\/a> will be close to the current value. Here is a deliberately simple but explicit example for classifying SSAS indicators in folders:<\/p>\n\n\n\n This is because financial data is often associated with the past year, the past quarter and the past month.<\/p>\n\n\n\n On the other hand, it is preferable to classify the measures in a functional way rather than in technical files. For greater usability in Reporting from cubes, the following properties are very useful:<\/p>\n\n\n\n Secondly, this example of MDX code creates a calculated indicator based on a group of measures in our “.cube” file. This group of measures usually contains one or more fields from a table previously created in the Data View named “Example_View.dsv”:<\/p>\n\n\n\n Finally, this example MDX script allows you to easily hide and organize SSAS measures into folders. Code explanation :<\/p>\n\n\n\n Finally, this short SSAS tutorial explains step by step how to organize SSAS measures in folders with a simple MDX script. To go further in Microsoft OLAP learning, here is another article on XMLA language<\/a><\/strong>.<\/p>\n\n\n\nCREATE MEMBER CURRENTCUBE.[Measures].[Area - Sales] AS [Measures].[Amount - Group],\nFORMAT_STRING = \"#,##0.00 \u20ac;-#,##0.00 \u20ac\",\nVISIBLE = 1 ,\nDISPLAY_FOLDER = 'Sales';<\/pre>\n\n\n\n
Explanation of the MDX code to structure calculated measures in a folder<\/h3>\n\n\n\n