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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Report design question (with code example)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Report design question (with code example)
 Login/Join
 
Member
posted
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>,


WebFOCUS 8
Windows, All Outputs
 
Posts: 26 | Location: Switzerland | Registered: August 17, 2016Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
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


WebFOCUS 8
Windows, All Outputs
 
Posts: 26 | Location: Switzerland | Registered: August 17, 2016Report This Post
Expert
posted Hide Post
You can right-click the report icon in Dev Studio and App Studio and select "Edit in Text Editor".


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
Expert
posted Hide Post
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


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
Member
posted Hide Post
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


WebFOCUS 8
Windows, All Outputs
 
Posts: 26 | Location: Switzerland | Registered: August 17, 2016Report This Post
Member
posted Hide Post
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


WebFOCUS 8
Windows, All Outputs
 
Posts: 26 | Location: Switzerland | Registered: August 17, 2016Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Member
posted Hide Post
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,


WebFOCUS 8
Windows, All Outputs
 
Posts: 26 | Location: Switzerland | Registered: August 17, 2016Report 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]Report design question (with code example)

Copyright © 1996-2020 Information Builders