troubleshooting Question

MDX: How can I reference a value outside the scope (slice) used by the query???

Avatar of jflanner
jflanner asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005
4 Comments2 Solutions757 ViewsLast Modified:
Folks:

I am new to MDX - so this is probably elementary.  I am in the credit card space - and putting together a report on declined transactions.  I want this report to function on a slice that my management finds of particular interest.  (Basically - their to 10 list.)

I have an MDX query that works.  It accesses a slice of "Clients" that are maintained in a named set in my cube.  I call that set [Clients Named Set]  below.  In my cube - the below from select down (if you remove measures.GrandCardsInvoicable) will work and return good results.

My problem is - I want to also include a count of the total number of cards we have outstanding - including those not in the slice.  (This is the GrandCardsInvoicable.)   I'm trying to do this with a named member at the top of the query - but my results always come back scoped.  The entire thing below will run - but CardsInvoicable will always be equal to GrandCardsInvoicable - which is not my goal.  I want to latter to be an absolute - all inclusive number.

My destination is SSRS so I need a single - two dimensional structure.

Thanks in advance!!!
with member [Measures].[GrandCardsInvoicable] as 
       '([Measures].[Cards Invoicable], [Date].[DateKey].&[20080630])'
SELECT
     { [Measures].[Auth Count], 
       [Measures].[Suspended Cards], 
       [Measures].[Active Cards], 
       [Measures].[Declined Amt], 
       [Measures].[Auth Amt], 
       [Measures].[Decline Count], 
       [Measures].[Cards Invoicable],
       [Measures].[GrandCardsInvoicable]  
     } 
ON COLUMNS, 
     NON EMPTY 
     { (
             [Decline Reason].[Reason Group].[Reason Group].ALLMEMBERS
       ) 
     } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME 
ON ROWS 
FROM 
( 
     SELECT 
     (     
          [Clients Named Set] 
     ) ON COLUMNS 
     FROM 
     ( 
           SELECT 
           ( 
                [Date].[DateKey].&[20080601] : 
                [Date].[DateKey].&[20080630]
           ) ON COLUMNS 
           FROM [My Cube]
     )
) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, 
FONT_NAME, FONT_SIZE, FONT_FLAGS
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros