Unable to join dimensions & measures from 2 diff context in single table

[Former Member]

Hello,

I am a newbie in the BO & Webi arena and have an issue in merging dimensions & measures from two different contexts(in universe) in a single table.

I tried using ForceMerge but dint work out.

Heres my scenario,

query 1- Fiscal Yr, Month, Physician,Metric,Rate

query 2- Fiscal Yr, Month,Metric,Rating Scale,lower limit,upper limit

So, i have to ceate a report involving Fiscal Yr, Month, Physician,Metric,Rate & Rating Scale.

For each 'Metric' there is specific range which defines its 'Rating Scale'.

Ex. Tetanus Vaccine(Metric) has a the following scale

Lower limit Upper Limit Rating Scale

0 69 0

70 89 1

90 90 2

91 95 3

95 100 4

The value of 'Rate' lies in between these values and accordingly it should get the scale.

So, the report should be like

fiscal yr Month Physician Metric Rate Scale

Any help will be highly appreciated!

Thanks in advance.

[Former Member]

Have you tried merging the dimensions fiscal year, month, and metric(if a dimension)?

Thanks

[amitrathi239]

Hi,

You can try like this.

1) Fiscal Yr, Month,Metric are coming in the both query.So that you can merge them in the webi report.

2) Make the 'Rating scale' as detail object for Metric object as this will specify the range for metric.

3) then drag objects in the report and see you are getting results in the report.

Thanks,

Amit

[Former Member]

Hello wavery,

Yes I did. Metric is a Dimension. .Also, I have merged the two queries onto the common dimensions and even after that when i tried to include the 'Rating Scale' in the table it throws a #CONTEXT error.

I assume this is because since 'Physician' is the uncommon dimension its giving this error. Pls correct me if I am wrong.

Thanks!

Edited by: VickyBobj on Jun 16, 2011 5:05 PM

[amitrathi239]

Hi,

one qns then rating scale is dimension?. if its data-type is number then you can try to make as a measure object and then use in the report.

Or it is possible to make rating scale object as a detail object for any dimension which is used in the query.

Thanks,

Amit

[Former Member]

Hello Amit,

I tried the thing that you mentioned that create 'Rating Scale' as a detail object of the 'Metric' but its giving a #MULTIVALUE error.

Also, 'Rating Scale' is a measure. Its a number and the value can be either 0,1,2,3,4 depending upon the 'Rate' of the Metric'.

For Ex. Tetanus Vaccine is a Metric and if the rate of the same is suppose 98% for a month then according to the range for the metric it should show a Rating Scale of 4.

Hope you got what I am trying to convey.

Thanks,

[Former Member]

Hi Vicky,

It could be the Physician dimension, but not sure. Do you have access to the universe where you can adjust the contexts? This may fix it.

Also, play around with the objects that are giving you a problem. Change the properties of "Metric" and Physician. Make a new report and place just the objects in question on it. See what they do. This may help you realize why they are not working on your existing report. This has helped me before.

Thanks

[Former Member]

Hi Vicky,

From what I have read in the above posts, you have merged Fiscal Yr, Month and Metric objects as they are the common dimensions. I assume "Physician" is the only other dimension among all the objects that you have listed.

In your report, you need to use the merged dimension for Fiscal Yr, Month and Metric. For Physician, you can use the dimension from query 1, as there are no other dimensions that you are planning to display.

If the above things are right, then your calculation should work correctly.

If it is not working correctly, I have a feeling that you have not merged Metric object. In case if you have any other dimensions than what is listed above, let me know.

Regards,

Srivatsa

[Former Member]

Details are meant to have a one-to-one relationship with the object that "owns" them. That's why you're getting your #MULTIVALUE.

Physician and Rating Scale will be incompatible because BusinessObjects doesn't know how to relate them. Do you know the relationship between them? Should there be a relationship between them? If so, what is it and can it be addressed in the universe?

[Former Member]

I created the universe for the same and there are two context. 'Rate' falls in context 1 along with 'Physician', FiscalYr, Month & Metric whereas the lower target, upper target & rating scale falls in the another one with FiscalYr, Month & Metric.

Hence I created two queries & merged the common dimensions- FiscalYr, Month & Metric. Also, tried to include the Rate & other measures in a single block but it gives #CONTEXT Error.