MultiDataProvider Specific Measures

[former_member402770]

Hi All,

I have an tricky requirement below:

I have merged common dimensions dim1,dim2, dim3 from three bex queris as one.

Now i have below where Products belong to query 1 dataprovider name,  Services belong to query 2 dataprovider name..

dataprovider([Query1].[Dim1]) = Products

dataprovider([Query2].[Dim2]) = Services

dataprovider([Query3].[Dim3]) = Support

Query/datpdr Name      Common Dimensions      Measures

Products query1          =   Dim1                          Sales Amt

Services query2         =   Dim2                           Sales Amt

Support   query3         =   Dim3                           Sales Amt

My reports has to display 2 columns consist of one merged dimensions and dataprovider specific measures as measures cannot be merged:

Merge(Dim1+Dim2+Dim3)       Products query1.SalesAmt

Merge(Dim1+Dim2+Dim3)       Services query2.SalesAmt

Merge(Dim1+Dim2+Dim3)       Support   query3.SalesAmt

In Screenshot:

Apperciate your help.

Thanks,

Dinya.

[former_member402770 · Accepted solution]

Hi,

Any thoughts on this am i making understandable what is the reqd and issue i have.

Appreciate your help.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

in first column use merged dimension.For second measured column use IF else condition to display all three measures in one column against of merged dimension.

Var=If(merged dim=Dim1) then   Products query1.SalesAmt elseIf(merged dim=Dim2) then Services query2.SalesAmt else Support   query3.SalesAmt

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Not sure i am making you clear or not.

I have three bex reports:  Products, Service, Support

Product                  Service                       Support

PftCtr                     PftCtr                          PftCtr 

Plant                      Plant                          Plant

SalesAmt               SalesAmt                   SalesAmt

I merged  PftCtr and Plant but we couldnt merge measures that we know..

So My Output:

Merge PftCtr         Merged Plant          SalesAmount?

From above how to show measures belonging to Products and Service and Support in one Single Column.

Thanks,

Dinya.               

[amitrathi239 · Accepted solution]

Hi,

try with this.

Var=If(Merge PftCtr=query1.PftCtr) then query1.SalesAmount elseIf(Merge PftCtr=query2.PftCtr) then query2.SalesAmount else query3.SalesAmount

Amit

[amitrathi239 · Accepted solution]

Hi,

try this.

in the measured columns put objects like this.

=query1.SalesAmt+query2.SalesAmt+query3.SalesAmt

See attached screenshot.

[former_member402770 · Accepted solution]

Hi Amit,

Thanks you got my understanding now, yeah what if if add fiscal month as merged column to the year and sales revenue columns whuch means

Year    Month   Salesrevenue

From above will it show Salesrevenue belonging to Year and Month Column.

So the point is we donot need dataprovider/querynames to pull the measures of its own. We can do this to the associated merged dimension itself.

Thanks,

Dinya