Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Writing 1 report row for multiple input rows

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Writing 1 report row for multiple input rows
 Login/Join
 
Silver Member
posted
Hello,
I am trying to create a report and am having trouble figuring out how to create what I want. I've worked in WebFocus for a little while but haven't done a lot of different types of reports.

I am doing direct sql and getting data from our db2 tables. I’m gathering info for name, date, and a percent. My query gathers the data and it's put into a hold file like this:

02/01/2016 Joe 93.11
02/08/2016 Joe 89.36
02/15/2016 Joe 91.78
02/01/2016 Sam 74.56
02/08/2016 Sam 83.91
02/15/2016 Sam 87.14

So always:
Date Name Percent

We will have many names and many dates. I want the output to print like this:

02/01/2016 02/08/2016 02/15/2016
Joe 93.11 89.36 91.78
Sam 74.56 83.91 87.14


What I'm getting is more like this:

02/01/2016 02/08/2016 02/15/2016
Joe 93.11 . .
. 89.36 .
. . 81.78
Sam 74.56 . .
. 83.91 .
. . 87.14

Here is how I'm building my report:
TABLE FILE HOLD001
PRINT
HOLD001.HOLD001.PERCENTAGE AS ''
BY LOWEST HOLD001.HOLD001.EmployeeName AS ''
ACROSS LOWEST HOLD001.HOLD001.K_BUSN_WK_END_DT 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:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

It's creating a row in the table for every row in the hold file. But I want it to write 1 row per person and put in the percentages with each of the corresponding dates. The amount of dates coming will be variable for every run.

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


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Platinum Member
posted Hide Post
Try something like ...

-*
TABLE FILE ??????
SUM PERCENT
ACROSS DATE
BY NAME
END


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Silver Member
posted Hide Post
Thanks jfr99. I swear I tried that and got funky results. But I did as you suggested and it gives me just what I want. Thank you!!


WebFOCUS 8.1.04
Windows, All Outputs
 
Posts: 34 | Registered: July 02, 2014Report This Post
Expert
posted Hide Post
The funky results were probably because you used PRINT instead of SUM.

There's a SET command that's used with PRINT and ACROSS called ACROSSPRT:

quote:

The ACROSSPRT parameter reduces the number of report lines within each request that uses the PRINT command and an ACROSS phrase.

The PRINT command generates a report that has a single line for each record retrieved from the data source after screening out those that fail IF or WHERE tests. When PRINT is used in conjunction with an ACROSS phrase, many of the generated columns may be empty. Those columns display the missing data symbol.

To avoid printing such a sparse report, you can use the SET ACROSSPRT command to compress the lines in the report. The number of lines is reduced within each sort group by swapping non-missing values from lower lines with missing values from higher lines, and then eliminating any lines whose columns all have missing values.

Because data may be moved to different report lines, row-based calculations, such as ROW-TOTAL and ACROSS-TOTAL in a compressed report are different from those in a non-compressed report. Column calculations are not affected by compressing the report lines.


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, 2005Report This Post
Platinum Member
posted Hide Post
Hi Francis

I'd never seen that before ... here's the same example using PRINT ...

TABLE FILE ??????
PRINT PERCENT
ACROSS DATE
BY NAME
ON TABLE SET ACROSSPRT COMPRESS
END

In my working example ... this gave me the same results as using SUM

FYI ... You could also use this SET command to effect the entire fex ...

SET ACROSSPRT = COMPRESS

Cheers!


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Writing 1 report row for multiple input rows

Copyright © 1996-2020 Information Builders