I have some interesting data that I need to put into a report.

A stored proc returns a number of rows which actually represent four sections of a report... all differentiated by a column called "ReportLevel".

When ReportLevel = 1, I just want to print out the one individual row with no subtotals. I don't want it broken out into any groups either.

When ReportLevel = any other value, I want to specify a different subtotal line specific to each level. I also want to "reset" the subtotals -- in other words, each report level (there will be 4) will come to their own subtotal. I don't want a grand total -- it's basically representing the same data in four different "sets".

Does this make sense? To further complicate matters, based on the level there are different control breaks specified. ReportLevel 2, for example, should subtotal by CATEGORY and MANAGER. ReportLevel 3 should subtotal BY ITEM, BY CATEGORY, BY MANAGER.

I can handle doing the different subtotal lines by:

BY REPORTLEVEL NOPRINT
ON REPORTLEVEL SUBFOOT
"Second Line"
WHEN REPORTLEVEL EQ 2;
SUBFOOT
"Third Line"
WHEN REPORTLEVEL EQ 3;
SUBFOOT
"Fourth Line"
WHEN REPORTLEVEL EQ 4;

but this will still result in having the identical "BY" for all levels. And I can't figure out how to make JUST THE ONE ROW that has REPORTLEVEL = 1 print out with no subbreaks.

I would do some pre-processing first - isolate the two parts of the report details - report level 1 in a hold file, the others in another hold file. Then use MORE and bring them together.

Here's something quick that may help:

TABLE FILE CAR
SUM 
SALES
COMPUTE BODYTYPE/A12 = ''; 
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS H002
END

TABLE FILE CAR
SUM SALES
BY COUNTRY
BY BODYTYPE
WHERE COUNTRY NE 'ENGLAND'
ON TABLE HOLD AS H003
END

TABLE FILE H002
SUM SALES
BY COUNTRY
BY BODYTYPE NOPRINT
ON BODYTYPE SUBHEAD
" "
"BODYTYPE: <BODYTYPE"
WHEN COUNTRY NE 'ENGLAND'

ON BODYTYPE SUBTOTAL SALES AS 'TOTAL'
WHEN COUNTRY NE 'ENGLAND'
MORE
FILE H003
END

The first problem would be easy enough if there is only one row - you can use the MULTILINES option on the subtotal. This eliminates subtotal lines if there is only a single record for that sort value.
As for different sortfields on each section, I don't think that will work unless you redefine columns like
SORT1/A30=IF REPORTLEVEL EQ 2 THEN CATEGORY ELSE IF REPORTLEVEL EQ 4 THEN ITEM;
SORT2/A30=IF REPORTLEVEL EQ 2 THEN MANAGER ELSE IF REPORTLEVEL EQ 3 THEN CATEGORY;
SORT3/A30=IF REPORTLEVEL EQ 2 THEN ' ' ELSE IF REPORTLEVEL EQ 3 THEN MANAGER;

then you could use
BY SORT1
BY SORT2
BY SORT3

In addition of Darin's solution you can create SORT fields as a combination of ITEM, CATEGORY, MANAGER depending on the reportlevel

DEFINE FILE XXX
SORTFIELD/A50=IF REPORTLEVEL EQ 2 THEN CATEGORIE||MANAGER ELSE IF REPORTLEVEL EQ 3 THEN ITEM||CATEGORIE||MANAGER ELSE MANAGER;
END

Now you have only one sortfield per level, but it will be difficult to get inner by totals on the sublevels.

Francis, I tried out your suggestion, and it's getting close but not quite.

I need to have four levels in all of reporting off this data. Each line of the report has about 10 columns in it, with computations. And I want to be able to make different headings and the like for each "section" of the report.

I tried doing the following expansion of your code suggestion:

TABLE FILE CAR
SUM SALES SEATS MODEL
COMPUTE BODYTYPE/A12 = ''; 
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND'
ON TABLE HOLD AS H002
END

TABLE FILE CAR
SUM SALES SEATS MODEL
BY COUNTRY
BY BODYTYPE
WHERE COUNTRY NE 'ENGLAND'
ON TABLE HOLD AS H003
END

TABLE FILE CAR
SUM SALES SEATS MODEL
COMPUTE BODYTYPE/A12 = 'x';
BY COUNTRY
WHERE COUNTRY NE 'ENGLAND'
ON TABLE HOLD AS H004
END

