Help Please!!! With Query Issues

[CrystalVisualBOracle]

Hi there. I'm REALLY REALLY hoping someone can help me with a problem I'm having. I just can't seem to figure my way around this one and I'm getting extremely desperate.

Right now I have a report which (simplified) looks like this...

Usage Report For Publisher A
Publication Name No_of_Unique_Users
Fortune 8
WMF 5
YM 6
GRAND TOTAL 7

The logistics behind the report is this, every publisher has publications which are used by users on one or more servers. The report sums up the total number of unique users on each server for each publication and prints it. At the end, it prints the total number of unique users for the publisher at the bottom.

The no of unique users for the publisher is the number of unique individuals who accessed that publisher's material. The no of unique users for the publication is the same but it's the number of unique indiv who accessed the publication. Since the same user may access more than one publication, we have to have a separate table hold the number of users for the publisher versus the publication so that no one is double counted.


The query basically involves three tables one which holds the publisher's "total" information for each server and one for each of the publication's usage data per server. The other table links the two tables together.

ie/

PUBLISHER_TBL REFERENCE PUBLICATION_TBL
server_id Publisher_id server_id
publisher_id Publication publisher_id
unique_users unique_users

Then the records return (ordered) like this
(PUBR=publisher PUBN=publication SRVR=Server)

PUBR_id PUBN_id SRVR_id PUBN_Users PUBR_Users
Dudes YM Srvr_1 4 30
Dudes YM Srvr_2 3 9
Dudes Forbes Srvr_1 4 30
Dudes Forbes Srvr_7 2 6
Dudes WMF Srvr_1 22 30
Dudes WMF Srvr_2 6 9
Dudes WMF Srvr_3 7 7
Dudes WMF Srvr_7 4 6
Dudes Journal Srvr_11 2 2

The problem is this. Most publishers have more than one publication on each server so the pubr_users column prints the same data over and over each time a server is printed. Does anyone know how I can only select the pubr rows once from each server. I need to total the PUBR_users
column for each distinct Srvr Id.

Does anyone have any ideas please? I would appreciate any help I can get. If this isn't clear please let me know how I can clarify it further.

Thanks,
CrystalVisualBOracle

 

[IdoMillet]

Sounds like you need two different reports:
One identical to what you have, but without the last column.
The other would drop the 2nd & 4th columns.

Another approach would be to sort and group by
Publisher/Server/Publication instead of by
Publisher/Publication/Server

Bottom line: figure out the layout of what you
want to show and the answer should present itself )

Cheers,
- Ido

 

[MalcolmW]

Similar to Ido's comment, using a subreport to get the publisher's unique users would probably be best. This would ensure that this number is accurate even if all the publications for a publisher aren't in the data set returned to the report. Malcolm Wynden
malcolm@wynden.net