Count of items with feature 1

[csperling]

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.

 

[kenhamady]

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/

 

[csperling]

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??

 

[kenhamady]

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/

 

[csperling]

I have hundreds of combinations, that would be a lot of tables

 

[kenhamady]

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/

 

[PeterDimitrov]

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.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[csperling]

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.