MDX with SSAS 2012 Cookbook -- Table of Contents -- MDX with SSAS 2012 Cookbook -- Credits -- About the Authors -- About the Reviewers -- www.PacktPub.com -- Support files, eBooks, discount offers and more -- Why Subscribe? -- Free Access for Packt account holders -- Instant Updates on New Packt Books -- Preface -- What this book covers -- What you need for this book -- Database Installation -- Sample Adventure Works 2012 Database -- For Creating PivotTable -- Who this book is for -- Conventions -- Reader feedback -- Customer support -- Downloading the example code -- Errata -- Piracy -- Questions -- 1. Elementary MDX Techniques -- Introduction -- Putting data on x and y axes -- Getting ready -- How to do it… -- How it works… -- There's more… -- Putting more hierarchies on x and y axes with cross join -- Skipping axes -- Getting ready -- How to do it… -- How it works… -- There's more… -- The idea behind it -- Possible workarounds - dummy column -- Using a WHERE clause to filter the data returned -- Getting ready -- How to do it… -- How it works… -- There's more… -- Optimizing MDX queries using the NonEmpty() function -- Getting ready -- How to do it… -- How it works… -- There's more… -- NonEmpty() versus NON EMPTY -- Common mistakes and useful tips -- Using the PROPERTIES() function to retrieve data from attribute relationships -- Getting ready -- How to do it… -- How it works… -- There's more… -- Basic sorting and ranking -- Getting ready -- How to do it… -- How it works… -- There's more… -- Handling division by zero errors -- Getting ready -- How to do it… -- How it works… -- There's more… -- Earlier versions of SSAS -- Setting a default member of a hierarchy in the MDX script -- Getting ready -- How to do it… -- How it works… -- There's more… -- Helpful tips -- 2. Working with Sets -- Introduction -- Implementing the NOT IN set logic.

Getting ready -- How to do it… -- How it works… -- There's more… -- See also -- Implementing the logical OR on members from different hierarchies -- Getting ready -- How to do it… -- How it works… -- There's more… -- A special case of a non-aggregatable dimension -- A very complex scenario -- See also -- Implementing the logical AND on members from the same hierarchy -- Getting ready -- How to do it… -- How it works… -- There's more… -- Where to put what? -- A very complex scenario -- See also -- Iterating on a set in order to reduce it -- Getting ready -- How to do it… -- How it works… -- There's more… -- Hints for query improvements -- See also -- Iterating on a set in order to create a new one -- Getting ready -- How to do it… -- How it works… -- There's more… -- Did you know -- See also -- Iterating on a set using recursion -- Getting ready -- How to do it… -- How it works… -- There's more… -- Earlier versions of SSAS -- See also -- Dissecting and debugging MDX queries -- Getting ready -- How to do it… -- How it works… -- There's more… -- Useful string functions -- See also -- 3. Working with Time -- Introduction -- Calculating the YTD (Year-To-Date) value -- Getting ready -- How to do it... -- How it works... -- There's more... -- Inception-To-Date calculation -- Using the argument in the YTD() function -- Common problems and how to avoid them -- YTD() and future dates -- See also -- Calculating the YoY (Year-over-Year) growth (parallel periods) -- Getting ready -- How to do it... -- How it works... -- There's more... -- ParallelPeriod is not a time-aware function -- See also -- Calculating moving averages -- Getting ready -- How to do it... -- How it works... -- There's more... -- Other ways to calculate the moving averages -- Moving averages and the future dates -- Finding the last date with data -- Getting ready -- How to do it...

