Hi all,

Bare with me here.. So I have a report, lets call it year. Also I have a quarter report, and month report.

I let the users drill from the year, to quarter to month, in the across value and back up to the quarter and the year. I also have parameterized controls that I let the select, so I'm passing those along as well. This is in version 8203 Page designer.

I have a report like I've created in our production environment and everything is working fine but not in our development environment for some reason. When I drill down to the last level, the month, I get this error

"0 ERROR AT OR NEAR LINE 80 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
0 ERROR AT OR NEAR LINE 81 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
0 ERROR AT OR NEAR LINE 82 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
0 ERROR AT OR NEAR LINE 83 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
0 ERROR AT OR NEAR LINE 84 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
0 ERROR AT OR NEAR LINE 91 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
0 ERROR AT OR NEAR LINE 92 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
0 ERROR AT OR NEAR LINE 93 IN PROCEDURE revenue_month_proj
(FOC224) SYNTAX ERROR: _FOC_NULL''
(FOC009) Request failed validation, not executed.
"

I believe it's adding extra quotes on the values being passed back and forth. Also I'm not having this issue in production. Is there an admin setting or something I'm missing in our dev environment?

here's my report

 -SET &ECHO=ALL;
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

-SET &TOTAL_PROJ_LC='SELLPRICEEXT_LC/D12 AS '' ''';
-SET &TOTAL_PROJ_USD='SELLPRICEEXT_USD/D12 AS '' ''';
-SET &TOTAL_PROJ_EUR='SELLPRICEEXT_EUR/D12 AS '' ''';
-SET &TOTAL_PROJ_AUD='SELLPRICEEXT_AUD/D12 AS '' ''';
-SET &TOTAL_MAINT_LC='MAINTENANCESELLPRICEEXT_LC/D12 AS '' ''';
-SET &TOTAL_MAINT_USD='MAINTENANCESELLPRICEEXT_USD/D12 AS '' ''';
-SET &TOTAL_MAINT_EUR='MAINTENANCESELLPRICEEXT_EUR/D12 AS '' ''';
-SET &TOTAL_MAINT_AUD='MAINTENANCESELLPRICEEXT_AUD/D12 AS '' ''';
-SET &GRAND_TOTAL_LC='TOTAL_SELLPRICE_LINE_LC/D12 AS '' ''';
-SET &GRAND_TOTAL_USD='TOTAL_SELLPRICE_LINE_USD/D12 AS ''  ''';
-SET &GRAND_TOTAL_EUR='TOTAL_SELLPRICE_LINE_EUR/D12 AS '' ''';
-SET &GRAND_TOTAL_AUD='TOTAL_SELLPRICE_LINE_AUD/D12 AS '' ''';

-SET &FIELD1= IF (&CURRENCY EQ 'LC' AND &PRICE_TYPE EQ 'PROJ') THEN &TOTAL_PROJ_LC ELSE
-             IF (&CURRENCY EQ 'USD' AND &PRICE_TYPE EQ 'PROJ') THEN &TOTAL_PROJ_USD ELSE
-             IF (&CURRENCY EQ 'EUR' AND &PRICE_TYPE EQ 'PROJ') THEN &TOTAL_PROJ_EUR ELSE
-             IF (&CURRENCY EQ 'AUD' AND &PRICE_TYPE EQ 'PROJ') THEN &TOTAL_PROJ_AUD ELSE
-             IF (&CURRENCY EQ 'LC' AND &PRICE_TYPE EQ 'MAINT') THEN &TOTAL_MAINT_LC ELSE
-             IF (&CURRENCY EQ 'USD' AND &PRICE_TYPE EQ 'MAINT') THEN &TOTAL_MAINT_USD ELSE
-             IF (&CURRENCY EQ 'EUR' AND &PRICE_TYPE EQ 'MAINT') THEN &TOTAL_MAINT_EUR ELSE
-             IF (&CURRENCY EQ 'AUD' AND &PRICE_TYPE EQ 'MAINT') THEN &TOTAL_MAINT_AUD ELSE
-             IF (&CURRENCY EQ 'LC' AND &PRICE_TYPE EQ 'TOTAL') THEN &GRAND_TOTAL_LC ELSE
-             IF (&CURRENCY EQ 'USD' AND &PRICE_TYPE EQ 'TOTAL') THEN &GRAND_TOTAL_USD ELSE
-             IF (&CURRENCY EQ 'EUR' AND &PRICE_TYPE EQ 'TOTAL') THEN &GRAND_TOTAL_EUR ELSE
-             IF (&CURRENCY EQ 'AUD' AND &PRICE_TYPE EQ 'TOTAL') THEN &GRAND_TOTAL_AUD ELSE '';

