Home » Developer & Programmer » Reports & Discoverer » Discoverer question
Discoverer question [message #230404] Wed, 11 April 2007 17:42 Go to next message
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 #231414 is a reply to message #230404] Tue, 17 April 2007 00:05 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
What did you try and where did you get stuck?
Re: Discoverer question [message #231644 is a reply to message #231414] Tue, 17 April 2007 17:29 Go to previous messageGo to next message
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 #231976 is a reply to message #231644] Thu, 19 April 2007 00:16 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
I'm not sure what you're trying to do. Could you please give same sample base data and a sample output?
Re: Discoverer question [message #232186 is a reply to message #231976] Thu, 19 April 2007 14:23 Go to previous messageGo to next message
wshieh
Messages: 4
Registered: April 2007
Junior Member
Three tables:

USERS
id username gender
1 USER1 M
2 USER2 F
3 USER3 M

CONTACTS
id user_id contact
1 1 user1@email.com
2 2 user2@email.com
3 2 (111)111-1111
4 3 (222)222-2222

ROLES
id user_id role
1 1 Sys Admin
2 1 Supervisor
3 2 Receptionist
4 3 Manager

I want my output to be like the attached image.
Is it possible?
  • Attachment: 1.jpg
    (Size: 14.52KB, Downloaded 145 times)
Re: Discoverer question [message #232193 is a reply to message #232186] Thu, 19 April 2007 14:46 Go to previous messageGo to next message
skooman
Messages: 912
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 Go to previous messageGo to next message
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 Go to previous message
skooman
Messages: 912
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.
Previous Topic: calculating OPENING BALANCE and adding it to RUNNING TOTAL dr and cr.....
Next Topic: oracle report builder
Goto Forum:
  


Current Time: Sun Dec 04 06:25:50 CST 2016

Total time taken to generate the page: 0.22897 seconds