Refreshing PivotChart Data

7 replies. Last post: June 14, 2010 12:31 PM by Andi Santoso
Tags :
  • (None)
  • New Discussion
  • New Question
  • New Product Feedback
Jason WrightMember

Apologies in advance if this is a really stupid question.

 

I have a WebGrid/Pivot chart, which is working really well.  The user can see the details in the grid, and the click on the chart where I've preset the Pivot Chart data content, axis, Filter config etc - as I the display to default when the user first hits the page.

As mentioned, this is working really well.

The problem I have is that I want the user to be able to enter a date range so that it will only show information for data items that match that date range.  The most user friendly mode I thought of was to put two text boxes above the webgrid, with calendar extenders that when the page loaded would default to the current financial year, and if user wanted to, they could modify the dates, click a button, and the data would refresh.

This is working fantastically well for the WebGrid, as the data is refreshing beautifully when the dates are entered and the refresh button clicked, however it doesn't update the Pivot Chart (still shows old values). 

I tried changing the select command for the WebGrid datasource so that it read directly from the two text boxes, rather than changing the select command programmatically (as I did in the first instance), which caused the Pivot Chart to error with the message "Unable to Communicate with Server" - though interestingly enough the Grid View still worked.

 

Attached is the sample code, along with the VB code used to alter the select command of the data source.

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="ReportOEV.aspx.vb" Inherits="MVMS.ReportOEV" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<%@ Register Assembly="ISNet.WebUI.WebDesktop" Namespace="ISNet.WebUI.WebDesktop"
    TagPrefix="ISWebDesktop" %>
<%@ Register Assembly="ISNet.WebUI.WebGrid" Namespace="ISNet.WebUI.WebGrid" TagPrefix="ISWebGrid" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<html>
<head id="Head1" runat="server">
    <title>MVMS Administration - OEV Report</title>
    <link rel="stylesheet" type="text/css" href="../GlobalMVMS.css" />
