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. Using JSON as Data Source and configuring PivotGrid ASP.Net MVC 5 C#

Using JSON as Data Source and configuring PivotGrid ASP.Net MVC 5 C#

Good day,

I have a couple of REST Services in my application. One specifically returns this data:

Sample:
[
  {
    "AnswerText": "sample string 1",
    "AnswerValue": 2.1,
    "EntryDateTime": "2017-07-06T09:27:38.5933208+02:00",
    "FormEntryId": "7d84785a-9ee9-4911-bf54-6afec99ea26c",
    "UserId": "29660401-272f-47fc-b3b6-c86fed354649",
    "QuestionFormId": "ec909cff-eea8-4469-9f36-ecb7a9919e87",
    "QuestionText": "sample string 7",
    "QuestionId": "35ffd723-6c72-4a48-9299-5dbb02376482",
    "AnswerId": "077c05e7-85bb-40dc-9654-5b0c46e9e8fc"
  },
  {
    "AnswerText": "sample string 1",
    "AnswerValue": 2.1,
    "EntryDateTime": "2017-07-06T09:27:38.5933208+02:00",
    "FormEntryId": "7d84785a-9ee9-4911-bf54-6afec99ea26c",
    "UserId": "29660401-272f-47fc-b3b6-c86fed354649",
    "QuestionFormId": "ec909cff-eea8-4469-9f36-ecb7a9919e87",
    "QuestionText": "sample string 7",
    "QuestionId": "35ffd723-6c72-4a48-9299-5dbb02376482",
    "AnswerId": "077c05e7-85bb-40dc-9654-5b0c46e9e8fc"
  }

]

I am simply trying to do the following with the PivotGrid

  • Bind to API Service (client or server side, the API service has several optional parameters)
  • Display available field
  • Drag and drop fields to Columns/Rows
  • Create calculated columns (using the 'AnswerValue' field value from the JSON, this could be accross multiple records with different 'QuestionId' values)
  • Grouping and Filtering
  • Export to Excel (or CSV)
  • Saving and Loading the grid configurate to/from MS SQL Server database (not I am making use of EF 6 and DTOs). This is because the data will be used to set up other reports
Any assistance in this would greatly be appreciated. The documentation on the PivotGrid does not provide me with the answers I am looking for and the Samples on this Control has so much going on, and there is no documentation on the classes and methods used, so I am flying blind.
Thanks in advance


1 Reply
SP Sastha Prathap Selvamoorthy Syncfusion Team July 7, 2017 12:21 PM UTC

Hi Johann,   
   
Thanks for using Syncfusion products.   
   
Please find the response below.   
   
I have a couple of REST Services in my application. One specifically returns this data:   
Sample:   
[   
  {   
    "AnswerText": "sample string 1",   
    "AnswerValue": 2.1,   
    "EntryDateTime": "2017-07-06T09:27:38.5933208+02:00",   
    "FormEntryId": "7d84785a-9ee9-4911-bf54-6afec99ea26c",   
    "UserId": "29660401-272f-47fc-b3b6-c86fed354649",   
    "QuestionFormId": "ec909cff-eea8-4469-9f36-ecb7a9919e87",   
    "QuestionText": "sample string 7",   
    "QuestionId": "35ffd723-6c72-4a48-9299-5dbb02376482",   
    "AnswerId": "077c05e7-85bb-40dc-9654-5b0c46e9e8fc"   
  },   
  {   
    "AnswerText": "sample string 1",   
    "AnswerValue": 2.1,   
    "EntryDateTime": "2017-07-06T09:27:38.5933208+02:00",   
    "FormEntryId": "7d84785a-9ee9-4911-bf54-6afec99ea26c",   
    "UserId": "29660401-272f-47fc-b3b6-c86fed354649",   
    "QuestionFormId": "ec909cff-eea8-4469-9f36-ecb7a9919e87",   
    "QuestionText": "sample string 7",   
    "QuestionId": "35ffd723-6c72-4a48-9299-5dbb02376482",   
    "AnswerId": "077c05e7-85bb-40dc-9654-5b0c46e9e8fc"   
  }]   
We suspect that your requirement is to display the PivotGrid control using data source obtained from the REST service. If so, you can simply render the PivotGrid using “$.ajax()” call in the load event. Please find the sample code snippet for your reference below.   
   
CSHTML:   
///..   
   
@Html.EJ().Pivot().PivotGrid("PivotGrid1”).ClientSideEvents(clientSideEvents => clientSideEvents.Load("onLoad"))   
         <script type="text/javascript">   
             function onLoad(args) {   
                 $.ajax({   
                     type: "POST",   
                     dataType: "json",   
                     async: false,   
                     contentType: "application/json; charset=utf-8",   
                     url: "",  //specify the url of data source that return   
                     success: function (result) {   
                         args.model.dataSource.data = result;  //returned datasource   
                     },   
                     error: function (jqXHR, textStatus, errorThrown) {   
                         alert(“Error”);   
                     }   
                 });   
             }   
     </script>   
   
Meanwhile, we have prepared a simple PivotGrid sample with the provided data source for your reference. Please find the sample in the below link for your reference.   
   
   
I am simply trying to do the following with the PivotGrid   
  • Bind to API Service (client or server side, the API service has several optional parameters)
  • Display available field
  • Drag and drop fields to Columns/Rows
  • Create calculated columns (using the 'AnswerValue' field value from the JSON, this could be accross multiple records with different 'QuestionId' values)
  • Grouping and Filtering
  • Export to Excel (or CSV)
  • Saving and Loading the grid configurate to/from MS SQL Server database (not I am making use of EF 6 and DTOs). This is because the data will be used to set up other reports
Any assistance in this would greatly be appreciated. The documentation on the PivotGrid does not provide me with the answers I am looking for and the Samples on this Control has so much going on, and there is no documentation on the classes and methods used, so I am flying blind.   
   
Please find the document details based on your requirements individually.    
   
  • Bind to API Service (client or server side, the API service has several optional parameters)
Refer the same attached above.   
  • Display available field
  • Drag and drop fields to Columns/Rows
To display all the fields by using the “Pivot Table Field List”. Also, you can drag/drop field from the TreeView to rows/columns/values/filters. Please find the document link below.   
   
   
·       Create calculated columns (using the 'AnswerValue' field value from the JSON, this could be accross multiple records with different 'QuestionId' values)   
You can enable the calculated fields by right clicking the pivot buttons in GroupingBar. Please find the document link below.   
   
   
  • Grouping and Filtering
You can group the fields by using “enalbeGroupingBar” property that contains sorting/filtering options for each field to display the records accordingly. Please find the document link below.   
   
   
   
  • Export to Excel (or CSV)
You can simply export the PivotGrid to Excel as well as to CSV documents using either JSON or Pivot Engine. Please find the document link below.   
   
JSON Export:   
   
PivotEngine Export:   
·       Saving and Loading the grid configurate to/from MS SQL Server database (not I am making use of EF 6 and DTOs). This is because the data will be used to set up other reports   
To save and load the report to/from SQL database. Please find the document link below.   
   
   
   
   
Regards,   
Sastha Prathap S.  

Loader.
Live Chat Icon For mobile
Up arrow icon