one to many subset 1

[catbert]

I am reporting on data in an external database I have no control over, using crystal 10.

I have a table with one to many relationship. Main table has job details and linked table has job type and payment type:
e.g.
job no 1 ----- type = a
----- disc = y
job no 2 ----- type = a
----- disc = n

I want to count the types of jobs - which I have achieved with a running total, but then I want a subset which counts how many are "disc" or not - grouped by types
i.e. type a 15 - disc - 5

I have the report grouped by job number and can count all disc and count all types - but how can I get a subset when they are in the same table?

 

[dgillz]

They are already all in the same table.

If you want them to sort together, add a second group by disc.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[catbert]

thanks for the quick response -

I can't add a second group it is the same field - I guess i didn't explain well but I was trying to be brief.

The many table is structured like this:

job number - links tables
parameter code - additional info such as type of job or discounted (disc) flag
parameter value - value for each code - so job type will say A/B/C and disc will display Y or N

I have tried grouping on parameter code but of course this separates where one job has two entries - it may be a group A and also flagged as discounted.

hence why I can count total types of job, then total discounted - but I need the subset.

But if I write and if query ( job type is A AND disc is Y -) it returns all zeroes as any one record cannot be both -
I have grouped by job number so I can see where a job is A and discounted - but can't get the count to work.

I hope this makes more sense but I am starting to confuse myself!

 

[dgillz]

Write a formula:

If {type}="A" and {disc}="Y" then 1 else 0.

Write additional formulas for all possibilites. Sum this formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[catbert]

I tried that:

"But if I write and if query ( job type is A AND disc is Y -) it returns all zeroes as any one record cannot be both -"

the if query returns all zeroes. I tried grouping on job number and placing it in the group footer - but any one row returned will be one or other parameter value - never both - so the logic statement fails.

I used the following:

if {enquiry_attribute.param_value_code} = "Y"
and
{enquiry_attribute.param_value_code} = "A"
then 1 else 0

I don't know how to say - "grouping on job if any job number has both a parameter code of disc and A then count

 

[catbert]

Here is some sample data to make it clearer:
enquiry_number param_type_code param_value_code
109011 BULK B
109012 BULK A
109012 DISC Y
109013 BULK C

as you can see, if you count where code is Y and A there will be a null return.

I want to be able to say
A =1 of which 1 discounted
B =1 of which none
C= 1 of which none

as I said - the database structure is predetermined so I am stuck with finding a way for the Powers that Be. Thanks for the input so far. Frustrating isn't it!

 

[lbass]

Try grouping on enquiry number and then create a formula:

//{@A}:
if {table.param_value_code} in ["A","Y"] then 1

Then insert a running total {#Awithdisc}, using distinctcount of enquiry_number, evaluate using a formula:

sum({@A},{table.enquiry_number}) = 2

Reset never.

Repeat for {#Awithoutdiscount}, changing the evaluation formula to equal 1. Repeat for B and C.

-LB

 

[catbert]

Making progress, thanks lbass, BUT

in [A,Y]

is the same as using OR - so I get a true result where other job types are discounted, and all the A's whether discounted or not.

I think this has to be the right way to get around it - flagging the job in each case and summing but I can't seem to get it to work.

I am going to try a formula to say true or false for discounted then count job type if true - but surely there must be an easier way? I also seem to need a way to loop through each entry for each enquiry as it it only returns true if it was the first parameter added ( when formula placed in group footer for job no.) but having never looped anything it may take some time.

Unless anyone has a neater solution I will post back if I can get it to work.

 

[catbert]

I give up - I have tried flagging each row with true or false and summing or counting but of course in every case I am applying an AND statement which precludes any results.

Can anyone help with this?

 

[GJParker]

The way I would do this is by using a subreport.

In your main report group by param_value_code and add a selection formula

{MyTable.param_type_code} <> 'DISC'

This should give you results

109012 BULK A
109011 BULK B
109013 BULK C

you can the insert a summary count to evaluate ho wmany of each type there are

Then insert a subreport in the deatail section the sub report would be linked on enquiry number and ahave a selection formula to select

{MyTable.param_type_code} = 'DISC'

again insert a summary count to count the number of records.

You can the use shared variables to pass this value back to the main report and accumulate these at value_code level.

HTH




Gary Parker
MIS Data Analyst
Manchester, England

 

[catbert]

ah! the dreaded sub report- I was wondering about trying that but I know it slows everything down and the general consensus seems to be that they are evil. But your suggestions sounds very sensible and likely to work so I am going to give it a go - thanks. I'll post in joy if I have any success.

 

[lbass]

catbert,

When you tried my solution, did you insert the group on enquiry number and implement it exactly as I suggested?

I assumed that in your data there could be only on type other than discount per enquiry number. If that is the case then my solution should have worked, since the running total would be evaluating the presence of a discount and a particular other value per enquiry number.

-LB

 

[catbert]

lbass
with profuse apologies I thought I had followed your instructions properly but I have only just had a chance to go back to this and the grouping was all up the creek and now it works great.

It seems such a simple solution now, I was originally thrown by the fact that it was flagging 1 for every A and every discount - but of course the count then only looks at those were both are flagged.

I am sure this method is useful for various problems where database structure restricts more simple counts.

Many thanks to everyone who helped and persisted with this odd one

CB