TABLE FILE H002
SUM SALES SEATS MODEL
BY COUNTRY
BY BODYTYPE NOPRINT
ON BODYTYPE SUBHEAD
" "
"BODYTYPE: <BODYTYPE"
WHERE COUNTRY NE 'ENGLAND'

ON BODYTYPE SUBTOTAL SALES AS 'TOTAL'
WHERE COUNTRY NE 'ENGLAND'
MORE
FILE H003
MORE
FILE H004
END

I had to define BODYTYPE as something or it would result in an error.

To take the CAR file example to what I want to do:

I would want the report to look something like this:

REPORT LEVEL   COUNTRY            MODEL     SALES       SEATS         
      1        ALL COUNTRIES       -        total       total

      2        FRANCE              -        total       total
               ITALY               -        total       total
               GERMANY             -        total       total
               ...
               SUBTOTAL                     total       total

      3        FRANCE             4 DOOR      amt         amt
                                  SEDAN       amt         amt
               SUBTOTAL                     total       total
               ITALY              2 DOOR      amt         amt
               SUBTOTAL                     total       total
               ...        
       
      4        --                 4 DOOR    total       total
                                  2 DOOR    total       total
                                  SEDAN     total       total
               SUBTOTAL                     total       total

This may get you closer:

-SET &ECHO=ALL;

SET HOLDLIST=PRINTONLY
-RUN

TABLE FILE CAR
SUM SALES SEATS
COMPUTE BODYTYPE/A12 = '-';
COMPUTE COUNTRY/A10 = 'ALL';
COMPUTE REPORT_LEVEL/A1 = '1';
ON TABLE HOLD AS H001
END
-RUN

TABLE FILE CAR
SUM SALES SEATS
COMPUTE BODYTYPE/A12 = '-';
COMPUTE REPORT_LEVEL/A1 = '2';
BY COUNTRY
ON TABLE HOLD AS H002
END
-RUN

TABLE FILE CAR
SUM SALES SEATS
COMPUTE REPORT_LEVEL/A1 = '3';
BY COUNTRY
BY BODYTYPE
ON TABLE HOLD AS H003
END
-RUN

TABLE FILE CAR
SUM SALES SEATS
COMPUTE REPORT_LEVEL/A1 = '4';
COMPUTE COUNTRY/A10 = '-';
BY BODYTYPE
ON TABLE HOLD AS H004
END
-RUN

TABLE FILE H001
SUM SALES/D10 SEATS
BY REPORT_LEVEL
BY COUNTRY
BY BODYTYPE

ON REPORT_LEVEL SUBTOTAL MULTILINES AS 'TOTAL REPORT_LEVEL'
ON COUNTRY SUBTOTAL MULTILINES AS 'TOTAL COUNTRY'
ON TABLE NOTOTAL

MORE
FILE H002
MORE
FILE H003
MORE
FILE H004
END
-RUN

I would tend to use McGyver for this:

SET HOLDLIST=PRINTONLY
JOIN BLANK WITH BODYTYPE IN CAR TO BLANK IN EC_MCGYV
DEFINE FILE CAR
BLANK/A1 WITH BODYTYPE = ' ';
SORT_GROUP/A100V = IF INSTANCE EQ 1 THEN ' ' ELSE
                   IF INSTANCE EQ 2 THEN COUNTRY ELSE
                   IF INSTANCE EQ 3 THEN COUNTRY||BODYTYPE ELSE
                                         BODYTYPE;
SORT_LEVEL1/A40V = IF INSTANCE EQ 1 THEN 'ALL COUNTRIES' ELSE
                   IF INSTANCE EQ 2 THEN COUNTRY ELSE
                   IF INSTANCE EQ 3 THEN COUNTRY ELSE '--';
SORT_LEVEL2/A40V = IF INSTANCE EQ 1 THEN '-' ELSE
                   IF INSTANCE EQ 2 THEN '-' ELSE BODYTYPE;
END
TABLE FILE CAR
SUM SALES SEATS
BY INSTANCE
BY SORT_GROUP NOPRINT
BY SORT_LEVEL1
BY SORT_LEVEL2
WHERE INSTANCE LE 4;
ON TABLE HOLD
END
TABLE FILE HOLD
SUM SALES SEATS
BY INSTANCE AS 'Report Level' SUBTOTAL MULTILINES  AS 'Total'
BY SORT_LEVEL1 AS 'Country' SUBTOTAL AS 'Total' WHEN INSTANCE EQ 3;
BY SORT_LEVEL2 AS 'Bodytype'
ON TABLE NOTOTAL
END

