Showing null values in radpivotgrid

[Entropy69]

I would like to show null values in a pivotgrid to indicate that a sample is misssing. It only shows zero's regardless the setting of IgnoreNullValues. I would expect null, empty or whatever the setting of EmptyValue. What am I missing here? Using UI for ASP.NET AJAX R2 2018 (version 2018.2.516)

<telerik:RadPivotGrid ID="mainpgrid" runat="server"  
            AllowFiltering ="false" 
            AllowNaturalSort="false"  
            AllowPaging ="false" 
            ShowFilterHeaderZone ="false" 
            EmptyValue ="(missing sample)"  >
        <TotalsSettings GrandTotalsVisibility ="None"  ColumnsSubTotalsPosition ="None" RowGrandTotalsPosition ="None" />
            <Fields >
                <telerik:PivotGridColumnField DataField ="SeriesLabel" Caption ="Label" ></telerik:PivotGridColumnField>
                <telerik:PivotGridAggregateField DataField ="Value"   IgnoreNullValues ="True"   Caption ="IgnoreNull" ></telerik:PivotGridAggregateField>
                <telerik:PivotGridAggregateField DataField ="Value"   IgnoreNullValues ="False"  Caption ="DoNotIgnoreNull" ></telerik:PivotGridAggregateField>
                <telerik:PivotGridRowField DataField ="UTZDate" Caption ="Date"></telerik:PivotGridRowField>
            </Fields>
        </telerik:RadPivotGrid>

 

Code:

Private Sub testpage_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim t As New DataTable
            t.Columns.Add("UTZDate")
            t.Columns.Add("SeriesLabel")
            t.Columns.Add("Value", System.Type.GetType("System.Int32"))

            Dim r As DataRow = t.NewRow
            r("UTZDate") = Now
            r("SeriesLabel") = "Series 0"
            r("Value") = DBNull.Value
            t.Rows.Add(r)

            r = t.NewRow
            r("UTZDate") = Now.AddMinutes(5)
            r("SeriesLabel") = "Series 0"
            r("Value") = 100
            t.Rows.Add(r)

            Me.mainpgrid.DataSource = t
            Me.mainpgrid.DataBind()
        End If
    End Sub

 

[Eyup]

Hi,

The EmptyValue property works properly, but for records which do not provide any info for the current row. To better clarify the situation, allow me to elaborate on the word "empty" in this case.

The provided pivot grid structure has 2 rows - with dates Now and +5 mins. The Series 0 provides information for both of these cases and regardless what is the value, the pivot grid interprets it that there IS data for this cell and this cell is not counted as "empty". If for example, you would add to your DataTable structure Series 1 and 2 which do not provide data for any of these row cases, the corresponding cells would be "truly empty" and the value you've set in EmptyValue property will be displayed:

r = t.NewRow
r("UTZDate") = Now
r("SeriesLabel") = "Series 1"
r("Value") = 3
t.Rows.Add(r)

r = t.NewRow
r("UTZDate") = Now.AddMinutes(5)
r("SeriesLabel") = "Series 2"
r("Value") = 4
t.Rows.Add(r)

I hope this clarification will be helpful.

Also, please ensure that you are not using the DataBind method to bind the pivot grid, similar to RadGrid:
https://www.telerik.com/support/kb/aspnet-ajax/grid/details/how-to-bind-radgrid-properly-on-server-side

You can check this blog post for additional information about RadPivotGrid:
http://www.telerik.com/blogs/aspnet-pivot-table-made-easy

Regards,
Eyup
Progress Telerik

[Entropy69]

Thank you, so it's designed to show the setting of EmptyValue when nothing is there. But if both series have a missing value on a specific date, how would I get that date shown? I would have to introduce a dummy value which has a value for each moment  in time and hide that? That does not seem very logical.

I have got a ranges of values on a timescale on which several values are missing. I would like to show the date but also indicate that the value is missing. The fact that it's missing on what point in time is relevant information, hence a filled date column and a null in the value column. How would I obtain that behavior? On the database I have a left join to always generate the timescale.

Only thing I could think of is use  'average' which generates an error which may be suppressed on cell databound? 

[Eyup]

Hi,

This is the default behavior of the control as explained in my previous post. If you want to change the 0 text, you can use the CellDataBound event handler to modify this value manually:

Protected Sub mainpgrid_CellDataBound(sender As Object, e As PivotGridCellDataBoundEventArgs)
    If TypeOf e.Cell Is PivotGridDataCell Then
        Dim cell As PivotGridDataCell = e.Cell
        If cell.Text = "0" Then
            cell.Text = mainpgrid.EmptyValue
        End If
    End If
End Sub

Regards,
Eyup
Progress Telerik

[Entropy69]

Thank you, but with that solution will still not be able to make a difference between real measured zero's  and nulls (missing values) and that was the whole point. It seems that RadPivotGrid is not the right tool for the job. I ended up creating the pivot in SQL server and bind the results to a RadGrid which does show nulls as empty cells.

[Eyup]

Hello,

I'm glad you've managed to find a solution for your specific scenario. 
Please feel free to turn to us if new questions arise.

Regards,
Eyup
Progress Telerik