Crystal Reports: Parameter setup

[former_member548403]

One of those days, I swear.

So, I'm modifying a report to simplify the parameter selections for users. For example, if a user selects a "Captain" parameter, the report is to pull all the data that pertains to the word Captain and captain-like. The selection is a single selection.

Now, the parameter works just fine until another parameter is paired with the position parameter. For example, if a position parameter is paired with "Day" parameter, the data pulled by the report is all wrong.

This is what I have in the Selection Expert.

if {?Day_Night} ='Day' then {?Day_Night}={Command.Day}
else if {?Day_Night}='Night' then {?Day_Night}={Command.Night}
else if {?Day_Night}='All' then {?Day_Night}= {Command.day_night}
and
If {?Position} = "ALL" then {Command.category} like "*"
else if {?Position} = "Captain" then {Command.category} like "Captain*"
else if {?Position} = "Driver" then {Command.category} like "Driver*"
else if {?Position} = "EOC staff" then {Command.category} like "EOC staff*"
else if {?Position} = "Firefighter" then {Command.category} like "Firefighter*"
else if {?Position} = "Lieutenant" then {Command.category} like "Lieutenant*"
else if {?Position} = "Master" then {Command.category} like "Master*"
else if {?Position} = "PSCS" then {Command.category} like "PSCS*"

And my SQL.

select  *,
case when Payhours_day>0 then 'Day' end as 'Day',
Case	when Payhours_night>0 then 'Night' end as 'Night',
case	when total_payhours>0 then 'All' end as 'day_night'
from vw_position_OTP t
inner join MCFRS_LKP_Position p on t.Pos_Desc_Ch=p.Pos_Desc_Ch

What could be the missing or broken link here? Please help!

[abhilash_kumar · Accepted solution]

Hi Kenshin,

Please see if this works:

(
{?Day_Night}='All'
OR
({?Day_Night} ='Day' AND {?Day_Night}={Command.Day})
OR
({?Day_Night}='Night' AND {?Day_Night}={Command.Night})
)
AND
(
{?Position} = "ALL"
OR
({?Position} = "Captain" AND {Command.category} like "Captain*")
OR
({?Position} = "Driver" AND {Command.category} like "Driver*")
OR
({?Position} = "EOC staff" AND {Command.category} like "EOC staff*")
OR
({?Position} = "Firefighter" AND {Command.category} like "Firefighter*")
OR
({?Position} = "Lieutenant" AND {Command.category} like "Lieutenant*")
OR
({?Position} = "Master" AND {Command.category} like "Master*")
OR
({?Position} = "PSCS" AND {Command.category} like "PSCS*")
)

-Abhilash

[former_member548403 · Accepted solution]

Thanks! It looks primitive compared to yours, but I think this works, too.

if ({?Day_Night} ='Day') and ({?Position} = "ALL") then ({?Day_Night}={Command.Day}; {Command.category} like "*") 
else if ({?Day_Night} ='Night') and ({?Position} = "ALL") then ({?Day_Night}={Command.Night}; {Command.category} like "*") 
else if ({?Day_Night} ='All') and ({?Position} = "ALL") then ({?Day_Night}={Command.day_night}; {Command.category} like "*") ...

[DellSC]

You shouldn't be using the Select Expert to filter a command. For the reasons why, see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

To include this in the Where clause of your query, it might look like this (assuming that the parameter is multi-select...):

(
  ('All' in {?Position}) OR
  (SubStr(t.Category, 1, 4) in {?Position})

) 
AND 
(
  ('{?Day_Night}' = 'All' and total_payhours>0) OR
  ('{?Day_Night}' = 'Day' and Payhours_day>0) OR
  ('{?Day_Night}' = 'Night' and Payhours_night>0)
)

Note the single quotes around the {?Day_Night} parameter - as outlined in the blog, if the parameter is a single select with at string value, you need to include those for the query to run correctly.

-Dell

[former_member548403]

Could you please elaborate a little further? I am now trying to include the parameters in the SQL. For example:

select  Staffing_Calendar_Da, payhours, Staffing_Start_Dt, unit_name_ch, t.Pos_Desc_Ch, Wstat_Abrv_Ch, Payhours_day, Payhours_night, total_payhours,p.category,
case when Payhours_day>0 then 'Day' end as 'Day',
Case	when Payhours_night>0 then 'Night' end as 'Night',
case	when total_payhours>0 then 'All' end as 'day_night'
 from vw_position_OTP t
inner join MCFRS_LKP_Position p on t.Pos_Desc_Ch=p.Pos_Desc_Ch
where t.Staffing_Calendar_Da between '1/28/2019' and '1/28/2019'
and
(
  ( 'Master' = {?Position}) or
  ('Lieutenant' =  {?Position})
) 
AND 
(
  ('{?Day_Night}' = 'All' and total_payhours > 0) OR
  ('{?Day_Night}' = 'Day' and Payhours_day > 0) OR
  ('{?Day_Night}' = 'Night' and Payhours_night > 0)
)<br>

Just to select the master or the lieutenant position and day/night selection, but I'm getting a syntax error.

[DellSC]

I would try changing

(
  ( 'Master' = {?Position}) or
  ('Lieutenant' =  {?Position})
)

to

(
  ( 'Master' = '{?Position}') or
  ('Lieutenant' =  '{?Position}')
)

-Dell