Solved

Need some help with counting in Crystal Reports

Posted on 2013-01-04
7
307 Views
Last Modified: 2013-01-07
Hello,

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.
0
Comment
Question by:marco_greven
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Expert Comment

by:vasto
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 ....
GROUP BY Part

With other databases the syntax might be slightly different
0
 
LVL 100

Expert Comment

by:mlmcc
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.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 38746395
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:marco_greven
ID: 38746918
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.
0
 
LVL 18

Expert Comment

by:vasto
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 * FROM #T

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
0
 
LVL 34

Expert Comment

by:James0628
ID: 38748872
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
0
 
LVL 100

Accepted Solution

by:
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

mlmcc
Parts-27985563.rpt
Parts-27985563.xls
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Report 2008 script 4 56
Crystal Reports Sub Report 4 79
Help with a Crystal Report Formula - remove time 5 31
A string is required here error 20 5
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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now