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]Heading on Across Report
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Heading on Across Report
 Login/Join
 
Silver Member
posted
I’m looking for help on how to get a column name on an across report. I’ve not done very many of these and haven’t been able to figure it out or find any other posts that would solve what I am trying to do. I have a lot of different types of data coming in based on dates. I want to print a row of data for each metric. That row will contain the data for that metric along with many columns per date. For example, this is what I want:

Metrics 01/15/2016 01/31/2016 02/01/2016 02/15/2016
Member Count 130 135 142 140
Total Assets 15000.00 15500.00 15800.00 15900.00
Average Age 45 48 44 50

I’m building out my report and can get everything I want except the heading of the word Metrics above my metric fields. My report is built like this:
TABLE FILE HOLD001
SUM
HOLD001.HOLD001.MBR_CNT AS 'Participant Count' OVER
HOLD001.HOLD001.TOT_AV_AMT AS 'Total Assets' OVER
HOLD001.HOLD001.AVG_MBR_AGE AS 'Average Age' OVER
ACROSS LOWEST HOLD001.HOLD001.DisplayDate AS ''
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/WFC/Repository/RIS/FSA_Participant_Overview/stylesheets/plain_report.sty,
$
ENDSTYLE
END

How can I get the word ‘Metrics’ to print on the left side of the across row?

This message has been edited. Last edited by: <Emily McAllister>,


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Platinum Member
posted Hide Post
SET ACROSSTITLE = SIDE
will do the trick.

The ACROSSTITLE parameter enables you to display both the ACROSS title and the ACROSS values on one line, you can issue the SET ACROSSTITLE = SIDE command. This command places ACROSS titles to the left of the ACROSS values. The titles are right justified in the space above the BY field titles. The heading line that is created by default to display the ACROSS title will not be generated.

As it will be right justified you need to add styling to make it left justified, something like:
TYPE=ACROSSTITLE, JUSTIFY=LEFT, $

success, Martin.


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 152 | Registered: March 29, 2013Reply With QuoteReport This Post
Silver Member
posted Hide Post
Martin, Thanks for the tip. I've never seen the ACROSSTITLE so it's a good thing to know.

I'm not able to get it to work though because I don't have a BY field. Instead of using the BY fields I'm printing out all of the different rows that I want and then using the OVER command.


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi April,
Try this:
  
-SET &ECHO=ALL;
-* File aprilc01.fex
TABLE FILE CAR
SUM 
SEATS AS Metrics OVER
SALES AS Sales OVER
RCOST AS Retail OVER
DCOST AS Dealer
ACROSS SEATS NOPRINT
END


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
Silver Member
posted Hide Post
Thanks Daniel, But with your example you aren't printing your across fields. I need to print my across row but put 'Metrics' to the left of the across data.


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
This will get you closer, but still not what you would like. OVER complicates report design. Since there is no BY statement, there's no place to put the ACROSS column title. In the example below, I create a dummy BY column so that the ACROSS column title is placed to the left of the ACROSS values, but the layout isn't pretty.

SET PAGE=NOLEAD
SET ACROSSTITLE=SIDE
DEFINE FILE GGSALES
DUMMY1/A1='';
END
TABLE FILE GGSALES
SUM
DOLLARS
OVER 
UNITS
OVER 
BUDDOLLARS
OVER 
BUDUNITS
ACROSS PRODUCT
BY DUMMY1 AS ''
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
April,
Did you run my example?
See that the ACROSS field (SEATS in my example) is NOPRINTed but also appears as the first field in the SUM.


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
Expert
posted Hide Post
Daniel's technique works beautifully. I've never coded something like this before. Bravo!

SET PAGE=NOLEAD
SET ACROSSTITLE=SIDE
TABLE FILE GGSALES
SUM
PRODUCT
OVER
DOLLARS
OVER 
UNITS
OVER 
BUDDOLLARS
OVER 
BUDUNITS
ACROSS PRODUCT NOPRINT
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Francis,
Coming from the expert, I really appreciate!
Thanks.


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
Silver Member
posted Hide Post
Thank you Daniel! Francis is right, it does work beautifully.

I had ran your example but didn't see that the across field and the first sum were the same pieces of data. I applied this same technique to my report and am getting exactly what I wanted. Thank you very much!


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
April,
Right on!
The lesson is WebFOCUS provides so many ways to tackle a problem, just be creative!


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
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Heading on Across Report

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