-SET &COL1TITLE= IF &CURRENCY EQ 'EUR'   THEN '(EUR)' ELSE
-                IF &CURRENCY EQ 'USD' THEN '(USD)'   ELSE
-				 IF &CURRENCY EQ 'AUD' THEN '(AUD)'   ELSE ' ';

DEFINE FILE HYP_PIPELINE
CLOSEDATE_Q/Q=HYP_PIPELINE.HYP_PIPELINE.CLOSEDATE_YEAR_Q;
CLOSEDATE_M/Mt=HYP_PIPELINE.HYP_PIPELINE.CLOSEDATE_YEAR_M;
END

TABLE FILE HYP_PIPELINE
SUM HYP_PIPELINE.HYP_PIPELINE.&FIELD1
BY HYP_PIPELINE.HYP_PIPELINE.PRODUCTPARENT AS 'Product Parent'
BY HYP_PIPELINE.HYP_PIPELINE.PRODUCTFAMILY
BY HYP_PIPELINE.HYP_PIPELINE.PRODUCTCLASS
BY LOWEST PRODUCTPARENT NOPRINT
HEADING
"Open Bookings By Year &COL1TITLE"
ACROSS HYP_PIPELINE.HYP_PIPELINE.CLOSEDATE_YEAR_Y AS ''
ACROSS CLOSEDATE_Q AS ''
ACROSS HYP_PIPELINE.HYP_PIPELINE.CLOSEDATE_M  NOPRINT
ACROSS HYP_PIPELINE.HYP_PIPELINE.CLOSEDATE_M AS ''
WHERE HYP_PIPELINE.HYP_PIPELINE.BUSINESS_UNIT_MANAGER_COMMIT EQ &BUSINESS_UNIT_MANAGER_COMMIT.(FIND HYP_PIPELINE.HYP_PIPELINE.BUSINESS_UNIT_MANAGER_COMMIT IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A3).BUSINESS_UNIT_MANAGER_COMMIT:.QUOTEDSTRING;
WHERE HYP_PIPELINE.HYP_PIPELINE.OPTIONAL EQ &OPTIONAL.(FIND HYP_PIPELINE.HYP_PIPELINE.OPTIONAL IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A5).OPTIONAL:.QUOTEDSTRING;
WHERE HYP_PIPELINE.HYP_PIPELINE.PRODUCTPARENT EQ &PRODUCTPARENT.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.PRODUCTPARENT IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A50V)).Product Parent.;
WHERE HYP_PIPELINE.HYP_PIPELINE.PRODUCTFAMILY EQ &PRODUCTFAMILY.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.PRODUCTFAMILY IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A50V)).Product Family.;
WHERE HYP_PIPELINE.HYP_PIPELINE.PRODUCTCLASS EQ &PRODUCTCLASS.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.PRODUCTCLASS IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A50V)).Product Class.;
WHERE HYP_PIPELINE.HYP_PIPELINE.OPPORTUNITY_TYPE__C EQ &OPPORTUNITY_TYPE__C.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.OPPORTUNITY_TYPE__C IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A255V)).OPPORTUNITY_TYPE__C:.;
WHERE HYP_PIPELINE.HYP_PIPELINE.PRODUCT_REVENUE_TYPE EQ &PRODUCT_REVENUE_TYPE.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.PRODUCT_REVENUE_TYPE IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A20)).PRODUCT_REVENUE_TYPE:.;
WHERE HYP_PIPELINE.HYP_PIPELINE.SIBI_REGION_BIN EQ &SIBI_REGION_BIN.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.SIBI_REGION_BIN IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A20)).SIBI_REGION_BIN:.;
WHERE HYP_PIPELINE.HYP_PIPELINE.INTERGRAPHOFFICE_QUOTE EQ &INTERGRAPHOFFICE_QUOTE.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.INTERGRAPHOFFICE_QUOTE IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A50)).Intergraph Office:.;
WHERE HYP_PIPELINE.HYP_PIPELINE.GBL_BUSUNIT EQ &GBL_BUSUNIT.(OR(FIND HYP_PIPELINE.HYP_PIPELINE.GBL_BUSUNIT IN si_bi/hyp_tables/HYP_PIPELINE |FORMAT=A50)).GBL_BUSUNIT:.;
WHERE HYP_PIPELINE.HYP_PIPELINE.CLOSEDATE GE &START_CLOSEDATE.(|FORMAT=YYMD).START_CLOSEDATE:.QUOTEDSTRING;
WHERE HYP_PIPELINE.HYP_PIPELINE.CLOSEDATE LE &END_CLOSEDATE.(|FORMAT=YYMD).END_CLOSEDATE:.QUOTEDSTRING;
WHERE HYP_PIPELINE.HYP_PIPELINE.PrimaryQuote EQ 1;
WHERE HYP_PIPELINE.HYP_PIPELINE.OPENOPPS EQ 1;
WHERE HYP_PIPELINE.HYP_PIPELINE.OPTIONAL EQ &CURRENCY.(<USD,USD>, <EUR,EUR>, <AUD,AUD> |FORMAT=A5).CURRENCY:.QUOTEDSTRING OR (1 EQ 1);
WHERE HYP_PIPELINE.HYP_PIPELINE.OPTIONAL EQ &PRICE_TYPE.(<PROJ,PROJ>, <MAINT,MAINT>, <'TOTAL','TOTAL'> |FORMAT=A5).PRICE_TYPE:.QUOTEDSTRING OR (1 EQ 1);
ON TABLE RECOMPUTE
ON TABLE SET CACHELINES 100
ON TABLE SET SUBTOTALS ABOVE
ON TABLE SET GRWIDTH 1
ON TABLE SET ASNAMES ON
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLENCODE ON
ON TABLE SET EMPTYREPORT ON
ON TABLE SET HTMLCSS ON
-*ON TABLE SET AUTOFIT ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING,  ARGRAPHENGINE=JSCHART,  SUMMARY=&WF_SUMMARY.QUOTEDSTRING, HFREEZE=ON, LINES-PER-PAGE=UNLIMITED, PAGESIZE=SCREEN, CALC-LOCATION=TOP, WINDOW-DISPLAY=CASCADE, $
TYPE=DATA, COLUMN=CUST_NAME, SQUEEZE=0.75,$
TYPE=DATA, COLUMN=NAME, SQUEEZE=0.75,$
TYPE = HEADING, JUSTIFY = LEFT, $
TYPE=TITLE, FONT='ARIAL', BACKCOLOR=RGB(0 151 189), $
TYPE=DATA, FONT='ARIAL',$
TYPE=DATA,
     COLUMN=N1,
     DRILLMENUITEM='Export To Excel',
	 FOCEXEC=IBFS:/WFC/Repository/CPQ_82/Charts/ProductHeirarchy/Excel_Detail.fex(									START_CLOSEDATE=&START_CLOSEDATE.QUOTEDSTRING
																													END_CLOSEDATE=&END_CLOSEDATE.QUOTEDSTRING
																													SIBI_REGION_BIN=&SIBI_REGION_BIN.QUOTEDSTRING
																													INTERGRAPHOFFICE_QUOTE=&INTERGRAPHOFFICE_QUOTE.QUOTEDSTRING
																													GBL_BUSUNIT=&GBL_BUSUNIT.QUOTEDSTRING
																													PRODUCT_REVENUE_TYPE=&PRODUCT_REVENUE_TYPE.QUOTEDSTRING
																													OPPORTUNITY_TYPE__C=&OPPORTUNITY_TYPE__C.QUOTEDSTRING
																													BUSINESS_UNIT_MANAGER_COMMIT=&BUSINESS_UNIT_MANAGER_COMMIT.QUOTEDSTRING
																													CURRENCY=&CURRENCY.QUOTEDSTRING
																													PRICE_TYPE=&PRICE_TYPE.QUOTEDSTRING
																													OPTIONAL=&OPTIONAL.QUOTEDSTRING
																													PRODUCTCLASS=&PRODUCTCLASS.QUOTEDSTRING
																													PRODUCTFAMILY=&PRODUCTFAMILY.QUOTEDSTRING
																													PRODUCTPARENT=&PRODUCTPARENT.QUOTEDSTRING
),
          TARGET='_blank',
$
TYPE=REPORT,
     LINES-PER-PAGE=UNLIMITED,
     CALC-LOCATION=TOP,
$
TYPE=ACROSSVALUE,
          FOCEXEC=IBFS:/WFC/Repository/CPQ_82/Charts/ProductHeirarchy/BOOKINGS_DRILL.fex( 								START_CLOSEDATE=&START_CLOSEDATE.QUOTEDSTRING
																													END_CLOSEDATE=&END_CLOSEDATE.QUOTEDSTRING
																													SIBI_REGION_BIN=&SIBI_REGION_BIN.QUOTEDSTRING
																													INTERGRAPHOFFICE_QUOTE=&INTERGRAPHOFFICE_QUOTE.QUOTEDSTRING
																													GBL_BUSUNIT=&GBL_BUSUNIT.QUOTEDSTRING
																													PRODUCT_REVENUE_TYPE=&PRODUCT_REVENUE_TYPE.QUOTEDSTRING
																													OPPORTUNITY_TYPE__C=&OPPORTUNITY_TYPE__C.QUOTEDSTRING
																													BUSINESS_UNIT_MANAGER_COMMIT=&BUSINESS_UNIT_MANAGER_COMMIT.QUOTEDSTRING
																													CURRENCY=&CURRENCY.QUOTEDSTRING
																													PRICE_TYPE=&PRICE_TYPE.QUOTEDSTRING
																													OPTIONAL=&OPTIONAL.QUOTEDSTRING
																													PRODUCTCLASS=&PRODUCTCLASS.QUOTEDSTRING
																													PRODUCTFAMILY=&PRODUCTFAMILY.QUOTEDSTRING
																													PRODUCTPARENT=&PRODUCTPARENT.QUOTEDSTRING

     ),
          TARGET='_self',
