Row Grand Totals not working

[Greg Moore]

I'm having trouble getting row totals to work. Column totals work fine. I've tried every property that is commented out below.

Any idea what I'm doing wrong? Below is an abbreviated version of the code.

$('#pivotGrid')

.ejPivotGrid({

//enableColumnGrandTotal: true,

//enableRowGrandTotal: true,

enableGrandTotal: true,

//showSubTotal: true,

beforeServiceInvoke: function (args) {

var grid = $('#budgetSummaryPivotGrid').data("ejPivotGrid");

grid.model.customObject = ...;

grid.model.customObject.budgetSummaryGridLevel = ...;

grid.model.customObject.drillPath = ...;

},

afterServiceInvoke: ...,

layout: ej.PivotGrid.Layout.Normal,

renderSuccess: ...

});

[Scintilla Arul Jothi Raj]

Hi Greg, 

Thanks for contacting Syncfusion support. 

We have checked the reported problem and it has not been reproduced at our end. Please find the screenshots below for your reference. 

Screen Shot: 

Relational:

Olap:

 

Meanwhile, we think that you are using Relational Server Mode, in which the reported problem may occur due to property ShowSubTotal = false has been provided with the bonded report in(For example, please refer the below-highlighted sample code snippet). This property hides the row/column sub-totals of the respective report field(Example: Product). So, we request you to kindly check the below-highlighted property has not been given with the bounded reported at Service/WebAPI Controller. 

Sample code : 

private PivotReport BindDefaultData() 

        { 

            PivotReport pivotSetting = new PivotReport(); 

            pivotSetting.PivotRows.Add(new PivotItem { FieldMappingName = "Product", FieldHeader = "Product", TotalHeader = "Total", ShowSubTotal = false }); 

            pivotSetting.PivotRows.Add(new PivotItem { FieldMappingName = "Date", FieldHeader = "Date", TotalHeader = "Total" }); 

            pivotSetting.PivotColumns.Add(new PivotItem { FieldMappingName = "Country", FieldHeader = "Country", TotalHeader = "Total", ShowSubTotal = false  }); 

            pivotSetting.PivotCalculations.Add(new PivotComputationInfo { CalculationName = "Amount", Description = "Amount", FieldHeader = "Amount", FieldName = "Amount", Format = "C", SummaryType = Syncfusion.PivotAnalysis.Base.SummaryType.DoubleTotalSum }); 

            return pivotSetting; 

        } 

We have prepared a prototype sample with above information. Please find the sample link below. 

Sample: http://www.syncfusion.com/downloads/support/forum/132765/ze/PivotGridDemo1506077776 

Regards, 

Scintilla A 

[Greg Moore]

We are using an OlapReport. I see you're using a PivotReport. Is that the issue?

ShowSubTotal has not been set to false anywhere in the application.

[Greg Moore]

Actually I see in your sample code that you are using an OlapReport so that's probably not it.

I am using ej.web.all.min.js version 12.3.

[Greg Moore]

I updated to pull in ej.web.all.min.js from the CDN for version 15.3.0.29 and am still getting the same result with no row totals.

[Greg Moore]

Here's one difference I see in our code. I do not do anything like line 231. If I comment that out in your report, the row totals are not on the far right of the grid and must be drilled down to be accessed.

We use some custom drill path code which I think may be the culprit.

[Greg Moore]

I added Year to the CategoricalElements and still doesn't add row sub totals on the far right.

I have enableGrandTotal set to true and enableGroupingBar set to false as we do custom drill down logic.

[Manikandan Murugesan]

Hi Greg, 

Thanks for your response. 

Please find the response below. 

S.No	Query	Comments
1.	Here's one difference I see in our code. I do not do anything like line 231. If I comment that out in your report, the row totals are not on the far right of the grid and must be drilled down to be accessed.	This is the default behavior of PivotGrid control. PivotGrid don’t have separate Grand Total column, if the column has only measure field without any dimension field. Here measures column act as a Grand Total.
2.	added Year to the CategoricalElements and still doesn't add row sub totals on the far right.  I have enableGrandTotal set to true and enableGroupingBar set to false as we do custom drill down logic.	We are unable to reproduce the reported problem at our end and also checked with the same scenario by binding the drilled report. Please refer below documentation link for binding drilled report.    https://help.syncfusion.com/aspnet-classic/olapcommon/olapreport    We suspect that, this problem may be occurred due to your custom drill down logic. So, please send your prototype sample to us that replicate the reported problem (Or) kindly reproduce the reported problem in the provided sample (Posted by Last Update) and send back to us? This will help us to proceed further.

 

