We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Group by a value and then sum up

Hi ,

I tried to build a pivot where the sum for "Anzahl" and "Zeit in h" should be calculated depending on the value in the left row.
Each value in the left has an unique value for Anzahl an Zeit in h.

As you see in the attached screenshot the sums of these values are the same for all values.
Is it possible to build a pivot like the one I wanted or do I have to use another widget?


Thanks.

Regards,
Patrick


4 Replies

GK Gregory Kohle March 14, 2017 01:34 PM UTC

Hi,

I tried to build this via a grid.
The designer shows the grid correctly. When the grid is published or previewed in the server
there is a data retieval error. There is even a data retrieval error if I try to use the grid with just one value.

Here is the errorlog:
-------------------------------------3/14/2017--2:18 PM--------------------------------
Error Code:-2147467261
System.NullReferenceException: Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.
   bei Syncfusion.DashboardService.DashboardWidgets.GridWidget.SetGridColumnBasicProperties(List`1 visibleColumnSchemaInfos, Int32 columnCount, Double controlWidth, List`1 templateColumn, List`1 columnList, Dictionary`2 formattedColumns, Dictionary`2 displayColumNames, String controlName, DashboardLink linkDetails, Boolean isLinkTemplate)
   bei Syncfusion.DashboardService.DashboardWidgets.GridWidget.GetGridControlData(DashboardItem controlObj, List`1& columnList, List`1& gridSchemaInfos, Dictionary`2& barData, Dictionary`2& formattedColumns, List`1& colourSaturationColumns, Boolean isFromVirtualScrolling, List`1 gridSortInfo, List`1 gridAllowFilterInfo)
   bei Syncfusion.DashboardService.DashboardWidgets.GridWidget.GetGridDataOnDemand(DashboardItem controlObj, Dictionary`2& dict, GridItem gridControl, RelationalReport& currentReport, List`1& gridSchemaInfos)
   bei Syncfusion.DashboardService.DashboardWidgets.GridWidget.GetData()
   bei Syncfusion.DashboardService.DashboardService.GetWidgetData(DashboardItem dashboardItem, WidgetAutoRefreshSetting widgetAutoRefreshSettings, ControlVirtualScrollingSettings controlVirtualScrollingSettings, ControlDrilldownSettings controlDrilldownSettings, AllowFilterSettings allowFilterSettings, Boolean isLoad, Boolean isExport, Int32 start, Int32 end)


Generated Query: SELECT TOP 50 [ErrorText] AS [Grid_Column_10],ISNULL(SUM([BreakReasonCounter]),0) AS [Grid_Column_1],SUM(CAST(("DashboardSelectSubQuery".[Worktime])  AS DECIMAL(38,0)))/NULLIF(3600,0) AS [Grid_Column_7],[STATIONNAME] AS [Grid_Column_8],[STEPNAME] AS [Grid_Column_15] FROM (select MDedata.ORDERNUM, Mdedata.CALLINDEX, TIMEID, BREAKREASON, Mdedata.USERID, PIECE, ErrorPiece, Useraction, LayerNum, MDEDATA.VTTERMNUM,
MDECounter, Worktime, EvalDate, BreakReasonCounter, STATIONNAME, Plandata.Orderstep, STEPNAME,
Name, ErrorText, Prod_BedingteUnterbrechung, Technisch_Bedingt, Organisatorisch_Bedingt, Einrichten_Bedingt, WerkzeugBedingt, case TIMEID when 2 then Worktime end as TP,
case TIMEID when 3 then worktime end as TR, case TIMEID when 4 then worktime end as TU ,case TIMEID when 1 then worktime end as TKA,  Bezeichner from MDEData
join Plandata on Plandata.VTTERMNUM = mdedata.VTTERMNUM and Plandata.ORDERNUM = MDEDATA.ORDERNUM and Plandata.CallNum = mdedata.callnum and Plandata.OrderStep = mdedata.CALLINDEX
join WORKSTATION on WORKSTATION.VTTERMNUM = MDEDATA.VTTERMNUM
join MASCHINE on Maschine.MACHINENUM = WORKSTATION.MACHINENUM
join BREAKREASONS on MASCHINE.BRGROUP = BREAKREASONS.BRGROUP
join ErrorTab on ErrorTab.Errornum = BREAKREASONS.ErrorNum
join Arbeitsgang on Arbeitsgang.ORDERSTEP = Plandata.ORDERSTEP
join Abteilung on Abteilung.ID = WORKSTATION.Devision_ID
where Mdedata.VTTERMNUM = 1 or MDEData.VTTERMNUM = 5 or MDEdata.VTTERMNUM = 15)"DashboardSelectSubQuery" GROUP BY [ErrorText],[STATIONNAME],[STEPNAME]  ORDER BY 1 ASC,4 ASC,5 ASC

Is there some kind of fix for this?

Thanks.

Regards,
Patrick


RN Renuka N Syncfusion Team March 15, 2017 01:46 PM UTC

Hi Patrick, 

Query 
Response 
I tried to build a pivot where the sum for "Anzahl" and "Zeit in h" should be calculated depending on the value in the left row.
Each value in the left has an unique value for Anzahl an Zeit in h. 

As you see in the attached screenshot the sums of these values are the same for all values. 
Is it possible to build a pivot like the one I wanted or do I have to use another widget?
 
We can achieve your requirement of “Display column based on calculation with left column value” with Pivot Grid by using Expression. Please refer the following steps to achieve in Pivot Grid. 

1.       Add fields to Pivot Grid. Click the icon, Expression editor window will be opened. 
 
2.       Choose the column which you need to calculate and apply your calculation on Expression box. 
 
3.       After defined the calculation, save the expression. 
4.       And we can use the expression field, where you want to be. 
 
 
 
Published Link: 
 
Login Details: 
Username: patrick 
Password: Patrick@123 
I tried to build this via a grid. 
The designer shows the grid correctly. When the grid is published or previewed in the server 
there is a data retieval error. There is even a data retrieval error if I try to use the grid with just one value.
 
We are unable to reproduce the mentioned issue with the latest setup (v2.1.0.2). Can you please send the .sydx file which you tested with Syncfusion Dashboard application, that would help us to serve you better. 

Regards, 
Renuka N. 
 



GK Gregory Kohle March 16, 2017 07:21 AM UTC

Hi Renuka,

thanks for your respone.
We just want to get something like in screeshot 1 where the values are correctly caluculated.
Each text on left like "Default UG" has to get its own sum(BreakReasonCounter) and sum(worktime).
Is this possible to do?
I attached a sample dataset.
Thanks.

Regards,
Patrick


Attachment: testMDE_628ffd97.zip


RN Renuka N Syncfusion Team March 17, 2017 12:53 PM UTC

  
 
Hi Patrick, 
 
It seems “Sum(BreakReasonCounter)” contains same value for “ErrorText” Dimension. If you are applying calculation based on “Sum(BreakReasonCounter)”, the calculated value also will be same. Please refer the following images illustrates comparison of data in Excel and Syncfusion Dashboard application. 
 
Pivot Table in Excel: 
 
 
 
PivotGrid in Syncfusion Dashboard application : 
 
 
 
PivotGrid with Expression based on “Sum(BreakReasonCounter)”: 
 
 
 
 
 
 
Pivot Table of the given Excel data: 
 
Regards, 
Renuka N 


  


Loader.
Live Chat Icon For mobile
Up arrow icon