$
TYPE=ACROSSVALUE,
     ACROSS=2,
          FOCEXEC=IBFS:/WFC/Repository/CPQ_82/Charts/ProductHeirarchy/Bookings_Quarter.fex( 	SIBI_REGION_BIN=&SIBI_REGION_BIN.QUOTEDSTRING
																						INTERGRAPHOFFICE_QUOTE=&INTERGRAPHOFFICE_QUOTE.QUOTEDSTRING
																						GBL_BUSUNIT=&GBL_BUSUNIT.QUOTEDSTRING
																						PRODUCT_REVENUE_TYPE=&PRODUCT_REVENUE_TYPE.QUOTEDSTRING
																						OPPORTUNITY_TYPE__C=&OPPORTUNITY_TYPE__C.QUOTEDSTRING
																						BUSINESS_UNIT_MANAGER_COMMIT=&BUSINESS_UNIT_MANAGER_COMMIT.QUOTEDSTRING
																						START_CLOSEDATE=&START_CLOSEDATE.QUOTEDSTRING
																						END_CLOSEDATE=&END_CLOSEDATE.QUOTEDSTRING
																						CURRENCY=&CURRENCY.QUOTEDSTRING
																						PRICE_TYPE=&PRICE_TYPE.QUOTEDSTRING
																						OPTIONAL=&OPTIONAL.QUOTEDSTRING
																						PRODUCTCLASS=&PRODUCTCLASS.QUOTEDSTRING
																						PRODUCTFAMILY=&PRODUCTFAMILY.QUOTEDSTRING
																						PRODUCTPARENT=&PRODUCTPARENT.QUOTEDSTRING
     ),
          TARGET='_self',
