BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

[Webi 4.x] Grouping and #Multivalue


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
poster74
Forum Member
Forum Member



Joined: 04 Jul 2013

Posts: 6



PostPosted: Tue Nov 03, 2015 4:33 am 
Post subject: Grouping and #Multivalue

Hi,

I was wondering if anybody could please help, I have done a search and am still unsure as to how/if I can achieve what I want.

I have a simple query using Webi 4.1SP5, BW 7, BICS query. The objects are ProfitCentre, FinancalStatementItem and Value. The structure is a crosstab in a report.

Very simple so far, however the issue is that the Profit centre is a 5 digit number from SAP, which actually contains both 'Activity' and Country. The first 3 digits denotes activity, the last 2 digits denote Country. The requirement is to create report which shows values by Country and/or Activity. I have created report varaibles for these objects by using Substring.

I understand why Multivalue is occuring; I've created a crosstab with Country, FSItem and value, but there can be multiple values for country, for example Country 20(USA) has values for mutiple activities (105,200,250) etc. I then created a Max around the Value dimension, thinking this would solve it but it doesnt.

I would appreciate any advice, can this be achieve at the report level ? Or does it have to be done in SAP, by seperating out Activity and Country instead of having a ProfitCentre that encompasses both ?

Thanks
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11432
Location: Manchester, UK


flag
PostPosted: Tue Nov 03, 2015 4:40 am 
Post subject: Re: Grouping and #Multivalue

Have you tried building it as a vertical table? This can often be a good way of debugging multivalue errors in crosstabs.
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
poster74
Forum Member
Forum Member



Joined: 04 Jul 2013

Posts: 6



PostPosted: Tue Nov 03, 2015 4:51 am 
Post subject: Re: Grouping and #Multivalue

Thanks, I have tried that and can kind of see where the issue is.

If I bring back just Country and Value in a vertical table, I get #Multivalues for the values. This is because, for example, Country 100 can have multiple activities.

If I bring ProfitCentre, Country and Values, then insert a break on Country, then a a sum on the values, it kind of gives me what I want - a Sum of values by Country. How do I recreate that in a crosstab?
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11432
Location: Manchester, UK


flag
PostPosted: Tue Nov 03, 2015 6:45 am 
Post subject: Re: Grouping and #Multivalue

Sum your values in the crosstab - what projection is defined against the measure? If there isn't any defined or it's database delegated then I'd suggest that you'd need =sum([value]) rather than just =[value] for it to aggregate properly.
_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
poster74
Forum Member
Forum Member



Joined: 04 Jul 2013

Posts: 6



PostPosted: Wed Nov 04, 2015 4:07 am 
Post subject: Re: Grouping and #Multivalue

I had added sum to the value in the crosstab, but each country was still appearing multiple times across the top (it wasn't summing). Crosstab with Country Y Axis, FinStatementItem X axis and values in the body. A Break applied on country.

However I found a solution:

I clicked in the body of the crosstab (values), used the sum function across both directions and then deleted the values, leaving the sums in place. Each Country was summed correctly. Thanks
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0407 seconds using 18 queries. (SQL 0.0156 Parse 0.0009 Other 0.0243)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo