BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Coding & Data

SAS BI: Conditional Highlighting for Reports

Submitted by on 2011-12-14 – 7:00 AM

Conditional highlighting, also referred to as traffic lighting, helps report consumers quickly recognize when the data is showing the desired trends based on their targets.  Today I’ll talk about the color choices for the highlighting from MS Excel, SAS Web Report Studio, and SAS Stored Processes.

Conditional Highlighting Example

Web Report Studio allows you to easily add conditional highlighting based on cell values. So if the value is positive or meets the target then you would select the green Good; likewise if the value is going away from the target then you would select the red Bad. Here’s an example of some sales target.  All sales over $10k were considered positive; likewise all sales under $5k were under targets.  This approach allows the analyst to focus on those quarters were the results were not desired.  (Code for this STP is below.)

sas bi conditional highlight 1

Example Conditional Highlighting

 

Determine Colors with SAS EG

I’m a slightly anal that my reports are perfect – particularly in appearance. If you need to combine stored process output with a Web Report Studio report – you may need to ensure that the conditional highlighting matches. To do this, you need to match the color from the color highlighting in SAS Web Report Studio.

sas bi conditional highlight web report studio

Conditional Highlight colors in Web Report Studio

SAS uses Hex code to indicate the foreground and background colors for cells. [You can learn more about Hex colors here.]  However, to find the color use the Snag It Eyedropper  tool – it’s RGB value is displayed.  For instance, here are the green and red RGB values when I hovered over them with the eyedropper tool.  

Once you know the RGB code  – you can use SAS to convert it to Hex.  Here’s a macro that does just that.

/*Convert RGB to HEX                                              */
%macro RGBHex(color,rrr,ggg,bbb);%let RED=%sysfunc(compress(#%sysfunc(putn(&rrr,hex2.))));
%let GREEN=%sysfunc(putn(&ggg,hex2.));	
%let BLUE=%sysfunc(putn(&bbb,hex2.));
%put "============&color.: &RED.&GREEN.&BLUE.";
%mend RGBHex;

%RGBHex(Lt Green, 200, 253, 200);

 Here’s what the log would look like after selecting all the colors.  The hex color in available …. tadah

1202d_sas_bi_cond_highlight

Log Results

 

Now if you have a stored process and a Web Report Studio report appearing together, the user cannot tell the difference in the highlighting colors.  Here’s the code to produce the proc tabulate chart above.  You can see how I integrated the values in the proc tabulate code.

 

 /* ============= ====================== =====================*/
proc format;	
value Text	low-4599='#CC0000'  /*Dk Red*/ 
	           10000-15000='#008000'; /*DK Green*/	 
value back  low-4599='#FFCCCC'  /*Lt Red*/ 
	           10000-15000='#C8FDC8'; /*Lt Green*/ 
run;
/* ============= ====================== =====================*/ 
 title "2011 Sales Target by Quarter" 
 ;title2 h=2"Targets: Under $5k Poor| Over $10k Excellent "; 
 proc tabulate data=eghelp.totals; 
 class dept site quarter; 
 var sales; 
 table site=''*(dept='' ) 
 	, quarter='Quarter' 
 		*sales='' 
 		*sum='' 
 		*format=dollar12. 
 		*{style=[background=back. foreground=text.]} 
 	  all='Total' 
 	  	*sales='' 
 		*sum='' 
 		*format=dollar12. 
 		*{style=[background=lightsteelblue font_weight=bold]} 
 /format_precedence=row; 
 run;	 
 /* ============= ====================== =====================*/ 

 

Want to Learn More about SAS BI?

In the Building Business Intelligence with SAS book, you can learn more tips and tricks for using SAS Web Report Studio, SAS Stored Process, and building dashboards using the SAS BI Dashboard. 

Never miss a BI Notes post!

Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: , , , ,

No Comment »

1 Pingbacks »