How to Assign 2 different PARAMETER in crystal report?

Question

select 
(distributor_code+ '-' +master_dealer_code+ '-' +state_code+ '-' +dealer_code) as agent_code, 
agent_type, company,contact_person, status, created_date as date,
(CASE
 WHEN contact_mobile_no IS NOT NULL THEN contact_mobile_no
 WHEN contact_mobile_no IS NULL and contact_office_no IS NOT NULL THEN contact_office_no
 ELSE NULL
END) as contact_no

from sdms_agent  

where agent_type ='{?AgentType}' and status ='{?Status}'

order by (distributor_code+ '-' +master_dealer_code+ '-' +state_code+ '-' +dealer_code)

Actually I want to set two parameters in the crystal report. where agent_type (Main,DIstributor,Master Dealer,Dealer) & status(active,unsigned,terminate,suspend).

I actually get the parameter. But when I choose the agent_type or status, I can only one by one choose and view on the report. Example: For agent_type I choose Distributor, status I choose Active. It sure will come out with the right report for it.

But let's say I now want to view the Distributor with all the status. But it's not working, even though I tried putting '%e%'. It really can't come out with the result. I want a list which contains distributors with different status, or alternatively different agent_type with one status, or view all in the report.

Answer 1

The easiest way within Crystal that I can think of to handle this is with a switch statement in the formula of the select expert.

In this example you could have something like:

switch ( 
{?AgentType} = "Distributor", {table1;1.agent_type} = "Distributor",
{?AgentType} = "Other", {table1;1.agent_type} = "other",
true, true
)

If you are using SQL Server then the better approach would be to handle it in the query with something like the following:

select (distributor_code+ '-' +master_dealer_code+ '-' +state_code+ '-' +dealer_code)as agent_code , agent_type, company,contact_person, status, created_date as date, (CASE WHEN contact_mobile_no IS NOT NULL THEN contact_mobile_no WHEN contact_mobile_no IS NULL and contact_office_no IS NOT NULL THEN contact_office_no ELSE NULL END )as contact_no
from sdms_agent
where agent_type = case @AgentType when '' then agent_type else @AgentType end
and status = case @Status when -1 then status else @Status end
order by (distributor_code+ '-' +master_dealer_code+ '-' +state_code+ '-' +dealer_code)

In the query you'd pass in a empty string if you wanted it to show all of the AgentTypes or a -1 if you wanted it to show all of the Statuses.

Hope it helps.