SSRS / MDX - Extra row for reversal charge

Question

I have a financial report in SSRS that runs off an MDX query. The report is run, exported to Excel, then loaded into a financial system, like SAP.

I need to include a "reversal" row, for charges that fall in a certain category. For example (below) for anything with a "Type" of "Product Freight", I also want to include an EXTRA row, with a NEGATIVE value, essentially reversing the charge in the report.

This:

| Charge           | Account | Type            | Invoice | GST |
|------------------|---------|-----------------|---------|-----|
| Apple            | 123     | Product         | $100    | $10 |
| Banana           | 123     | Product         | $200    | $20 |
| Orange           | 456     | Product         | $150    | $15 |
| Orange (Freight) | 456     | Product Freight | $50     | 0   |

Would become this:

| Charge           | Account | Type            | Invoice | GST |
|------------------|---------|-----------------|---------|-----|
| Apple            | 123     | Product         | $100    | $10 |
| Banana           | 123     | Product         | $200    | $20 |
| Orange           | 456     | Product         | $150    | $15 |
| Orange           | 456     | Product Freight | $50     | 0   |
| Orange (Freight) | 456     | Product Freight | ($50)   | 0   |

UPDATE

This is a simple version of the MDX query:

WITH MEMBER measures.[Charge] AS
  [Charge].[Charge All].CurrentMember .member_name

MEMBER measures.[Account] AS
  [Account].[Account All].CurrentMember .member_name

MEMBER measures.[ChargeType] AS
  [Charge Type].[Charge Type Group].CurrentMember .member_name

MEMBER measures.[GST] AS
  ( [Charge Type].[Charge Type Class].&[GST], measures.[value] )

MEMBER measures.[InvExcGST] AS
  measures.[Value] - measures.[GST]

SELECT
{ 
measures.[Charge], 
measures.[Account], 
measures.[ChargeType], 
measures.[InvExcGST], 
measures.[GST] 
} 
ON 0,

NON EMPTY 
[Charge].[Charge All].[All].Children * 
[Account].[Account all].[all].Children * 
[Charge Type].[Charge Type Group].[All].Children 
HAVING measures.[Value] <> 0 
ON 1

FROM CubeName

Can you please add the current mdx? – whytheq Apr 19 '16 at 19:16 — whytheq, Apr 19 '16 at 19:16
I have updated the question with an MDX query – TaylorN Apr 19 '16 at 22:36 — TaylorN, Apr 19 '16 at 22:36

score 1 · Accepted Answer · answered May 10 '16 at 17:53

I think the following will duplicate the target rows - still unsure how to add the negative measure:

WITH 
MEMBER [measures].[Charge] AS
  [Charge].[Charge All].CurrentMember.member_name
MEMBER [measures].[Account] AS
  [Account].[Account All].CurrentMember.member_name
MEMBER [measures].[ChargeType] AS
  [Charge Type].[Charge Type Group].CurrentMember.member_name
MEMBER [measures].[GST] AS
  ( 
    [Charge Type].[Charge Type Class].&[GST]
  , [measures].[value] 
  )
MEMBER [measures].[InvExcGST] AS
  [measures].[Value] - [measures].[GST]
SET [AllCombos] AS
  NonEmpty(
     [Charge].[Charge All].[All].Children 
    *[Account].[Account all].[all].Children 
    *[Charge Type].[Charge Type Group].[All].Children 
    ,[measures].[Value]
  )
SET [AllCombosFilter] AS
  FILTER(
   [AllCombos] AS S
  ,S.ITEM(0).ITEM(2) 
      IS [Charge Type].[Charge Type Group].[Charge Type Group].&[Product Freight]
  )
SELECT
  { 
   [measures].[Charge], 
   [measures].[Account], 
   [measures].[ChargeType], 
   [measures].[InvExcGST], 
   [measures].[GST] 
  } 
  ON 0,   
NON EMPTY 
  UNION([AllCombos], [AllCombosFilter], ALL) ON 1
FROM CubeName;

SSRS / MDX - Extra row for reversal charge

1 Answers1