cancel
Showing results for 
Search instead for 
Did you mean: 

How to put multiple dimension fields one under another without losing data in Webi

Former Member
0 Kudos

Hi!

I am creating a  report called Root cause Summary report. This report should take values of multiple root causes and get a count of all the incidents that has any of the root causes associated with it. Basicall all the data is coming from a Universe used in Webi as below:

Root Cause 1 |  Root Cause 2 | Root Cuase 3 | Root Cause 4|

I want to be able to put these root causes one under the other so I get all the root cause into One column and then put a variable for count to get the top 10 root causes.

I tried using the Formula variable  Root cause 1 + Root cause 2+ Root case 3+ Root Cause 4 to create just one column but unfortunately in many instances I get a cocantenation of root causes which I do not want. I want each root cause to show on the report on individual line and then its summary count .

If I put the individual fields of root cause one under another still the count does not work as it counts each of the root cause separately and then the count messes. Merging the 4 root causes also causes issue , gives me a message "The selected cells contain multiple values. Merging will keep the contents of the first selected cell only. Merge anyway?" which I do not want as I want all the data in my summary count.

Can anyone suggest me how to handle this situation to be able to generate a Root cause summary report with out loss of data and all rootcasues in just one column???

Help will be greatly appreciated.

Thanks,


Aarav

Accepted Solutions (1)

Accepted Solutions (1)

amitrathi239
Active Contributor
0 Kudos

HI,

all all these objects are dimension or measure objects?how are the values in  objects?

Amit

Former Member
0 Kudos

Hi Amit!

These all are Dimesnions  and they are texts.

Example:

Rootcause1Rootcause2Rootcause3Rootcause4
10.2-Inadequate consideration of human factors/ergonomics
10.3-Inadequate standards, specifications and/or design criteria
10.3-Inadequate standards, specifications and/or design criteria13.1-Inadequate assessment of needs and risks15.2-Improper extension of service life
10.4-Inadequate monitoring of construction
10.5-Inadequate assessment of operational readiness
10.6-Inadequate or improper controls
10.8-Inadequate evaluation of changes
11.2-Inadequate research on materials/equipment11.5-Inadequate receiving inspection and acceptance12.2.3 -Examination of units
12.1.1-Assessment of needs
13.1-Inadequate assessment of needs and risks
13.2-Inadequate human factors/ergonomics
13.5-Inadequate adjustment/repair/maintenance
14.1.1-Inventory and evaluation of exposures and needs10.1-Inadequate assessment of loss exposures
14.1.3-Employee involvement
14.1.4-Procedures/practices/rules14.3.3-Monitoring use of procedures/practices/rules9.7-Inadequate instructions, orientation and/or training
14.4-Inadequate Monitoring of compliance
14.4-Inadequate Monitoring of compliance10.8-Inadequate evaluation of changes10.8-Inadequate evaluation of changes

And I would like to put all the 4 columns into 1 columns and get a count of how many times they were used in the incidents.

Thanks,

Aarav

amitrathi239
Active Contributor
0 Kudos

Hi,

one option is create 4 webi queries .one for one rootcause.After that merge all these four objects in the report and use.

Amit

Former Member
0 Kudos

How do I merge Webi reports, I can merge multiple queries but I amnot sure how to merge Webi reports???Or did you mean create 4 info providers and merge them on the Webi???

Thanks

amitrathi239
Active Contributor
0 Kudos

Hi,

4 info providers..

one single report and 4 queries inside.

Amit

former_member184594
Active Contributor
0 Kudos

Hi aarav,

There are 2 ways to do this,

1. Create a 4 queries in your WebI report. Each query should contain your Root Cause dimensions. Let's say in your Query 1 you include only Root Cause 1 object, in Query 2, include Root Cause 2 object and so on. In WebI you merge all these Root Cause 1-4 dimensions coming from 4 different queries and build you report.

You can check the links below on how to merge dimensions and queries in WebI report.

2. You can create a derives table in your universe with UNION ALL and do this.

Have a nice one.

Former Member
0 Kudos

Is it possible to do the same tihng on the BEx query level and then use the BEx for Webi, as my universe is humongous it will take a lot of time to refersh the report with the 4 time usage of teh report.

I was wondering if I can merge the 4 fields into one column on BEx itself and use that as an info provider, it will be quick on teh runtime of teh report.

Tahnks,

Aarav

Former Member
former_member184594
Active Contributor
0 Kudos

I don't think you can do the same thing in Bex level. You can do it on BW level you can try to create a Multiprovider, but then this means to copy the cube for each "Root Cause" which I don't think would be the best solution.

You can try to achieve this with Breaks in WebI, but then you will have limited sorting.

Former Member
0 Kudos

Hi Zahid!

Thans for your reply on this , I managed to do it with the 4 queries as you suggested  and the count of items after merge also seems to be looking good to an extent except for one fact.

There are some similar items that show up on different lines with count assigned to it. When we total the two lines the count is fine, but is there a way to show them as just one line or in one row and count them together, else it looks like duplicate entries .  Like below.

Root Cause AnalysisCount
14.1.4-Procedures/practices/rules
11
5.6-Lack of situational awareness
9
14.1.1-Inventory and evaluation of expos
8
14.1.4-Procedures/practices/rules
8
13.1-Inadequate assessment of needs and
7
Root Cause Analysis - Header Data
6
7.6-Improper attempt to save time or eff
5
13.1-Inadequate assessment of needs and
5
8.2.1-Intentional
5
17-Failure to Identify Hazard/Risk
5
I am getting data like above which is great along with the count, the only problem is that the highlighted lines show as duplicate which I want to show as one line with their totals added up together.  For example in the above scenerio the line
14.1.4- Procedue/practice/rules should show as only one line with a total of 11 + 8 = 19
Similarly the line:
13.1-Inadequate assessment of needs and should show in one line with a total of 7 + 5 = 12..

Would it be possible to do so by any chance else my report will not be usable??
Help will be greatly appreciated.
Aarav
former_member184594
Active Contributor
0 Kudos

Even though, the lines which you stated above look like the same, there must be some characters like space character which make them different. That might be the reason they show up in different line and they don't add up.

If the records are the same then they are merged and added up. If there is a little change, then they will be processed as differen records.

There might be some kind of space character in the beginning or ending of the word try using "Trim()" function.

Former Member
0 Kudos

Thanks Zahid!

The Trim() function re-grouped the similar items together giving us the correct count. When I merged the same query 4 times as you had suggested , I see one thing that happened, I had used one Bex query 4 times and it has teh Prompts screen for dates ranges and few others. Now when I run teh report by double clicking or going into modify mode, it directly takes me to the result page from teh previous run and not to teh prompt screen where we can enter input values, any idea how to bring my webi report first to the prompt screen rather than jumping into the results screen from previous run , which will casue confusion to users as to what data they are looking for???

Thanks,

Aarav

former_member184594
Active Contributor
0 Kudos

Set "Refresh on Open" in your document. That should take care of your requirement.

Former Member
0 Kudos

Thanks , thats what I did and its working as expected.

Former Member
0 Kudos

Thanks Swapnil .

Answers (1)

Answers (1)

TammyPowlas
Active Contributor
0 Kudos

Moved to the Web Intelligence space for faster response

Former Member
0 Kudos

Thanks Tammy!!