Complex Query

[subhash5678]

Hi Folks,

I have a very complex transactional data model. I have to create a report out this complex data-model. I can give the picture of this data model in simpler terms. I have the following tables:

Employee Table
--------------
Employee_id Employee Name
----------- -------------
1 Arnold
2 Boby
3 Caren

EmployeeAddress Table
---------------------
Employee_id Address_id Address
------------ ---------- --------
1 1 Addr_1_1
1 2 Add_1_2
1 3 Add_1_3
2 4 Add_2_1
2 5 Add_2_2
3 6 Add_3_1

And the report should be in the following format:

Employee Name Address1 Address2 Address3
-------------
Arnold Addr_1_1 Add_1_2 Add_1_3
Boby Add_2_1 Add_2_2
Caren Add_3_1

Is this achievable in any way using COGNOS Impromptu without using stored procedure.

Thanks,
Subhash.

 

[griffindm]

Subhash,

Is the address ID predictable? i.e. are there a maximum of three address codes per employee id? If so, you can create a view to collapse the data exactly the way you want, then use Impromptu to report on it.

You could probably do the same thing within Impromptu itself just using a crosstab report, grouping Address Iid's within Employee Id's, and then pull the Address Id's into the X dimension.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[subhash5678]

Thanks for the reply Dave!

We cannot predict the address ID.

I tried with cross tab, But Addresses of second guy starts after the addresses of first guy as show below:

Ananth Addr_1_1 Add_1_2 Add_1_3
Boby Add_2_1 Add_2_2

Is there any other way?

-Subhash.