PivotGrid Data Binding From Database

[Anis]

Hi,

I have been looking for a sample with data binding in PivotGrid, I found some in this forum, but I was not able to run them due to the existing errors (error like: Syncfusion.JavaScript.Olap does not exist).

So can you please make changes in my sample below to make it work:

 - using JSON

 - Entity Framework code first from database

 - using the table "TestPivotGrid" in my database

sample: https://drive.google.com/open?id=0B_MYjZx6kN7-REhzcGtIY3QyYkk

(username: admin, passwd: serenity)

Regards,

Anis

[Anis]

Hi,

Sorry, I make a mistake, I want my sample to use PivotClient (Report) not PivotGrid.

So can you please make changes in my sample below to make it work:

 - using JSON

 - Entity Framework code first from database

 - using the table "TestPivotGrid" in my database

sample: https://drive.google.com/open?id=0B_MYjZx6kN7-REhzcGtIY3QyYkk

(username: admin, passwd: serenity)

Regards,

Anis

[Ramesh Govindaraj]

Hi Anis, 

Thank you for using Syncfusion products. 

We are unable to access your sample completely due to the absence of referred SQL database “devextreme”.  So, we have prepared a simple PivotClient sample which is available in the following link. 

Sample link:  http://www.syncfusion.com/downloads/support/forum/127738/ze/SyncfusionMvcApplication51416678589  

Kindly use the above sample as a reference or you can modify the above sample as per your requirement. 

Please let us know if you need any further assistance. 

Thanks and Regards, 

Ramesh G 

[Anis]

Hi,

I have the same example (created with syncfusion sample creator), that's not what I'm looking for.

