Change Columns dynamically using computed and input fields.
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.
KKThis message has been edited. Last edited by: KK,
June 19, 2007, 11:27 AM
Leah
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
June 19, 2007, 11:43 AM
KK
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
June 19, 2007, 11:49 AM
Darin Lee
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
June 19, 2007, 11:54 AM
Leah
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.
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
June 19, 2007, 12:18 PM
KK
Thank you Leah and Darin.
I will try that. I need to do some research on how to use Dialogue Manager.
KK
June 20, 2007, 08:34 AM
linus
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
June 20, 2007, 12:30 PM
KK
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
June 20, 2007, 12:53 PM
Alan B
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
June 20, 2007, 01:06 PM
linus
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
June 20, 2007, 01:20 PM
KK
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
June 20, 2007, 01:46 PM
Prarie
Do some research on COMPOUND Reports
In Focus since 1993. WebFOCUS 7.7.03 Win 2003
June 20, 2007, 01:50 PM
KK
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
June 20, 2007, 01:55 PM
Prarie
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
June 20, 2007, 04:07 PM
FrankDutch
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
June 27, 2007, 02:23 PM
Kerry
Hi KK,
Has this issue been resolved? Please let us know the status.
Can you please also update your signature in profile (https://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.
Cheers,
Kerry
Kerry Zhan Focal Point Moderator Information Builders, Inc.
June 28, 2007, 09:51 AM
KK
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