SSAS-Info.com - Microsoft SQL Server Analysis Services (SSAS) 2005/2008 resource hub
SSAS FAQ MDX FAQ
MDX FAQ

List of frequently asked questions (FAQ) and answers releated to building MDX queries in Microsoft SQL Server Analysis Services 2005. Most of the code here was tested on Adventure Works database that comes with SQL Server.
Filter     Display # 
# Article Title Author
1 How to calculate YTD monthly average and compare it over several years for the same selected month Thomas Ivarsson
2 MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category Deepak Puri
3 How do I find the bottom 10 customers with the lowest sales in 2003 that were not null? Thomas Ivarsson
4 How do I create a Rolling 12 Months Accumulated Sum that can show a trend without seasonal variations Thomas Ivarsson
5 How do I group dimension members dynamically in MDX? Thomas Ivarsson
6 How do I check for the top level in a dimension to avoid division by an nonexsting parent(-1.#INF) Thomas Ivarsson
7 How do I check for division by zero and null to avoid -1.#INF in cells Thomas Ivarsson
8 How in MDX query can I get top 3 sales years based on order quantity? Vidas Matelis
9 How in the report can I order date dimension members in descending order? Vidas Matelis
10 MDX-When I process cube, I can see that data is loaded, but when I query data, all measures are empty Vidas Matelis
11 MDX-How can I change currency symbol based on selected currency dimension member Vidas Matelis
12 MDX-How can I get attribute key with MDX Ramunas Balukonis
13 MDX-How do I calculate sales for 12 Month to date? Vidas Matelis
14 MDX-How do I clear Analysis Services (SSAS) database cache? Vidas Matelis
15 MDX-How do you format or round KPI expression value? Vidas Matelis
16 MDX-How to create calculated member for AVG sales over last 3 years based on NOW()? Vidas Matelis
17 MDX-How can I get Last (Previous) Year to Date (YTD) values? Vidas Matelis
18 MDX query to get sales by prod line for period plus number of months with NON EMPTY sales? Vidas Matelis
19 MDX-How Can I define SCOPE in MDX for all measures in multiple measuregroups? Vidas Matelis
20 MDX-How do you write query that returns measure ratio to parent value? Vidas Matelis
21 MDX-Why YDT function sometimes return incorrect results for measure with NON_EMPTY_BEHAVIOR? Vidas Matelis
22 MDX-Why simple calculated measure works much slower than real measure. Vidas Matelis
23 MDX-I would like to create calculated measure that instead of summing children amounts,uses last child Vidas Matelis
24 MDX-How do you write query that uses execution date/time (NOW()) as a parameter? Vidas Matelis
25 MDX-How do you calculate monthly average of one year, optionally including empty months? Vidas Matelis
26 MDX-How can I compare members from different dimensions that have the same key values? Vidas Matelis
27 MDX-How do you make calculated measure to be default cube measure Vidas Matelis
28 MDX-How can I setup default dimension member in cube calculation script? Vidas Matelis
29 MDX-How Can I define SCOPE statement for all but one measures in one measure group Vidas Matelis
30 MDX-How Can I define SCOPE statement for all measures in one measuregroup Vidas Matelis
31 MDX-Query to get count of months with sales amount > 0 in defined period Vidas Matelis
32 MDX-Query to get sales by product line for specific period plus number of months with sales Vidas Matelis
33 MDX-Query example that returns list of months from start of year up to specified month Vidas Matelis
34 MDX-Query to get latest months and previous years same months data Vidas Matelis
35 MDX-How to execute query from SQL Server Vidas Matelis
36 MDX-Query to show year level data for all years except last one, and month level data for last year Vidas Matelis
37 MDX-Query to show the first day of the last date month in the cube Vidas Matelis
38 MDX-Query to get the first month of the last year loaded into a cube Vidas Matelis
39 MDX-query to get the last Month loaded into a cube Vidas Matelis
40 MDX-Query to show totals of a set with just some members Vidas Matelis
41 MDX-How to use the Filter function in a SCOPE statement Vidas Matelis
42 MDX-How to create a SCOPE statement based on the Member property Vidas Matelis
43 MDX-How do you specify SCOPE For Dimension to include all members before certain member Vidas Matelis
44 MDX-How do you specify SCOPE For Dimension to include all members after certain member Vidas Matelis
45 MDX-How can I use dimension members/measures in query when sometimes they might not exists Vidas Matelis
46 MDX-How do you compare dimension level name to specific value Vidas Matelis
47 MDX-How do you get Last month in the time dimension Vidas Matelis
48 MDX-How do you extract first tuple from the set? Vidas Matelis
49 MDX-How do you extract first member from the tuple? Vidas Matelis