Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Getting Counts from table using 2 parameters Post Reply Post New Topic
Author Message
DC_Wings
Newbie
Newbie
Avatar

Joined: 12 Mar 2008
Location: United States
Online Status: Offline
Posts: 4
Quote DC_Wings Replybullet Topic: Getting Counts from table using 2 parameters
    Posted: 12 Mar 2008 at 3:07pm
Hi. I am new to Crystal Reports going thru VB.Net 2005/MS-SQL. I have hit a wall in trying to build a report and could use some help.
 
I have to build a report that captures sales processed thru 9 sites. The sales need to be reported for each site divided by the month they were completed in.
 
I tried this --- ({caselog.case_district}="1") and (Month ({caselog.case_rptdate})=1) --- but when put in the report, all I got was a the word False next to the site number and then the site number repeated and a "False" appeared next to the site number, and so for each record in the table. Not good...
 
What I'm looking for is this.....
 
 
Unit    Jan    Feb    Mar    Apr      ....     YTD
1          15     24      11      17                67
2          24     11      35        5                75
3          15     19      10      21                65
4          21     31      11      12                75
...
 
 
Someone else mentioned something about "running totals", but I couldn't find that anywhere in the Formula Builder. Can somebody point me in the right direction? Any help would be greatly appreciated....  Thanks....
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 12 Mar 2008 at 3:28pm
I don't think this should be a big deal to fix, but I'm confused by the question. Why are you testing if the two fields are equal to 1? How does this help you with the calculation of dividing sales by month? And what does 1 have to do with anything?

Also, a running total is not in the formula builder. You can right-click on a field and select Insert > Running Total. This works for simple totals without much logic needed. But in your case, you might need more work done to test when the formula should be calculated. In that case you have to create your own formula using a Global variable and in the formula use your logic to determine when to increment the variable's total.

Does this help at all?
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
DC_Wings
Newbie
Newbie
Avatar

Joined: 12 Mar 2008
Location: United States
Online Status: Offline
Posts: 4
Quote DC_Wings Replybullet Posted: 13 Mar 2008 at 10:38am
My apologies....
 
the 1's represent January (ie month #1) and District 1 (of 10) ....
 
I was thinking I had to provide a check against each record....
 
What has to be captured is each event that happened in that district for that month...
 
my thought was a sql event like ---> select all from caselog for district = 1 and month(of rptdate) =1 for example, but how could I represent that in CR?


Edited by DC_Wings - 13 Mar 2008 at 11:09am
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 14 Mar 2008 at 2:17pm

Group on {caselog.case_district}.  Now create a set of running totals - one for each month - using something like the following format:

Field to summarize = the field you want to count
Type of summary = Count (or possibly DistinctCount, depending on your data.)
Evaluate: Use a Formula.  The formula will be like this: Month ({caselog.case_rptdate})=1 with the number changing for each month. 
Reset: On change of Group.  Select your case district group.

Put your data in the group footer and suppress the details section.
 
-Dell
IP IP Logged
DC_Wings
Newbie
Newbie
Avatar

Joined: 12 Mar 2008
Location: United States
Online Status: Offline
Posts: 4
Quote DC_Wings Replybullet Posted: 17 Mar 2008 at 2:16pm
I'm stumped.... I did everything exactly as you laid it out, but all I got was a 0 in my count field, even though I know there should be a total of 2 records that meet the criteria. I tried both COUNT and DISTINCT COUNT and got the same result...
 
The only thing I did extra was create a parmeter to set the SQL to only bring in the records for the year selected by the user. But even doing that leaves me with two records for the current year....
 
What really burns is that there is no way to actually step through the process to see what's being returned. There supposedly is a "debug mode" but I can't get it to work....
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 17 Mar 2008 at 3:32pm
To test/debug, put some data in the details section along with your running total field.  You should see it increment for each record.  If it doesn't then something's not set up correctly.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.000 seconds.