[CASE OPEN] ENGINE SSAS SET ROLLUP_BY_VISUALTOTALS

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,

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

MartinY

Virtuoso

posted

Hide Post

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.

WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007

Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013

Ignored post by MartinY posted

Show Post

Nicholas Spyrison

Gold member

posted

Hide Post

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?

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted

Show Post

Francis Mariani

Expert

posted

Hide Post

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?

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Nicholas Spyrison

Gold member

posted

Hide Post

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?

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted

Show Post

Nicholas Spyrison

Gold member

posted

Hide Post

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

Maybe my issue isn't related to ROLLUP_BY_VISUALTOTALS ?

_____
WF 8.1.04
Win 7// Windows Server 2012 R2
SASS OLAP Cube

Posts: 92 | Registered: July 31, 2015

Ignored post by Nicholas Spyrison posted