CRYSTAL ARRAY QUESTION

[RPOC]

I am using crystal reports version 11.
My task is to do the following.
I am working with a database that tracks if a client has a take out for a medication . All the medications are the same. I am going to be running this for a date range and i would like the final output to indicate

10 unique clients had 44 takeouts
9 unique clients had 3 takeouts
etc.....
each client has 1 line item per transaction so if the client had 10 takeouts there will be ten line items. I can group by client to get a sum of take outs. I know how to do this.


I think using a crystal array might be the best way to go with this , but am not too familiar with using arrays.
My thought would be to have a formula in the grouping that took the take out number and made it the array number example:
3 takeouts would be array[3] then to add a 1 to the internal value in array 3 (create a counter.) By the time the report is completed i would have all my arrays with values in them.

example array[3] would have a value of 5, array [4] would have 2 etc

My question is
1: how would i go about getting the output to occur for only those values that have a value greater then zero.
2. Any thoughts of an easier way of doing this?

any help would be appreciated.

thanks

 

[hilfy]

This is one of those reports where it might be much easier to get the data you're looking for by writing a command instead of using tables. Assuming that your database is one where Crystal will let you write a command (a command is just a SQL select statement!) and that you just need the client counts without any details, your SQL will look something like this:

Select Client_ID, count(*) as Transaction_Count
from Transactions
where Transaction_Date between {?Start_Date} and {?End_Date}

Note the use of parameters in the query - reports with commands are much more efficient when you create the parameters in the Command Editor so that they can be used in the command which will push the filter to the database for processing. When you create params in the report and use the Select Expert, Crystal will pull all of the data from the command into memory and then filter it.

Because this query work with transactions only, you don't have to worry about whether there are 0 transactions.

To create your report, group by Transaction count and then count the client ID to get your data.

If you need to add client detail info, do an inner join from the transaction table to the table with the client detail info - that way you will still be filtering out any clients with no transactions.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

I would try using a command for this, something like:

select table.clientID,table.otherfield,
(
select count(a.clientID)
from table a
where a.date>={?Start} and
a.date<{?End}+1
) as TransCnt
from table
where table.date>={?Start} and
table.date<{?End}+1

Create the parameters on the right within the command.

Then you can group on {command.TransCnt} in the main report and insert a distinctcount on {command.clientID} at the group level.

-LB

 

[RPOC]

Ok , I am not sure where the sql statement would go. I have not written any reports like that. on my menu i have
file, edit ,view,insert,format,database,report, window and help.

please let me know.

thanks
.

 

[hilfy]

Go to the Database Expert and open your connection (as if you were going to add tables to the report.) Inside the tree under the connection you should see "Add a command". Click on it and you'll be able to enter a SQL Select statement.

Some of this depends on what type of database you're connecting to - most of them will handle commands, but there are a few that don't. Since you haven't told us which db you're using, we can't tell you for sure whether this is doable on your end.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.