Formatting problem

system · July 30, 2004, 1:33pm

Hi
I have data like this

Name Role
John Contractor
Alex SubContractor
John SubContractor

I need to represent the above in the report like this

Col1 Col2
John Contractor,SubContractor
Alex SubContractor

All the roles executed by ‘John’ should appear in the same row along with his name
Please share u r ideas
Thanks in advance

john999 (BOB member since 2004-03-22)

system · July 30, 2004, 1:41pm

I don’t think it’s possible to do “Contractor” and “Sub Contractor” for John in one cell/row. Why don’t you use breaks like:

Name Function
John Contractor
____ Subcontractor
Alex Contractor
Kolli Contractor

jobjoris (BOB member since 2004-06-30)

system · July 30, 2004, 2:11pm

We Proposed the same but our user is not satisfied with that
He wants to view some thing like
Company Name Role
Amdox Inc John Contractor,Subcontractor
Sunrise Inc Smith SubContractor

john999 (BOB member since 2004-03-22)

system · July 30, 2004, 3:08pm

One workaround is to create a measure on Role with definition as
Role+’ ’

Now use this object instead of dim Role in your report, this owuld give you the desired format.

Hope it helps
Reema

reemagupta (BOB member since 2002-09-18)

system · July 30, 2004, 4:02pm

Hi Reemagupta
When i tried that by creating a measure as
role+’ ’
I am getting this error
Connection or Sql sentence error
Invalid number 1722

I tried by runnig the sql generated by BO in SQL PLUS of no use

please clarify this
Thanks in advance

john999 (BOB member since 2004-03-22)

system · July 30, 2004, 6:24pm

By Role I meant the tablename.column name which has role information in it and then add +’ ’ to it.

For oracle you can try ||’ ’ instead of +’ ’

reemagupta (BOB member since 2002-09-18)

system · August 2, 2004, 6:32am

Reema,

I’m not a real long-time user in BO but how would you do a concatenation on one database column?

jobjoris (BOB member since 2004-06-30)

system · August 2, 2004, 8:56am

SOLUTION!

You have a table called ROLES_TABLE composed by 4 records:


NAME, ROLE
===========
John,Consultant
Kenneth,Sales Manager
John,Project Manager
William,Executive Manager

You create a STORED FUNCTION in Oracle, compiling the following SQL code:


CREATE OR REPLACE FUNCTION extract_attributes_for_a_name (
   name_of_the_actor   IN   VARCHAR2
)
   RETURN VARCHAR2
IS
BEGIN
   DECLARE
      concatenation_string   VARCHAR2 (500);

      CURSOR list_of_attributes_for_name
      IS
         SELECT DISTINCT ROLE
                    FROM roles_table
                   WHERE NAME = name_of_the_actor;

      c1_record              list_of_attributes_for_name%ROWTYPE;
   BEGIN
      concatenation_string := '';

      FOR c1_record IN list_of_attributes_for_name
      LOOP
         concatenation_string :=
                          concatenation_string || c1_record.role || ', ';
      END LOOP;

      concatenation_string :=
            SUBSTR (concatenation_string, 1, LENGTH (concatenation_string) - 2);
      RETURN concatenation_string;
   END;
END extract_attributes_for_a_name;
/

You create a VIEW as follows:


CREATE VIEW roles_view
AS
   SELECT DISTINCT NAME, extract_attributes_for_a_name (NAME) ATTRIBUTES
              FROM roles_table;

… and you use in BusinessObjects Designer the columns NAME and ATTRIBUTES from the VIEW called ROLES_VIEW!

Let us know!

Donald Duck (BOB member since 2004-07-29)

system · August 2, 2004, 10:03am

HI Donald,
Wonderful!!!
Thanks a lot for u r solution its works fine
but unfortunately we have got readonly permissions on the database
We r not supposed to create any objects
Can u plz suggest a soln that can be done either universe or report level

Hi Reeema
I tried with ||’’ this also
i was not successful
can u plz elaborate with some explanation(Example)

