Master Detail Problem

rbuser

Dear all

I have an issue that ive had in several of my templates.

In my data Definitions ( Data Tab) I have 2 Grouped Result sets :

CALLS = Select GroupId,HourOfDAY,Avg(Ringtime), Count(Ringtime) from CALLS
Where EndPort<>-1
group by GroupId, HourOfDAY

-1 signifies lost calls

LOST_CALLS = Select GroupId,HourOfDAY,Avg(Ringtime), Count(Ringtime) from
CALLS
Where EndPort =-1
group by GroupId, HourOfDAY

These are defined using the Group and Calcs Tabs -im just showing the SQL
above.

When i view the data i get what i expect, e.g.

CALLS is
groupid HOD Avg Count
1 8 21.74926 706
1 9 8.6487 1930
1 10 8.726 1994

LOST_CALLS is
groupid HOD Avg Count
1 8 42.65 114
1 9 6.6492 211
1 10 16.538 210

All Great

So what i want is a Master - Detail, so that i can show these values on the
same line in the Report.

i.e.

Groupid Calls Avg Ring Lost calls Avg Ring Lost

So i do a master detail, with LOST CALLS Master = CALLS, joined on Groupid,
and HourOfDay.
HERE IS THE PROBLEM
What then happens is the LOST_CALLS data multiplies the Count with the
count(*) of the CALLS so that
LOST_CALLS looks like this
groupid HOD Avg Count
1 8 42.6578 80484 (706*114)
1 9 6.6492 407230 (1930 * 211)
1 10 16.538 418740 (1994*210)

Why? this cant be right? The averages done change? why does the count(*)
multiply with the Master?
Is this a bug or am i doing something wrong?

Regards

Ian Robinson

nicocizik

Ian,

A master-detail report implicates that you will have a one to many
relationship between the master and detail dataset. This is not the case
for your two datasets. I would recommend simply joining the CALLS and
LOST_CALLS tables directly in SQL to get the output you are after. Just add
both tables to a single dataview and use the join dialog to define how they
should be arranged.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico

"implicates", does not mean it has to be a 1- many. In this case i have
a 1-1.

I could have created views in my DB, to represent these CALLS, and LOST
CALLS views, but that means creating a views unnecessarily in the
Database, thus more DB maintenance overhead. This is what im trying to
avoid.

The issue im reporting is that if you have two views, each having a
Count(*), and then join them, the detail's Count(*) result for some
reason becomes Master(count(*)) * Detail(Count(*)).

Can you confirm that this is a fault? and is not what should happen.

Reagrds

Ian Robinson

nicocizik

Hi Ian,

Please use your real name when posting to these newsgroups.

Sorry, I misread your initial post. Yes this is functioning as designed.
If you hold the key and click the detail dataset, you will see the
underlying SQL that will be executed for that linked table. The data is
essentially being joined on the linked field and is therefore why you are
getting an increased count value.

What exactly would you like your report to look like? Perhaps there is a
different way to go about this.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico

basically, i need those 2 query results on 1 line on the report.

CALLS is
groupid HOD Avg Count
1 8 21.74926 706
1 9 8.6487 1930
1 10 8.726 1994

LOST_CALLS is
groupid HOD Avg Count
1 8 42.65 114
1 9 6.6492 211
1 10 16.538 210

Desired output
Group 1
Hour of Day TOTAL ANS CALLS LOST CALLS
8 (706+114) 706 114
9 {1930+211) 1930 211
10 (1994+210) 1994 210

etc...

The TOTAL column is easy if the join would work, and not * then 2 counts.

incidently
I have resolved it by creating Views in Database, and then simply
joining the views in Report builder.

regards

ian