Databases
StackOverflow2013
Postsdbo
POCustom Aggregation/Column Concatenation Within an MDX Query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCustom Aggregation/Column Concatenation Within an MDX Query
    text
    copied!<p>I have a <code>date</code> dimension and a <code>time</code> dimension and a measure called <code>[Count - Sales]</code>. I would like to run a query that would give me the count of sales over a range of <code>time</code> on a given date, and also concatenate the <code>date</code> and <code>time</code> values and show me the start and end <code>date-time</code>. So the end results I am expecting would look something like this:</p> <pre><code>StartDateTime | EndDateTime | Count - Sales ------------------------------------------------ 20130901 12 | 20130901 16 | 4000 20130902 12 | 20130902 16 | 4123 </code></pre> <p>I have come up with a way to concatenate the date and time values, but I haven't been able to figure out a way to have the two columns <code>StartDateTime</code> and <code>EndDateTime</code>. The query I have right now gives me the <code>[Count - Sales]</code> for every hour:</p> <pre><code>WITH MEMBER Measures.DateTime AS [Date].[Date].CurrentMember.Name + " " + [Time].[Hour 24].CurrentMember.Name + ":00" SELECT {{[Date].[Date].&amp;[20131101]:[Date].[Date].&amp;[20131101]}*{[Time].[Hour 24].&amp;[10]:[Time].[Hour 24].&amp;[14]}, {[Date].[Date].&amp;[20131102]:[Date].[Date].&amp;[20131102]}*{[Time].[Hour 24].&amp;[10]:[Time].[Hour 24].&amp;[14]} } ON ROWS, {Measures.DateTime, [Measures].[Count - Sales] } ON COLUMNS FROM Sales </code></pre> <p>And the result is:</p> <pre><code>Date Time DateTime Count - Sales 20131101 10 20131101 10 206 20131101 11 20131101 11 251 20131101 12 20131101 12 318 20131101 13 20131101 13 304 20131101 14 20131101 14 300 20131102 10 20131102 10 194 20131102 11 20131102 11 251 20131102 12 20131102 12 298 20131102 13 20131102 13 329 20131102 14 20131102 14 345 </code></pre> <p>Any help/guidance would be appreciated. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload