Crosstab and formulas Question

[dday01]

Hi All,

I have a crosstab report that displays months as columns and agencies as rows, the values are number of client encounters. What I am trying to do is have the report display rows only when the value of the last 3 months is zero. ie. the row for the last 12 months would only be displayed if the value for October, November and December is Zero. I am pretty much a novice and don't have the slightest idea as to how I can do this. Any help would be greatly appreciated.

Thanks,

D

 

[lbass]

If you are able to use a SQL expression, then try the following:

Create the following SQL expression {%last3mos}:

(select count(AKA.`ClientEncounters`) from Table AKA where
{fn YEAR(AKA.`Date`)} = {fn YEAR(Table.`Date`)} and
{fn MONTH(AKA.`Date`)} >= 9 and
{fn MONTH(AKA.`Date`)} <= 12 and
AKA.`AgencyID` = Table.`AgencyID`)

Replace "Table" with your table name wherever it appears in the formula, and replace "ClientEncounters","Date", and "AgencyID" with your exact field names. Leave AKA as is, since it is an alias table name.

If you want the count for the last 90 days (not necessarily the last three months of the year), then use:

(select count(AKA.`ClientEncounters`) from Table AKA where
AKA.`Date` >= {fn NOW()} - 90 and
AKA.`AgencyID` = Table.`AgencyID`)

Then create a formula {@Agency}:

if {%last3mos} = 0 then {table.AgencyID}

Then use {@Agency} as your row field instead of {table.AgencyID}. Then highlight it and choose "group options"->specified order->select all except the "0" option from the dropdown box. Then choose the "Others" tab and check "Discard all others".

-LB

 

[dday01]

Thanks LB, that looks like a good starting point, I'll play around with it a little bit. I neglected to mention that it has to work for the last 3 months based on when the report is run. (it is not just an end of year report, I need to be able to run it any month and return rows with a value of zero for the previous three months).

Thanks again,

D

 

[lbass]

For the last 3 full months, try:

(select count(AKA.`ClientEncounters`) from Table AKA where
AKA.`Date` >= {fn NOW()}-{fn DAYOFMONTH({fn NOW()})}-70 -
{fn DAYOFMONTH({fn NOW()}-{fn DAYOFMONTH({fn NOW()})}-70}+1 and
AKA.`Date` <= {fn NOW()}-{fn DAYOFMONTH({fn NOW()})} and
AKA.`AgencyID` = Table.`AgencyID`)

Given today's date, the formula would show the count for 9/1/04 to 11/30/04.

-LB