Chart seems to ignore formula

[Kevinski]

Hi

I have a monthly report which counts the number of service calls logged by each individual each month (runs as Last Full Month).

Some clients who receive this report are only interested in anyone who logs more than 5 calls, some more than 10 others more than 25 etc. I have a parameter to select the CompanyID and then I have a formula like so:

if {CCS_JN_Service_Desk_TM.Company ID} = '1234'
then (DistinctCount ({CCS_JN_Service_Desk_TM.Call Number}, {CCS_JN_Service_Desk_TM.Contact Name})) >= 6
else if {CCS_JN_Service_Desk_TM.Company ID} = '4567'
then (DistinctCount ({CCS_JN_Service_Desk_TM.Call Number}, {CCS_JN_Service_Desk_TM.Contact Name})) >= 25

and I could add a new line for each customers different requirements. But 2 things bug me:

1) If I chart the results I get everyone for that client not just the people with >= 6 or >= 25 calls (the report header details are correct but the chart charts everyone)

2) There must be a better way of selecting - how could I have an additional parameter which prompts for the number of calls to limt the data to as well as the company ID?

 

[lbass]

First, set up the parameter in the record selection formula:

{?company} = {CCS_JN_Service_Desk_TM.Company ID}

Next create a formula {@topN}:

if {?CompanyID} = 1234 and
distinctcount({CCS_JN_Service_Desk_TM.Call Number}, {CCS_JN_Service_Desk_TM.Contact Name}) >= 6 then {CCS_JN_Service_Desk_TM.Contact Name} else
if {?CompanyID} = 4567 and
distinctcount({CCS_JN_Service_Desk_TM.Call Number}, {CCS_JN_Service_Desk_TM.Contact Name}) >= 25 then {CCS_JN_Service_Desk_TM.Contact Name} else
if {?CompanyID} = 8910 and
distinctcount({CCS_JN_Service_Desk_TM.Call Number}, {CCS_JN_Service_Desk_TM.Contact Name}) > 15 then {CCS_JN_Service_Desk_TM.Contact Name} //etc.

Use {@topN} as your "on change of" field in the advanced layout chart. In the same screen, click on {@topN} and choose "Order"->specified and add ALL possible values of {CCS_JN_Service_Desk_TM.Contact Name} (not just the ones in the dropdown for your current parameter selection) so that they will be available for charting when the parameter changes. Then select the "Other" tab and choose "Discard all others". Then add the summary field "DistinctCount of
{CCS_JN_Service_Desk_TM.Contact Name}".

-LB