I do not want to use defined data like this:

 var pivot_dataset = [
                               { Amount: 100, Country: "Canada", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Alberta" },
                               { Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Van", Quantity: 3, State: "British Columbia" },
                               { Amount: 300, Country: "Canada", Date: "FY 2007", Product: "Car", Quantity: 4, State: "Brunswick" },
                               { Amount: 150, Country: "Canada", Date: "FY 2008", Product: "Bike", Quantity: 3, State: "Manitoba" },
                               { Amount: 200, Country: "Canada", Date: "FY 2006", Product: "Car", Quantity: 4, State: "Ontario" },
                               { Amount: 100, Country: "Canada", Date: "FY 2007", Product: "Van", Quantity: 1, State: "Quebec" },
                               { Amount: 200, Country: "France", Date: "FY 2005", Product: "Bike", Quantity: 2, State: "Charente-Maritime" },
                               { Amount: 250, Country: "France", Date: "FY 2006", Product: "Van", Quantity: 4, State: "Essonne" },
                               { Amount: 300, Country: "France", Date: "FY 2007", Product: "Car", Quantity: 3, State: "Garonne (Haute)" },
                               { Amount: 150, Country: "France", Date: "FY 2008", Product: "Van", Quantity: 2, State: "Gers" },
                               { Amount: 200, Country: "Germany", Date: "FY 2006", Product: "Van", Quantity: 3, State: "Bayern" }
    ];

I want to use the data from the database.

There is no problem in my sample, but you will find below the mdf database file.

Regards,

Anis

Attachment: DBase_f0a2e95a.rar

[Ramesh Govindaraj]

Hi Anis, 

Thank you for the database. 

We are unable to attach the given database in our SQL Server Management Studio 2012 which throws below exception. So, we are unable to run your sample completely. 

 

Hence, We have modified our sample based on your requirement where you can bind the data to PivotClient using SQL database. Please find the sample and code in below. 

Sample link:  http://www.syncfusion.com/downloads/support/forum/127738/ze/SyncfusionMvcApplication52060449776  

Code: 

PivotClientFeatures.cshtml: 

<script> 

    var pivot_dataset = []; 

 

    function onLoad(args) { 

        $.ajax({ 

            type: "POST", 

            url: "../api/OlapClient/GetData", 

            contentType: 'application/json; charset=utf-8', 

            dataType: 'json', 

            success: function (val) { 

                pivot_dataset = JSON.parse(val); 

                args.model.dataSource.data = pivot_dataset; 

            }, 

        }); 

    } 

 

              //code here 

 

</script> 

OlapClientController.cs(C#): 

       [System.Web.Http.ActionName("GetData")] 

        [System.Web.Http.HttpPost] 

        public string GetData() 

        { 

            string sJSON = ""; 

 

            //You can frame your logic here to get data from SQL database and send to client side as string format. 

 

            return sJSON; 

        } 

Note: The above sample is just a prototype. You can modify the sample based on your requirement. 

Thanks and Regards, 

Ramesh G 

[Anis]

Hi Ramesh,

I follow your sample and the documentation in "https://help.syncfusion.com/aspnetmvc/pivotclient/olap-getting-started", but it does not help, I always get empty PivotClient.

I'm using database in Sql server (not local sql server)

You find my code and screenshot in below. 

Regards,

Anis

Attachment: PivotClient_e1547638.rar

[Sastha Prathap Selvamoorthy]

Hi Anis,  

 

We think that the reported problem occurred due to the ajax call made in ‘load’ event.  It would get called after getting through all the client side codes by default.  Inorder to perform the ajax call at the event itself, you can set provide the property “async” as true in “$.ajax” function call.  Please find the sample code snippet below for your reference. 

function onLoad(args) { 

         $.ajax({ 

            type: "POST", 

            url: "../api/OlapClient/GetData", 

            contentType: 'application/json; charset=utf-8', 

            dataType: 'json', 

            async: false, 

            success: function (val) { 

                pivot_dataset = JSON.parse(val); 

                args.model.dataSource.data = pivot_dataset; 

            }, 

        }); 

    } 

Meanwhile, we have prepared a sample for your reference. Please find the sample in the below link. 

Sample link: http://www.syncfusion.com/downloads/support/forum/127738/ze/DataBinding-1149072974 

 

NOTE: Please ensure that all the dll’s which are added to the reference are referred from the "lib" folder properly. 

If the problem persists, please let us know. 

Regards, 

[Anis]

Hi,

I still have the same problem (empty PivotClient), Can you please download my project template from one of these links (It works well with your colleges in forums, so they should work for you):

http://www.syncfusion.com/downloads/support/forum/127421/ze/FileExplorerMVC-1302190228  

https://www.syncfusion.com/forums/127421/fileexplorer-navigate-to-specific-paths

http://www.syncfusion.com/downloads/support/forum/127281/ze/File_Explorer.Web-1231883455  

http://www.syncfusion.com/downloads/support/forum/127281/ze/File_Explorer.Web-1102198667  

And add this table to database:

CREATE TABLE [dbo].[PivotClient] (
    [Id]               INT        NOT NULL,
    [DateCtrl]         DATETIME   NOT NULL,
    [Ctrl]             NCHAR (50) NOT NULL,
    [m]            NCHAR (50) NOT NULL,
    [Customer]         NCHAR (50) NOT NULL,
    [Quantity]         INT        NOT NULL,
    [ErrorDescription] NCHAR (50) NOT NULL,
    [Section]          NCHAR (50) NOT NULL,
    [nb1]              INT        NOT NULL,
    [nb2]              INT        NOT NULL,
    [nb3]              INT        NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Then extract data from this tables.

Regards,

Anis

[Sastha Prathap Selvamoorthy]

Hi Anis,  

We are unable to reproduced the reported problem at our end. So, Kindly provide us the returned JSONData from the ajax success event in your sample, which will be more helpful to assist you further. Please find the below highlighted code in the snippet for your reference. 

function onLoad(args) { 

         $.ajax({ 

            type: "POST", 

            url: "../api/OlapClient/GetData", 

            contentType: 'application/json; charset=utf-8', 

            dataType: 'json', 

            async: false, 

            success: function (val) { 

                pivot_dataset = JSON.parse(val); // get the returned JSONData from service 

                args.model.dataSource.data = pivot_dataset; 

            }, 

        }); 

    } 

 

Regards, 

Sastha Prathap S. 

[Anis]

Hi,

I can not provide you the JSON file because of confidential data, so can you please create a sample using "Serenity Application Template" and add this table to database (default database generated by app)

CREATE TABLE [dbo].[PivotClient] (
    [Id]               INT        NOT NULL,
    [DateCtrl]         DATETIME   NOT NULL,
    [Ctrl]             NCHAR (50) NOT NULL,
    [m]            NCHAR (50) NOT NULL,
    [Customer]         NCHAR (50) NOT NULL,
    [Quantity]         INT        NOT NULL,
    [ErrorDescription] NCHAR (50) NOT NULL,
    [Section]          NCHAR (50) NOT NULL,
    [nb1]              INT        NOT NULL,
    [nb2]              INT        NOT NULL,
    [nb3]              INT        NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Add soma data and extract them to PivotClient.

Regards,

Anis.

[Sastha Prathap Selvamoorthy]

Hi Anis, 

 

We have prepared a sample which load JSONData from the SQL Server. Please find the sample and SQL Table with screen shot in the below links. 

 

Sample Link: http://www.syncfusion.com/downloads/support/forum/127738/ze/DataBinding-201701720 

 

SQL Table: http://www.syncfusion.com/downloads/support/forum/127738/ze/SQLQuery2-380026478 

 

Screen shot: 

 

 

 

Please let us know if you have any concern. 

 

Regards, 

Sastha Prathap S. 

[Anis]

Thank you, that's what I was looking for.

[Sastha Prathap Selvamoorthy]

Hi Anis, 

  

Thanks for the response. 

  

Please let us know if you need any further assistance. 

  

Regards, 

Sastha Prathap S.