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
Solved

Crystal Reports – How to count a summed field

Posted on 2014-01-09
7
3,870 Views
Last Modified: 2014-01-18
Hello Everyone, I have a crystal report which shows a list of customers and how much then spend each year…(see below)

Customer             2014      2013      2012      2011
customer1            0         $2,000      1,000          0
customer2                   1,500       0      4,000       2,500
customer3                     500      1,000        0               0
customer4            0      1,000      2,000          0      

The sales figures are a sum of everything that customer purchased in each year.  I want to count how many customer had sales in each year.  When I try and create a summary for all accounts, my formula does not show up to select.  Is there a way to do it in crystal?  I wanted to get a total like this…
2014  - 2      2013 – 3   2012 -3    2011 – 1

Thanks,

Bill
0
Comment
Question by:bjennings
  • 3
  • 2
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39769755
Try this

DistinctCount({CustomerField})

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 39770684
mlmcc,

 The OP is looking for a count for each year.  That's obviously going to give them a count for the entire report.


 bjennings,

 Does the report look like the example in your post?

 Assuming that it does, is that a cross-tab?  If not, how are you producing that output?  Is the report grouped by the year?

 If you have a cross-tab or report that is grouped by year, then you might simply be able to do a distinct count, like mlmcc suggested (but for each year).  But if you want to get the counts for all of the years together on one line, as in your post, that will take some more work.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39771021
The grouped report would require changing the formula to
   
   DistinctCount({CustomerField},{YearGroupingField})


James brings up a good idea.  Is it a crosstab?
If not will a cross tab work?

If it isn't a crosstab or a grouped report how are you getting the values?

mlmcc
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:bjennings
ID: 39771308
Thank you for helping me on this issue!.....This report gives me the sales for all of our customers going back 4 years.  It is not a cross tab report.  The data source on this report has the following fields: Customer Account, salesdate, salesamt, and returnamt.  I have the report grouped by customer account number.  I created 4 formula fields to find the total sales for that account in a given year.   The formula below (Revenue4years) gives me all the revenue for an account 4 years ago.  I then have 3 more formula fields where I just change the number of years.

if YEAR({Report1.Salesdate}) = Year(DateAdd ("m",-1 ,CurrentDate ))-4
 then (Report.salesamt}-{Report1.returnamt)
else 0

In my example above I created a summary that summed up each these formula fields and place it on the group footer of the customer account number group .
I have tried creating a new summary to count each of the summary formulas that had sales, but the summaries do not show up as available.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 39771710
FWIW, it looks like the existing report (total sales by customer and year) could be handled by a cross-tab, but if you're happy with what you've got, that's fine.  Just throwing it out there.


 But a cross-tab may be the easiest way to get your customer counts by year.  I mean a separate cross-tab, just for the counts.  The rest of your report would remain as is.

 You just want one row, so you can just create a "dummy" formula (call it whatever you like) to use for the rows.  The formula will produce the same value for each record, so you get one row in the cross-tab.  You could just create a formula containing an empty string, and then you'd get an empty label for the row.  Or you might want to make it something like "Sales Per Year", and that would become your label for the counts.

 Create a formula like the following (call it whatever you like) and use it for the cross-tab columns:
Year ({Report1.Salesdate})

 If you want the most recent year first (as in your example), go into the group options for the column and change the sort order.

 For the summary, select your Customer Account field and make the summary a Distinct Count.

 And you probably want to go to the Customize Style tab and check the Suppress options for row and column grand totals (and you can remove the grid lines if you like, change the formatting for the year in the column header, etc.).

 I think that will give you the counts that you're looking for.  If you just don't want to use a cross-tab for some reason, there are other ways to get the counts, but the cross-tab is probably by far the easiest.

 James
0
 

Author Closing Comment

by:bjennings
ID: 39789361
Thank you all!
0
 
LVL 34

Expert Comment

by:James0628
ID: 39790576
You're welcome.  Glad I could help.

 James
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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. …
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

790 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