Multi select on same MDX query
First, my knowledge of MDX is very limited
Basically, I have 2 queries; one with HQ sales per departments and one with stock room sales per departments. I would like to return the results of the department sales of the stock room followed by the department sales of the HQ in one go.
Thanks in advance.
In SQL, the query should be something like this:
SELECT StockRoom.Code, StockRoom.Sales, StockRoom.Department, StockRoom.HQ
, (SELECT SUM(HQ.Sales)
FROM StockRoom HQ
WHERE HQ.Department = StockRoom.Department
AND HQ.HQ = StockRoom.HQ) as HQSales
FROM StockRoom StockRoom
WHERE Week = 12
AND Year = 2011
AND Code = 'C001'
EDIT: I'm working on a OLAP Cube using SQL Server Reporting Services (Visual Studio 2k8R2). Here is my current MDX query:
SELECT NON EMPTY { [Measures].[CATTC], [Measures].[CATTC N-1], [Measures].[PROG] }
ON COLUMNS
, NON EMPTY { ([Stock room].[Stock room class Enseigne].[Stock room class Enseigne].ALLMEMBERS * [Stock room].[Stock room geographical dynamic hierarchy]开发者_JAVA百科.[Stock room].ALLMEMBERS * [Product].[Product Hierarchy].[Product main class level 3].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( STRTOSET(@TimecalendarTimecalendarweekofyear, CONSTRAINED) )
ON COLUMNS FROM ( SELECT ( STRTOSET(@TimecalendarTimecalendaryear, CONSTRAINED) )
ON COLUMNS FROM ( SELECT ( STRTOSET(@StockroomStockroomcode, CONSTRAINED) )
ON COLUMNS FROM [CustomNextTest])))
WHERE ( IIF( STRTOSET(@StockroomStockroomcode, CONSTRAINED).Count = 1, STRTOSET(@StockroomStockroomcode, CONSTRAINED), [Stock room].[Stock room code].currentmember )
, IIF( STRTOSET(@TimecalendarTimecalendaryear, CONSTRAINED).Count = 1, STRTOSET(@TimecalendarTimecalendaryear, CONSTRAINED), [Time calendar].[Time calendar year].currentmember )
, IIF( STRTOSET(@TimecalendarTimecalendarweekofyear, CONSTRAINED).Count = 1, STRTOSET(@TimecalendarTimecalendarweekofyear, CONSTRAINED), [Time calendar].[Time calendar week of year].currentmember ) )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
精彩评论