Tony has a very good example for creating a McGyver file, which can be found in the forum.

Francis, Alan, thanks for your assistance. I have no idea what "mcGyver" means -- I've seen it mentioned here before but don't understand a thing about implementing it or what it means.

Francis, I tried your example and expanded it further, and I think that will end up working for me. Thanks so much for your assistance on this!

There are plenty of examples of McGyver on the IB site, however a base explanation and one use of McGyver is to create multiple instances of a record. In this case the requirement is to have 4 different reporting levels, therefore, 4 instances of each record is required, one for each report level.
A quick McGyver file can be created by:

FILEDEF MCMAS DISK MCGYVER.MAS
FILEDEF MCGYVER DISK MCGYVER.DAT
-RUN
-WRITE MCMAS FILE=NAMETAB,SUFFIX=FIX
-WRITE MCMAS SEGNAME=SEG1
-WRITE MCMAS FIELD=BLANK,,A1,A1,$
-WRITE MCMAS SEGNAME=SEG2,OCCURS=VARIABLE
-WRITE MCMAS FIELD=INSTANCE,,I2,A2,$
-WRITE MCGYVER  01020304

(note 2 spaces between -WRITE MCGYVER and 01020304)
The 01020304 are the number of instances that are needed for the request, the number of time the record needs to occur to create the report.

Then JOIN with the DEFINE. If this was used:

JOIN BLANK WITH country IN CAR TO BLANK IN MCGYVER
DEFINE FILE CAR
BLANK/A1 WITH COUNTRY = ' ';

The result of PRINT COUNTRY BY INSTANCE would be:

COUNTRY INSTANCE
ENGLAND        1
ENGLAND        2
ENGLAND        3
ENGLAND        4
FRANCE         1
FRANCE         2
FRANCE         3
FRANCE         4
.
.

So although each COUNTRY occurs once with a PRINT, it now occurs 4 times.

The JOIN structure gives 4 occurrences of instance for every host field. So now for every record retrieved, you can have the 4 reporting levels, the number of times INSTANCE occurs for every host field.

okay that starts to make sense. However it's not really what I have in my situation. I have four sets of data (all same columns) returned by a stored procedure. I want to report on each set of data "independently" within the same report -- each report set has a different "group by" and "subtotal" calculation that is independent of the others although all four are basically reporting on the same sort of data.

There will only be one ReportLevel 1 record (with all columns). 4-10 ReportLevel 2, 30-50 ReportLevel 3, 100-500 ReportLevel 4. I basically want to have different grouping and subtotals for each reportLevel.

Then perhaps my suggestion gets you closer to the solution?

I would go with Francis on this then.

It is definitely getting me closer to my goal... thanks Francis. Now I'm trying to get it to work with the various formatting that I need to do to it -- different color headings based on the levels, subtotals, etc.

My biggest issue arises in trying to get the formatting to work in it now. I initially was doing things with a PRINT command and formatting instructions such as only showing the first 10 characters of some of the fields, formatting dollar and percent display, and doing some additional COMPUTE displays based on a field as a percent of the total of that field for a given section. I am not sure how to do that using the current MORE construct where I'm just using multiple simple SUM statements.

Formatting particular rows based on a column value would be done with the WHEN clause:

TYPE=DATA, COLOR=RED, WHEN= REPORT_LEVEL EQ '2', $

I don't know if this helps.

oh that's very helpful, thanks. That handles the color stuff. But the other thing is that I want to be able to format the individual columns of data as well as generate some additional columns from the other database-provided columns.

Things like

COMPUTE PPERCENT/D7.1% = (PDollars / TOT.PDollars) * 100; AS '% TO,TOTAL'
PDollars/D10M AS '$$$'

I was doing that in the PRINT statement from the data before I tried splitting it all up into the four sections, but now the only way I seem to be able to generate the four sections of data is through the SUM verb which won't allow me to handle that type of stuff.

I've just been getting caught up on the forum posts and if I understand the start of this correctly you have data similar to this (I hope it shows up ok)...

