Processing "Sets" of Data

[MikeCopeland]

Using CRXI...
I have a number of linked tables in a report, and the data access seems to conflict with my report's needs. 8<{{

T1 T2 T3 T4 T5 T5
Name Goal Date Len Ind Fam
ClientA 1 01/04/07 4 60 0 {set1}
ClientA 2 01/04/07 9 0 0 {set1}
ClientA 1 01/12/07 4 60 0 {set2}
ClientA 2 01/12/07 9 0 0 {set2}
ClientA 1 01/16/07 4 60 0 {set3}
ClientA 2 01/16/07 9 0 0 {set3}
ClientB 1 01/04/07 4 60 0 {set1}
ClientB 2 01/04/07 4 0 60 {set1}
ClientB 1 01/11/07 4 60 0 {set2}
ClientB 2 01/11/07 4 0 0 {set2}
ClientB 1 01/18/07 4 0 0 {set3}
ClientB 2 01/18/07 4 0 60 {set3}
ClientB 1 01/25/07 4 45 0 {set4}
ClientB 2 01/25/07 4 0 0 {set4}
ClientB 1 01/31/07 4 60 0 {set5}
ClientB 2 01/31/07 4 0 0 {set5}

In this example, I have 2 "sets" of client data, and I need to compute and evaluate several things:
1. How many weeks of activity (sets) each client has (3 for ClientA and 5 for ClientB);
2. Calculate a total planned processing for each client, based on T4.Len - but value is to be computed only once for each client;
3. Compute sums of T5.Ind and T5.Fam values, placing these totals in a Group break line;
4. Calculate a value based on T2.Goal for each "set".

My difficulties at this stage are:
(1) knowing how many "sets" (weeks of activities) exist for each client,
(2) how to calculate the planned processing for each client (s/b 180 Ind/0 Fam for ClientA and 300 Ind/300 Fam for ClientB),
(3) NOT duplicate all record processing because there are 2 records/set for each client packet (I suppose this is yet another Group process...but I'm already Grouping by T1.Name, so I don't know if this can be done).

I would expect to see the following results:
Name Ind Fam Plan_Ind Plan_Fam
ClientA 180 0 180 0
ClientB 225 120 300 300
I hope I've explained this well enough... TIA

 

[synapsevampire]

You'll be best served to use conventional terms, I've no idea what "s/b 180 Ind/0 Fam for ClientA and 300 Ind/300 Fam for ClientB" means.

And the numbers here differ from the numbers below.

It sows 225 for one of the Ind...

Anyway, it's probably very easy to do what you need, and the advantage of using Crystal is that you don't need to code for many things, it's built in.

For instance your notion of arrays to perform calcs would be over kill:

I believe that this:
" (1) knowing how many "sets" (weeks of activities) exist for each client, "

would translate in technical terms to the number of unique dates?

If so, group by client and use a formula of:

distinctcount({table.date},{table.client})

You'll need to flesh this out for me:

" (2) how to calculate the planned processing for each client (s/b 180 Ind/0 Fam for ClientA and 300 Ind/300 Fam for ClientB), "

You might use a formula of:

sum({table.ind},{table.client})

Not sure what not duplicate record processing means, perhaps you should clarify. Does this mean not count duplicate rows in some other formulas? If so, state the formulas.

There isn't a single duplicate row in your data examples, so I gather you mean some sort of record inflation?

Your notion of showing example data and the expected output is great, just elaborate on how you achieved the reults and Crystal will likely handle all of this purdy easily...

In general, I would avoid leaping to arrays or other less efficient means of providing aggregates until you understand Crystal and it's functioality and limitations.

In fact curb ing the desire to design/architect would serve you well. Later on you'll have plenty of opportunities to use Crystal's arrays and advanced prgrasmming functions.

btw, Crystal has 2 languages, both fairly extensive, though primitive:

Crystal syntax, which is a C like language, and Basic, which is very similar to VB, minus the object oriented aspects.

-k

 

[MikeCopeland]

You'll be best served to use conventional terms, I've no idea what "s/b 180 Ind/0 Fam for ClientA and 300 Ind/300 Fam for ClientB" means.
It means "Planned processing for ClientA should compute 180 for Ind & 0 for Fam...and for ClientB 300 Ind and 300 Fam". As I saw my post becoming biger and bigger, I tried to use a bit of shorthand to state what I was expecting. Sorry about that...

And the numbers here differ from the numbers below.
It sows 225 for one of the Ind...
That's what I want - the total Ind sum for ClientB in the sample data _is_ 225, whereas the Planned Ind for ClientB is 300. That's one of the things this report needs to show, the variance from Planned to actual values.

For instance your notion of arrays to perform calcs would be over kill:

I believe that this:
" (1) knowing how many "sets" (weeks of activities) exist for each client, "

would translate in technical terms to the number of unique dates?
Not always. "Sets" here means the number of T2.Goal sets, which could map to the T3.Date values...but doesn't have to. I probably shouldn't have included the Date element, because I'm only using that table field to qualify my data selection. Sorry...

You'll need to flesh this out for me:
" (2) how to calculate the planned processing for each client (s/b 180 Ind/0 Fam for ClientA and 300 Ind/300 Fam for ClientB), "
You might use a formula of:
sum({table.ind},{table.client})

The calculation is actually: (T4.Len*4)*#sets (except when T4.Len = 9). The problem is that I want to calculate this value - and display it - only once per client. My difficulty seems to be that there are many sets/client, so I'm getting a value that's "correct*sets"...

There isn't a single duplicate row in your data examples, so I gather you mean some sort of record inflation?

True, but my Detail lines (and the sums & totals) reflect that the query linking is pulling 2 sets of detail information for each "set". That is, because T2.Goal(1) is in a different record than T2.Goal(2), the query linkage is returning 2 instances of the client's data each time. Yes, I know I could (and should) apply Grouping on this, but I'm unsure if I can keep adding Groups within existing Groups as the report becomes more complex.

Your notion of showing example data and the expected output is great, just elaborate on how you achieved the reults and Crystal will likely handle all of this purdy easily...
I hope I have done so above; if not, please inquire.
In the meantime, I will flail away at the suggestions you've made here. I'm leaving for the day (such is the life of a volunteer/retiree), so I'll attack it tomorrow...8<}} Thanks, sv.

 

[synapsevampire]

Create the formula:

numbervar MyValue:= 0;
if {T4.Len} <> 9 then
MyValue:=(T4.Len*4)*(whatever the sets formula is)

Place it in the client group header and refer to it later as required.

Let's hear what you might need assistance with.

-k