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     [CLOSED] How to Eliminate unneeded columns in an across burst report
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] How to Eliminate unneeded columns in an across burst report
 Login/Join
 
Member
posted
I have a report that gets created with multiple customers - it contains an across on charge service type - when ran for previous week (sorted by customer id) it results in 50+ charge service types. I then want to burst on company id so customers only see their data but if a charge service type is blank for that customer I do not want it to show up on the burst report - any suggestions?

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


7611 - Win 7
all output
 
Posts: 14 | Location: Indianapolis, IN | Registered: December 07, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Can you just add a WHERE clause?

WHERE CHARGE_SERVICE_TYPE NE '' (or NOT MISSING);


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Member
posted Hide Post
No because there are no columns that blank throughout the whole master report. But when I go to burst on an ID in report Caster not all ID's have charges under each Columns.

Example:

ID 1 - Charge = $50 - Special Charge =NULL
ID 2 - Charge = NULL - Special Charge = $50

When I create the report it would have 2 rows and 2 columns.

But When I burst the report so the ID 1 gets only its charges (Charge) it would burst Charge and Special Charge Column even though Special charge is NULL for ID 1.


7611 - Win 7
all output
 
Posts: 14 | Location: Indianapolis, IN | Registered: December 07, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This is not easily done.
What I did in a similar situation (years and years ago) was to add both charges into one field and print that. Under the covers I counted and totalled the two types of charges and printed a summary of that at the end of the report.
Something like:
Who     Date     Charges
ID-1    01012009      75
ID-1    01022009     175

Total ID-1: 250, Normal charges: 2, 100$, Special charges:1, 150$
That way you do not print extraneous columns, and just 1 total line containing almost all information needed.

Hope this helps ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Master
posted Hide Post
People,


7.7.0.2 Release notes page 99:

SET HIDENULLACRS = { ON | OFF }

ON
Hides columns with missing data in ACROSS groups within a BY generated page break.

OFF
Does not hide columns. OFF is the default value.



I know this doesn't help you now, but perhaps you have an update planned?

Greets,Dave


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
This is glorious news!

I had come up with this code that works (though I don't know if the syntax is valid), but it does NOT work in ReportCaster.

-* File across2.fex

-SET &ECHO=ON;

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
SET PAGE=NOLEAD
SET HTMLCSS=ON
-RUN

-*-- Create 'driver' file
TABLE FILE CAR
SUM COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS HCOUNTRY
END
-RUN

-*-- Create report, using 'driver' file for looping
-READ HCOUNTRY NOCLOSE &COUNTRY.A10.

TABLE FILE CAR

-REPEAT END_REP1 WHILE &IORETURN EQ 0;
SUM
SALES/D6

ACROSS SEATS
BY COUNTRY NOPRINT PAGE-BREAK
BY MODEL

WHERE COUNTRY EQ '&COUNTRY'

HEADING
"SALES REPORT FOR <COUNTRY"

ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, FONT='Arial', SIZE=8, BORDER=1, BORDER-COLOR=SILVER, $
ENDSTYLE

-READ HCOUNTRY NOCLOSE &COUNTRY.A10.

-*-- Avoid last RUN
-IF &IORETURN NE 0 GOTO END_REP1

RUN

-END_REP1

END
-RUN


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
Expert
posted Hide Post
Hi Francis, a missing semi-colon, and, possibly FOCCACHE. I don't use IORETURN, I use &LINES. Even though your SET HOLDFORMAT=ALPHA, I would try it on the line, also. Just an idea...

  
-* File across2.fex

-SET &ECHO=ON;

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
SET HOLDFORMAT=ALPHA
SET PAGE=NOLEAD
SET HTMLCSS=ON
-RUN

-*-- Create 'driver' file
TABLE FILE CAR
SUM COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS FOCCACHE/HCOUNTRY FORMAT ALPHA
END
-RUN

-SET &XLINES = &LINES;
-*-- Create report, using 'driver' file for looping
-READ FOCCACHE/HCOUNTRY NOCLOSE &COUNTRY.A10.

TABLE FILE CAR

-REPEAT END_REP1 &XLINES TIMES
SUM
SALES/D6

ACROSS SEATS
BY COUNTRY NOPRINT PAGE-BREAK
BY MODEL

WHERE COUNTRY EQ '&COUNTRY'

HEADING
"SALES REPORT FOR <COUNTRY"

ON TABLE SET STYLE *
TYPE=REPORT, SQUEEZE=ON, FONT='Arial', SIZE=8, BORDER=1, BORDER-COLOR=SILVER, $
ENDSTYLE

-READ FOCCACHE/HCOUNTRY NOCLOSE &COUNTRY.A10.

-*-- Avoid last RUN
-* Tom Flynn - don't need this with &LINES dictating the -REPEAT, but, does need a semi-colon
-* -IF &IORETURN NE 0 GOTO END_REP1 ;

RUN

-END_REP1

END
-RUN


hth

Happy Holidays to you, Francis. Thanks for all the help this year.

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1973 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Two READ lines is a normal programming technique - 1 read to start, then a read in a loop until end-of-file, learnt during my COBOL days oh so long ago.

The problem with my fex is that ReportCaster does not like the RUN statement, WebFOCUS accepts it and generates 5 separate reports, but ReportCaster issues an error.

Happy Holidays Tom.

Cheers,


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
Expert
posted Hide Post
Yes, I took out my statement regarding the 2 -READs, I understood them after I re-read the code. I always place them within the -REPEAT, just 2 different ways to accomplish the task!

COBOL - geez! I couldn't tell you when in the last century I used it!

Thanks!

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1973 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] How to Eliminate unneeded columns in an across burst report

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