Hi,

I am stuck on a situation to display custom error message in compound report in EXL2K (having 2 or more than 2 sheets). To display custom error message, I include a .fex file to program based on a below condition:

-IF &LINES EQ 0 THEN GOTO ERRORMSG;
-ERRORMSG
-INCLUDE ERROR_MESSAGE.FEX

-GOTO END_RPT


I don't want to used to hold data and print error message based on the no of records in hold file(should satisfied above condition). I want to print data directly from database like below:

TABLE FILE CAR
SUM
AMOUNT
RATE
BY MODEL
WHERE COUNTRY EQ 'INDIA';
ON TABLE PCHOLD FORMAT EXL2K OPEN
END

-IF &LINES EQ 0 THEN GOTO ERRORMSG1 ELSE GOTO SKIP_ERRORMSG1;
-ERRORMSG1
-INCLUDE ERROR_MESSAGE.FEX

-SKIP_ERRORMSG1

TABLE FILE CAR
SUM
AMOUNT
RATE
BY MODEL
WHERE COUNTRY EQ 'U.K.';
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END

-IF &LINES EQ 0 THEN GOTO ERRORMSG2 ELSE GOTO SKIP_ERRORMSG2;
-ERRORMSG2
-INCLUDE ERROR_MESSAGE.FEX

-SKIP_ERRORMSG2



Problem situation:
Suppose there is situation when there is data only for 'INDIA' not for 'U.K.', then 2nd PCHOLD will close and report will not print any record. After that processor reads condition for error message, which comes true. But report will not include this error message sheet because compound report got closed in 2nd PCHOLD.

Please suggest me here any best approach to deal with this problem situation.


Thanks,
Sandeep Sagar

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

Is there any specific reason, you should not hold the data?

&LINES wont be assigned until the TABLE FILE request ends either as a HOLD or a PCHOLD...

One simple solution is something as below. Have a dummy sheet at the end printing nothing...

 

TABLE FILE CAR
SUM
SALES
BY MODEL
WHERE COUNTRY EQ 'JAPAN';
ON TABLE PCHOLD FORMAT EXL2K OPEN
END

-IF &LINES EQ 0 THEN ERRORMSG1 ELSE SKIP_ERRORMSG1; 
-ERRORMSG1
-INCLUDE error_message.fex 

-SKIP_ERRORMSG1

TABLE FILE CAR
SUM
SALES
BY MODEL
WHERE COUNTRY EQ 'INDIA';
ON TABLE PCHOLD FORMAT EXL2K
END

-IF &LINES EQ 0 THEN ERRORMSG2 ELSE SKIP_ERRORMSG2; 
-ERRORMSG2
-INCLUDE error_message.fex
-SKIP_ERRORMSG2

TABLE FILE CAR
PRINT
CAR NOPRINT
ON TABLE PCHOLD FORMAT EXL2K CLOSE
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='x',$
END

Thanks for your suggestion Ram.

I already go ahead with this approach but my WF Manager does not want solution like this way. He suggests me to passes data as minimum as possible. That's the reason to not use HOLD file.
In this approach, we call TABLE 3 times, but I want solution in 2 calls only.


Thanks,
Sandeep Sagar

quote:

&LINES wont be assigned until the TABLE FILE request ends either as a HOLD or a PCHOLD...

Hopefully it can't be. Let us see, if someone else has something with this.

You might try:

SET EMPTYREPORT = ON

This should cause a blank sheet to be created when there is no data.

or....

SET EMPTYREPORT = ANSI

..will cause a blank sheet with headings and column titles to be created.

Also, please add a -RUN after the TABLE requests.

Dialog Manager is evaluated before TABLE requests, and &LINES will not be correct.

Hi Dan,

Thanks for your suggestions.
By using SET EMPTYREPORT = ON, I get the report with having column name and heading information. But here is different scenario for me.
I have another .fex file for displaying error message on the report when there is not data found in Database. This error message may be one of given below:
1. WebFocus Error Code
2. No data found in Database

Also, report output needs to display heading information, image/logo and footer information(Footer information can be achieved by using SET EMPTYREPORT = ANSI).

My main motive is "To include my custom error message .fex file by using only two passes of data (If there only sheets in the report)."


Thanks,
Sandeep Sagar

We have had the same requirement and there is no real solution outside of validating if data is returned and if not, including a no data fex. The only way to do that is to use a hold file.

Here's an approach that might work. My embedded error logic would be replaced with your -INCLUDE ERRORMSG.FEX. You can uncomment one or both of the WHERE clauses to see how report failures are handled.

-* Set EMPTYREPORT off so empty sheets are not created.
SET HOLDLIST    = PRINTONLY
SET ASNAMES     = ON
SET EMPTYREPORT = OFF
-*
-* Run the first report request, which opens the spreadsheet file.
TABLE FILE CAR
 SUM SALES
 BY COUNTRY
 BY CAR
-* WHERE COUNTRY EQ 'INDIA';
 ON TABLE PCHOLD FORMAT EXL07 OPEN
END
-*
-RUN
-* If the first report request produces output, go to the second report request.
-IF (&LINES GT 0) GOTO :RPT_2 ;
-*
-* If the first report request fails, display an error message.
TABLE FILE CAR
 HEADING
  "Your first report did not produce any output."
 PRINT COUNTRY NOPRINT
 WHERE RECORDLIMIT EQ 1 ;
 ON TABLE PCHOLD FORMAT EXL07
END
-RUN
-*
-:RPT_2
-*
-* Run the second report request.
TABLE FILE CAR
 SUM RETAIL_COST
 BY COUNTRY
 BY CAR
-* WHERE COUNTRY EQ 'SPAIN';
 ON TABLE PCHOLD FORMAT EXL07
END
-*
-RUN
-* If the second report request produces output, go to a final dummy request to close the spreadsheet.
-IF (&LINES GT 0) GOTO :CLOSE_RPT ;
-*
-* If the second report request fails, display an error message.
TABLE FILE CAR
 HEADING
  "Your second report did not produce any output."
 PRINT COUNTRY NOPRINT
 WHERE RECORDLIMIT EQ 1 ;
 ON TABLE PCHOLD FORMAT EXL07
END
-RUN
-*
-:CLOSE_RPT
-* The only purpose of this final dummy report request is to close the spreadsheet file.
TABLE FILE CAR
 PRINT COUNTRY
 WHERE COUNTRY EQ 'NONSENSE';
 WHERE RECORDLIMIT EQ 1 ;
 ON TABLE PCHOLD FORMAT EXL07 CLOSE
END
-RUN

This message has been edited. Last edited by: Dan Satchell,

quote:

In this approach, we call TABLE 3 times, but I want solution in 2 calls only.

Hello Dan,

I suggested the same.. He is not fine with that solution.

Thanks,
Ram.

One thing that I would remind you about when using an -INCLUDE multiple times within the same procedure and that is that you should ensure that you do not use GOTO [Label] syntax as you could inadvertently cause a loop.

So, what is the most important thing to achieve here? Is it to produce a report with the minimal amount of data handling or is it to produce the error message(s)?

T