Please let us know, if you have any queries. 

Thanks, 

Manikandan.

[Greg Moore]

"This is the default behavior of PivotGrid control. PivotGrid don’t have separate Grand Total column, if the column has only measure field without any dimension field. Here measures column act as a Grand Total. "

That was precisely what I needed, thank you!

Once I moved the dimension to the columns every thing worked as expected.

[Manikandan Murugesan]

Hi Greg, 

Thanks for your response. 

Please let us know if you have any queries. 

 

Thanks, 

Manikandan. 

[Greg Moore]

One last question for you on this.

Should calculated members work with row totaling?

When I add a member which is a calculation in the cube as a measure element, the data will show properly in the grid but sub totals will not work.

Quantity (below) is the only true measure. The others are calculated members. "Quantity difference" only spits out "Quantity" where it should be outputting the difference between Quantity and "Quantity - prior year".

If I switch it to a calculated member, I get the same result with no row totals.

[Sastha Prathap Selvamoorthy]

Hi Greg   

  

Thanks for the screenshot.   

  

We have checked the reported problem by creating the calculated measures at cube level and it has not been reproduced at our end. Please find the screen shot below for your reference. Hence, we would request to provide us the dummy cube along with OLAP report, which would helpful for us to proceed further.   

   

Screen shot:   

  
  

Additionally, calculated measures can be created at OLAP report level and please find the online document to create calculated measures in report level. You could check the same scenario by creating calculated measure on report level and let us know the details.   

  

Online Document: https://help.syncfusion.com/aspnetmvc-classic/olapcommon/olapreport#calculated-measure   

   

Regards,   

Sastha Prathap S. 

[Greg Moore]

Do you happen to have the code for the sample project showing the row totals working with those calculated measures?

That would help if you could attach a zip example.

It would be too challenging for me to dummy down our project as it is highly complex at this point.

[Sastha Prathap Selvamoorthy]

Hi Greg, 

We have prepared a prototype sample for binding the calculated measures at OLAP report level. Please find the sample in the below link. 

 

Sample: http://www.syncfusion.com/downloads/support/forum/132765/ze/PivotGridDemo-985424643  

 

Please let us know if you have any concern. 

 

Regards, 

Sastha Prathap S. 

[Greg Moore]

I've figured out the issue.

The problem is this calculation. We simply have a calculated measure in the cube with this

"(PARALLELPERIOD([Transaction Date].[Calendar Hierarchy-Month].[Fiscal Year], 1,[Transaction Date].[Calendar Hierarchy-Month].CurrentMember),[Measures].[Quantity])"

It will calculate just fine for everything but the totals. I've modified your demo solution and attached it.

Just change the connection string and cube name back.

What's interesting is if instead of the Parallel period expression I just add 1000 to the quantity, subtotals sort of work (see below). The total is actually off though.

Are there too many characters in the parallel period calculation for Syncfusion to handle the row totals?

What do you recommend for this?

Attachment: PivotGridDemo985424643_68540004.zip

[Manikandan Murugesan]

Hi Greg,   

  

Thanks for the response.   

  

We have analyzed your scenario; we suspect that the total values are not returned for the MDX query while using ParralelPeriod expression in SQL Server Analysis Services itself. Hence, we are displaying the result which was returned from ADOMD cell set. So, this problem is not relevant to control and please refer below MDX query and result returned from SQL Server Analysis Services.   

  

Query: 

WITH  MEMBER [Measures].[Quantity - Prior Year] As  (PARALLELPERIOD([Date].[Fiscal Year].[Fiscal Year], 2,[Date].[Fiscal Year].CurrentMember),[Measures].[Order Quantity])   

SELECT   NONEMPTY( VISUALTOTALS( ({{Drilldownlevel({ [Date].[Fiscal] },,,INCLUDE_CALC_MEMBERS)}}) ),{[Measures].[Order Quantity], [Measures].[Quantity - Prior Year]})  dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME  ON COLUMNS ,   

NONEMPTY({[Measures].[Order Quantity], [Measures].[Quantity - Prior Year]}, VISUALTOTALS( ({{Drilldownlevel({ [Date].[Fiscal] },,,INCLUDE_CALC_MEMBERS)}}) ))  dimension properties MEMBER_TYPE, PARENT_UNIQUE_NAME  ON ROWS   

FROM [Adventure Works]  CELL PROPERTIES VALUE, FORMAT_STRING, FORMATTED_VALUE 

 

Result: 

 

 

Please let us know if you need any other assistance.  

Regards, 

Manikandan.