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.
Pre-format text region
- Trent S
- Noventri Digital Signage Expert
- Posts: 71
- Joined: Thu Dec 10, 2009 1:51 pm
- Location: Maryland, USA
- Contact:
Re: Pre-format text region
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,
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
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
Re: Pre-format text region
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. ![Shocked :shock:](./images/smilies/icon_eek.gif)
![Shocked :shock:](./images/smilies/icon_eek.gif)