Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

count of records based on criteria 1

Status
Not open for further replies.

NewbiDoobie

Technical User
Jul 25, 2005
63
US
I am fairly new to CR XI and I am trying to develp a formula to determine the original record count then the final active record count.

How can I put these conditions in formula?

count({Transactions.UserId})
isnull({Transactions.InactiveDate})
or
{Transactions.InactiveDate} <= DateTime (2005, 11, 08, 00, 00, 00)
and {Transactions.Transaction_Type} = "Opt-In
 
Try creating a formula:

if
(isnull({Transactions.InactiveDate}) or
{Transactions.InactiveDate} <= DateTime (2005, 11, 08, 00, 00, 00)
) and
{Transactions.Transaction_Type} = "Opt-In" then 1

Right click on this formula and insert a sum, not a count.

-LB
 
So you don't need any rows returned, just the summary values?

You can do this within Crystal, but it's pretty inefficient to do so.

A Command object (SQL) would be fast.

select 'nulls' Typereturn, count(Transactions.UserId)
from table
where Transactions.InactiveDate is null
UNION ALL
select 'dates' Typereturn, count(Transactions.UserId)
from table
where Transactions.InactiveDate < '2005-11-08 00:00:00'

Now you'll get 2 rows, and the datbase does all of the work.

Of course there's a hitch here, you ask for help and then don't include your dtaabase being used, a basic that should be included with every post.

AN alternative within Crystal is to use 2 Running Totals and in the Evaluate->Use a Formula place the criteria.

Slow and cumbersome.

-k
 
Synapse,

Can you have multiple command objects in 1 report? I need to do this first for the opt-in, then the opt-outs column for this week and the previous 9 weeks.

Also, we are creating this in access and then moving it to MYSQL. Will the command objects still be effective?
 
Why not just use a Union All in one Command? It'll be faster and you'll get the same results.

Just group each by the week, add in a max(date) column for the week, and add whatever criteria.

The short answer to your multiple commands is no, although I've seen people cheat it.

It's easy enough to use subreports to return other values, you can even pass them back to the main report and vice versa using shared variables.

-k
 
Ooops, should have addressed the MS Access issue, didn't pay attention, sorry.

A better method might be to create an MS Access query to return a single data set, and then use that in the report.

When you then move to MySQL, link (not import) the MySQL tables into the MS Access database, then recreate the queries based on the MySQL structure and the same query will work in Crystal.

Also consider that Oracle is now giving away a 4GB version to compete with MySQL, and were I to tackle implementing smaller databases, I would select this over MySQL.

If you're going to scale over 4GB, it could get costly, but I have a bad history with the reliability and functionality of MySQL anyway.

-k
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top