Showing null values in radpivotgrid

6 posts, 0 answers
  1. Entropy69
    Entropy69 avatar
    8 posts
    Member since:
    Mar 2015

    Posted 03 Jul 2018 Link to this post

    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

     


  2. Eyup
    Admin
    Eyup avatar
    3785 posts

    Posted 06 Jul 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  3. Entropy69
    Entropy69 avatar
    8 posts
    Member since:
    Mar 2015

    Posted 06 Jul 2018 in reply to Eyup Link to this post

    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? 

  4. Eyup
    Admin
    Eyup avatar
    3785 posts

    Posted 10 Jul 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
  5. Entropy69
    Entropy69 avatar
    8 posts
    Member since:
    Mar 2015

    Posted 10 Jul 2018 in reply to Eyup Link to this post

    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.
  6. Eyup
    Admin
    Eyup avatar
    3785 posts

    Posted 12 Jul 2018 Link to this post

    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
    Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
Back to Top