We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
  1. Home
  2. Forum
  3. ASP.NET MVC
  4. PivotGrid Data Binding From Database

PivotGrid Data Binding From Database

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

(username: admin, passwd: serenity)

Regards,
Anis

12 Replies
AN Anis December 7, 2016 06:48 AM UTC

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

(username: admin, passwd: serenity)

Regards,
Anis

RG Ramesh Govindaraj Syncfusion Team December 8, 2016 11:26 AM UTC

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. 


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 


AN Anis December 8, 2016 03:32 PM UTC

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

RG Ramesh Govindaraj Syncfusion Team December 9, 2016 09:12 AM UTC

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. 


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 


AN Anis December 15, 2016 01:40 PM UTC

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

SP Sastha Prathap Selvamoorthy Syncfusion Team December 16, 2016 11:20 AM UTC

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. 

 
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, 


AN Anis December 16, 2016 04:04 PM UTC

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):


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

SP Sastha Prathap Selvamoorthy Syncfusion Team December 19, 2016 12:19 PM UTC

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. 


AN Anis December 19, 2016 01:54 PM UTC

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.



SP Sastha Prathap Selvamoorthy Syncfusion Team December 20, 2016 01:17 PM UTC

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. 
 
 
 
Screen shot: 
 
 
 
Please let us know if you have any concern. 
 
Regards, 
Sastha Prathap S. 


AN Anis December 22, 2016 07:07 AM UTC

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

SP Sastha Prathap Selvamoorthy Syncfusion Team December 23, 2016 04:08 AM UTC

Hi Anis, 
  
Thanks for the response. 
  
Please let us know if you need any further assistance. 
  
Regards, 
Sastha Prathap S. 


Loader.
Up arrow icon