When I need to display data from a cube on a web page, I usually store the MDX query in a text file and make this file part of a resource file. This allows me to store the MDX in a readable format. In the image below, I have two files in the App_GlobalResources folder. The first [...]
MDX
ParallelPeriod & PeriodsToDate
The query below returns the reseller sales for Jan 2007 and Jan 2008. SELECT { [Measures].[Reseller Sales Amount] } ON COLUMNS, { [Date].[Calendar].[Month].[January 2007], [Date].[Calendar].[Month].[January 2008] } ON ROWS FROM [Adventure Works] To determine the sales amount for the same month last year, use the ParallelPeriod function. ParallelPeriod( [Date].[Calendar].[Calandar Year] — Level, 1, — Numeric [...]
MDX Member Key
When writing MDX queries that will be consumed programmatically it is useful to have the identity keys of each member. Take the example below, you could program a datatable or cellset and use the product name to select/identify data however this have problems. For one the product name could change or there may be more than one [...]
Current Date, StrToMember & StrToSet
The majority of MDX queries I write return figures for the previous quarter or the last six weeks (or some variation that involves starting from today and going back in time so many units). In order to create these reports I first need to get the current date and then work that into the query. The MDX [...]
