Pre-format text region

Using Excel,SQL,Oracle, and other ODBC Compliant Databases
Post Reply
sirluke34
Regular Contributor
Regular Contributor
Posts: 9
Joined: Thu Feb 16, 2012 5:58 pm

Pre-format text region

Post by sirluke34 »

Hello,
I've seen a couple of references to conditionally pre-formatting data acquired from external data, but can't find anything about this topic in the manual. I'd like to do a simple comparison of an "actual production" number vs. the goal and color it red/green depending if it's below or above the goal. Can someone point me in the right direction?

I'm getting my data from Access and can include the "goal" in the query or hard-code it into Noventri Suite.

Any help appreciated.
User avatar
Trent S
Noventri Digital Signage Expert
Noventri Digital Signage Expert
Posts: 71
Joined: Thu Dec 10, 2009 1:51 pm
Location: Maryland, USA
Contact:

Re: Pre-format text region

Post by Trent S »

Hello sirluke34,

Below is an example select statement that does what you are looking to achieve. While this example pulls data from a CSV, it should work the same looking at an Access database.

SELECT IIf(Actual<Goal,Actual,' ') AS below, IIf(Actual>Goal,Actual,(IIf(Actual=Goal,Actual,' '))) AS above, * FROM databasename.csv

You can substitute "Goal" and "Actual" used above with the names of your actual columns (or column numbers) from Access.

We are essentially creating new columns on the fly for "below" and "above" that either get populated with your "Actual" value or a blank value. This is based on the data comparison between "Goal" and "Actual."

In Suite, you would need two regions stacked on top of one another - one formatted with red text, and one formatted with green text. They both use the select statement above, but the green formatted region looks at the "above" column, and the red formatted region looks at the "below" column.

Example formatters:
Red region: [1:below]
Green region: [1:above]

(The "1" in the above formatters would be replaced by your row number)

You won't need to worry about both regions showing up, because a blank is added to the column if the IF statement requirements are not satisfied. Also of note - in the event that the Actual and the Goal match one another, it is being considered the same as the Actual being higher than the Goal (and will hence be displayed in Green).

Please let me know if you have any further questions - or need any clarification.

I hope this was of some help to you!

Kind Regards,
Trent Semler
Art Director
Specialized Communications Corp. / Noventri
20940 Twin Springs Dr.
Smithsburg, MD 21783-1510 USA
301-790-0103
Digital Information Display
Menu Display
Flight Board
sirluke34
Regular Contributor
Regular Contributor
Posts: 9
Joined: Thu Feb 16, 2012 5:58 pm

Re: Pre-format text region

Post by sirluke34 »

Thanks for the info - makes sense. I just need to get in the mindset of making layered regions for conditional circumstances. The programmer in me doesn't think that way. :shock:
Post Reply