</head>
<body id="Body1" runat="server">
    <form id="frmReportOEV" runat="server">
    <div class="" style="margin: 10px;">
        <asp:SqlDataSource ID="sqlValuations" runat="server" ConnectionString="<%$ ConnectionStrings:MVMS %>"
            SelectCommand="SELECT * FROM [Valuations] ">
           
        </asp:SqlDataSource>
        <asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </asp:ToolkitScriptManager>
        <asp:Table ID="Table1" runat="server">
            <asp:TableRow>
                <asp:TableCell>
                    <asp:Label ID="Label1" runat="server" CssClass="label" Text="Date Created From"></asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:TextBox ID="txtDateFrom" CssClass="textbox" runat="server"></asp:TextBox>
                    <asp:CalendarExtender ID="CalendarExtender1" TargetControlID="txtDateFrom" Format="dd/MM/yyyy"
                        PopupButtonID="imgDatePicker" runat="server" />
                    <asp:ImageButton ID="imgDatePicker" runat="server" ImageUrl="~/Images/date_picker.gif" />
                </asp:TableCell>
                <asp:TableCell></asp:TableCell>
                <asp:TableCell>
                    <asp:Label ID="Label2" runat="server" CssClass="label" Text="Date Created To"></asp:Label>
                </asp:TableCell>
                <asp:TableCell>
                    <asp:TextBox ID="txtDateTo" CssClass="textbox" runat="server"></asp:TextBox>
                    <asp:CalendarExtender ID="CalendarExtender2" TargetControlID="txtDateTo" Format="dd/MM/yyyy"
                        PopupButtonID="imgDatePicker2" runat="server" />
                    <asp:ImageButton ID="imgDatePicker2" runat="server" ImageUrl="~/Images/date_picker.gif" />
                </asp:TableCell>
                <asp:TableCell></asp:TableCell>
                <asp:TableCell>
                    <asp:Button ID="btnUpdateData" CssClass="button2" runat="server" Text="Update Data" />
                </asp:TableCell>
            </asp:TableRow>
        </asp:Table>
        <ISWebGrid:WebGrid ID="grdValuations" runat="server" Height="100%" UseDefaultStyle="True"
            Width="100%" AllowPivotCharting="True" DataSourceID="sqlValuations" DefaultStyleMode="Win7">
            <ChartSettings ChartType="Column">
            </ChartSettings>
            <ChartInteractiveUI ChartAutoRefresh="true">
            </ChartInteractiveUI>
            <ChartSeriesCollection>
                <ISWebGrid:ChartPivotFilterConfig DataMember="State" />
            </ChartSeriesCollection>
            <ChartDataCollection>
                <ISWebGrid:ChartPivotDataConfig DataMember="Val_Id" AutoCalc="Count" />
            </ChartDataCollection>
            <ChartSettings>
                <XAxis>
                    <Title Text="States"></Title>
                </XAxis>
                <YAxis>
                    <Title Text="Valuation Count"></Title>
                </YAxis>
            </ChartSettings>
            <LayoutSettings AllowColumnMove="Yes" AllowExport="Yes" AllowFilter="Yes" AllowGrouping="Yes"
                AllowSelectColumns="Yes" AllowSorting="Yes">
            </LayoutSettings>
            <RootTable DataKeyField="Val_Id">
                <Columns>
                    <ISWebGrid:WebGridColumn Caption="Valuation Id" DataMember="Val_Id" Name="Val_Id"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Client Id" DataMember="Client_Id" Name="Client_Id"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Bank Id" DataMember="Bank_Id" Name="Bank_Id" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Date Created" DataMember="Date_Created" DataType="System.DateTime"
                        Name="Date_Created" DataFormatString="dd/MM/yyyy" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Status" DataMember="Status" Name="Status" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Applicant Name" DataMember="App_Name" Name="App_Name"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Address" DataMember="Address" Name="Address" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Suburb" DataMember="Suburb" Name="Suburb" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="State" DataMember="State" Name="State" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Post Code" DataMember="PostCode" DataType="System.Int32"
                        Name="PostCode" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Property_Name" DataMember="Property_Name" Name="Property_Name"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Valuation Type" DataMember="Val_Type" DataType="System.Int16"
                        Name="Val_Type" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Volume" DataMember="Volume" Name="Volume" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Lot Num" DataMember="Lot_Num" Name="Lot_Num" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Property Type" DataMember="Property_Type" Name="Property_Type"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Contact" DataMember="Contact" Name="Contact" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Contact Number" DataMember="Contact_Num" Name="Contact_Num"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Estimated By" DataMember="Est_By" Name="Est_By"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Estimate Value" DataMember="Est_Value" DataType="System.Decimal"
                        Name="Est_Value" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Valuation Amount" DataMember="ValAmt" DataType="System.Decimal"
                        Name="ValAmt" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Valuation Completed Date" DataMember="Val_Comp_Date"
                        DataType="System.DateTime" DataFormatString="dd/MM/yyyy" Name="Val_Comp_Date"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Tenancy" DataMember="Tenancy" Name="Tenancy" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Current Use" DataMember="Current_Use" Name="Current_Use"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Review" DataMember="Review" Name="Review" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Purpose of Report" DataMember="POR" Name="POR"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Format" DataMember="Format" Name="Format" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Fee Scale" DataMember="Val_Fee" Name="Val_Fee"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Fee Override Amount" DataMember="ValFeeOverride"
                        DataType="System.Decimal" Name="ValFeeOverride" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Req By Date" DataMember="Req_By_Date" DataType="System.DateTime"
                        Name="Req_By_Date" DataFormatString="dd/MM/yyyy" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Req a Quote" DataMember="Req_Quote" Name="Req_Quote"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Quote Req By" DataMember="Req_By" Name="Req_By"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Override Valuer" DataMember="Override_Val" Name="Override_Val"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Override Approved" DataMember="Override_Approved"
                        Name="Override_Approved" Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Comments" DataMember="Comments" Name="Comments"
                        Width="100px">
                    </ISWebGrid:WebGridColumn>
                    <ISWebGrid:WebGridColumn Caption="Valuer" DataMember="Valuer" Name="Valuer" Width="100px">
                    </ISWebGrid:WebGridColumn>
                </Columns>
            </RootTable>
        </ISWebGrid:WebGrid>
    </div>
    </form>
</body>
</html>


Partial Public Class ReportOEV
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then


            If Month(Today()) > 6 Then
                txtDateFrom.Text = "01/07/" & Year(Today())
                txtDateTo.Text = "30/06/" & (Year(Today()) + 1)
            Else
                txtDateFrom.Text = "01/07/" & (Year(Today()) - 1)
                txtDateTo.Text = "30/06/" & Year(Today())
            End If

        End If

    End Sub


    Private Sub btnUpdateData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateData.Click

        Dim DateFrom As Date
        Dim DateTo As Date
        Dim sqlDateFrom As String
        Dim sqlDateTo As String

        If txtDateFrom.Text = "" Then
            DateFrom = Convert.ToDateTime("01/01/1900")
        Else
            DateFrom = Convert.ToDateTime(txtDateFrom.Text)
        End If

        If txtDateTo.Text = "" Then
            DateTo = Convert.ToDateTime("31/12/2999")
        Else
            DateTo = Convert.ToDateTime(txtDateTo.Text)
        End If

        sqlDateFrom = Year(DateFrom) & "-" & Month(DateFrom) & "-" & Day(DateFrom)
        sqlDateTo = Year(DateTo) & "-" & Month(DateTo) & "-" & Day(DateTo)

        sqlValuations.SelectCommand = "SELECT * FROM [Valuations] where [Date_Created] >= '" & sqlDateFrom & "' and [Date_Created] <= '" & sqlDateTo & "'"
        grdValuations.RebindDataSource()

    End Sub
End Class
Is there any way to refresh the pivotchart data so that it reflects changes to the Web Grid?

 

Cheers,

 

Jason.

All times are GMT -5. The time now is 4:11 AM.
Previous Next