Print Page | Close Window

Select count where field = "whatever"

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12380
Printed Date: 06 May 2024 at 9:56pm


Topic: Select count where field = "whatever"
Posted By: macking
Subject: Select count where field = "whatever"
Date Posted: 20 Feb 2011 at 1:43pm
Going crazy trying to figure out how to get a simple mysql permit into CR.

I need to have just a count of how many times a field is equal to "whatever".

So in my report it has a text box: How Many Instances and then a field that has the qurey behind it and shows XXX whatever the number is.

Like: Select count from database.customer where state = california

Can someone help me out here?


Replies:
Posted By: lockwelle
Date Posted: 21 Feb 2011 at 5:48am
use a global variable.  In the formula, you would have something like:
global numbervar thisCount;
if {table.field} = "California" Then
 thisCount := thisCount + 1;
 
""//hides the formula
 
this formula would be placed in the detail section
 
in the footer, you would have another formula:
global numbervar thisTotal
 

Posted By: macking
Date Posted: 21 Feb 2011 at 7:34am
Thanks for your help lockwelle, I appreciate it.

This got me a little further, and I realize that I need this done a little differently.

To try to make my question a little better look at it like this:

My Table:

Server / User / DateTime / Action

Row 1: miami1 / carlosj / 05-04-2010 11:23:31 / Exported source
Row 2: miami1 / davidc / 05-04-2010 11:24:41 / Ran DB import
Row 3: denver7/ carlosj / 05-04-2010 11:27:29 / Exported source

I need to have all of the servers grouped in my report.

Under each grouping I need several totals for exampe: total count of action where action = Exported source

But I don't want to write a separate query for each total for each server.

Can CR know that I am on the group miami and pull the count for action where = exported source?

Thanks again for your expert help

Posted By: lockwelle
Date Posted: 22 Feb 2011 at 3:01am
while it may not know which server it is on(of course it does, but for the purpose of the report), you can reset the variable, so that each group gets its own totals.  If the servers data are interleaved in the display(not in a group) then it becomes harder as you need more variables, or to some other structure to store your information.  You could use an array, it just becomes a bit more complex as all of the arrays are 1 dimensional, so it is harder to keep track of information as you need several arrays to accomplish simple tasks.
 
HTH

Posted By: macking
Date Posted: 22 Feb 2011 at 4:41am
WOW, that's probably too deep for me to understand, I will try though.

So, my server data should be grouped, I don't think it is interleaved, very simple table with what I thought would be a simple report.

So I should just reset this variable for each group.

Any more tips?

Posted By: macking
Date Posted: 22 Feb 2011 at 5:10am
So do I create a formula to reset my count? Where do I put that formula on the report layout?

Posted By: lockwelle
Date Posted: 22 Feb 2011 at 9:08am
to reset a variable, it's something like:
global numbervar aVar := 0;
"" //hides the reset from being display.
 
Usually I would place a reset formula in the group header.
 
Running totals will also work, but they're not my forte...DBlank's the master of them.


Print Page | Close Window