Grouping on a "non DB field" Parameter

[CRCRCR]

Hi,

Finally managed to register with the best Crystal Forum out there. Hopefully I can start answering some questions myself.

I have a problem, which I hope the attached picture may help to explain.

I am trying to group on a parameter. This parameter is not a database field, just a plain simple number.

I've tried grouping on an actual database field, (Analysis) but this comes unstuck when there are more than one groups of (arbitary,user-driven) analysis

Is this even possible?

Kai

 

[CRCRCR]

Forgot to add, CRXI Developer

 

[lbass]

Are the groups ONLY distinguished by the number of samples? This doesn't seem to make much sense.

-LB

 

[CRCRCR]

Yes exactly.

Its a 'simple' quoting report.

I'm not really creating a report of data in the Database, rather just using it for the prices of the analyses.


The User selects a set of analyses (a normal db parameter) and selects a numerical parameter. This is the number of samples that will get those analyses.


i.e in 1 group, we have 5 samples requiring pH ($4), EC ($6) Faecal Coliforms ($20).

Totalling ~ $120

then they go on to choose another set of analyses, and chooses another number of samples.

so the second group for example, would consist of 10 samples with Pb($10) , Cu($6) , Zn($10) analyses.

Totaling ~ $260

And so on.

There are reasons they need to be grouped in such a way, one being, further on in the Calcluations there are Volume dicounts.

So in group 1, with 5 samples, there may be a 0% discout, however, group 2, with 10 samples, ther may be a 10% discount.

I will limit it to a maximum of five groups of analysis and 'numbers of samples'.

Hope thats a little clearer.

Kai

 

[lbass]

You can try this. Open a new blank report->your datasource->add command.

First create six parameters, three string parameters Analysis1, Analysis2, Analysis3, and three number parameters Grp1, Grp2, Grp3. When entering the command, note that for the string parameters in this case you do NOT put single quotes around the parameter. This is because commands only accept single value parameters, so what you will need to do is enter your parameter selections at the prompt exactly like this, with parens and quotes:

('pH','EC','Faecal Coliforms')

Set up the command like this (using Access syntax here):

Select {?Grp1} as NumberOfSamples, 1 as Grp, table.`Analysis`, table.`price`
From table
where table.`Analysis` in {?Analysis1}
Union All
Select {?Grp2} as NumberOfSamples, 2 as Grp, table.`Analysis`, table.`price`
From table
where table.`Analysis` in {?Analysis2}
Union all
Select {?Grp3} as NumberOfSamples, 3 as Grp, table.`Analysis`, table.`price`
From table
where table.`Analysis` in {?Analysis3}

Then you would insert a group on {command.Grp}, and create your total price by using a formula:

{command.NumberOfSamples}*{command.price}

This should get you close to what you want.

-LB

 

[CRCRCR]

Thanks for your reply.

I'll tackle this, this Friday afternoon.

I'll be away, next week, so thanks very much in advance.

I'll let you know how I get on.

I didn't think it would be possible.

Kai.