Need some help with counting in Crystal Reports

You can do in SQL. For example if your database is SQLServer you can create a view , stored procedure or command:

SELECT Part, MAX(Number) as Number
FROM <tableName>
WHERE .... if you want to put some filtering ....
GROUP BY Part

With other databases the syntax might be slightly different

I don't follow how you determine what the maximum number is.

I don't see  6 related to steel ring and the 3 for wheel seems to be from the next day.

mlmcc

mlmcc,

 I believe he's trying to get the total Number (quantity) for each part for each day, and then wants to see the maximum daily total for each part.  "Steel Ring" has 1 + 5 on 01/01, for a total of 6, and a total of 5 on 02/01, so the maximum daily total is 6.


 marco_greven,

 Assuming that what I said above is correct, vasto's suggestion won't work.  That's just giving you the highest individual Number for each part, not the highest daily total.

 Unfortunately, I don't think there's going to be any easy way to do this in CR.  You said that there could be a lot of parts.  How many parts could be on one report?  One option would be to save the maximum daily total for each part in an array, but CR arrays are limited to 1000 elements (unless that has changed in the more recent versions), so if there could be more than 1000 parts on a single report, that would complicate things.

 The simplest (and most efficient) thing might be if you could create the daily total in the db (eg. create a stored procedure or a CR Command that calculates the total and use that as the datasource for the report).  Then the report wouldn't have to calculate the total and you could just use Maximum on that field to get the highest total for each part.

 Another option might be a SQL Expression.  I've never really used them (they weren't an option in most of my reports), but you might be able to use one to calculate the daily total for each part, and then use Maximum on that.

 James

James,

You are right with my goal.
I am first trying to get the daily quantity per day, and then want a list of all used parts with the maximum daily total.

I think there will be less then 1000 parts in the report in reality.

I can get a report with the quantity of parts per day, but then my knowledge stops.

The result from my suggestion might be different than what marco_greven wants to see , but his request was for Parts and Max Numbers. I cannot see where totals are mentioned.
if you want the total - just replace MAX with SUM.

I am not very clear why James believes that my approaxh won't work.  In fact I am sure that there is no other way except using aggregate functions and SQL. Using arrays in Crystal is not different than creating your own "proto" aggregating which will be expensive , complicate , slow and not flexible.

If you want to see the Max number for the period then you can use a subquery , temp table or CTE - all depends from what is your database type.
The version with CTE in SQLServer 2008 will look like this:

WITH Result_CTE (DateValue,Part,TotalNumber)
AS  (
         SELECT [Date] as DateValue, Part, Sum(Number) as TotalNumber
         FROM <tableName> 
         WHERE .... if you want to put some filtering ....
         GROUP BY Date, Part
       )
SELECT r.DateValue, r.Part, r.TotalNumber, m.MaxNumber
FROM Result_CTE r  INNER JOIN (SELECT Part,MAX(TotalNumber) AS MaxNumber FROM Result_CTE GROUP BY Part) m on r.Part=m.Part;

Select all Open in new window

Create a command (you can also use this in a view or stored procedure) , paste the code above in it and it will return Date, Part , TotalNumber per Part per Date and Max number per part for the period. I guess this is all you need to show.  As far as I can see there are 9 lines of code - I cannot wait to see the solution with arrays in Crystal reports.
Again , this is for SQLServer 2008 - if your database is different you might need to change the code. Let me know what is your database type if this is the case.

If you want to see 2 different lists : one with the total numbers per day and one with the Max numbers for the period. There are plenty of options too:
 1. Create a view

SELECT [Date] as DateValue, Part, Sum(Number) as TotalNumber
FROM <tableName> 
WHERE .... if you want to put some filtering ....
GROUP BY Date, Part

Select all Open in new window

Your main report will get the data directly :
SELECT * FROM <viewname>
- add a subreport for the max numbers per period and use this SQL to get the data:
SELECT Part,MAX(TotalNumber) AS MaxNumber FROM <viewname> GROUP BY Part

2 . Another approach (which will be faster because there will be one call to the database) is to create a stored procedure

SELECT [Date] as DateValue, Part, Sum(Number) as TotalNumber
INTO #T FROM <tableName> 
WHERE .... if you want to put some filtering ....
GROUP BY Date, Part

SELECT * FROM #T

SELECT Part,MAX(TotalNumber) AS MaxNumber FROM #T GROUP BY Part

Select all Open in new window

Then use a report viewer like R-Tag (www.r-tag.com) , which supports external datasources and set the dataset returned by the stored procedure to your report. The returned dataset will contain 2 datatables - the first one will be assigned to your main report and will show totals per day, the second one to the subreport with the MAX values.
This viewer will allow you to create a query instead of a stored procedure , so you can use this approach even if you don't have permissions to create stored procedures

vasto,

 That was partly an inference on my part, because he said that the result for Steel Ring should be 6, and his data showed 1 and 5 for Steel Ring on the first date, so it appeared that he wanted a total for each date, and then to show the largest daily total for each part at the end.  And the OP confirmed that.

 Your original suggestion wouldn't work, because it was just taking the maximum for each part.  That's not what he wanted.

 James