| Discoverer question [message #230404] |
Wed, 11 April 2007 17:42  |
wshieh
Messages: 4
Registered: April 2007
|
Junior Member |
|
|
Hello,
I'm wondering if this is possible to do in Discoverer Report (v4.1):
Say I've 3 tables, Users, Roles, Contacts
Users 1:n Roles
Users 1:n Contacts
I want to be able to show all columns from Users, and a list of all Roles associated with the User, and a list of all Contacts (e.g. email, phone #) with the User.
I want the report to display something in a table form something to this effect:
for each user, first few columns display column info from Users table. Second last column displays a list of roles (can be more than 1), last column displays a list of contact (can be more than 1)
Thanks,
W
|
|
|
|
|
|
| Re: Discoverer question [message #231644 is a reply to message #231414] |
Tue, 17 April 2007 17:29  |
wshieh
Messages: 4
Registered: April 2007
|
Junior Member |
|
|
Consider a user with 2 contacts and 2 roles
The best result I get is something like, with group sort:
row1: [ user 1 info ] [contact 1] [role1]
row2: [ ........... ] [contact 2] [role1]
row3: [ ........... ] [contact 1] [role2]
row4: [ ........... ] [contact 2] [role2]
I can't avoid the cross product between contacts and roles...
And can't find any workaround as of yet.
[Updated on: Tue, 17 April 2007 17:30] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Discoverer question [message #232193 is a reply to message #232186] |
Thu, 19 April 2007 14:46 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
|
Ok, I see. Just select the columns you need (with proper joins of course) and group on the ones you don't want to see doubled.
|
|
|
|
| Re: Discoverer question [message #232199 is a reply to message #232193] |
Thu, 19 April 2007 15:17 |
wshieh
Messages: 4
Registered: April 2007
|
Junior Member |
|
|
Well that means i'll have to group sort all columns. I don't want any of them to have double. But it won't work, I will still have double.
If I group contact and role, I will see repeated user name and gender.
If I group user and contact, I will see double role etc.
|
|
|
|
| Re: Discoverer question [message #232203 is a reply to message #232199] |
Thu, 19 April 2007 15:32 |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
Oh, I didn't notice that there can be more contacts too (sorry, it's late on this side of the planet...)
I'm afraid this can not be done (well, that is, not without some ridiculous efforts). If it's kind of urgent, than you could create it in SQL, make a view on that and use it in your report. But that would be very inflexible. I would suggest to deal with the fact that it can't be done.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
