Solved

Need some help with counting in Crystal Reports

Posted on 2013-01-04
7
303 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:marco_greven
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 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

10 Experts available now in Live!

Get 1:1 Help Now