In a cube I have:

TABLE FILE DM
SUM 
     COMPUTE Actual_MTD/D12.2 = IF Date_Date_Calculation EQ 'MTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_MTD_LY/D12.2 = IF Date_Date_Calculation EQ 'MTD LY' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_QTD/D12.2 = IF Date_Date_Calculation EQ 'QTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_QTD_LY/D12.2 = IF Date_Date_Calculation EQ 'QTD LY' THEN Ledger_Amount ELSE 0;
     COMPUTE ORDER/I5 = 1 + LAST ORDER;
BY  GL_Account_Finance_Tree_name HIERARCHY
BY  Date_Calendar_Month

But instead of SUMming the 0's into 1 line. the Cube treats it as PRINT or perhaps force authoring BY Date_Date_Calculation NOPRINT.

basically I get what I'd expect from

TABLE FILE DM
SUM 
     COMPUTE Actual_MTD/D12.2 = IF Date_Date_Calculation EQ 'MTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_MTD_LY/D12.2 = IF Date_Date_Calculation EQ 'MTD LY' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_QTD/D12.2 = IF Date_Date_Calculation EQ 'QTD' THEN Ledger_Amount ELSE 0;
     COMPUTE Actual_QTD_LY/D12.2 = IF Date_Date_Calculation EQ 'QTD LY' THEN Ledger_Amount ELSE 0;
     COMPUTE ORDER/I5 = 1 + LAST ORDER;
BY  GL_Account_Finance_Tree_name HIERARCHY
BY  Date_Calendar_Month
[B]BY Date_Date_Calulation NOPRINT[/B]

___
I think this issues can be addressed with ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS {ON|OFF}. Yet when toggleing between On and Off I see no difference.

Am I misusing ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS?
Am I going down the right path?


ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS Documentation:
http://infocenter.informationb...ource%2Ftopic495.htm

This message has been edited. Last edited by: Nicholas Spyrison,

Nicholas,

Pay also attention to the fact that you are using COMPUTEd fields which are different than DEFINEd and may (and most of the time do) produce different result depending on your BY/WHERE fields.

A COMPUTE is applied/resolved only once the data is extracted and "pre-aggregated" based on BY/WHERE fields when a DEFINE is applied/resolved to every single read line.

The fact that you need to include another hidden (NOPRINT) BY field to have to result that you expect, let me think that maybe they should be DEFINEd fields instead of COMPUTEd.

I understand the difference between define/compute. OLAP data has trouble doing defines on maesures, so in this case, I am forced to use COMPUTE. we are on different pages though;

I can't get get my expected output; that is one line Summed up on my by fields;
A B C D
1 2 3 4

but rather, because of the olap cube data set it displays as if there was a NOPRINT BY field;
A B C D
1 0 0 0
0 2 0 0
0 0 3 0
0 0 0 4
of course the field it is spiting on isn't in the BY statement at all. This isn't caused by an across field as there are none.


I am under the impression that ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS is the way to fix this, but have not been able to get it to work.


Does anyone else have experience with this?

I feel bad that we can't help with your SSAS issues.

Usually, these "ENGINE" statements can be coded in the fex that runs the report, but I don't see any mention of that in the manual link you provided - it appears that these settings must be in edasprof.prf. Is that where you're setting them?

I was only attempting at the fex level with no change of output (but also no run errors).

I'll look into edasprof.prf and at the master file level.

If I change the setting in edasprof.prf, ie) the adapter the new settings will only be applied to synonyms processed after the change, correct?

applied the change directly to the synonym. but still did not see any change.

Maybe my issue isn't related to ROLLUP_BY_VISUALTOTALS ?