RPTLVL COUNTRY_COL       MODEL_COL                   SALES    SEATS 
1      ALL COUNTRIES     -                           208420   70 
2      ENGLAND           -                           12000    13 
2      FRANCE            -                           0        5 
2      ITALY             -                           30200    10 
2      JAPAN             -                           78030    8 
2      W GERMANY         -                           88190    34 
3      ENGLAND           INTERCEPTOR III             0        4 
3      ENGLAND           TR7                         0        2 
3      ENGLAND           V12XKE AUTO                 0        2 
3      ENGLAND           XJ12L AUTO                  12000    5 
3      FRANCE            504 4 DOOR                  0        5 
3      ITALY             2000 4 DOOR BERLINA         4800     4 
3      ITALY             2000 GT VELOCE              12400    2 
3      ITALY             2000 SPIDER VELOCE          13000    2 
3      ITALY             DORA 2 DOOR                 0        2 
3      JAPAN             B210 2 DOOR AUTO            43000    4 
3      JAPAN             COROLLA 4 DOOR DIX AUTO     35030    4 
3      W GERMANY         100 LS 2 DOOR AUTO          7800     5 
3      W GERMANY         2002 2 DOOR                 8950     5 
3      W GERMANY         2002 2 DOOR AUTO            8900     4 
3      W GERMANY         3.0 SI 4 DOOR               14000    5 
3      W GERMANY         3.0 SI 4 DOOR AUTO          18940    5 
3      W GERMANY         530I 4 DOOR                 14000    5 
3      W GERMANY         530I 4 DOOR AUTO            15600    5 
4      -                 100 LS 2 DOOR AUTO          7800     5 
4      -                 2000 4 DOOR BERLINA         4800     4 
4      -                 2000 GT VELOCE              12400    2 
4      -                 2000 SPIDER VELOCE          13000    2  
4      -                 2002 2 DOOR                 8950     5 
4      -                 2002 2 DOOR AUTO            8900     4 
4      -                 3.0 SI 4 DOOR               14000    5 
4      -                 3.0 SI 4 DOOR AUTO          18940    5 
4      -                 504 4 DOOR                  0        5 
4      -                 530I 4 DOOR                 14000    5 
4      -                 530I 4 DOOR AUTO            15600    5 
4      -                 B210 2 DOOR AUTO            43000    4 
4      -                 COROLLA 4 DOOR DIX AUTO     35030    4 
4      -                 DORA 2 DOOR                 0        2 
4      -                 INTERCEPTOR III             0        4 
4      -                 TR7                         0        2 
4      -                 V12XKE AUTO                 0        2 
4      -                 XJ12L AUTO                  12000    5 
  

I created a hold file that looks like this and all it took to report from it was:

 TABLE FILE FORUM1
SUM SALES SEATS
BY RPTLVL
BY COUNTRY_COL
BY MODEL_COL
ON RPTLVL SUBTOTAL
WHEN RPTLVL NE 1
ON COUNTRY_COL SUBTOTAL MULTI-LINES
WHEN RPTLVL EQ 3
ON TABLE NOTOTAL
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END
 

Is that similar to what you were originally trying to do?

As far as formatting a specific column use:

 TYPE=DATA,column=fieldname, ... ,$ 

Specify fonts colours, etc after the fieldname.

Computed fields can be added on the sum statement, but why don't you show us where you are at with your code so we can help further.

I've been trying to "simplify" it using the CARS so that people can pick up on what I'm doing, but I'm having difficulties translating it to use with the data I have.

Your example looks great and very simple and straightforward. It is showing great promise in working with my data -- and then unfortunately most of the test data has been deleted (by another part of the project) so I can't fully test it.

Here's what my code is looking like now:

-SET &ECHO=ALL;

-DEFAULT &ddlDirector = 137;
-DEFAULT &ddlYear = 2007;

SET HOLDLIST=PRINTONLY
-RUN

-SET &SERVERNAME = 'XXXXX';
-SET &DBNAME = 'XXXXX.dbo.';
-DEFAULT &rptfmt = 'PDF';
-SET &RPTHEAD = ' REPORT FOR ' | &ddlYear ;

SQL SQLMSS SET SERVER &SERVERNAME
SQL SQLMSS
EX &DBNAME.STOREDPROC '&ddlDirector', '&ddlYear';

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS RAWDATA
END

