Dear Community

I’m new to Webfocus in general. However I have to design a report but have some difficulties to design it.
The report itself is not a “classical” report: There's no repeating row with figures. Have a look at my example.

My report should looks like this:

  	
      				       Hours		        Days
A desc of Line 1 (kind of title)	A	 		B		(color blue)
A desc of Line 2 (kind of title)	C			D       (color grey)
A desc of Line 3 (kind of title)    	E		        F 	(color blue)
...					...	 		...

Special is, that the figures A,B,C,D are all special calculated with formulas and they don't repeat (and sometimes they need each other).
Examples:
A/D12.2 = E*12-D-F;
B/12.1 = SUM.OF.A.FIELD.IN.MASTERFILE / 12 * AVG.OF.A.OTHER.FIELD.IN.SAMEMASTERFILE
...

My questions are:
- Could you give me some coding advices?
- How can I achieve such a design a report in IBI (output ist html)?
- Is there a posibility in a TABLE FILE definition to make some row brakes for a new line?
- The descriptions of line 1,2,3... are static and hardcoded into the report. I though it is best to do that with a DEFINE statement. Is that the right way?

I tried to make for hold file for each line and merge it together with the MORE statement. In example:

  	
TABLE FILE SYNONYM1
SUM 
	 COMPUTE DESC/A36 = 'A desc of Line 1 '; AS 'COL1'
     COMPUTE A/D12.1 = SUM.RAP_DURATION;
     COMPUTE B/D12.1 = A / 12;
WHERE (PER_ID EQ '&P_PER_ID') AND (RAP_CATEGORY EQ 'CAT1') AND (RAP_STARTDATE GT '01/01/2016');
ON TABLE HOLD AS X23
ON TABLE SET HOLDLIST PRINTONLY
END
[...]

TABLE FILE X1
PRINT *
MORE
FILE X20
MORE
FILE X21
MORE
FILE X22
MORE
FILE X23
MORE
FILE X24
END

But that brougth me to some other problems:
- I wasn't able to design the table output (i.e. no column titles, no borders, no page nums, etc.) ... very strange (but the documentation said, that I can setup my layout properties in the first hold file definition (here hold file X1) but that simply does not work?!
- When I save my coded report it does not save my code at all ?! It just breaks everything and delete a lot of code lines. I realy don't understand what's going. Same behavior when I switch to the report painter and back. 90% of my TABLE FILE definitions disapear.

Next question:
- Is there a better way of creating my report?

I read a lot in the ibi bookshelf documentation escpecially the "Create reports with the Webfocus language" and many more.

Thanks for your help.

best regards
Matt

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

Hi Matt, welcome to Focus world.

You have a lot of question and they are not all clear for me.

Here an answer to one of them.
Styling must be placed BEFORE the MORE command such as below:

TABLE FILE CAR
SUM RETAIL_COST AS 'COST'
BY COUNTRY
BY TOTAL COMPUTE ROWNAME/A10 = 'Retail';
ON TABLE HOLD AS TMP1 FORMAT FOCUS
END
-RUN

TABLE FILE CAR
SUM DEALER_COST AS 'COST'
BY COUNTRY
BY TOTAL COMPUTE ROWNAME/A10 = 'Dealer';
ON TABLE HOLD AS TMP2 FORMAT FOCUS
END
-RUN

TABLE FILE TMP1
PRINT COST AS 'Cost'
BY COUNTRY AS 'Country'
BY ROWNAME AS 'Type'
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     PAGESIZE='Letter',
     ORIENTATION=LANDSCAPE,
$
     DEFMACRO=LT10K,
     MACTYPE=RULE,
     WHEN=COST  LT 10000,
$
     DEFMACRO=GT50K,
     MACTYPE=RULE,
     WHEN=COST  GT 50000,
$
TYPE=TITLE,
     COLOR='WHITE',
     BACKCOLOR=RGB(27 80 182),
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
TYPE=DATA,
     SIZE=10,
     BACKCOLOR=( RGB(230 230 230) 'WHITE' ),
$
TYPE=DATA,
     COLUMN=COST,
     JUSTIFY=RIGHT,
     COLOR=BLUE,
$
TYPE=DATA,
     COLUMN=COST,
     COLOR=GREEN,
     MACRO=GT50K,
$
TYPE=DATA,
     COLUMN=COST,
     COLOR=RED,
     MACRO=LT10K,
