1

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].[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
Flesym
  • 573
  • 2
  • 9
  • 18
  • Are you looking for the equivalent in MDX ? – cairnz Apr 29 '11 at 12:03
  • Yes, something like that... or any other way to solve my problem ^^ – Flesym Apr 29 '11 at 13:00
  • You are not specifying if you are querying a relational database or an OLAP cube. They are fundamentally and symantically different. – cairnz May 02 '11 at 06:58
  • When you say you want both sets of results "in one go", what do you mean? You want just one MDX query that returns everything stuck together somehow? – Magnus Smith Jun 02 '11 at 16:26

0 Answers0