OTBI condition not working

User_RD03L

Hello,

We're trying to create a custom OTBI report with the below requirement.
- If employees reported hours is null/blanked, it should return 'WO' value

Please see the formula that we've used:
WHEN "Reported Time Cards"."Reported Hours" = '' THEN 'WO' ELSE CAST("Reported Time Cards"."Reported Hours" AS CHAR) END

Output:

Upon using this formula, it doesn't return the 'WO' value even though the reported hours for that specific employee is blanked.

Thank you in advance for your help.

Kind Regards

Nathan CCC

Hi On the advanced tab is the logical SQL from above with the left outer join with the cols t and m from the select bit of that SQL on the criteria tab with the formula on col m on the fact measure string.

Nathan CCC

Hi

"Reported Time Cards"."Reported Hours" = ''

is not the same as

"Reported Time Cards"."Reported Hours" is null

Maybe try testing for null instead

select all 0 s_0

, "Reported Time Cards"."Reported Hours" as n

, (case when ("Reported Time Cards"."Reported Hours" is null) then upper('wo') else cast("Reported Time Cards"."Reported Hours" as character) end) as c

from "Workforce Management - Reported Time Cards Real Time"

order by 1 desc nulls last, 2 asc nulls last

fetch first 7 rows only

User_RD03L

Hello Nathan,

I see but I've also tried to use
"Reported Time Cards"."Reported Hours" is null and it also didn't work

Nathan CCC

But looking at your screen shot it is perhaps a "pivot table" in which case the "blank" as you call it is "no rows at all" for that day for that row in the pivot table so no matter what you do in your case statement it will not work because you have no row returned in the result set from your query. So if you did a left outer join from the calendar dimension so you will always get a row for each day whether a timecard entry exists or not then only will the case statement approach work.

At the moment in yout table view you have 2 rows?

dimension X tue 9.476 hurs

dimension X thu 8.969 hours

(no row in the table for wed for dimension X)

but lower down in the table another row

dimension Y wed 9.99 hours

so in the pivot table for the row for dimension X there is no value in Wed?

User_RD03L

Hello Nathan,

Thank your for the detailed explanation. Indeed, I have 2 rows on my table view

Yes, I confirm that pivot table in dimension X has no value on Wed.

With regards to using left join in order to use the case statement approach, can you please elaborate it more? Also, if you have other approach you might have?

Thanks!

Nathan CCC

Hi,

Here is a pivot showing a month for which we have no timecards as "WO" and another that does have timecards.

SQL with left outer join

select t1.t

, ifnull(t2.c,upper('wo')) as m

from

(

select all "Time"."Calendar Month" as t

from "Workforce Management - Reported Time Cards Real Time"

) t1

left outer join

(

select all "Time"."Calendar Month" as t

, "Reported Time Cards"."Reported Hours" as n

, cast("Reported Time Cards"."Reported Hours" as character) as c

from "Workforce Management - Reported Time Cards Real Time"

) t2

on (

t1.t = t2.t

)

where 1 = 1

and (t1.t in ('2022411','2022410'))

User_RD03L

Hi Nathan,

Based on the sql provided above, may I know in which column did you add it on?

Nathan CCC

"Reported Time Cards"."Reported Hours"

User_RD03L

Hello,

After applying the sql in the reported hours column, I'm encountering an error.

select t1.t

,ifnull(t2.c,upper('wo')) as m

from

(

select all "Time"."Calendar Date" as t

from "Workforce Management - Reported Time Cards Real Time"

)t1

left outer join

(

select all "Time"."Calendar Date" as t

,"Reported Time Cards"."Reported Hours" as n

,cast("Reported Time Cards"."Reported Hours" as character) as c

from "Workforce Management - Reported Time Cards Real Time"

)t2

on(

t1.t = t2.t

)

where 1 = 1

Nathan CCC

Hi,

Sorry I did not understand what you meant by "may I know in which column did you add it on?"

You cannot cut paste SQL into the "Edit Formula" of a column in an analysis. I did not edit any column.

This is the logical SQL that you generated on your advanced tab. So you can edit it there on your advanced tab.

Or create a new query using Create, Analysis, "Create analysis from simple logical SQL"

User_RD03L

Hello Nathan,

I see, so technically we can't use the given formula in the reported hours column correct?

Given that we're creating this analysis with this requirement and some other requirements by using pivot table, case when statement. Would you know if it's possible to satisfy the criteria using the case when statement or any other expressions in the "Edit Formula" for the Reported Hours column instead of using the logical sql?

Thanks!