I have an OLAP report that generally runs without errors, but in a certain situation, I am receiving a (FOC153) VERB OBJECTS TIMES RETRIEVED COLUMNS EXCEEDS MAXIMUM. I have seen posts on this problem, and based on what I have read, I am not sure why I am getting this error.

My understanding is the limit is based on the number of verb objects * number of ACROSS values. In my case, I have a report with 13 verb objects (I am including BY fields - do they count as verb objects?) Initially, the report has no ACROSS values, but it summarizes by week. The report allows the week column to be moved to the top of the report as an ACROSS, and when I have a full 52-week year I am reporting on, I receive the FOC153 error when week is moved as an ACROSS.

I am running WF 7.6.8 on a Unix platform. The latest I have seen on this topic is that the limit is 1056 for the number of verb objects * ACROSS values. In my case, I have 12 verb objects (since one of the verb objects moves to an ACROSS) with 52 ACROSS values, which totals to 624. This is well below the limit of 1056.

Is there something I am missing in my calculations? In addition, I would like to have better control over this, and was wondering if there were some ideas of how I might do this.

I am fairly new to OLAP reports on WebFOCUS.

Thanks!

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

Can you please post the code of the fex you're running?
It may be that there are so-called hidden verb objects. These exist if you use a reformat, a noprint, a compute, or in some cases a define to present the verb object in the desired manner. For instance if you specify SUM SALES/D12.5 two fields will be created, SALES with the original format and SALES with the new format. To be able to see if your request has something like that, we need to be able to look at your code.

Here is the code - hopefully it helps. I had to make it as generic as I could due to confidentiality issues. As a note, two of the BY fields and the ACROSS field are user-provided via an HTML page. They are always OLAP dimension fields.

 
JOIN CLEAR *
JOIN LEFT_OUTER KEY IN DATA1 TO KEY IN DATA2 AS J1
JOIN LEFT_OUTER KEY IN DATA1 TO KEY IN DATA3 AS J2
JOIN STORE IN DATA1 TO STORE IN DATA3 AS J3

DEFINE FILE DATA1
CALC1/D11 = IF STORE_COUNT EQ 0 THEN 0 ELSE NUMHRS1 ;
CALC2/D11 = IF STORE_COUNT EQ 0 THEN 0 ELSE NUMHRS2 ;
CALC3/D7 = IF SS IS 'Y' THEN 0 ELSE VARIANCE ;
FISC_PERIOD_I2/I2 = FISC_PERIOD;
END

OLAP DIMENSIONS
Time: FISC_YEAR, FISC_PERIOD, FISC_WEEK;
Stores: COMPANY_NAME, REGION_NAME, DIVISION_NAME, AREA_NAME, DISTRICT_NAME, STORE_NAME;
END

TABLE FILE ODSDATA
SUM
    STORE_COUNT 
    COMPUTE NS_AMT/D11M     = NET_SALES / STORE_COUNT ; 
    COMPUTE TRANS_CNT/D11   = TRANSACTION_CNT / STORE_COUNT ;
    COMPUTE FC_PCT/P11.1%   = (SUM.FC_AMT / SUM.GS_AMT) * 100; 
    COMPUTE FC_PCT2/P11.1%  = (SUM.FC2_AMT / SUM.GS_AMT) * 100; 
    COMPUTE FC_PCT3/P11.1%  = FC_PCT - FC_PCT2 ;
    COMPUTE HRS1/D11        = CALC1 / STORE_COUNT ;
    COMPUTE HRS2/D11        = CALC2 / STORE_COUNT ;
    COMPUTE VAR_AMT/P11.1   = CALC3 / STORE_COUNT ;
    COMPUTE PUW/D11         = (PUW_TRANS - TOTAL_CARS ) / STORE_COUNT ;

BY &SORT1
BY &SORT2
BY FISC_WEEK

ACROSS &ACROSS1

ON TABLE SET PAGE-NUM OFF
ON TABLE SET POPUPDESC ON
ON TABLE SET EMPTYREPORT OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLCSS ON
ON TABLE SET AUTODRILL ON
ON TABLE SET OLAPPANE TABBED
ON TABLE SET OLAPDRAGDROP ON

END

Based on what GamP described and the number of computed fields in your sample code and the fact that you have an ACROSS, perhaps

SET HOLDLIST = PRINTONLY

will help.

quote:

The HOLDLIST parameter controls whether only displayed fields or all fields are included in the HOLD or PCHOLD file.

The syntax is:

SET HOLDLIST = {PRINTONLY|ALL}
where:

PRINTONLY
Includes only those fields in the HOLD or PCHOLD file that are specified in the report request.

