Intersoft Community - WebGrid Enterprise - Refreshing PivotChart Data

Refreshing PivotChart Data

andi@intersoftpt.com — Mon, 14 Jun 2010 12:31:19 GMT

Hi Jason,

Forgive me, however, could you re-attach those file again?It seems that there are some corrupt data, I can not open the .doc file. On the other file, you just sent me the .sln file in there which I cannot open them if you do not include the other files.

However, have you tried to re-create a new simple project, use Nothwind database and use my sample .aspx? See it this error is still occuring.

Thank you and have a nice day.

Best Regards,

Andi Santoso

Refreshing PivotChart Data

jasonw43 — Sat, 12 Jun 2010 01:43:30 GMT

I've attached my project - it has the database that I've been using. I've removed the ISNET files from the bin directory to save space (you should be able to add them in) - I've attached a word doc to show the files that I had.

Love to hear what I'm doing wrong.

Cheers,

Jason.

Refreshing PivotChart Data

andi@intersoftpt.com — Fri, 11 Jun 2010 10:24:56 GMT

Hi Jason,

Have you tried to use my sample in a new project file? I have attached you a simple sample, which basically is the same. I am still having a problem to replicate the issue of yours. Please see my doc attachment on how I do this scenario step by step, and the result is just fine.

Could this happend because of your database? Have you tried to use Nothwind Database? If this still does not help, could you provide me with a simple sample on your data if you would not mind? Just a dummy data would be fine. I was wondering if you have set something on them.

Thank you and have a nice day.

Best Regards,

Andi Santoso

Refreshing PivotChart Data

jasonw43 — Thu, 10 Jun 2010 20:12:53 GMT

Hello Andi,

The <http handlers> are there - as you said, the chart prob wouldn't show at all without it.

Not sure what you mean by "does the status bar of WebGrid is getting filtered?" I've attached a file with the code showing the table accessing the Personnels table - as well as some screen dumps of the issue I'm having.

What is interesting is that after I click on the button, the grid shows the correct data and the status bar is showing the correct number of records loaded - yet when I click on the Pivot Chart, it still shows the old data but the status bar is showing the correct number of records loaded.

Please see attached.

Cheers,

Jason.

Refreshing PivotChart Data

andi@intersoftpt.com — Thu, 10 Jun 2010 11:07:39 GMT

Hi Jason,

It is weird. Eveything is running perfectly in my end. No, we do not need to add a config variable. Since the Chart is showing in your end, I assume that you have already added the <http handlers> off ISChar.axd as well.

May I know, when you click on the button, does the status bar of WebGrid is getting filtered? Does it load the exact amount of data with those validation?

Best Regards,

Andi Santoso

Refreshing PivotChart Data

jasonw43 — Thu, 10 Jun 2010 01:01:28 GMT

Ok this one is really doing my head in.

I've copied your code and modified the table so that it accesses the Personnels table from the Database.mdf file so that I can use the SQL call.

Here is my code.

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Imports ISNet.WebUI.WebGrid
Imports System.Data.SqlClient.SqlCommand

Partial Class Test

    Inherits System.Web.UI.Page
    Dim i As Boolean

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

    End Sub

    Protected Sub WebGrid1_InitializeDataSource(ByVal sender As Object, ByVal e As ISNet.WebUI.WebGrid.DataSourceEventArgs) Handles WebGrid1.InitializeDataSource

        Dim ds As New DataSet
        Dim orders As New SqlDataAdapter
        Dim conn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnectionString").ConnectionString)
        conn.Open()

        Dim stringcon As String

        If (i) Then
            stringcon = "SELECT * FROM [Personnels] where [DepartmentID] = 2"
        Else
            stringcon = "SELECT * FROM [Personnels]"
        End If

        orders.SelectCommand = New SqlCommand(stringcon, conn)
        orders.Fill(ds, "Personnels")
        e.DataSource = ds
        conn.Close()

    End Sub

    Protected Sub WebGrid1_PrepareDataBinding(ByVal sender As Object, ByVal e As ISNet.WebUI.WebGrid.DataSourceEventArgs) Handles WebGrid1.PrepareDataBinding
        WebGrid1.RetrieveStructure()
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        i = True
        WebGrid1.ClearCachedDataSource()
        WebGrid1.RebindDataSource()
    End Sub
End Class


<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Test.aspx.vb" Inherits="Test" %>

<%@ Register Assembly="ISNet.WebUI.WebGrid" Namespace="ISNet.WebUI.WebGrid" TagPrefix="ISWebGrid" %>
<html>
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <ISWebGrid:WebGrid ID="WebGrid1" runat="server" Height="100%" UseDefaultStyle="True"
            Width="100%" AllowPivotCharting="True" ChartMode="InteractiveUI" DefaultStyleMode="Win7">
            <FlyPostBackSettings PostInputControls="true" PostControlState="true" PostViewState="true"
                PostHiddenFields="true" />
            <ChartSettings ChartType="Column">
            </ChartSettings>
            <ChartInteractiveUI ChartAutoRefresh="true">
            </ChartInteractiveUI>
            <ChartSeriesCollection>
                <ISWebGrid:ChartPivotFilterConfig DataMember="Name" />
            </ChartSeriesCollection>
            <ChartDataCollection>
                <ISWebGrid:ChartPivotDataConfig DataMember="PersonnelID" AutoCalc="Count" />
            </ChartDataCollection>
            <RootTable DataKeyField="PersonnelID">
            </RootTable>
        </ISWebGrid:WebGrid>
        <asp:Button ID="Button1" runat="server" Text="Button" />
    </div>
    </form>
</body>
</html>

The really annoying thing is that whilst I still can't get it to work in my project (the Pivot Chart is still not updating!) it works fine when I add it to the Intersoft Tutorial Project. I don't know why - the connection strings in the Web.Config are the same - and I can't see anything that would be different between the two (the code on both projects are identical - straight copy/paste). Is there a config variable that I should be looking out for? Sorry for nagging but I've run out of hair to pull out.

Cheers,

Jason.

Refreshing PivotChart Data

andi@intersoftpt.com — Wed, 09 Jun 2010 12:01:18 GMT

Hi Jason,

Forgive me, however, I cannot use your sample since I do not have the data of yours. But, I have created a simple sample with Northwind database that runs under your scenario. In my sample, when button is clicked, it will only load an Orders table where the CustomerID is equal to 5.

Later, you can set this validation under your scenario. For your information, to rebind data source, we will need to do this in InitializeDataSource of WebGrid's server side event. That is why we cannot bind them in this button click event.

You will notice that when the button is clicked, it will only load the data with that particular validation, and it will be rendered in both, WebGrid View and WebGrid Chart View.

I hope it helps. Thank you and have a nice day.

Best Regards,

Andi Santoso

Refreshing PivotChart Data

jasonw43 — Tue, 08 Jun 2010 21:54:05 GMT

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.