Skip to Content

Crystal Reports: Parameter setup

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!

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Jan 28 at 07:44 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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 "*") ...
  • Jan 28 at 04:58 PM

    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

    Add comment
    10|10000 characters needed characters exceeded