Please, I need help. My report is failing. The report, as illustrated below, has been working since last year until now.

The report uses cube data and the only change between last year and this year was just Jan-2014 partition that was added to the cube.

I have tested each session of the code and found out that sessions 2 & 3 does not have data. But I don't know why this should lead for a failure because the two sessions never had data since Aug-2013 and the report was working up to Dec-2013.

Now when Jan-2014 is selected, the following error is generated:

0 NUMBER OF RECORDS IN TABLE= 3 LINES= 1
0 EXL2K FILE SAVED ...
1
0 NUMBER OF RECORDS IN TABLE= 3 LINES= 1
1
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
(FOC3289) TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
(FOC3289) TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Please, can someone help me here?

My initial questions are: does it mean that the excel sheet/template for sessions 2 and 3 are not created (or will not exist) for sessions 4 and 4 to use because there was no data for sessions 2 & 3?

Any help/advice will be appreciated. Thanks.

 
-*code
APP FI STORE_STOCK1 DISK STOCK_CAT1.MHT
APP FI STORE_STOCK2 DISK STOCK_CAT2.MHT
APP FI STORE_STOCK3 DISK STOCK_CAT3.MHT
APP FI STORE_STOCK4 DISK STOCK_CAT4.MHT

-*Session 1
TABLE FILE TREND_CAT
SUM
	CLOSING_STOCK
	COMPUTE STOCK_CT/D20 = STOCKA_CT + STOCKB_CT;
	COMPUTE STOCK_PC/D12.3 = (STOCK_CT / CLOSING_STOCK);
BY LOCATION
BY MARKETING_TYPE
BY BRAND_COLOR
BY BRAND_TYPE
BY MONTH
WHERE BRAND_TYPE EQ 'TYPE1';
WHERE MONTH EQ '&MONTH1' OR '&MNTH2' OR '&MNTH3';
WHERE BRAND_COLOR EQ 'BLUE' OR 'SILVER';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD STORE_STOCK1 FORMAT EXL2K TEMPLATE 'STOCK_TEMP' SHEETNUMBER 2
END

-*Session 2
TABLE FILE TREND_CAT
SUM
	CLOSING_STOCK
	COMPUTE STOCK_CT/D20 = STOCKA_CT + STOCKB_CT;
	COMPUTE STOCK_PC/D12.3 = (STOCK_CT / CLOSING_STOCK);
BY LOCATION
BY MARKETING_TYPE
BY BRAND_COLOR
BY BRAND_TYPE
BY MONTH
WHERE BRAND_TYPE EQ 'TYPE2';
WHERE MONTH EQ '&MONTH1' OR '&MNTH2' OR '&MNTH3';
WHERE BRAND_COLOR EQ 'BLUE' OR 'SILVER';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD STORE_STOCK2 FORMAT EXL2K TEMPLATE 'STOCK_CAT1' SHEETNUMBER 3
END

-*Session 3
TABLE FILE TREND_CAT
SUM
	CLOSING_STOCK
	COMPUTE STOCK_CT/D20 = STOCKA_CT + STOCKB_CT;
	COMPUTE STOCK_PC/D12.3 = (STOCK_CT / CLOSING_STOCK);
BY LOCATION
BY MARKETING_TYPE
BY BRAND_COLOR
BY BRAND_TYPE
BY MONTH
WHERE BRAND_TYPE EQ 'TYPE2';
WHERE MONTH EQ '&MONTH1' OR '&MNTH2' OR '&MNTH3';
WHERE BRAND_COLOR EQ 'BLUE' OR 'SILVER';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD STORE_STOCK3 FORMAT EXL2K TEMPLATE 'STOCK_CAT2' SHEETNUMBER 4
END

-*Session 4
TABLE FILE TREND_CAT
SUM
	CLOSING_STOCK
	COMPUTE STOCK_CT/D20 = STOCKA_CT + STOCKB_CT;
	COMPUTE STOCK_PC/D12.3 = (STOCK_CT / CLOSING_STOCK);
BY LOCATION
BY MARKETING_TYPE
BY BRAND_COLOR
BY BRAND_TYPE
BY MONTH
WHERE BRAND_TYPE EQ 'TYPE2';
WHERE MONTH EQ '&MONTH1' OR '&MNTH2' OR '&MNTH3';
WHERE BRAND_COLOR EQ 'BLUE' OR 'SILVER';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD STORE_STOCK4 FORMAT EXL2K TEMPLATE 'STOCK_CAT3' SHEETNUMBER 5
END

-*Session 5
TABLE FILE TREND_CAT
SUM
	CLOSING_STOCK
	COMPUTE STOCK_CT/D20 = STOCKA_CT + STOCKB_CT;
	COMPUTE STOCK_PC/D12.3 = (STOCK_CT / CLOSING_STOCK);
BY LOCATION
BY MARKETING_TYPE
BY BRAND_COLOR
BY BRAND_TYPE
BY MONTH
WHERE BRAND_TYPE EQ 'TYPE2';
WHERE MONTH EQ '&MONTH1' OR '&MNTH2' OR '&MNTH3';
WHERE BRAND_COLOR EQ 'BLUE' OR 'SILVER';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET BYDISPLAY ON
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'STOCK_CAT4' SHEETNUMBER 6
END
 

This message has been edited. Last edited by: <Kathryn Henning>,

quote:

My initial questions are: does it mean that the excel sheet/template for sessions 2 and 3 are not created (or will not exist) for sessions 4 and 4 to use because there was no data for sessions 2 & 3?

Yes, it does.

Try adding SET EMPTYREPORT = ON at the top of your code. This will ensure that the intermediate template file will be produced in the event of no records.

As to why it no longer works with no records when it did before? What has changed on your configuration - something must have been changed.

T

Thank you Tony. It worked.