john999 (BOB member since 2004-03-22)

system · August 2, 2004, 10:14am

Then only block to your activity is that you CANNOT create the stored function and the view, if I understood well.

As you can see, a CURSOR in my opinion is NECESSARY to create a VIRTUAL TABLE (“VIEW”) from which you can read directly the results.

Please ask to the Admin of the Oracle Server if you can create a simple VIEW and a simple STORED FUNCTION, that make no modifies on the DB, but only offer a proper way to read and group some data.

In the while, I’ll think for a solution at Designer / Reporter level, but I think it is not possible without DDL integration.

Donald Duck (BOB member since 2004-07-29)

system · August 2, 2004, 12:16pm

Have a look at this Thread and see if it helps you out of the maze…

Sridharan (BOB member since 2002-11-08)

system · August 2, 2004, 12:29pm

Check this thread -

this should solve the problem

Reporter Bloke (BOB member since 2004-05-20)

system · August 2, 2004, 12:52pm

Let’s suppose you have a table called “ROLES_TABLE”, with 2 columns: NAME varchar2(20), and ROLE varchar2(20).

You can insert a class in the Designer, called “Roles Table”, with two objects: Name (Character) and Role (Character).

Now in B.O. Reporter you can create a simple table with Name and Role in BODY (nothing in column, and nothing in row): just a standard table.

Set Name column as master, creating a section . Right click on the table, then select “Rotate table”. In the column with the formula “=NameOf()”, you can insert the formula “=” (avoid " of course).

Then, right click on the cell you set previously as master of the section, and click Delete (DON’T delete the section , but delete only the cell containing che formula “=”!!).

To reduce the spaces between every “bar”, just move the table (you can also use the Format Table to specify the Appearance of the table…) to the upper limit of the section : you’ll obtain an optical effect similar to what you wish to yield, with exception of comma (every role appears in a different cell).

The length of the bars depends on how many roles every person has.

Donald Duck (BOB member since 2004-07-29)

system · August 3, 2004, 1:09pm

Hi sridharan,
It worked fine for me. I followed this

But how do i do , If I dont want the roles to be repeated
Thanks in advance

john999 (BOB member since 2004-03-22)

system · August 3, 2004, 1:22pm

Kolli, go for Donalds solution. It took me some seconds to understand what he meant but is is a briliant solution I might add…

jobjoris (BOB member since 2004-06-30)

system · August 3, 2004, 2:02pm

Hi Reporter Bloke
I tried with this

Thanks for the nice solution

john999 (BOB member since 2004-03-22)

system · August 3, 2004, 2:11pm

Hi Jobjoris
Donalds soln is fine
but how shall i do if
u have one more column like “id” to display in the report

john999 (BOB member since 2004-03-22)

system · August 3, 2004, 2:40pm

kolli, we have two possibilities.

The other column called “ID” is UNIQUE;
The other column called “ID” is NOT UNIQUE (so, you have to GROUP BY Role and ID).

Please, select alternate and let us know!

Donald Duck (BOB member since 2004-07-29)

system · August 3, 2004, 2:41pm

Hi reporter bloke

Regarding the soln given here

How shall i go if i have data like this
i have one more record with desc ‘a’

name | id | desc
abc 1 x
abc 1 y
abc 1 z
abc 1 a
cde 2 w
cde 2 t

thanks in advance

john999 (BOB member since 2004-03-22)

system · August 3, 2004, 2:42pm

In your last example, I see that if name is constant, ID is constant.

So, the previous view:


CREATE VIEW roles_view 
AS 
   SELECT DISTINCT NAME, extract_attributes_for_a_name (NAME) ATTRIBUTES 
              FROM roles_table;

has only to be rewritten as follows:


CREATE OR REPLACE VIEW roles_view 
AS 
   SELECT DISTINCT NAME, ID, extract_attributes_for_a_name (NAME) ATTRIBUTES 
              FROM roles_table;

Let us know!

Donald Duck (BOB member since 2004-07-29)