Conditionally selected query calculation in crosstab in DMR

[Kyne]

Hi,

I've been trying to add one column, which is conditionally calculated for the gap between target sale and current sale , let's say Target Gap Sale in DMR.

the Day dimension hierarchy is [Sales Cube].[Period].[Financial Year].[Day]
and the crosstab result for Jan. 2015 is as below:


                               Current Actual Sales $         LY Actual Sales $          Difference
Thu, 01 Jan 2015    -$332                                    $180                          -284.19%
Fri, 02 Jan 2015       $373,156                              $363,481                        2.66%
Sat, 03 Jan 2015     $269,458                              $341,395                     -21.07%
-----

for this crosstab I want to add the  Target Gap Sale, which is the calculation of current actual sales for each day - targeted daily sale, which comes from another query. let's say [TargetSale] including data items for each day's target sale value.

                               Current Actual Sales $         LY Actual Sales $          Difference   Target Sale Gap
Thu, 01 Jan 2015    -$332                                    $180                          -284.19%     -832 = -332 - 500[<-target]
Fri, 02 Jan 2015       $373,156                              $363,481                        2.66%     -current sale - target sale
Sat, 03 Jan 2015     $269,458                              $341,395                     -21.07%    ---

so, as I can think of, the expression for    Target Gap Sale would be like this,

IF [Sales Cube].[Period].[Financial Year].[Day] =  'Thu, 01 Jan 2015'
THEN [TargetSale].[DataItem1]
ELSE IF ------

but, it's for relational data , not for DMR.

please guide me if anyone has any idea to achieve this goal.

thanks in advance.

[MFGF]

Hi,

I've been trying to add one column, which is conditionally calculated for the gap between target sale and current sale , let's say Target Gap Sale in DMR.

the Day dimension hierarchy is [Sales Cube].[Period].[Financial Year].[Day]
and the crosstab result for Jan. 2015 is as below:


                               Current Actual Sales $         LY Actual Sales $          Difference
Thu, 01 Jan 2015    -$332                                    $180                          -284.19%
Fri, 02 Jan 2015       $373,156                              $363,481                        2.66%
Sat, 03 Jan 2015     $269,458                              $341,395                     -21.07%
-----

for this crosstab I want to add the  Target Gap Sale, which is the calculation of current actual sales for each day - targeted daily sale, which comes from another query. let's say [TargetSale] including data items for each day's target sale value.

                               Current Actual Sales $         LY Actual Sales $          Difference   Target Sale Gap
Thu, 01 Jan 2015    -$332                                    $180                          -284.19%     -832 = -332 - 500[<-target]
Fri, 02 Jan 2015       $373,156                              $363,481                        2.66%     -current sale - target sale
Sat, 03 Jan 2015     $269,458                              $341,395                     -21.07%    ---

so, as I can think of, the expression for    Target Gap Sale would be like this,

IF [Sales Cube].[Period].[Financial Year].[Day] =  'Thu, 01 Jan 2015'
THEN [TargetSale].[DataItem1]
ELSE IF ------

but, it's for relational data , not for DMR.

please guide me if anyone has any idea to achieve this goal.

thanks in advance.

Hi,

When using dimensional sources (including DMR) there's usually no need to create multiple queries. You ought to be able to do this in one query, which should make things quite easy. Is Target Sale an existing value or does it need to be calculated? Can you explain why you put it into a different query?

Cheers!

MF.

[Kyne]

Thanks for your reply.

the thing is that target sale values are supposed to be provided by the client on a monthly basis. So, it's dynamic, not fixed figures.

I couldn't find the way to have input data without using stored procedure by framework manager, which this client doesn't have the license.
so, i'm trying to work around by adding another query with all input data inserted.

is there any other ways to fulfil this requirement?

thanks in advance.

[Kyne]

As you suggest,
I made a decision to add the feature in the cube.
I thought that it should be done in Report Studio.

Thank you.  :)

Hi,

When using dimensional sources (including DMR) there's usually no need to create multiple queries. You ought to be able to do this in one query, which should make things quite easy. Is Target Sale an existing value or does it need to be calculated? Can you explain why you put it into a different query?

Cheers!

MF.