[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Need some help with counting in Crystal Reports

Posted on 2013-01-04
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 101

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 35

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.

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


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 (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
LVL 35

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 101

Accepted Solution

mlmcc earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 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