Dashboard view rule question

[rirish (Ryan Irish)]

Hi all,

I’m trying to color code a dashboard based on lot expiration dates.

I created the calc fields that I’m using as my rule values in the BAQ, but the expiration date field is null for many parts, and the null fields are for some reason satisfying the criteria. I can create another calc field and assign a bool value to check for null, but I don’t see a way to use multiple conditions in the view rules.

Is there a way to create multiple conditions that I’m unaware of, or maybe a creative work around that someone can share?

[carlosqt (Carlos Quintanilla)]

Hi Ryan,
You can add a new calculated field to your BAQ with as many conditions as you need on one or multiple fields. Then you can return the column as an integer value instead of a boolean. That will give you the flexibility you are looking for. On the Dashboard RowRule, use the integer value to decide what to do (if calcfield = 0 green, if calcfield = 1 red, if = 2 yellow, etc.)

An example here:

Counting Row Rules Color In Dashboard E9.05

Hi Mikel, That is not possible using view rules, but one option you have to achieve that, is moving your formula to a calculated field in your dashboard’s BAQ and then using the Grid’s Group By functionality to let it Count items on each group. Example: Display Fields: … Duration Calculated_DurationColor with the logic of your current Dashboard rules (edit as required): if (Duration < 30) then ‘GREEN’ else if (Duration > 30 and < 50 then ‘YELLOW’) else if (Duration > 50) then ‘RED’ else ‘…

Regards,
PSE

[rirish (Ryan Irish)]

Awesome, thank you for that link, Carlos! Much appreciated.

[rirish (Ryan Irish)]

A few hours of cussing at my monitor and I’m still unable to come up with a calc field that works.

I finally came up with something that the editor doesn’t reject, but the query will no longer run with the calc field. When I test the query I get this: Table: , Level:, Type: , RowID: , Text: Business Query Execution error. Please contact your System Administrator.

I couldn’t get anything to work using isNull, so I went with the following:

if ((PartLot.ExpirationDate) < Today and (PartLot.ExpirationDate) <> ‘’) then ‘1’
else if ((PartLot.ExpirationDate) >= Today and (PartLot.ExpirationDate) <= (Today + 29) and (PartLot.ExpirationDate) <> ‘’) then ‘2’
else if ((PartLot.ExpirationDate) >= (today + 30) and (PartLot.ExpirationDate) <> ‘’) then ‘3’
else ‘4’

[carlosqt (Carlos Quintanilla)]

Hi Ryan,

I did it like this based on your formula and it seems to work, or at least it doesn’t fail when I Test the BAQ. I don’t have dates, so you need to confirm date conditions are met.

if ( PartLot.ExpirationDate = '' ) then 4
else if ( IsNull(PartLot.ExpirationDate) ) then 5
else if (PartLot.ExpirationDate < Today) then 1
else if ((PartLot.ExpirationDate >= Today) and (PartLot.ExpirationDate <= (Today + 29))) then 2
else if (PartLot.ExpirationDate >= (today + 30)) then 3
else 6

and the test gives:

kr

[rirish (Ryan Irish)]

Thank you for the continued help, Carlos.

Well I don’t know what could be going on here, but even with your example, my query will not run.

I started with a fresh query as well to test.

PartLot is my only table, PartNum, LotNum, ExpirationDate and the calc field are the only things I’m displaying, yet I’m still getting the same error.

If I remove the calc field, the query completes as expected.

[Ernie (Ernie Lowell)]

Since PartLot.ExpirationDate is a date field, it can’t be equal to a blank… try taking that first expression out and see if it at least runs.

[rirish (Ryan Irish)]

That was it, Ernie. Thank you!

Thank you again, Carlos. I have a feeling that since you aren’t using that field, and have no dates in that table, that troublesome expression didn’t rear it’s ugly head.