ALL
Includes all fields referenced in a request in the HOLD or PCHOLD file, including both computed fields and fields referenced in a COMPUTE command. ALL is the default value. (OLD may be used as a synonym for ALL.)

Note: Vertical sort (BY) fields specified in the request with the NOPRINT option are not included in the HOLD file, even with SET HOLDLIST=ALL.

Francis, thanks for the reply. Unfortunately, adding the SET HOLDLIST = PRINTONLY did not work. I am still getting the FOC153. Any other thoughts on this? I am still wondering why I am even getting this error, since it seems to me that the number of verb objects * ACROSS values is still under the limit of 1056.

Process of elimination: Try changing the ACROSS to BY to see if that makes it work. If it does work, then the number of ACROSS values may be the problem. For testing purposes, try adding a WHERE to limit the number of ACROSS values, increasing the numbers until the error occurs.

SUM
    STORE_COUNT 
    COMPUTE NS_AMT/D11M     = NET_SALES / STORE_COUNT ; 
    COMPUTE TRANS_CNT/D11   = TRANSACTION_CNT / STORE_COUNT ;
    COMPUTE FC_PCT/P11.1%   = (SUM.FC_AMT / SUM.GS_AMT) * 100; 
    COMPUTE FC_PCT2/P11.1%  = (SUM.FC2_AMT / SUM.GS_AMT) * 100; 
    COMPUTE FC_PCT3/P11.1%  = FC_PCT - FC_PCT2 ;
    COMPUTE HRS1/D11        = CALC1 / STORE_COUNT ;
    COMPUTE HRS2/D11        = CALC2 / STORE_COUNT ;
    COMPUTE VAR_AMT/P11.1   = CALC3 / STORE_COUNT ;
    COMPUTE PUW/D11         = (PUW_TRANS - TOTAL_CARS ) / STORE_COUNT ;

This amounts to at least 25 fields that are absolutely needed to build your report. This includes the SUMmed db-field STORE_COUNT, all computes, and all fields needed to compute the computed fields. For 52 weeks, this amounts to 1300 fields in total in your internal matrix, which far exceeds the limit.
Francis' suggestion to use SET HOLDLIST PRINTONLY works, but only if you prepare a holdfile before doing the olap request. Try to create the holdfile, and then do the OLAP stuff on the holdfile:

....
TABLE FILE ODSDATA
SUM
    STORE_COUNT 
    COMPUTE NS_AMT/D11M     = NET_SALES / STORE_COUNT ; 
    COMPUTE TRANS_CNT/D11   = TRANSACTION_CNT / STORE_COUNT ;
    COMPUTE FC_PCT/P11.1%   = (SUM.FC_AMT / SUM.GS_AMT) * 100; 
    COMPUTE FC_PCT2/P11.1%  = (SUM.FC2_AMT / SUM.GS_AMT) * 100; 
    COMPUTE FC_PCT3/P11.1%  = FC_PCT - FC_PCT2 ;
    COMPUTE HRS1/D11        = CALC1 / STORE_COUNT ;
    COMPUTE HRS2/D11        = CALC2 / STORE_COUNT ;
    COMPUTE VAR_AMT/P11.1   = CALC3 / STORE_COUNT ;
    COMPUTE PUW/D11         = (PUW_TRANS - TOTAL_CARS ) / STORE_COUNT ;

BY &SORT1
BY &SORT2
BY FISC_WEEK

ACROSS &ACROSS1

ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS INTERMED
END

TABLE FILE INTERMED
SUM
    STORE_COUNT 
    NS_AMT
    TRANS_CNT
    FC_PCT 
    FC_PCT2 
    FC_PCT3
    HRS1
    HRS2
    VAR_AMT
    PUW

BY &SORT1
BY &SORT2
BY FISC_WEEK

ACROSS &ACROSS1
...

Hope this helps and gets you on your way ...

GamP, your suggestion did the trick. I made the changes to my report, and was able to successfully do an ACROSS with a full year's worth of weeks.

I appreciate all the suggestions and the education!

I have done some testing using the suggested technique, and another issue has arisen with the approach. When I initially run the report, everything looks fine. However, when I drill down to the next level, I am not getting all the values I need to see. For example, in my code example, &SORT1 may be the REGION_NAME value in the Stores dimension. When I click on it to get to the next level, I should see all values of DIVISION_NAME, but only get one.

I believe the reason is that when I build the hold file now, I SUM at the REGION_NAME level and I lose the detail at the DIVISION_NAME level. Is there a way for me to know in my code which level in the OLAP DIMENSION hierarchy I am at? If I knew that, I think I could fix this.

Does this make sense? I am struggling with the OLAP reports - I think I am trying to do some things that I may not be able to do with OLAP, but I am not sure.

Any ideas out there?