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

Quick Poll - Please vote

What is the most important feature for you in the BIDS Helper utility?