Count records of a group

[ClowneUser]

Hi,
OK so I've got some data an example below

TRANSACTION_ID MACHINE_NO PART_NO QTY VALUE ORDER_NO DATE
1234              123        abc   5    12    123     17/07/2018
1234              123        abc   5    7     123     17/07/2018
1234              123        abc   5    3     123     17/07/2018
5678              456        def   10   25    456     17/07/2018
5678              456        def   10   4     456     17/07/2018

in the table for each transaction there are multiple entries, this is basically different cost centres, ie machine cost, labour cost etc.. sometimes there are only 2 cost sets used sometimes there is more more or 4 there is no way to know.

So what I need to be able to do is to count the records per transaction id and use this to get the actual QTY ( if this makes sense ) this is my thinking, maybe there is an easier way to do this in Crystal?

Any help would be appreciated.

Regards,

Mick.

 

[lbass]

Insert a group on transaction ID and then insert a count on transaction ID and/or insert a sum on QTY at the group level.

-LB

 

[ClowneUser]

Hi LBass,

Thanks for your reply, OK so I have the transaction count, but how do I use this to get the correct qty, I've tried a few different formulas but nothing is giving me the correct amount?

Thanks,
Mick.

 

[lbass]

Are you saying there is only one quantity per transaction ID and that the qty’s are repeating? If so, insert a running total that sums quantity on change of the group (transaction ID). Place the running total in a footer section.

-LB

 

[ClowneUser]

Lbass,

Thanks again,i thought I wouldn't have to go into great detail with describing what other grouping I needed.. sorry for that, I'll add some more detail that may change the approach to this....

I also need to group on date and I also need to group by the part type ( which is just the part initial ) I've created a formula for this, whether these are groups or I can just sort by these? anyway adding these into the mix I'm not able to get the transaction count working correctly.

Hope this all makes sense?

Regards,

mick.

 

[lbass]

No, that still isn’t enough info. What are groups #1, #2, and #3? If you just want a count of total transactions, insert a distinctcount on transaction ID. I thought you wanted a sum of quantity. Please explain what summaries you are trying to do and what level you want them—report totals? Or at some group level? If so, which one?

-LB

 

[ClowneUser]

LBass,

OK.. currently on the report I have the following groups..

Group1 - Date

Group2 - Initial ( the part type )

Group3 - Transaction id - this was just a test to see if I could get the count from this, but unfortunately not.

So I want to break the report up firstly by date, then by part type ( this will group all parts with the same initial together, I don't know what they want this for, would make it easier if they didn't lol ) so these are the only 2 groups I need, but I need somehow to workout the correct QTY per part type.

Regards,

mick.

 

[lbass]

Do you mean at the report level—disregarding date and transaction ID?

-LB

 

[ClowneUser]

Lbass,

Yes at the report level.

regards,

mick.

 

[lbass]

Try putting a crosstab in the report footer and use partno as the row field and sum of quantity as the summary field.

-LB

 

[ClowneUser]

LBass,
Thanks for your suggestion, I've attached a image of how i would like the report to be laid out, I'm not sure a cross tab would achieve this?
But any thoughts/ideas would be appreciated.

regards,

mick.

 

[ClowneUser]

Or would it be easier to add grouping to the Oracle view?

Regards,

mick.

 

[lbass]

It looks like you could just use running totals that evaluate on change of group (part number) and reset on change of group (part number). Place the part number summaries in the group footer for part number.

To get the correct totals in the date footer, use another running total that evaluates on change of group (part no) and resets on change of group (date).

If you can’t get this to work, then you need to attach a file with data.

-LB

 

[ClowneUser]

LB, sorry I'm not following you, I've attached a basic report with data saved with it.

If you could take a look I would appreciate it.

Regards,
Mick.

 

[lbass]

For your existing running total, you should be using QTY as the field to be summarized, and the summary should be a sum. Otherwise, correct.

Your groups are set up incorrectly, too. Group #1 should be year/week, Group #2 should be @initial (part number), group #3 should be transaction ID. You can just go into design mode and drag the groups to their correct position.

The running totals should be in the group #2 footer.

For the group #1 total, do the same running total set up except have it reset on change of year/week.

For the report total, do the same running total, reset never.

-LB

 

[ClowneUser]

LB thanks for the pointers..

Yes I had the groups as you said initially, but couldn't get the correct output so thought I'd move them around..

I think I have the running totals setup as you said, but I'm still not getting the correct QTY, see attached image, as you can see for week 15 2018 there are 2 transactions for Parts beginning with 'C' so I would expect the QTY to be 6, but its only giving 5, which is what I was getting, also Part 'M' the QTY should be much more than 4, so I'm not sure what is happening there?

Regards,

mick.

 

[ClowneUser]

This is the running total i have created.

 

[lbass]

Try changing the evaluation to be on change of group (Transaction ID).

-LB

 

[ClowneUser]

OK I think I've got it working, I changed the Evaluate to be the Transaction ID group and it now looks like its giving the correct figures...

Still got some checking to do but looks a little better now.

Thanks,

mick.

 

[ClowneUser]

Ha Ha... just seen you'd posted the same

Mick.