Summing up multiple MDX queries in SSAS

i need to SUM the results of multiple queries.

the challenge that i have is that each query has defined members (to calculate a date range)

i need to be able to combine/sum those members across multiple mdx queries

WITH Member [M1] AS Sum(DateRange, Measure)

SELECT [M1]

FROM [Cube]

WHERE {[x].&[y]}



WITH Member [M1] AS Sum(Different DateRange, Measure)

SELECT [M1]

FROM [Cube]

WHERE {[z].&[q]}

each query selects the same members based on different criteria.

the only way i can think of doing this is a UNION and than SUM([M1]) but no idea how that is possible in MDX

UPDATE - in reply to icCube question, here is why i need to have a separate W开发者_运维知识库HERE clause for each query:

i need separate WHERE sections for each query because i need to aggregate the results of different slices. and my slices are defined by n number of dimensions. i emit the mdx query for each slice dynamically based on user configuration input (and construct my WHERE clause dynamically to filter by user preferences). Users are allowed to configure overlapping slices (these are the ones i need to sum up together). then I need to combine these slice row counts into a report. The way i am doing is by passing a string with MDX query to a report. but since i can't think of a way to get multiple queries into one executable string, (nor do i know how many queries there will be) this approach is no longer possible (unless there is some way to union / sum them.

The only way i could think of accomplishing this for now, is with additional batching step that will iterate through all queries, process them (using Adomd.net) into a staging table, and then i can aggregate them into a report using SQL sum(..). Biggest disadvantage to this approach being additional system to be maintained and more possibilities that the data in the report will be stale.

---

Not sure if this is what you're looking

WITH Member [M1] AS Sum(Different DateRange, ([z].&[q],Measure) ) + 
                    Sum(DateRange, ([x].&[y],Measure))

SELECT [M1]

FROM [Cube]

or

WITH Member [M1] AS Sum(Different DateRange * {[z].&[q]}, Measure ) + 
                    Sum(DateRange * {[x].&[y]}, Measure)

SELECT [M1]

FROM [Cube]

I don't know any way adding the result of two selects in MDX...

---

I believe you need Aggregate() not Sum.

---

You could implement the UNION behavior in MDX using SubCubes on this way:

Select
{...} On Columns,
{...} On Rows
From (
     Select 
            {
              {Dimension1.Level.Members * Dimension2.&[1] * Dimension3.&[2]},
              {Dimension1.&[X] * Dimension2.Members * Dimension3.&[5]}  
            } On Columns
     From [Cube]
     )