$
ENDSTYLE

MORE
FILE TMP2

END
-RUN

Another answer : If the painter do remove some of your code it's because the code may not be supported by the painter. But difficult to say without seeing your code.

To proceed with color pattern there are several ways to perform such a thing : STYLING, DEFMACRO with STYLING, STYLING using WHEN clause, ...
I put some sample in my code

Dear Martin

Thanks Martin for that insane fast answer!

Can I prevent App Studio to open the report in painter mode resp. to open the report per default in the text editor mode?

best regards
Matt

You can right-click the report icon in Dev Studio and App Studio and select "Edit in Text Editor".

When requesting assistance, we often use IBI supplied tables and create code that anyone on the forum can run (and tweak). The CAR file is the most popular. Here is some code using the CAR file to generate a simple report that uses OVER. Perhaps we can play with this code to simulate what you want to achieve.

SET PAGE=NOLEAD
SET HTMLCSS=ON

TABLE FILE CAR
SUM
COMPUTE TEXT01/A20 = 'LINE 1'; AS ''
COMPUTE MEASURE11/D20 = WEIGHT * HEIGHT; AS ''
COMPUTE MEASURE12/D25 = HEIGHT * LENGTH ; AS ''
OVER
COMPUTE TEXT02/A20 = 'LINE 2'; AS ''
COMPUTE MEASURE21/D20.2 = SALES * 12; AS ''
COMPUTE MEASURE22/D20.2 = HEIGHT * WIDTH; AS ''

HEADING
"REPORT 1"
" <+0>HOURS <+0>DAYS"

ON TABLE SET STYLE *
TYPE=REPORT, FONT=CALIBRI, SIZE=10, $
TYPE=HEADING, HEADALIGN=BODY, $
TYPE=HEADING, LINE=1, COLSPAN=3, $
ENDSTYLE
END

Dear Francis

Thank you very much for your fast answers and this very cool example.

I puted your example to my Appstudio and it looks exactly what I need.

In future I will deliver runable example with the car file. I didn't know that one yet.


best regards
Matt

Dear Francis

Thanks to your help I made good progress in designing my report. But I struggling at one point again:

How can I make conditional formatting working from Martins code with your code (the over command way)? So that line 1 is i.e. green and line 2 is grey .. and so on?

I tried a lot and read the manual, but wasn't able to figure out the correct way.

Best regards
Matt

This could be a way but not the most effective since you have to style each column individually (but it may be the only way)

SET PAGE=NOLEAD
SET HTMLCSS=ON

TABLE FILE CAR
SUM
COMPUTE TEXT01/A20 = 'LINE 1'; AS ''
COMPUTE MEASURE11/D20 = WEIGHT * HEIGHT; AS ''
COMPUTE MEASURE12/D25 = HEIGHT * LENGTH ; AS ''
OVER
COMPUTE TEXT02/A20 = 'LINE 2'; AS ''
COMPUTE MEASURE21/D20.2 = SALES * 12; AS ''
COMPUTE MEASURE22/D20.2 = HEIGHT * WIDTH; AS ''

HEADING
"REPORT 1"
" <+0>HOURS <+0>DAYS"

ON TABLE SET STYLE *
TYPE=REPORT, FONT=CALIBRI, SIZE=10, $
TYPE=HEADING, HEADALIGN=BODY, $
TYPE=HEADING, LINE=1, COLSPAN=3, $
TYPE=DATA,
     COLUMN=1,
     COLOR='WHITE',
     BACKCOLOR='GREEN',
$
TYPE=DATA,
     COLUMN=2,
     COLOR='WHITE',
     BACKCOLOR='GREEN',
$
TYPE=DATA,
     COLUMN=3,
     COLOR='WHITE',
     BACKCOLOR='GREEN',
$

TYPE=DATA,
     COLUMN=4,
     COLOR='WHITE',
     BACKCOLOR='GREY',
$
TYPE=DATA,
     COLUMN=5,
     COLOR='WHITE',
     BACKCOLOR='GREY',
$
TYPE=DATA,
     COLUMN=6,
     COLOR='WHITE',
     BACKCOLOR='GREY',
$
ENDSTYLE
END

Hi Martin

Ok, column identification by number would do the trick. ARGS, why I didn't think of that.

Well there are only three columns, so no big deal at all here.

I had to read it twice: Your example format every cell.

Thanks a lot Martin.

Chears
Matt

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