Field is generating multiple records 2

[laina222]

Hello all,
I'm trying to run a report that has customer data. I want one record per customer.

What is happening is that I have included a field that creates multiple records for one customer. It's basically to indicate what items a customer has.

For example, let's say customer "Bob" has the following items: "Apple", "Orange", "Banana" (3 items). Customer "Fred" has one item: "Orange". So in my report thusfar, "Bob" has three records and "Fred" has one record. How can I get it so that "Bob" has one record that lists all three items?

What I have:
Bob Apple
Bob Orange
Bob Banana
Fred Orange

What I want:
Bob Apple Orange Banana
Fred Orange
OR
Bob Apple Orange Banana
Fred Orange

I don't care about formatting. I figure I will have my columns say something like "Item1" "Item2", etc.

Any help is appreciated - thanks in advance!

 

[Sridharan]

I dunno which version are you on... Anyway check this thread and see if it works for you...

http://www.forumtopics.com/busobj/viewtopic.php?t=29236

Sri

 

[blom0344]

Alternative:

If you have an up to date RDBMS you could add a rownumber function that resets itself when the name changes:

ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME)

This will net you:

Bob Apple 1
Bob Orange 2
Bob Banana 3
Fred Orange 1

Define the new object as a detail. If you create a crosstab with these 3 objects, then BO will create a new row for each unique combination. With Format Crosstab you can then reset it to : Display Business Objects 3.1 Crosstab.
You will then have a row per name...

Do not ask me why they did not solve this more elegantly, but it work nevertheless

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

That was a cool one blom...

There was one more approach given by another user on BOB where you basically use the Sectioning trick to get the stuff in a single row... Really don't have the thread link in hand... Will post once I get hold of it... In essence we need to set the column containing BOB, FRED and master and then the left over table within the section we need to rotate... Now whats left over is basically arranging them closer to the Section Cell so that it appears in a single row...

Sri

 

[blom0344]

Yeah, I really am into those OLAP functions. Gives a LOT more to play with within reporter.......

I can visualize the other solution with the rotating table, which should be a more 'standard' solution

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

A Star for Blom...

Sri

 

[blom0344]

Well, you came up with TWO possible solutions, so that should net you at least a star

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[BrianTyler]

If there is a fixed, small number of 'types of fruit' I would create an object for each fruit:

Apples: sum(case when fruit ='Apples' then 1 else 0 end)
Oranges: sum(case when fruit ='Oranges' then 1 else 0 end)
etc

You can then report on Person,Apples,Oranges etc and get the required result.

This is definately not a safe and secure method, as you will have to change the universe and the report parameters every time a new fruit is introduced.

 

[laina222]

Well let's see...

Regarding Sridharan's solution, I tried to define a new object for my supposed "Name" field - actually the field is really a 8-digit identifier (like a customer ID), so perhaps something went horribly wrong there. I did this by going into Designer and copying the old field and pasting it, giving it a new name, and then I set the Type as "Long Text", set the Qualification to "Measure", and then set it as a Sum function. This didn't decrease my number of rows, however.

Also, regarding blom0344's solution - it sounds interesting, but I have no idea how to go about doing this. I'm pretty green/novice when it comes to Business Objects, and so I'm not sure where that code would go - would it be a formula?

Thank you everyone for being so patient and so helpful - I'm learning a lot!

Laina

 

[Sridharan]

Laina,

You need to define the Measure Object for the column retrieving the Fruit values and not on Customer... When you do so you will still get that many number of rows but when presented in the report you will get one row for each Customer... Again as I told earlier I dunno which version are you on??? I heard from people that it doesn't work in certain versions...

Sri

 

[blom0344]

The row_number solution takes advantage of modern RDBMS (databases) that allow for so-called OLAP functions.

These can be used in universe-objects (so not within the report itself)

OLAP functions come in 3 flavours:

1. Ranking functions (RANK(),DENSE_RANK())
2. Rownumber functions (ROW_NUMBER())
3. Aggregate functions

The PARTITION BY allows you to reset the counter when the field that it references changes in value.

These functions allow for even more complex reports, cause it is possible to know more about the dataset itself.
Drawback is possible performance drain with very large sources...



Ties Blom
Information analyst
tbl@shimano-eu.com