How To Structure a Query from Multiple Tables 1

[byrne1]

What I am trying to do seems pretty much straightforward - I am trying to generate an employee productivity report based on hours worked versus dollars sold. But I am having a very difficult time linking my tables so that the report will generate (thus far I've been able to consistently freeze up Crystal).

I will try to lay out my tables in an orderly fashion below listing the pertinent fields:

TABLE
<field> ---> TABLE.<field> **represents my link and direction of link

TICKHISH
<salesmannum> ---> SALESMN.<salesmannum>
<systemdate> ---> EMPLTIME.<systemdate>
<salesamt>

SALESMN
<emplnum>
<salesmannum>

EMPLTIME
<emplnum> ---> SALESMN.<emplnum>
<startdate>
<starttime>
<enddate>
<endtime>
<systemdate>


I think my problem is trying to link to the EMPLTIME table. From my experience, if the EMPLTIME table had the field <salesmannum>, then the report would be easy. But I have to somehow route the link through the SALESMN table.

I hope that someone can offer me some suggestions. I am using Crystal 10 Developer on a Pervasive SQL database.

 

[Madawc]

What you need is to get <emplnum> added to TICKHISH, terrible design not to have it so. But if that's not feasible, define the connections in Database> Database Expert

TICKHISH<salesmannum> link to SALESMN
<salesmannum>

SALESMN<emplnum> link to EMPLTIME
<emplnum>

In Report > Selection Formulas, test for <systemdate> on both TICKHISH and EMPLTIME.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[byrne1]

I've linked the TICKHISH<salesmannum> and SALESMN<emplnum> fields as you've mentioned above, but what do you mean by "test for <systemdate> on both TICKHISH and EMPLTIME"?

Am I supposed to enter a formula like:

{TICKHISH.SystemDateYyyymmdd}={EMPTIME.SystemDateYyyymmdd}

 

[Madawc]

That should work. If not, go Database > Show SQL Query and show what you have.

It helps to give your Crystal version.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[byrne1]

Thanks. I did that and it certainly has sped up the report.

I thought I did give my Crystal version in my original post. Crystal 10 Developer.

Thanks again!

 

[Madawc]

Yes, you did say Crystal 10. Glad to be of help, anyway/

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[byrne1]

Now I have a new problem. My report is showing an incorrect number of hours worked for each employee. I can see that this is because the report is accounting for the number of tickets an employee wrote on a given day. An employee can write x number of tickets in one day (x number of TICHHISH records for that day) but they will only work a certain number of hours for that day (1 EMPLTIME record).

As an example to help illustrate my point, if an employee worked five days, ten hours each day, and accumulated a total of $500 in sales, then my totals would be as follows:

hours worked = 50
total sales = $500
sales/hour = $10

But since my report is accounting for the TICKHISH data, if my employee wrote ten tickets each day, my totals are skewed as follows:

hours worked = 500
total sales = $500
sales/hour = $1

Somehow I have to get a summary of the number of hours worked and the total sales for each day for each employee, without having anything in the DETAILS section of my report. But how do I do this?

My report is grouped as follows:

SALESMN<salesmnnum> (sum of hours worked, sum of tickettotal)
EMPL<systemdate> (hours worked, sum of tickettotal)
DETAILS (ticketnum, tickettotal)

 

[Madawc]

You should start a new thread for a new problem. But look at Running Totals, they should have the necessary controls.

There are several ways to find totals: running totals, summary totals and variables. 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.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.

Directly Calculated Totals within a Formula Field can be coded directly, with commands like Sum ({ADV01.Advance}, {ADV01.AccType}). The same result can be achieved by picking up an existing Variable, and will keep the code even if the Variable itself is later deleted. Formula fields can also include Running Totals and other Formula Fields, with some limits depending on when the values are calculated.

It is also possible to get get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.

To get yourself familiar with the idea, try doing a test report with a summary total and a running total for the same field, placed on the detail line. You'll find that the running total increases as each line is printed, whereas the summary total has the final value all along.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10