Method Community

I'm trying to create a new dispatcher's dashboard and wanted to color code the Work Order grid based on the priority of the activity.  I figured I could do something along the lines of:

CASE WHEN [viewActivity.ActivityPriority]='1-Emergency' then '<font color="red">' WHEN [viewActivity.ActivityPriority]='2-24 Hour Response' then '<font color="blue">' ELSE END + '<b>' + viewActivity.Entity + '</b></font><br />' + '<i>' + ISNULL(viewActivity.ActivityPriority,'') + '</i><br />' + ISNULL(viewActivity.WorkOrderInstructions,'')

When I tried this I got "Error Generating Grid"

Do I have an issue with my CASE statement wrong or is this beyond the SQL Override?

Thanks,

Shawn

In case anybody is interested,  I figured out my own solution:

CASE WHEN [ActivityPriority] = '1-Emergency' THEN '<font color="red"><b>' WHEN [ActivityPriority] = '2-24 Hour Response' THEN '<font color="blue"><b>' ELSE '<font color="black"><b>' END + viewActivity.Entity + '</b></font><br />' + '<i>' + ISNULL(viewActivity.ActivityPriority,'') + '</i><br />' + ISNULL(viewActivity.WorkOrderInstructions,'')

Thanks,

Shawn

So I have another one that I could use some help on.  I want to be able to compare the current time to the requested start date and then color it red if its late.  Here was my idea, but it returns "error generating grid"

CASE WHEN [DueDateStart] < GETDATE() THEN '<font color="red"><b>' ELSE '<font color="black"><b>'  END + viewActivity.DueDateStart + '</b></font>'

Any thoughts?

Thanks,

Shawn

Try:

(CASE WHEN [DueDateStart] < GETUTCDATE() THEN '<font color="red"><b>' ELSE '<font color="black"><b>'  + viewActivity.DueDateStart + '</b></font>' END) 

- I haven't tried this, just a first glance attempt.

- better to use GETUTCDATE() instead, since all times are actually stored on the server without timezone information, so it can be translated and viewed properly anywhere in the world based on where you are.

- Your END was in the wrong spot, and you weren't wrapping in brackets.

BUT - note, using a date in an SQL override is undesirable. The date will come in UTC rather than converted to your local time zone, and it will come unformatted. No easy way around this.

Paul,

If I move the END to the end of the statement, the case will not display the requested field unless it results in the ELSE portion of the CASE

I did try your suggestion but ended up with the same error.

I'll keep playing

In that case you want:

(CASE WHEN [DueDateStart] < GETUTCDATE() THEN '<font color="red"><b>' ELSE '<font color="black"><b>'  END) + viewActivity.DueDateStart + '</b></font>' 

if you want to get fancy, and dance around the date conversion a little.....

(CASE WHEN [DueDateStart] < GETUTCDATE() THEN '<font color="red"><b>' ELSE '<font color="black"><b>'  END)  + CONVERT(varchar(10), DateAdd(HOUR,-5,viewActivity.DueDateStart), 20) + '</b></font>'

Your second "fancy" solution did the trick.  I think without converting to character was causing the issue.  Once it was converted, worked like a charm.

Thanks!!!

Shawn