Merge on dimensions[not measures]

[Former Member]

I am getting data from two different universes. I am trying to merge these two results based on a common dimension. Universe is not letting me do this. it's disabling one universe data elements when I select another universe elements in the report. Is it possible to Merge dimensions or BObj allows merging only measures?

query1:

Staff Name Service Name

query2:

Staff Name Activities Name

query 1 brings different rows from quey2 for same staff, but staff name is same. can these two be merged?

Thanks for any info on this.

[Former Member · Accepted solution]

Universe doesn't allow merging. You will have to do this in report.

So create 2 Queries in Report.

There is nothing like merge of measures. Only merge of dimension is allowed.

You can select the dimensions(Ctrl+Click) you want and click on the merge dimension option to merge the dimensions.

Anil

[Former Member · Accepted solution]

Sorry!! Poor choice of words..When I said Universe is not letting me merge, I really meant WEBi reports is not letting me merge data from these two queries. Sorry about that.

So, how do you do this in reports (I am guessing when you said reports, it's WEBi)? in WEBi reports, if I have a result set with Measures (ike aggregated data) I can merge two queries:

eg:

      query1-->  staff_name                  total_services
                          ABC                                200

      query2--> Staff_name                  total_activities
                            ABC                                300
                            DEF                                 100

When I Merge on Staff_name, I get results like:

      Staff_name                Total_services                 Total_activities
             ABC                           200                                   300
              DEF                                                                    100

that is fine. But instead of measures , If I have all dimensions

like

              query1-->   Staff_name                   Service_description
                                    ABC                                  Personal Services

              and
               query2--> Staff_name                    Activity_description
                                    ABC                                   Phone Services

Now I want a result like:

                    Staf_name              service_description                  activity_description
                       ABC                       Personal Services                     Phone Services

Is that possible, if so how?

Hope this explanation is somewhat better than my earlier one.

[former_member142702 · Accepted solution]

This will work only if you define your dimensions as key figures in your universe.

An option is to create a copy of those dimension objects and change the type to Key figures on those copies. Set the aggregation type to Min or Max. Make sure that you switch the data type back to Character when setting those objects to be key figures.

But it will not be that easy to educate your users about this if you want to use your universe for ad-hoc reporting.

Regards,

Stratos

[Former Member · Accepted solution]

Thanks for your response. When you said KEY FIGURES, I am guessing you meant "Measures". So, I tried that as well..when I changed them to Measures, it doesn't complain or error out, but it gives null value for either one of the measures. And I cannot do Max/Min as this might bring only one value from that set([if a staf has 20 different services max will get the service type that matches maximum).

[former_member142702 · Accepted solution]

How do you want to aggregate the dimensions? If you merge then you have a way to create a single value for your measures out of multiple values. What would be it?

Regards,

Stratos

[Former Member · Accepted solution]

That is my problem I am not having any aggregates on dimension. the result set I am working on is detail level. For each staff I have a report that shows how many services they provided in a given period of time. But for that aggregated report, our users wanted details showing all those services. Hence the sticky situation. I could merge the aggregated report. Now in detail level report, how can I merge these two and show? I have an option to show them as two seperate reports.but that's the last course of action.

Thanks for all your help

[Former Member · Accepted solution]

>

>

> that is fine. But instead of measures , If I have all dimensions like

> query1--> Staff_name Service_description

> ABC Personal Services

>

> and

> query2--> Staff_name Activity_description

> ABC Phone Services

>

> Now I want a result like:

> Staf_name service_description activity_description

> ABC Personal Services Phone Services

>

> Is that possible, if so how?

Yes, it is possible.

Merge on Staff_Name from Q1 and Q2.

Then, instead of using the service_description and activity_description objects from the queries, you need to create variables and use them in your report. When you create the variables, set the qualification to be Detail and choose your Staff_Name merged field as the Associated Dimension, the formula can just be service_description or activity_description. Put the new variables in the report along with your Staff_name and that should give you your desired results.

[Former Member · Accepted solution]

For a minute, I had my hopes up with your solution but when I tried, it was giving all "#Multivalue " in both the variables

Looks like it's not working. But it's working fine in DESKi..

[Former Member · Accepted solution]

>

> For a minute, I had my hopes up with your solution  but when I tried, it was giving all "#Multivalue " in both the variables 
> 
> Looks like it's not working. But it's working fine in DESKi..

Is your data 1 to many? If so, try turning off row aggregation (though if it is you probably aren't going to get the results you want.) You can also try turning on Extend merged dimension values.

Working with merged dimensions can be VERY, VERY, VERY flaky and the behavior changes from SP to SP and at times even from FP to FP. I have to use them all the time and they drive me nuts by the lack of consistency.

[Former Member · Accepted solution]

I have some bad news for you: there is no way to create the report you want because the data doesn't fit. Or that is, you cannot put everything into one block. What you can do is create two blocks in your report and create a section on Staff Name. That will create a report like this:

STAFF ABC
Service Description 1    Activity Description 1
Service Description 2    Activity Description 2
Service Description 3
Service Description 4

By using a section, you will gather everything related to the staff member into one place. By placing the two other objects (service and activity) into separate blocks, you avoid the fact that they really have no relationship other than to the service provider. There is no relationship that will allow you to assign a service to an activity or vice versa.

But a sectioned report should work fine, and you don't have to do anything weird with aggregation or measures or anything else in the universe.

[Former Member · Accepted solution]

Thanks for all your valuable suggestions. I think I got the solution. Two ways we can solve as per the replies here:

1) Create Seperate blocks for all dimensions and create a section on merged dimension. Easiest solution (easy to explain users) might not look like a single report but we can manage.

2) Merge dimensions by creating varables for other dimensions and then check row aggregation property. Too much work for end users as they have to create as many varaibles as data elements in the report (other than merged dimension). But looks like a single report

Thanks for all your help.

Sree