The Highlight of my day...

[robinsonky]

Highlights.

So now I turn over the first dashboard to the Financial Controller, and he says "Wow. Now if you can just highlight all the values over 5%"....

I've seen rumours of using css, but is there an easier way?

[Bob Cergol]

Here's one way you could do this.
You could then control the actual colors in the style sheet. it''s pretty easy to do.

DAI_Color_Formatting.png (139.15 KiB) Viewed 12847 times

I also embed html tags in plus columns to introduce fixed or conditional formatting of specific data.
For example:
Plus column: Job Tile
'<b>' + [Title] + '</b>' + (Case When isNull(Specs_7,'') <> '' then ('<BR><I>' + Specs_7 + '</I>') else '' End) +(Case When isNull(Specs_8,'') <> '' then ('<BR><I>' + Specs_8 + '</I>') else '' End)
gives this result:

DAI_Embedded_Formatting_Example.png (9.79 KiB) Viewed 12847 times

However the disadvantage is that exporting the data results in the tags getting exported as data. Also on iPads due to international character set being used the tags display instead of being rendered.

[robinsonky]

Wow, high-speed response!
The top example is working with the Grouped Data columns, whereas I want to make my distinction based on the values in a calculated column.
I was going the CASE statement route but if I can use the normal dialogs so much the better

By the way just where are the 'full' manuals? I don't see any on the server where we installed,and I know I'm asking questions that should have obvious answers.

[Bob Cergol]

Well my repsonse might not be so fast after I get some turkey (triptocane) in my system starting tomorrow!

The "help" link on the bottom menu bar: reports / databases / administration / help -- will take you to the full-blown, on-line manual. The documentation is pretty darn good -- but admittedly it lags behind the latest version of the product. I have some ancillary stuff I can send you if you email me directly.

If your calculated column is a total column the range coloring can be applied to it -- though you may not like having to position it where the color ranges would apply to it.

I think finer control over conditional formatting in the design interface is definitely an area for enhancement in the product.

Some of the folks at Cintac know a lot more about such tricks than I and may have some ideas for you.

Bob

[robinsonky]

The Case statement I'm trying (just bolding instead of applying colour so far) is ...

CASE When [OOSPercent]>4.99 THEN ("<b>" + [OOSPercent] + "</b>") else [OOSPercent] END

fairly crude, and AI tells me to check the log files. The most relevant time-wise seems to be

DAI ERROR ALERT DF__S Used SQL commands are not allowed here - DAI 0005640 - - K_=ENVELOPE02 - L_=0 - SQL -
Illegal SQL 1130_268 DATAWH: dbo.OOSScans 55277longstringofnumbers

Same effect when I try and use
IF [OOSPercent]>4.99 THEN ("<b>" + [OOSPercent] + "</b>") else ([OOSPercent]) ENDIF

[Bob Cergol]

The syntax is pretty unforgiving and a bit odd when it comes to referencing other plus columns.

But first off -- avoid double quotes like the plague. Use single quotes. SQL doesn't like them.

If you're referencing other existing calculated columns then you'll want to use d_column-name_d.

I find the on-screen formula editor useful in teaching the correct syntax. That's the small rectangle just right of the last column of your report display. It always knows whether to use [[ name ]] or d_name_d, etc.

Finally -- if the report is built on a dyn-join you may want to use the alias name,i.e. dai_.column or j2.clolumn -- and under some circumstances if you reference a column in the fomula that you don't use in the report --in details or totals -- you'll get an error -- because only the columns referenced in the report are in the select statement to the server and the formula is applied to the result set.

Bob

[robinsonky]

You up stuffing your Goosturduckhen?

Duh! I've been doing SQL long enough to have know about the double quotes....

'Unfortunately' I switched off logmein on my desktop so I can't remote in to try this right now, but I'll give it a whizz in the morning....

Thanks

[robinsonky]

Hope the poultry population was successfully culled.

For speed, in this 'proof-of-concept', I calculate the outofstock% in the backend so no I'm not doing a dynamic join.

I tried the code again with single quotes but still get the same logged error
IF ([OOSPercent]>4.99) THEN ('<b>' + [OOSPercent] + '</b>') else ([OOSPercent]) ENDIF

That seems as though it should work.....

If I turn all other columns into strings will the conditional formatting then work? Or is it going to cast '99' back into 99?

[Bob Cergol]

...whew...that was one big bird!

Gosh, I forgot to mention -- you do have to cast numeric columns as strings when using in an expression where concatenation is being done.

The example below shows numbers (Rep_ID) and dates being re-typed as strings.
Plus column: Hdr_Title
[Division]+' | '+(cast([Rep_Id] as varchar))+': '+[rep_LastName]+' | PO# '+[PO_Number]+'<br><b>'+[job_title]+'</b><br><i>'+[Specs_1]+'<br>'+[Specs_2]+'</i><br><b>'+[cust_idno]+': '+[cust_name]+'</b><br>Entered: '+(convert(char,[job_Entered],101))+' | Promised: '+(convert(char,[Promised],101))+'<br>Shipped: '+(convert(char,[Shipped],101))+' | Qty: '+(convert(char,[Ship_Qty]))
gives this result:

DAI_Complex_Rpt_Header.png (8.89 KiB) Viewed 12833 times

Note: in the report, the first grouping is on Job Number, and Hdr_Title is a row-caption to Job Number in the groupings.

Regards,
Bob