Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Add Filter to Excel Column Header
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Add Filter to Excel Column Header
 Login/Join
 
Platinum Member
posted
Is there a stylesheet option/code for adding the filters to a report output to EXL2K format?

This message has been edited. Last edited by: Kerry,


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
Is there a stylesheet option/code for adding the filters to a report output to EXL2K format

NO!


So here is a dodgy way to do it. Big Grin

DEFINE FILE CAR
 AUTOFILTER/A162 = '</td></tr><tr><td x:autofilter="all" x:autofilterrange="$A$2:$C$2">COUNTRY</td>'
                 | '<td class=xl100 x:autofilter="all">CAR</td>'
                 | '<td class=xl100 x:autofilter="all">MODEL' ;
END
TABLE FILE CAR
HEADING
"<AUTOFILTER "
PRINT COUNTRY AS ''
      CAR     AS ''
      MODEL   AS ''
ON TABLE PCHOLD FORMAT EXL2K
END

This message has been edited. Last edited by: Waz,


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6072 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Waz,

Right on! Good One


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You rock!

This is really a very helpful forum!


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
In case anyone is interested in styling the column titles produced by Waz's great post, the standard StyleSheet options don't work. There may be a better way to do this (using a class?), but I found that brute force in-line CSS does the job:

DEFINE FILE CAR
 AUTOFILTER/A700 = '<tr>'
                 | '<td style="white-space:normal;mso-pattern:auto none;font-family:arial;color:white;font-size:12px;font-weight:700;text-align:center;background-color:rgb(79,129,189);" x:autofilter="all" x:autofilterrange="$A$2:$C$2">Country<br></td>'
                 | '<td style="white-space:normal;mso-pattern:auto none;font-family:arial;color:white;font-size:12px;font-weight:700;text-align:center;background-color:rgb(79,129,189);" x:autofilter="all">Car<br></td>'
                 | '<td style="white-space:normal;mso-pattern:auto none;font-family:arial;color:white;font-size:12px;font-weight:700;text-align:center;background-color:rgb(79,129,189);" x:autofilter="all">Model<br></td>'
                 | '</tr>' ;
END
-*
TABLE FILE CAR
HEADING
 "Report Heading<AUTOFILTER"
 PRINT
  COUNTRY AS ''
  CAR     AS ''
  MODEL   AS ''
 ON TABLE SET STYLE *
  TYPE=REPORT, GRID=ON, FONT='ARIAL NARROW', SIZE=10, COLOR='BLACK', STYLE=NORMAL,
       WRAP=ON, SQUEEZE=ON, TITLETEXT='Car Info', $
  TYPE=HEADING, SIZE=13, STYLE=BOLD, COLOR=WHITE, BACKCOLOR=RGB(165 165 165), JUSTIFY=CENTER, $
 ENDSTYLE
 ON TABLE PCHOLD FORMAT EXL2K
END

Edit: Had to remove ENDEFLT.STY because it was somehow corrupting the auto-filter values.

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Master
posted Hide Post
I never realized you could do that. Is there somewhere I could look up more of these classes / tricks to do other things in excel?


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Waz & Dan,

I will be having to use your solution to create an XL sheet with filters.
I tried to minimize Dan's example by eliminating some of the attributes. However I find that I must have
  
white-space:normal;mso-pattern:auto none;

Can you tell me why. Or better, how would I go about to provide only the color and size attributes?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Danny,

Sorry, but I am by no means a CSS expert. I found the CSS code on-line somewhere and played with it until it worked. I also don't know why some of it is necessary, I just know it stopped working if I removed certain pieces of the code. You should be able to remove the background color, text alignment, font weight, etc. if not needed. But the "white-space" and "mso-pattern" attributes must be necesssary because otherwise I would have removed them myself.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I have been looking a way to style the background color for the cloumn in the autofilter.

Thanks Dan for posting on how to style the columns


WebFOCUS
7703/7705/8105m/8201m/8202m

 
Posts: 99 | Registered: September 10, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
This is cool!

Marked for future use Nice Thread


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Reply With QuoteReport This Post
Master
posted Hide Post
If you have a copy of Excel 2003 laying around, you can do just about anything that WebFOCUS doesn't provide by creating a template (.mht file) with an autorun macro. You can turn on filters, freeze panes, rotate column titles, add filtered subtotals, use excel graphs, vertically center column titles, embed excel comments in cells, more border options, etc.

There are lots of posts on Focal Point about using Excel 2003 template files.

We are migrating to WF8 and all of this works in WF8 but you must build the .mht file in Excel 2003. MS stopped support for .mht files beginning with Excel 2007. I have not yet played with the new template formats in Excel 2010. But I am hoping they will work as well, if not, I will wait for IBI to support that and hold on to my copy of Excel 2003 until they do.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 908 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
WOW!!!!You guys are amazing.... Nice Thread


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
 
Posts: 69 | Location: India | Registered: November 03, 2015Reply With QuoteReport This Post


Can we help me please Confused


WebFocus V8.2.03M
Member
posted Hide Post
I don't understand because in layout , I have neither autofilter nor that the column name.
Here my code :
DEFINE FILE G210_FIAS_MVT_FINAL
AUTOFILTER2/A500 = '</td></tr><tr><td style="text-align:center;background-color:rgb(79,129,189);white-space:normal;mso-pattern:auto none;" x:autofilter="all" x:autofilterrange="$A$2:$B$">MATNR</td>'
                 | '<td style="text-align:center;background-color:rgb(79,129,189);white-space:normal;mso-pattern:auto none;" x:autofilter="all">MAKTX</td></tr>' ;
END
-RUN

TABLE FILE G210_FIAS_MVT_FINAL
HEADING
"report 2</1<AUTOFILTER2"
PRINT 	MATNR AS ''
		MAKTX AS ''
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT  


I have in line 1 : report 2
in line 2 :
MATNRMAKTX
 
Posts: 12 | Registered: April 09, 2019Reply With QuoteReport This Post
....MAKTX' ;

I didn't have the autofilter, and not the title column


WebFocus V8.2.03M
Member
posted Hide Post
Following in line 2 : I have the code copy AUTOFILTER2: "
 
Posts: 12 | Registered: April 09, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Add Filter to Excel Column Header

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.