on 01-25-2019 8:07 PM
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!
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 "*") ...
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
I would try changing
(
( 'Master' = {?Position}) or
('Lieutenant' = {?Position})
)
to
(
( 'Master' = '{?Position}') or
('Lieutenant' = '{?Position}')
)
-Dell
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.