LookupCube

The following is the MDX query for the report

WITH MEMBER

Measures.[TreatmentCount]

AS

'LookupCube(

"Patient Hospital and Drug",

"(" + MemberToStr(AgencyID.CurrentMember) + ", [Measures].[Pharmacy DW Count])"

)'

SELECT

{ Measures.[TreatmentCount]} ON COLUMNS,

NON EMPTY { ([Agency Id].[Agency Id].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Last Six Months Pharmacy DW])) WHERE ( IIF( STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED).Count = 1, STRTOSET(@.LastSixMonthsDrugDrugName, CONSTRAINED), [Last Six Months Drug].[Drug Name].currentmember ), IIF( STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED).Count = 1, STRTOSET(@.LastSixMonthsDrugProtocolCode, CONSTRAINED), [Last Six Months Drug].[Protocol Code].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The MDX query is based on the two OLAP cubes, "Patient Hospital and Drug" and "Last Six Months Pharmacy DW". The second OLAP cube is used to fined out all the "Agency ID" for a selcted "Drug" and "Protocol Code" prescriped to a patient at least once for the last six months from today date. Then apply these set of "Agency ID" to filter out the entire warehouse data i.e. the first OLAP cube and find out the total count of the treatment for the patient for this selected "Drug" and "Protocol Code".

OLAP cube:Patient Hospital and Drug

Measures: Pharmacy DW

Pharmacy DW Count

Dimension: Agency Id

Agency Id

Agency Id

Dimension: Drug

Drug Name

Drug Name

Dimension: Drug

Protocol Code

Protocol Code

The second OLAP cube has the same structure as the first one and it has only the last 6 months data (filter out in the data source view). The data warehouse is updated and the OLAP cube is re-build daily.

Thanks

Maybe I'm missing something, but what's your question in this case - does the MDX query work as expected?