Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Change Columns dynamically using computed and input fields.
Go
New
Search
Notify
Tools
Reply
  
Change Columns dynamically using computed and input fields.
 Login/Join
 
Gold member
posted
I am trying to change the column titles for a fiscal year calendar (April - March) using "@Year"(input field) and "SEL_MONTH" (computed field). I tried
to use || concatenation but its not allowing me to do that. I was able to do same in the page heading, but its not working for the column titles.

TABLE FILE TEST
SUM
COMPUTE SEL_MONTH/A20 = IF '&IMONTH' EQ '1' THEN 'January' ELSE IF '&IMONTH' EQ '2' THEN 'February' ELSE IF '&IMONTH' EQ '3' THEN 'March' ELSE IF '&IMONTH' EQ '4' THEN 'April' ELSE IF '&IMONTH' EQ '5' THEN 'May' ELSE IF '&IMONTH' EQ '6' THEN 'June' ELSE IF '&IMONTH' EQ '7' THEN 'July' ELSE IF '&IMONTH' EQ '8' THEN 'August' ELSE IF '&IMONTH' EQ '9' THEN 'September' ELSE IF '&IMONTH' EQ '10' THEN 'October' ELSE IF '&IMONTH' EQ '11' THEN 'November' ELSE IF '&IMONTH' EQ '12' THEN 'December'; NOPRINT

'CURRENTSALES/D12C' AS 'Actual Sales' (something like this SEL_MONTH + <@Year + 'Actual Sales')
'CURRENTGP/D12.1%' AS 'Actual GP%'
'PREVSALES/D12C' AS 'Actual Sales'
'PREVACHIEVED/D12%' AS 'Achvd'
'YTDCURRENTSALES/D12C' AS 'Actual Sales'
'YTDCURRENTGP/D12.1%' AS 'Actual GP%'
'YTDPREVSALES/D12C' AS 'Actual Sales'
'YTDPREVACHIEVED/D12%' AS 'Achvd'
END

For e.g. if my selected month is June and year 2007, the report should display the column headings in the following format.

June 2007 June 2007 May 2007 May 2007 June 2006 June 2006 May 2006 May 2006
Actual Sales Actual GP% Actual Sales Achvd Actual Sales Actual GP% Actual Sales Achvd

Thanks in advance for your help.

KK

This message has been edited. Last edited by: KK,
 
Posts: 59 | Registered: May 01, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You can't use variable names in headers, but you can use amper variables. Here's an example of one I do for a report:

SUM
SIDFORM AS 'NU ID'
ENR_STAT AS 'Enr,Status'
FALLHOURS AS 'Enr,Status,Fall,&FALLYEAR'
SPRHOURS AS 'Enr,Status,Spring,&SPRINGYEAR'
SUMHOURS AS 'Enr,Status,Summer,&SUMYEAR'
BY ENR_STAT AS 'ENR,STATUS'

To give an idea of what you can do.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
Thank you for your prompt reply. I am new to WEBFOCUS. Could you please show me how I can declare an amper variable and assign a computed field to it in my example.

KK
 