$
TYPE=REPORT,
     OBJECT=STATUS-AREA,
     JUSTIFY=LEFT,
     PAGE-LOCATION=BOTTOM,
	 $
ENDSTYLE
END

-RUN

-HTMLFORM BEGIN
<style>
a:link 	 {text-decoration:none;color:Black;background:none;outline:none;}
a:visited {text-decoration:none;color:Black;background:none;outline: none;}
a:active  {text-decoration:none;color:Black;background:none;outline: none;}
a:hover   {text-decoration:underline;color:Black;background:none;outline: none;}
  DIV.clsMDMenu {
    background-color: #0097bd !important;
	snap:false;
  }
  DIV.clsMDMenu A span {
    font-family: arial !important;
    font-size: 10pt !important;
    text-decoration: none !important;
    color: #ffffff !important;
    font-weight: normal !important;
  }
  DIV.clsMDMenu A:hover span {
    font-family: arial !important;
    text-decoration: underline !important;
    color: #ffffff !important;
    font-weight: bold !important;
  }
</style>
-HTMLFORM END
 

This message has been edited. Last edited by: FP Mod Chuck,

In 8.2xx under Admin Console/Application Settings/Parameter Prompting there's setting for Null Behavior. What is it set to and is it the same in the environment where your code is working?

Also, what do you see when you look at the echo of your code? That'll show how the parameters are being resolved.

Thank you for your reponse, Null Behavior is set to '_FOC_NULL' in both environments.

Here's how the parameters are set in dev:
START_CLOSEDATE='20180101'
END_CLOSEDATE='20181231'
SIBI_REGION_BIN='''''_FOC_NULL'''''
INTERGRAPHOFFICE_QUOTE='''''_FOC_NULL'''''
GBL_BUSUNIT='''''_FOC_NULL'''''
PRODUCT_REVENUE_TYPE='''''_FOC_NULL'''''
OPPORTUNITY_TYPE__C='''''_FOC_NULL'''''
BUSINESS_UNIT_MANAGER_COMMIT='_FOC_NULL'
CURRENCY='USD'
PRICE_TYPE='TOTAL'
OPTIONAL='_FOC_NULL'
PRODUCTCLASS='''''_FOC_NULL'''''
PRODUCTFAMILY='''''_FOC_NULL'''''
PRODUCTPARENT='''''_FOC_NULL'''''


Here's how they're set in prod:
INTERGRAPHOFFICE_QUOTE='_FOC_NULL'
GBL_BUSUNIT='_FOC_NULL'
PRODUCT_REVENUE_TYPE='_FOC_NULL'
OPPORTUNITY_TYPE__C='_FOC_NULL'
BUSINESS_UNIT_MANAGER_COMMIT='_FOC_NULL'
START_CLOSEDATE='20180101'
END_CLOSEDATE='20181231'
CURRENCY='USD'
PRICE_TYPE='TOTAL'
OPTIONAL='_FOC_NULL'

It's definitely adding more '' to the parameters for some reason? Not sure why?

Alright, I take that back.. So it's not a setting that's different between dev and prod. We moved it to prod and it's still adding '' to the parameters. Is it a setting/property on the report? I'm confused as to why it's adding them..

Perhaps your HTML is set to add extra quotes?
Or are you assigning values to your parameters using .QUOTEDSTRING as well, resulting in applying it double?

It would help if you could supply a copy of &ECHO=ON output, so we can see what the offending lines contain after all the amper vars have been resolved.

If you have trouble grabbing it, try (temporarily) adding
-SET &STACK=OFF;
at the top.