Multiple Values in one Object - how to show all in one row

building reports webintelligence
1

Webi XI
I have an object that can have multiple Risk Factor values for each customer, each visit. I need to create a variable that will show all of them in one row. Some customers will not have any values, some will have 3, some will have 7. Any ideas on how to do this? I have tried creating a variable using previous, but it seems to repeat the values if there are only 2 or 3 values - as it is designed to show up to 7. Here is my UV example that isn’t working as I’d like:

=If Not(IsNull(Previous(Previous(Previous(Previous(Previous(Previous([UV Risk Factors])))))))) Then ([UV Risk Factors] + Previous([UV Risk Factors]) + Previous(Previous([UV Risk Factors])) + Previous(Previous(Previous([UV Risk Factors]))) + Previous(Previous(Previous(Previous([UV Risk Factors])))) + Previous(Previous(Previous(Previous(Previous([UV Risk Factors]))))) + Previous(Previous(Previous(Previous(Previous(Previous([UV Risk Factors])))))))

wallenr (BOB member since 2011-04-08)

2

Have a look at the RelativeValue() function.

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

3

Thank you for your quick reply!
I will play with this function and see what I can create.
Cheers!

wallenr (BOB member since 2011-04-08)

4

I have created a RelativeValue variable and my rpt does not like it. I am getting this error message when I try to add it to my rpt:
index: The argument is out of range: 2<0 or 2>=2. (Error: RWI 00012)

I do have all these objects in this variable on my rpt

=RelativeValue([Risk Factor];([Link ID]);-6)

  • Risk Factor can have zero or many values per vist
  • Link ID is the unique visit ID
  • Date - I have also used this inplace of the Link ID and get same error message.
  • (-6) as I want to go that far back if applicable.

Any thoughts on how to rewrite / reattempt this?

wallenr (BOB member since 2011-04-08)

5

Unfortunately, not really :(.

How many values does it actually let you go back?
Maybe its a Java memory related thing, have you changed your Java runtime parameters?

https://bobj-board.org/t/145084

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

6

Even if I change the offset to -1, I still get this same error message.
When I had my crude, huge variables, I was getting all (up to 7) but like I say - it’s pretty crude results.

Is it the way I have my variable setup?
Link ID does not change
Date does not change

wallenr (BOB member since 2011-04-08)

7

Heres an example, from one of my reports. Future Base Date is month and year Dec 2010.

=RelativeValue([Headcount Operational 14 Month];([FutureBaseCalendarDateMMMyyyy]);-1)

Are you using a supported version of Java?

Have you tried to apply the formula in structure view?
Sometimes, in the case of Java errors, you have to shut IE down otherwise the error will continue to re-occur.
It also worth trying the memory tweak I suggested, providing you have enough memory in your machine.
Also, maybe, you could see if it works in the Rich Client.

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

8

I will ask my administrator about the Java settings. We have a new BO setup so I would think we have the latest supported version of Java.
Also - I only have Webi - no Rich Client. My company is not implementing Rich Client. Also I don’t have option for Structure view :frowning: in Webi.

Thank you for all your suggestions.

wallenr (BOB member since 2011-04-08)

9

As I recall, you must display all values in a table for Previous to work, maybe thats better in 3.1? The best approach is to have the DBAs provide a table/View that privides the data this way, you shouldn’t have to jump through hoops to make it work.

pcgeekus :us: (BOB member since 2003-10-16)

10

Hi,

I believe somebody has already posted a solution in the forum for a similar problem:

[b][i]reemagupta
Forum Enthusiast

Joined: 18 Sep 2002

Posts: 1201
Location: Houston, TX

Posted: Thu Apr 01, 2004 2:45 pm
Post subject: Source code for sample function

Wanted to post another crude way of doing it on Universe side.

Create an object
=+’ ’

Define this object as a measure

Now if this object is used with bunch of dimension it will concatenate the salesperson values for each set of dimensions.

Hope it is useful.

Reema [/i][/b]

Refer the link below:

Is that what you were looking for?

MPSDEEP (BOB member since 2006-09-26)

11

I think you misunderstood what the poster was asking for. Although a database function is a possible solution, it is not related to recursive tables and flattening the structure.
Consider an application where multiple comments can be made over a period of time. Each entry has a datetime stamp and each comment is tied to the same dimension like Project Number. I think what the poster was asking was similar to asking to see all comments made on this project in 1 row instead of multiple rows (one for each comment).

pcgeekus :us: (BOB member since 2003-10-16)

12

That is correct - that was what I was wanting to do. I did find a previous post that helped me resolve this. It was the second attachment that had the variables I needed to achieve this.

Data values in single line with commas in a field:

Thank you all!

wallenr (BOB member since 2011-04-08)

13

Now thats quite an inventive solution :yesnod:

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

14

Agreed. I wonder if anyone has a link to SQL that does the same thing?

pcgeekus :us: (BOB member since 2003-10-16)

15

I believe in SQL Server you could use the pivot functionality for this sort of thing.

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

16

DB2 doesn’t have a Pivot function, but I found a post that said try this from IBM:

To PIVOT use

MAX(CASE WHEN pivotcol = 1 THEN c1 END) AS P1,
MAX(CASE WHEN pivotcol = 2 THEN c2 END) AS P2
and so forth,

To UNPIVOT:

SELECT pivotcol, c1 FROM T, LATERAL(VALUES(1, T.c1), (2, T.c2), ...) AS
S(pivotcol, c1)

To generalize this use a stored procedure and dynamic SQL to glue
together the statements.

pcgeekus :us: (BOB member since 2003-10-16)

17

I suspect that you could do a similar thing in most RDBMS.

The only caveat is that this will be quite resource hungry.

However, it should be appreciated that this will be similar when using any pivot functionality, built into the RDBMS or otherwise.

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

18

I was wondering if I can do this with an issue I am having!?

I have an object that returns values A1 and A2; I want to return the row of information I want from A2

Possible ???

Joealyche (BOB member since 2012-02-29)

19

I can’t access the link b/c it’s blocked by my work, for some reason… But for oracle databases this link was in that same thread: http://irfansworld.wordpress.com/2011/09/15/how-to-display-column-values-in-a-single-cell-in-web-intelligence-using-oracle-database/

Not sure if it’s any good or not, but there you have it :smiley:

sredden1 :us: (BOB member since 2011-06-09)

20

This will stream all the values together and now what I want to do. I need to take the latest row as mentioned and ONLY display that rows values.

Tough to do if it can be done that is :frowning:

Joealyche (BOB member since 2012-02-29)