TABLE FILE RAWDATA
SUM ManagerLogin Category Rank Company Location Avg ActualUnits ActualDollars PlannedUnits PlannedDollars LYActualUnits LYActualDollars  
BY ReportLevel NOPRINT
BY ManagerLogin NOPRINT
BY Category NOPRINT
BY Rank NOPRINT
ON ReportLevel SUBTOTAL
WHEN ReportLevel EQ 2
ON ManagerLogin SUBTOTAL MULTI-LINES
WHEN ReportLevel EQ 3
ON ManagerLogin SUBTOTAL MULTI-LINES
WHEN ReportLevel EQ 4
ON Rank SUBTOTAL MULTI-LINES
ON Category SUBTOTAL MULTI-LINES
ON TABLE NOTOTAL


ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLEMODE FIXED
ON TABLE PCHOLD FORMAT &rptfmt
ON TABLE SET STYLE *
	UNITS=IN,
	PAGESIZE='LETTER',
	SQUEEZE=ON,
	LEFTMARGIN=0.10,
	RIGHTMARGIN=0.10,
	TOPMARGIN=0.10,
	BOTTOMMARGIN=0.10,
	ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
	GRID=ON,
	FONT='ARIAL',
	SIZE=6,
	COLOR='BLACK',
	BACKCOLOR='NONE',
	STYLE=NORMAL,
	LINEBREAK='CRLF',
$
TYPE=TITLE,STYLE=BOLD,JUSTIFY=CENTER,$
TYPE=HEADING,STYLE=BOLD,JUSTIFY=CENTER,BACKCOLOR=RGB(170 213 255),$

TYPE=DATA,COLOR=RED,WHEN=ReportLevel EQ 1,$
TYPE=DATA,COLOR=GREEN,WHEN=ReportLevel EQ 2,$
TYPE=DATA,COLOR=BLUE,WHEN=ReportLevel EQ 3,$
TYPE=DATA,COLOR=ORANGE,WHEN=ReportLevel EQ 4,$


ENDSTYLE

END

The remaining issues for the first three levels (data is missing for level 4 right now so i can't test) are cosmetic and compute issues -- I have tried various places to put the COMPUTE directive to determine things like "percent of ActualDollars to Total Actual Dollars" WITHIN THE REPORT LEVEL.

What i have is looking really good now (thanks to your help and Francis' help) except for being able to format the displays and do those computes, and I will have to manually do the subtotals of course since WebFocus breaks it into two lines for you all the time (I hate that).

Thanks a ton.

The (sub)total issue has been discussed here many times.
Try to find those points here, it has something to do with adding an extra blank field. And do a by on that field.

[rant]
Yeah, but I've never, never, never, managed to figure out CONSISTENTLY how to resolve the problem EVERY TIME I have it. Enough people have spent enough time on this issue, and enough users have asked too many times why this happens. I think the software should take care of it.
[/rant]

Francis, I second that rant. It is ridiculous that you have to make gyrations to get that to work. FrankDutch, I have gone through the documents repeatedly and pulled my hair out over getting something to work properly with that. I've gotten to work but it was arduous. The software should not put you through that much trouble for something that seems to be wanted enough to take up a ton of questions on how to do it on the forum.

quote:

I have tried various places to put the COMPUTE directive to determine things like "percent of ActualDollars to Total Actual Dollars" WITHIN THE REPORT LEVEL.

Not sure if this will work but try...

SUM ...
COMPUTE PCT_DOLL/D6.2% = (ActualDollars /ST.ActualDollars) * 100;
BY ReportLevel NOPRINT
BY ManagerLogin NOPRINT
...etc...


Otherwise it would be a multi-verb request.

SUM ActualDollars
BY ReportLevel
SUM ...
COMPUTE PCT_DOLL/D6.2% = (ActualDollars /C1) * 100;
BY ReportLevel NOPRINT
BY ManagerLogin NOPRINT
...etc...


Let me know how that goes.

Also with the COMPUTE you'd need to use RECOMPUTE or SUMMARIZE instead of SUBTOTAL.

quote:

SUM ActualDollars
BY ReportLevel
SUM ...
COMPUTE PCT_DOLL/D6.2% = (ActualDollars /C1) * 100;
BY ReportLevel NOPRINT
BY ManagerLogin NOPRINT
...etc...

This method worked. However, I still am struggling with how to shorten some of my longer text fields displaying in the report. That's where I was using the PRINT before, to PRINT Label/A10 for example to shorten it to 10 characters. Can't do the PRINT statement the way it's going here because PRINT has to be the last statement, and I'm trying to format the first couple of columns. Same thing with applying dollar formats to the dollars, or percentages as you have using the COMPUTE verb.