Need some help with counting in Crystal Reports

Posted on 2013-01-04
Last Modified: 2013-01-07

I need some help how to Create a report in Crystal reports...

I have data which contains parts used on the job.
The data looks like this:

Date        Employee         Number           Part
1-1.           2.                      2.                      Wheel
1-1.           2.                      4.                      Bolts
1-1.           1.                      1.                      Steel ring
1-1.           2.                      5.                      Steel ring

2-1.           2.                       3.                      Wheel
2-1.           2.                       5.                      Steel ring

I want to Create a report which shows all the used parts and the maximum numbers used on a day. Like this:

Part.             Number
Wheel.           3
Steel ring.     6
Bolts.             4

I can count the parts used per day. But how do i get the max numbers per part after that?
There can be a lot of parts being used.
Question by:marco_greven
  • 2
  • 2
  • 2
  • +1
LVL 18

Expert Comment

ID: 38746036
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 ....

With other databases the syntax might be slightly different
LVL 100

Expert Comment

ID: 38746066
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.

LVL 34

Expert Comment

ID: 38746395

 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.


 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.

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.


Author Comment

ID: 38746918

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.
LVL 18

Expert Comment

ID: 38747147
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;

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

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 Part,MAX(TotalNumber) AS MaxNumber FROM #T GROUP BY Part

Open in new window

Then use a report viewer like R-Tag ( , 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
LVL 34

Expert Comment

ID: 38748872

 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.

LVL 100

Accepted Solution

mlmcc earned 500 total points
ID: 38748951
The SQL may work but if the number of pars for a given timeframe is small you can do it in the report with minimal impact


Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Change start of Week from Sunday to Friday 8 51
Formatting a Crystal Report 2 56
Left and Right Trim a field in Crystal Report 2 94
Conditional Text in Crystal 4 51
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question