I am writing a reporting object that uses CUBE. I need to put a condition where the month = Last month (which is last partion). This is proving difficult.
Please does anyone know how this can be done? ThanksThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
February 17, 2010, 11:26 AM
umun
I can get last month by using system date. But the problem is that system last month may not be last month in cube. For example, last month is Jan 2010 but my cube has last month as Dec 2009.
I am looking for ways for the report to pick cube's last month. Thanks.
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
February 18, 2010, 04:15 AM
umun
Here is an example of what I want to do: -SET &VAR_MONTH = -1; TABLE FILE TEST_REPORT SUM TOTAL_OUTSTANDING_AMT BY COUNTRY BY BRAND ACROSS MONTH WHERE MONTH EQ '&LAST_DT');
The &VAR_MONTH value needs to either be -1 or -2. If &VAR_MONTH = -1 and no record is return, then let &VAR_MONTH= -2.
Please, guys any suggestions on how I can achieve this?
Thank you.This message has been edited. Last edited by: umun,
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
February 18, 2010, 05:33 AM
umun
Here is what I have tried but not successful. Please can someone check to see what could be wrong. The data source is anaylysis cube.
Thank you.This message has been edited. Last edited by: umun,
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
February 18, 2010, 06:58 AM
Dan Satchell
Here are two possible solutions. The first uses a multi-verb request to find the most recent MONTH in your table and then uses that value (C1 = Column 1) to produce the report. The second saves the latest MONTH value to a variable, which is used in the WHERE clause of the subsequent report request.
#1:
TABLE FILE TEST_REPORT
SUM MAX.MONTH NOPRINT
SUM TOTAL_OUTSTANDING_AMT
BY COUNTRY
BY BRAND
ACROSS MONTH
WHERE MONTH EQ C1 ;
END
#2:
TABLE FILE TEST_REPORT
SUM MAX.MONTH
ON TABLE SAVE
END
-*
-RUN
-READ SAVE, &LASTMONTH
-*
TABLE FILE TEST_REPORT
SUM TOTAL_OUTSTANDING_AMT
BY COUNTRY
BY BRAND
ACROSS MONTH
WHERE MONTH EQ '&LASTMONTH';
END
WebFOCUS 7.7.05
February 18, 2010, 08:06 AM
umun
quote:
Originally posted by Dan Satchell: Here are two possible solutions. The first uses a multi-verb request to find the most recent MONTH in your table and then uses that value (C1 = Column 1) to produce the report. The second saves the latest MONTH value to a variable, which is used in the WHERE clause of the subsequent report request.
#1:
TABLE FILE TEST_REPORT
SUM MAX.MONTH NOPRINT
SUM TOTAL_OUTSTANDING_AMT
BY COUNTRY
BY BRAND
ACROSS MONTH
WHERE MONTH EQ C1 ;
END
#2:
TABLE FILE TEST_REPORT
SUM MAX.MONTH
ON TABLE SAVE
END
-*
-RUN
-READ SAVE, &LASTMONTH
-*
TABLE FILE TEST_REPORT
SUM TOTAL_OUTSTANDING_AMT
BY COUNTRY
BY BRAND
ACROSS MONTH
WHERE MONTH EQ '&LASTMONTH';
END
Hi Dan, I have been able to resolve the issue. Thank you. But I used "ON TABLE HOLD AS HOLDTEST FORMAT FOCUS" instead of "ON TABLE SAVE" and I didn't use the -RUN command. Is there any difference? what is the effect of the -RUN command?
Thank you
WebFOCUS 7.7.02 Windows/SQL/CUBES HTML/PDF/XLS
February 18, 2010, 08:57 AM
Dan Satchell
quote:
But I used "ON TABLE HOLD AS HOLDTEST FORMAT FOCUS" instead of "ON TABLE SAVE" and I didn't use the -RUN command. Is there any difference? what is the effect of the -RUN command?
ON TABLE HOLD FORMAT FOCUS saves the results in a FOCUS file, which cannot be read in any meaningful way with a -READ. ON TABLE SAVE saves the results in a text file which can be read with -READ.
As WebFOCUS reads the statements in a focexec, standard commands and statements (TABLE FILE, etc.) are put into the FOCSTACK for later execution, and Dialogue Manager statements, which all begin with a dash, are executed immediately. The statements in the FOCSTACK are executed when the end of the focexec is reached, or whenver a -RUN is encountered.
In the example I provided, without the -RUN, the TABLE FILE...ON TABLE SAVE would be put in the FOCSTACK and the -READ would execute immediately. This would result in an error message about the SAVE file not existing.