Thanks everyone for your help! I tried to use the "mailing label" approach but when I started to add my header/footer that fell apart. So what I decided to do was create a hold file for the row/column data, a row for the header data and a row for the footer data. Then I used MATCH logic to put them all together in one big HOLD file. Since this report is only going after one student at a time, I don't have to worry about response time.This message has been edited. Last edited by: gmb,
I find it a bit hard to understand what you're trying.
But if I get it correctly you might be better of using a DEFINE instead of ACROSS.
Like DEFINE holdfile1 CNTR1/D4 = IF CNTR EQ 1 THEN the_data ELSE 0; CNTR2/D4 = IF CNTR EQ 2 THEN the_data ELSE 0; etc... END
or a MATCH... ( we use this a lot )
MATCH FILE holdfile1 PRINT the_data AS 'the_data1' the_other AS 'the_other1' the_next AS 'the_next1' BY WHATEVER1 BY WHATEVER2 WHERE CNTR EQ 1; RUN FILE holdfile1 PRINT the_data AS 'the_data2' the_other AS 'the_other2' the_next AS 'the_next2' BY WHATEVER1 BY WHATEVER2 WHERE CNTR EQ 2; AFTER MATCH HOLD OLD-OR-NEW etc.
Anyway. Really create seperate columns instead of using ACROSS.
Or perhaps use ACROSS COLUMNS 1 AND 2 AND 3 AND 4 .....
( check DevStudio's Help, search for "ACROSS COLUMNS AND" )
If the number of across-values is limited... G'luck !
_____________________ WF: 8.0.0.9 > going 8.2.0.5
April 21, 2010, 09:41 AM
gmb
Thanks Dave, I'm trying to create two columns of data. Think about mainframe reports, how the data is parsed into two columns. My example didn't print as I thought it would so I will try again.
This is column 1 This is column 2 school_a term Q1 high school 12 course1 school_a term Q2 high school 12 course1 course2 course53 school_a term Q3 high school 12 course2 school_b term 5 summer school 8 course4 course8 course3 school_c term 5 summer school course22 course44 I won't know how many ROWS of data each column will have. Basically this is a School Transcript Report. So you could have grades K-12 or you may just have Grades 9-12 on your transcript. I have tried the ACROSS command and like I mentioned above it's doing the split with the first column on the first set of rows and then the second column on the next set of rows.
I can't seem to get my spacing to hold when I type my request in. But the second column starts with school_a_term. So let me see if I can describe it this way.
This is column 1 data school_a term Q1 high school 12 course1 Course4 This is column 2 data school_a term Q2 high school 12 course1 course53 This is column 1 data school_a term Q3 high school 12 course2 course8 This is column 2 data school_b term 5 summer school 8 course4 course3
To ensure multiple blanks are not squeezed down to one, use the
[code]
[/code]
tags around the report output (achieved by clicking on </>)
I can't figure out what you'd like your output to look like.
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
April 21, 2010, 10:21 AM
Ram Prasad E
<html>
<body>
<table border=1>
<tr align=center>
<td>[b]Column - 1 Title[/b]</td>
<td>[b]Column - 2 Title[/b]</td>
</tr>
<tr>
<td>school_a term Q1 high school 12 course1 Course4</td>
<td>school_a term Q2 high school 12 course1 course53</td>
</tr>
<tr>
<td>school_a term Q3 high school 12 course2 course8</td>
<td>school_b term 5 summer school 8 course4 course3</td>
</tr>
</table>
</body>
</html>
SET ACROSSPRT=COMPRESS
TABLE FILE CAR
SUM
SALES
RETAIL_COST
DEALER_COST
BY CAR
-*In your case sort by Quarter
ON TABLE HOLD
END
DEFINE FILE HOLD
CNTR/I1=IF CAR EQ LAST CAR THEN CNTR ELSE CNTR+1;
-*Instead of CAR test the above expression with Quarter
CNTR_1/I3 = IMOD(CNTR, 2, CNTR_1);
END
TABLE FILE HOLD
PRINT
CAR AS 'Car'
SALES AS 'Sales'
RETAIL_COST AS 'Retail Cost'
DEALER_COST AS 'Dealer Cost'
COMPUTE AA/A2=''; AS ''
ACROSS CNTR_1 NOPRINT
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=REPORT, ORIENTATION=LANDSCAPE,PAGESIZE=LEGAL, SQUEEZE=ON, $
TYPE=DATA, ACROSSCOLUMN=CAR, STYLE=BOLD, $
TYPE=TITLE, STYLE=BOLD, $
ENDSTYLE
END
Well I can't get this to display correctly. But in the last post you can see that I have highschool term Q1 (that is column 1) highschool term Q2 (column 2) then on the next row, highschool term Q3 (column 1) middle school term 5 (column 2) next row, summer school 5 (column 1). Sorry I can't get this silly thing to display correctly. Hopefully someone can see what I'm trying to build. I'm trying to use the technique that creates mailing labels. But if anyone else has another idea I would love to hear it!
Ram, You are close, but unfortunately the sort fields are Schoolyear, School, Term, Grade. When I go across CNTR then I get the rows staggered by the above mentioned sort fields.
DEFINE FILE HOLD
CNTR/I1=IF ( SCHOOLYEAR EQ LAST SCHOOLYEAR ) AND ( SCHOOL EQ LAST SCHOOL ) AND ( TERM EQ LAST TERM ) AND ( GRADE EQ LAST GRADE ) THEN CNTR ELSE CNTR+1;
CNTR_1/I3 = IMOD(CNTR, 2, CNTR_1);
END
In this case the CNTR will only 'flip' when all your BY fields are the same as the ones on the LAST row...
hope this works...
_____________________ WF: 8.0.0.9 > going 8.2.0.5
April 22, 2010, 08:44 AM
gmb
Dave, I wish it were that easy! The rub here is that the customer doesn't care if these sort values are the same or not. They want each "block" of data to go across the page, in 2 columns. So I could have HighSchool Q1 and then next column HighSchool Q2, next row would be HighSchool Q3 and then column two may be MiddleSchool Term 1. You can see that the sort fields wouldn't not be the same.
DEFINE FILE CAR
COLNR/I4 WITH MODEL = IF COLNR EQ 2 THEN 1 ELSE 2;
ROWNR/I4 WITH MODEL = IF COLNR EQ 2 THEN ROWNR + 1 ELSE ROWNR;
EMPTY/A4 WITH MODEL = ' ';
END
TABLE FILE CAR
SUM COUNTRY AS ''
OVER CAR AS ''
OVER MODEL AS ''
OVER EMPTY AS ''
BY ROWNR NOPRINT
ACROSS COLNR NOPRINT
END
Does this help you in any way?
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
April 22, 2010, 10:31 AM
gmb
Thanks GamP, that is what I was just working with. I created a row_cntr and col_cntr in a hold file then used those for my by/across. Now I'm trying to get all the headings and footings in the way they want to see them!
