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? Thanks

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

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.

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,

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,

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

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

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.