Print Page | Close Window

Count Items in Group

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=9293
Printed Date: 07 May 2024 at 9:16am


Topic: Count Items in Group
Posted By: Miills
Subject: Count Items in Group
Date Posted: 04 Mar 2010 at 9:05am

I have seen a few posts relating to this but have not been able to implement the answers to solve my problem so my apologies if this is a duplicate post!

 
I have a database with information on our company's computer assets. Of course, there's a table with a unique record for each workstation, printer, server, etc. Each has a unique number. Each of these assets has a set of components in another table (like a mouse, keyboard, etc), and an associated price. I need to create a report that can display the total money in the asset (sum of the cost of the components for all assets of that type), but also the number of that type of asset that the company owns. So it would look like this:
 
Asset: Computers  Count: 53   TotalCost: $102,350

Asset: Printers   Count: 4    TotalCost: $26,000

 

And so on...So I need to group by the asset type and put the component cost in the details section so that I can do running totals, right? I get everything to work except the count. it counts the number of components, not the number of assets in the group. {A computer with 5 components would count as "5" not as "1"} So how do I count the group items, not the detail items? I have seen some cryptic answers to this problem, but all entail writing some code in the sql expression or formulas, which I'm not exactly comfortable with yet. I can get it to work if I take the cost out of the details section but I believe I need it in there to calculate the $total.

 
Any help? When I figure out how to do this, I'll be able to add a top level group to group assets by branch office.

Thanks so much in advance!!

Katie


Replies:
Posted By: DBlank
Date Posted: 04 Mar 2010 at 9:10am
Please post a little sample row level dataliley you can just do a distinctcount of the correct primarykey (reset at the correct grouping level) but you may need to post sample some row level data (including PK's) to and how you want it counted.

Posted By: Miills
Date Posted: 04 Mar 2010 at 9:58am
I hope this will help. I have listed it by table name, then the field names, then small sample data:
 

WorkStation

WS_Num             AssetTypeID      PurchaseDate

53                           46                           10/1/2007

54                           28                           8/16/2009
AssetType

AssetTypeID      AssetType

46                           Computer

54                           Printer
Components

WS_Num             Component        PurchasePrice

54                           Main Unit           $5,000
54                           Power Cables     $100
53                           CPU                    $1,500
53                           Optical Mouse   $20

53                           Keyboard            $30

53                           Docking Station $50

I want the output to read:

Computer         1          $1600
Printer              1          $5100
 
i have tried doing count, distinct count, summary  and running totals on the WS_Num (my PK) to no avail. I still get the wrong count values, in this case Computer  4  $1600 and Printer  2  $5100.

Posted By: DBlank
Date Posted: 04 Mar 2010 at 10:24am
SO you are joining WS to AT on assestypeID and also WS to COmponents on WS_Num, correct?
If you do an insert sumamry on the Workstation.WS_Num as a DistinctCount Summary Location on the corerct grouping level...
DistinctCount(Workstation.WS_Num, grouping field here)

Posted By: Miills
Date Posted: 04 Mar 2010 at 11:02am
Sha-Bam!
You are amazing. I really thought I had tried everything.
Thanks so much!


Print Page | Close Window