Create two results based on Dimension Member value

[erwink]

Hi there

I've a DRM query with Employee, NbrHours per Segment, per month. Segment is part of a dimension.

I was asked to create a crosstab with

                |   Month
                | Segment 1  |  All other Segment
---------------------------------------------------
employee | val 1           | val2

I've tried everithing.

dataItem  if([Dimensions].[VCRPortfolio].[VCRPortfolioH].[Segment])=[CostCenter]) then ([ActualHoursIndirect]) else (0)
than with filter
did also try with two queries and Join

It looked so easy first :-(

Thank you
erwin

[MFGF]

Hi there

I've a DRM query with Employee, NbrHours per Segment, per month. Segment is part of a dimension.

I was asked to create a crosstab with

                |   Month
                | Segment 1  |  All other Segment
---------------------------------------------------
employee | val 1           | val2

I've tried everithing.

dataItem  if([Dimensions].[VCRPortfolio].[VCRPortfolioH].[Segment])=[CostCenter]) then ([ActualHoursIndirect]) else (0)
than with filter
did also try with two queries and Join

It looked so easy first :-(

Thank you
erwin

Hi,

DRM? Do you mean you are using a DMR package? If so, you have a whole raft of dimensional functions and expressions that could help you here.

Getting the ActualHoursIndirect value for the CostCenter segment should be really simple. Drop the CostCenter member into your column heading below Month, and put in ActualHoursIndirect as the default measure.
Getting the ActualHoursIndirect value for the other segments is not too hard. Create a query calculation that returns all the "other" segments using the Except() function, then aggregate your measure within this set of members

eg aggregate(currentMeasure within set except([Dimensions].[VCRPortfolio].[VCRPortfolioH].[Segment], [CostCenter]))

Drop this into your crosstab as the other column heading below Month.

Cheers!

MF.

[erwink]

Hi MFGF

Yes DMR :-)

Manny thank for your help. So easy...gosch. The except was the one .Grr

erwin