How it works... -- There's more... -- See also -- Getting values on the last date with data -- Getting ready -- How to do it... -- How it works... -- There's more... -- Format members on the Date dimension properly -- Optimizing time-non-sensitive calculation -- Calculating today's date using the string functions -- Getting ready -- How to do it... -- How it works... -- There's more... -- Relative periods -- Potential problems -- See also -- Calculating today's date using the MemberValue function -- Getting ready -- How to do it... -- How it works... -- There's more... -- Using the ValueColumn property in the Date dimension -- See also -- Calculating today's date using an attribute hierarchy -- Getting ready -- How to do it... -- How it works... -- There's more... -- Member Yes as a default member? -- Other approaches -- See also -- Calculating the difference between two dates -- Getting ready -- How to do it... -- How it works... -- There's more... -- Dates in other scenarios -- The problem of non-consecutive dates -- See also -- Calculating the difference between two times -- Getting ready -- How to do it... -- How it works... -- There's more... -- Formatting the duration -- Examples of formatting the duration on the web -- Counting working days only -- See also -- Calculating parallel periods for multiple dates in a set -- Getting ready -- How to do it... -- How it works... -- There's more... -- Parameters -- Reporting covered by design -- See also -- Calculating parallel periods for multiple dates in a slicer -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- 4. Concise Reporting -- Introduction -- Isolating the best N members in a set -- Getting ready -- How to do it... -- How it works... -- There's more... -- The top N member is evaluated in All Periods, not in the context of the opposite query axis.

The top N member will be evaluated in the context of the slicer -- Use a tuple in the third argument of the TopCount() function to overwrite the member on the slicer -- Testing the correctness of the result -- Multidimensional sets -- TopPercent() and TopSum() functions -- See also -- Isolating the worst N members in a set -- Getting ready -- How to do it... -- How it works... -- There's more... -- See also -- Identifying the best/worst members for each member of another hierarchy -- Getting ready -- How to do it... -- How it works... -- There's more... -- Support for the relative context and multidimensional sets in SSAS frontends -- See also -- Displaying few important members, others as a single row, and the total at the end -- Getting ready -- How to do it... -- How it works... -- There's more... -- Making the query even more generic -- See also -- Combining two hierarchies into one -- Getting ready -- How to do it... -- How it works... -- There's more... -- Use it, but don't abuse it -- Limitations -- Finding the name of a child with the best/worst value -- Getting ready -- How to do it... -- How it works... -- There's more... -- Variations on a theme -- Displaying more than one member's caption -- See also -- Highlighting siblings with the best/worst values -- Getting ready -- How to do it... -- How it works... -- There's more... -- Troubleshooting -- See also -- Implementing bubble-up exceptions -- Getting ready -- How to do it... -- How it works... -- There's more... -- Practical value of bubble-up exceptions -- Potential problems -- See also -- 5. Navigation -- Introduction -- Detecting a particular member in a hierarchy -- Getting ready -- How to do it... -- How it works... -- There's more... -- Important remarks -- Comparing members versus comparing values -- Detecting complex combination of members -- See also -- Detecting the root member.

Getting ready -- How to do it... -- How it works... -- There's more... -- The scope-based solution -- See also -- Detecting members on the same branch -- Getting ready -- How to do it... -- How it works... -- There's more... -- The query-based alternative -- Children() will return empty sets when out of boundaries -- Various options of the Descendants() function -- See also -- Finding related members in the same dimension -- Getting ready -- How to do it... -- How it works... -- There's more... -- Tips and trick related to the EXISTING keyword -- Filter() versus Exists(), Existing(), and EXISTING -- A friendly warning -- See also -- Finding related members in another dimension -- Getting ready -- How to do it... -- How it works... -- There's more... -- Leaf and non-leaf calculations -- See also -- Calculating various percentages -- Getting ready -- How to do it... -- How it works... -- There's more... -- Use cases -- The alternative syntax for the root member -- The case of nonexisting [All] level -- The percentage of leaf member values -- See also -- Calculating various averages -- Getting ready -- How to do it... -- How it works... -- There's more... -- Preserving empty rows -- Other specifics of average calculations -- See also -- Calculating various ranks -- Getting ready -- How to do it... -- How it works... -- There's more... -- Tie in ranks -- Preserving empty rows -- Ranks in multidimensional sets -- The pluses and minuses of named sets -- See also -- 6. Business Analytics -- Introduction -- Forecasting using the linear regression -- Getting ready -- How to do it... -- How it works... -- There's more... -- Tips and tricks -- Where to find more information? -- See also -- Forecasting using the periodic cycles -- Getting ready -- How to do it... -- How it works... -- There's more... -- Other approaches -- See also.

Allocating the nonallocated company expenses to departments.