Posts: 59 | Registered: May 01, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You can't assign a computed variable to an &var. All of the &vars are evaluated before any data is read so they can't be based on anything in the data (which would be any COMPUTEs/DEFINEs. You may have to use DEFINES to "categorize" you data into columns then use titles as determined by &variables to appropriate columns. You declare the &variable like -SET &VARNAME=&VALUE; Don't forget the semicolon at the end and include single quote marks around &VALUE if it may contain embedded blanks.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
As Darin says, you can't use computes to set & variables, however you could create an &SEL_MONTH setting it with dialog manager commands, unfortunately, I don't feel expert enough in Dialog Manager to actually code you an example. DM experts please reply.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Check out this post:

Defined field in AS phrase


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Thank you Leah and Darin.

I will try that. I need to do some research on how to use Dialogue Manager.

KK
 
Posts: 59 | Registered: May 01, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Here's a rough example of how to use DM to dynamically change your column headings:
&RPT is signifying a value passed in from an html page that will control what is displayed for the column heading.

-SET &RPT = 'CALENDAR';
-SET &COL3HDR = IF &RPT EQ 'CALENDAR' THEN 'JAN SALES' ELSE 'JUN SALES';
-RUN

TABLE FILE CAR
PRINT MODEL
BODYTYPE
RETAIL_COST AS '&COL3HDR.EVAL'
WHERE RECORDLIMIT EQ 5
END

Here's the output generated from the above code:
PAGE 1

MODEL BODYTYPE JAN SALES
V12XKE AUTO CONVERTIBLE 8,878
XJ12L AUTO SEDAN 13,491
INTERCEPTOR III SEDAN 17,850
TR7 HARDTOP 5,100
B210 2 DOOR AUTO SEDAN 3,139


Hope this helps.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Linus,

Thank you. I tried your code but I am getting a "Error parsing report request" error on the set statement.

-SET &SELECTED_MONTH = IF '&IMONTH' EQ '1' THEN 'January' ELSE IF '&IMONTH' EQ '2' THEN 'February' ELSE IF '&IMONTH' EQ '3' THEN 'March' ELSE IF '&IMONTH' EQ '4' THEN 'April' ELSE IF '&IMONTH' EQ '5' THEN 'May' ELSE IF '&IMONTH' EQ '6' THEN 'June' ELSE IF '&IMONTH' EQ '7' THEN 'July' ELSE IF '&IMONTH' EQ '8' THEN 'August' ELSE IF '&IMONTH' EQ '9' THEN 'September' ELSE IF '&IMONTH' EQ '10' THEN 'October' ELSE IF '&IMONTH' EQ '11' THEN 'November' ELSE IF '&IMONTH' EQ '12' THEN 'December';
-RUN
TABLE FILE NEO_SALESMTDYTD_REPORT
SUM
SEL_MONTH NOPRINT
'NEO_SALESMTDYTD_REPORT.ANSWERSET1.CURRENTSALES/D12C'
AS 'Curr Month,bAct. Sales'
'NEO_SALESMTDYTD_REPORT.ANSWERSET1.CURRENTGP/D12.1%'
AS 'Curr Month,Act. GP%'
END
Please advise.

KK
 
Posts: 59 | Registered: May 01, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
KK
DM lines start with a '-'. Also rather than doing an IF..THEN..ELSE, a DECODE can be more effective.

I've written up both methods for you to view.

-DEFAULTS &IMONTH=3;
-SET &SELECTED_MONTH = IF &IMONTH EQ '1' THEN 'January'
-                 ELSE IF &IMONTH EQ '2' THEN 'February'
-                 ELSE IF &IMONTH EQ '3' THEN 'March'
-                 ELSE IF &IMONTH EQ '4' THEN 'April'
-                 ELSE IF &IMONTH EQ '5' THEN 'May'
-                 ELSE IF &IMONTH EQ '6' THEN 'June'
-                 ELSE IF &IMONTH EQ '7' THEN 'July'
-                 ELSE IF &IMONTH EQ '8' THEN 'August'
-                 ELSE IF &IMONTH EQ '9' THEN 'September'
-                 ELSE IF &IMONTH EQ '10' THEN 'October'
-                 ELSE IF &IMONTH EQ '11' THEN 'November'
-                 ELSE IF &IMONTH EQ '12' THEN 'December' ELSE ' ';
-TYPE &SELECTED_MONTH
-SET &SELECTED_MONTH = DECODE &IMONTH (1 January
-                                      2 February
-                                      3 March
-                                      4 April
-                                      5 May
-                                      6 June
-                                      7 July
-                                      8 August
-                                      9 September
-                                      10 October
-                                      11 November
-                                      12 December ELSE ' ');
-TYPE &SELECTED_MONTH


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Yes, I agree with Alan, I would use a decode for what you are attempting to do.


WF 7.7.05
HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files
HTML, Excel, PDF
 
Posts: 149 | Location: Dallas, TX | Registered: June 08, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Linus,

Thank you very much for the code. It worked !!.

Alan,

I will try using decode.

I am having problem exporting report to excel/ppt. This report contains three fexs (2 fexs(1 report is a graph and the other is a regular report) use 1 sql server stored proc and the third one uses a different stored procedure.) The chart is in PNG format.

When I run the report (right click run on the main report) in excel/ppt output format, it displays only the first report.

Anybody any suggestions ?

Thank you,
KK
 
Posts: 59 | Registered: May 01, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Do some research on COMPOUND Reports


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Prarie,
I tried using compound reports
1. It works partially with Excel format - two reports are loaded into 2 different sheets in excel, I guess that's fine but the chart is not being displayed.

2. With PowerPoint format, it displays only the first report.

KK
 
Posts: 59 | Registered: May 01, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
This is one way

If you save the Graph as an image
ON GRAPH HOLD AS GRAPHFILEA FORMAT PNG

Then in your report you can reference the graph where you would like it to be.
TYPE=HEADING,
IMAGE=GRAPHFILEA ,
POSITION=(+0.200000 +4.300000),
SIZE=(10.000000 4.000000),
$


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
KK

I would suggest you start a new question when you have a new question....

It's a bit confusing now. Your first problem had something to do with amper variables and your second is a compound report....

I also would advice you to update your signature so we all know which version you are using...




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Hi KK,

Has this issue been resolved? Please let us know the status.

Can you please also update your signature in profile (http://forums.informationbuilders.com/eve/personal?x_myspace_page=profile) to let all know the following info:

1) What version of WebFOCUS are you using?
2) What platform are you on?
3) What output format(s) are you trying to create?

Many thanks for everyone's input on this one. Smiler

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
Yes this issue has been resolved. Thank you everyone for your suggestions.

Regarding to questions 1 and 2 I already have updated my signature.

3) HTML,AHTML,EXCEL - The output format changes dynamically based on the user's selection on a .Net web page and is being passed as a parameter to the report. I created this report as a compound report and is working fine.

KK


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Change Columns dynamically using computed and input fields.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.