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>,

Try something like ...

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

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!!

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.

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!