Ticket Q361155
Visible to All Users

Using SQL/Store Procedure in Analysis of XAF

created 13 years ago

Dear Support,
I want to set to query by SQL to bind to the PivotGrid in Analysis. I have worked on AnalysisDataBindController to do something when the BindDataAction is executed as following:
                   Analysis analysis = (Analysis) View.CurrentObject;
                    ObjectSpace objectSpace = (ObjectSpace) this.ObjectSpace;
                    string sql = "SELECT P.PoNo, P.SoNo, CAST(P.PoDate as smalldatetime) as PoDate, P.VendorCode, ";
                    sql += @"P.VendorName, P.ShipToCode, P.ShipToName, ";
                    sql += @"SUM(D.UnitPrice * D.Quantity) as [Amount] FROM PO P ";
                    sql += @"LEFT OUTER JOIN PO_Detail D ON P.Oid = D.Po ";
                    sql += @"GROUP BY P.PoNo, P.SoNo, P.PoDate, P.VendorCode, P.VendorName, P.ShipToCode, P.ShipToName ";
                    sql += @"ORDER BY Amount DESC ";
                    SelectedData selectedData = objectSpace.Session.ExecuteQuery(sql);
                    XPDataView xpDataView = new XPDataView();
                    xpDataView.AddProperty("PoNo", typeof(String));
                    xpDataView.AddProperty("SoNo", typeof(String));
                    xpDataView.AddProperty("PoDate", typeof(DateTime));
                    xpDataView.AddProperty("VendorCode", typeof(String));
                    xpDataView.AddProperty("VendorName", typeof(String));
                    xpDataView.AddProperty("ShipToCode", typeof(String));
                    xpDataView.AddProperty("ShipToName", typeof(String));
                    xpDataView.AddProperty("Amount", typeof(Decimal));
                    xpDataView.LoadData(selectedData);
                    editor.Control.DataSource = new AnalysisDataSource(analysis, xpDataView);
                    editor.Control.PivotGrid.DataBind();
However, the PivotGrid can't display any data but only the column header. I tried to stop at debug before editor.Control.PivotGrid.DataBind() and sure the xpDataView have get the correct data from database. What wrong with my code? Please help.
Regards,
Gary

Show previous comments (6)
DevExpress Support Team 13 years ago

    Hi Gary,
    The AnalysisDataSource instance is required for IAnalysisControl, but the actual data source assigned to the pivot grid is AnalysisDataSource.DataSource - the one you pass in the AnalysisDataSource constructor.
    Please let me know if you need any additional assistance.
    Thanks,
    Michael.

    VC VC
    Vincent Cheng (ME-HK) 13 years ago

      Dear Michael,
      Thank you for your explanation. It is clear now.
      Regards,
      Gary

      DevExpress Support Team 13 years ago

        You are welcome, Gary.
        Please let me know if I can help you further.
        Thanks,
        Michael.

        Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

        Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.