×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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!

*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

Count of items with feature

Count of items with feature

Count of items with feature

(OP)
I need to pull information from this table:



I'd like for my report to output the Stock Code for any Sales Order which has the line "~~HARD ANODIZE WATER JACKET~~ 151.00". I'd like for the stock codes to be grouped and the count for each stock code to be displayed.

Stock Code LineType will always = 1.

RE: Count of items with feature

To solve this you need to add the sales order table to the report two times. The second time Crystal will give the table a slightly different name (alias) usually by adding _1 to the end of the table name. Then you treat these as if they were two separate tables. You link them based on the Sales Order. Then you add two filters in the selection formula. Something like this:

{SalesOrder.LineType} = "1" and
{SalesOrder_1.NComment} = "~~HARD ANODIZE WATER JACKET~~ 151.00"

Note that the two rules reference one field from each of the two tables. From there you can group by StockCode (from the FIRST table) and count the number or records. If the comment can occur twice on the same SalesOrder than you might need to do a DistinctCount of the SalesOrder instead of just a count.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks
http://www.kenhamady.com/

RE: Count of items with feature

(OP)
Awesome, that did the trick (see report below)!! Thank you Ken!

Would I be able to do the same thing to get counts of Comment Combinations??



RE: Count of items with feature

I think so. Filter each instance for one of the comment strings. You could even have 3 or 4 instances if you needed more complex combinations.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks
http://www.kenhamady.com/

RE: Count of items with feature

(OP)
I have hundreds of combinations, that would be a lot of tables :(

RE: Count of items with feature

Maybe I wasn't clear. I think each combination will need another report. You could reuse this report for any combination of 2 records. Just change the filter. Add another instance if you need a report that find a combination of 3 different records and change the filter to include three rules.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks
http://www.kenhamady.com/

RE: Count of items with feature

If I understand correctly, you want to get the number of times each comment is used per StockCode ignoring the order number. If this is your case, you can use a command like this :

SELECT o.MStockCode, cmm .NComment, Count(*) as NumberOfComments
FROM Orders o
INNER JOIN (
SELECT SalesOrder, NComment
FROM Orders
WHERE LineType=6
) cmm ON cmm .SalesOrder=o.SalesOrder and o.LineType=1
GROUP BY o.MStockCode, cmm .NComment

The inline query will get each comment per stock code using salesorder number to match them. The main query and the group statement will count the number of the same comments per stock code (ignoring the sales order). Again , I am not sure if this is what you want, but usually grouping is easier and more flexible using a command, so you may benefit if you explore this approach.

www.R-Tag.com Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

RE: Count of items with feature

(OP)
I believe we're heading down the right path. I want to group each stock code combination (by stock code) that is exactly the same and provide a count for each group.

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! Already a Member? Login

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