pivot data.

building reports webintelligence
1

Hi
I am new to site and not sure this is correct place to post this question. Currently I am using SAP BusinessObjects BI Platform 4.1 Support Pack 4
Version: 14.1.4.1327. Infoview is what everyone in my office calls it. I am supposed to have Crystal reports but I am having issues with access so I am not sure the version. I need to create a report that takes data in multiple rows and creates one object for it.
Labels prod1 prod2
case1 abd ttt
case1 ddd fdsa
case1 eft jhgjg
case2 jhgg ddd
case2 bbb fdsa

Needs to look like:
label prod 1 prod2
case1 abd, ddd,eft ttt,fdsa,jhgig
case2 jhgg,bbb ddd,fdsa

Help! Cross tabs did not work.
Lori

middleman66 (BOB member since 2015-08-27)

2

[Moderator Note: Moved from WebIntelligence Version 6 to WebIntelligence XI]

And welcome to B:bob:B!

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Guess you mean as discussed here:

MBLM :netherlands: (BOB member since 2014-01-31)

4

You could do this with a small bit of custom SQL:

  SELECT labels,
         LISTAGG (prod1, ',') WITHIN GROUP (ORDER BY prod1) AS prod1,
         LISTAGG (prod2, ',') WITHIN GROUP (ORDER BY prod2) AS prod2
    FROM your_table
GROUP BY labels

thesnow :us: (BOB member since 2011-08-10)

5

Personally I would get who designed the database to create a remodelled version of that table that looks like;

Labels ProductType ProductCode
Case1 Prod1 Abd
Case1 Prod1 ddd
Case1 Prod2 ttt
Case1 Prod2 fdsa

With volume data, solving data modelling problems in the reporting tool invariably leads to performance issues (or very complex, hard to maintain reports).

Only other solution is if your database has a PIVOT function or similar, build something in the BO universe.

ABILtd :uk: (BOB member since 2006-02-08)

6

Agreed, Andys modelling seems the best approach.

The problem with pivoting “on the fly” via the universe, is the potential performace issues you may face, better to have it pre-canned.

Mak 1 :uk: (BOB member since 2005-01-06)

7

I appreciate the quick response. Unfortunately changing the data model is not an option. I will see if the SQL will work.

middleman66 (BOB member since 2015-08-27)