Group by a value and then sum up

[Gregory Kohle]

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

[Gregory Kohle]

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

[Renuka N]

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:  http://ds-support.syncfusion.com/en-us/dashboards/view/129377/PivotGrid_Updated     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. 

 

[Gregory Kohle]

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

[Renuka N]

  

 

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: 

Updated_testMDE 

 

Regards, 

Renuka N