generate report from 2 tables

[sugu]

Hi,


I have 2 tables, Application and AgentByApplication. They are both linked by timestamp and applicationID fields. I need to get the total number of callsOffered(from application table). but the total doesnt tally with the expected result as there are duplicates in the timestamp field.
eg. AT 9.15 Agent1 gets 2 calls offered and Agent2 gets 2 calles offered. At 9.30, Agent1 gets 1 calloffered. The total should be 3 calls offered but it generates as 5 calls.

I have tried grouping with AgentByApplication.timestamp..but it still get the same value.

I urgently need to solve this. And i'm not using SQL db.

regards,

 

[MisterMo]

how do you set the layout of the report?

both agent and timestamp could be quite long and uless you use across tab you might encounter some problems.

eg where time is
9.00, 9.15, 9.30 and so on

or AgentId is

01, 02, 03, 04 and so on


getting the results shouldn't be a problem but displaying them is another issue.

Mo

 

[Madawc]

Have you tried DistinctCount?

Failing that, try a Running Total - I assume you have been using summaries.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

That's for Crystal 10; slightly different for other versions, but still should work.

Madawc Williams (East Anglia, UK)

 

[lbass]

But why would you expect three calls when there were, according to your description, actually five?

-LB

 

[MisterMo]

Agent1 had 3 and Agent2 had 3
that is way I am questioning about the layout

how many Agents 2, 20, 30 ?

Time stamp every 10 mins 15, 20, 1 hour ?

how long the report is going to be ??

Mo

 

[sugu]

hi i've resolved it with running total. thanks.

timstamp is every 15 mins with runs bet 00:00 to 23:45.
and also 40 application n 40 agents. 1 agent may be attached to many applications.

now i have another problem. i need to get the subtotal value for all the applications from 00:00 to 23:45 eg. there may be many many agents attached to an application at 10:00..say Appliaction a1 at 10:00 agent1 callsoffered= 2 , agent5 calloffered= 2, agent39 callsoffered=2 ( calls offered is always same for each application.

the fields are grouped by group1: iAgentByApplication.time and group2 iApplication.Application.

so now the table displays as such: x-axis= data fields and y-axis= timestamp

the format:
eg:
CallsOffered
10:00 AM
Application: Ap1
2/8/2005 10:00AM 1
subtotal=1
Application: Ap2
2/8/2005 10:00AM 3
2/8/2005 10:00AM 3
subtotal=6 (by right it should be 3..refer to the 1st post)

Applocation: Ap3
2/8/2005 10:00Am 2
subtotal=2
grandTotal=6.

I also want the format to be as such that the timestamp should only hv 1 occurance and then display the subtotal of each field respectively
eg
callsOffered
Total 453

2/8/2005 10:00AM 6
2/8/2005 10:15Am 4
2/8/2005 10:30AM 8
. .
. .
. .
2/8/2005 23:45AM 4


i hope i hv made myself clear.

thanks