×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Count records of a group

Count records of a group

Count records of a group

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

CODE -->

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.

RE: Count records of a group

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

RE: Count records of a group

(OP)
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.

RE: Count records of a group

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

RE: Count records of a group

(OP)
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.

RE: Count records of a group

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

RE: Count records of a group

(OP)
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.

RE: Count records of a group

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

-LB

RE: Count records of a group

(OP)
Lbass,

Yes at the report level.

regards,

mick.

RE: Count records of a group

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

-LB

RE: Count records of a group

(OP)


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.

RE: Count records of a group

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

Regards,

mick.

RE: Count records of a group

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

RE: Count records of a group

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

RE: Count records of a group

(OP)


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.

RE: Count records of a group

(OP)


This is the running total i have created.

RE: Count records of a group

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

-LB

RE: Count records of a group

(OP)
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.

RE: Count records of a group

(OP)
Ha Ha... just seen you'd posted the same bigsmile

Mick.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close