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. Update Datasource in Javascript and Column Formatting

Update Datasource in Javascript and Column Formatting

I have two questions:

1) How do I update the datasource via ajax. Using a Grid control I am able to do something like this:

success: function(result) {
$("#Grid").ejGrid("dataSource", ej.parseJSON(result));
}

2) I am allowing the user to customize the PivotGrid through the FieldList/PivotSchemaDesigner. I understand that you can format columns with the following:

.Values(values =>
{
values.FieldName("EnterFillAmount").Format("currency").Add();
values.FieldName("EnterCommissionAmount").Format("currency").Add();
values.FieldName("RealizedReturn").Format("currency").Add();
values.FieldName("UnrealizedReturn").Format("currency").Add();
values.FieldName("TotalReturn").Format("currency").Add();
})))

When a value field is removed from the grid and then readded the format appears to be lost - is there a way to retain this formatting?

I have quite a few fields that may be put into the Value columns (several more than in the above .Values statement - in fact, I would want to remove most of the fields in the above "Values" statement but it was the only way I could figure out to apply a format). How can I set the format without having to have it added to the grid at design time. That is, let say, a user now drags a column called "SlippageAmount" onto the values part of the pivot table - How can I have that formatted as a currency?

Where are the types of format strings defined? I tried using {0:N2} - which works on the Grid control but that didn't appear to work on the pivot grid.

Regards, Jeff


9 Replies
SP Sastha Prathap Selvamoorthy Syncfusion Team April 25, 2016 12:31 PM UTC

Hi Jeffrey, 
 
Thank you for contacting Syncfusion support. 
 
1)     In the success method of the jQuery Ajax post, you can populate the Grid content either using the set model or dataSource method of ejGrid. Refer to the following code example.  
 
@Html.EJ().Button("ClickMe").Text("Populate Grid Content").ClientSideEvents(e => e.Click("btnClick")) 
 
@(Html.EJ().Grid<MvcApplication66.OrdersView>("FlatGrid") 
.AllowPaging() 
.Columns(col => 
{ 
col.Field("OrderID").Add(); 
col.Field("CustomerID").Add(); 
col.Field("EmployeeID").Add(); 
col.Field("OrderDate").Format("{0:MM/dd/yyyy}").Add(); 
col.Field("Freight").Format("{0:C3}").Add(); 
}) 
) 
<script> 
function btnClick(args) { 
$.ajax({ 
type: "POST", 
url: "/Home/Data/", 
contentType: "application/json; charset=utf-8", 
dataType: "json", 
success: function (data) { 
var formatData = ej.parseJSON(data); //after success, parsing the data to convert the date Object 
//$("#FlatGrid").ejGrid({ dataSource: formatData }); //updating the dataSource through setModel 
var gridObj = $("#FlatGrid").ejGrid("instance"); 
gridObj.dataSource(formatData);//dataSource method 
} 
}); 
} 
</script> 
 
public ActionResult Data() 
{ 
var data = new NorthwindDataContext().OrdersViews.ToList(); 
return Json(data); 
} 
 
In the click event of ejButton, we have repopulated the Grid content. Refer to the following Help Document.  
 
 
 
2)      We have created an incident to log a feature report on behalf of you. Our support engineer will assist you through incident. We request you to follow with the incident for further details.
 
https://www.syncfusion.com/account/login?ReturnUrl=/support/directtrac/incidents 

Regards, 
Sastha Prathap S. 


JS Jeffrey Stone April 25, 2016 05:39 PM UTC

Thanks for the quick response and I apologize that it appears that my question was unclear. I am able to update the datasource for a Grid - I am unable to update it for a PivotGrid.  Would you be able to provide a similar example of updating the PivotGrid datasource via Ajax.

Regards, Jeff

SP Sastha Prathap Selvamoorthy Syncfusion Team April 26, 2016 12:50 PM UTC

Hi Jeffrey, 

Thank you for your update. 

You could update the PivotGrid in ajax success as already provided for ejGrid in the previous update. We have provided sample code/prototype model for your reference, so you could load the JSON data through ajax success as follows,  

Code snippet [JavaScript]: 
     ajaxSuccess: function (data) {     
 
         var formatData = ej.parseJSON(data); //after success, parsing the JSON string as data Object  
 
         //update the data object: For example interchanging the Column and Row fieldNames, assigning formats for value field as follows 
 
         $("#PivotGrid").ejPivotGrid ({ 
             dataSource: { 
                 data: formatData, 
                 rows: [ 
                         { 
                             fieldName: "Products", 
                             fieldCaption: "Products" 
                         }, 
                         { 
                             fieldName: "State", 
                             fieldCaption: "State" 
                         } 
                     ], 
                 columns: [ 
                         { 
                             fieldName: "Country", 
                             fieldCaption: "Country" 
                         } 
                     ], 
                 values: [ 
                         { 
                             fieldName: "Amount", 
                             fieldCaption: "Amount", 
                             format: "currency", 
                         }, 
                         { 
                             fieldName: "Quantity", 
                             fieldCaption: "Quantity", 
                             format: "decimal" 
                         } 
                     ] 
                 } 
             }); 
         } 

Please check JSPlayground and user guide documentation to check the input model for initialize the PivotGrid. 



Regards, 
Sastha Prathap S. 
 


JS Jeffrey Stone May 2, 2016 02:38 PM UTC

Sastha - Thanks for the response. I have the initial population of it working through ajax; however, subsequent ones don't work. How can I update the same PivotTable through ajax multiple times? I am also displaying the fieldlist allowing the user to customize the PivotGrid so I would want to refresh the data for the grid - without having to specify the rows/columns/values as the user may have modified these on screen.

Regards, Jeff.

RG Ramesh Govindaraj Syncfusion Team May 3, 2016 09:00 AM UTC

Hi Jeffrey, 

The code under “ajaxSuccess” method binds the report initially and you could modify the report through UI (PivotTable Field List). If the same method fires again, the same report will be bounded with the given data but the positions (row/column/values/filter) of the fields won’t be maintained (modifications done by PivotTable Field List).  

So, we have prepared a PivotGrid prototype where a button “Refresh PivotGrid”  has been included. If the button is clicked, the data alone will be refreshed and the position of fields will be maintained (won’t be changed). Please find the prototype in the following link. 


Regards,
Ramesh G. 
 


JS Jeffrey Stone May 10, 2016 01:21 AM UTC

The logic worked perfectly! Thanks! Jeff

For others with the same issue here is the code to refresh the dataSource of the PivotGrid without having to respecify the rows, columns, values:

var pivotGrid = $("#PivotGrid1").data("ejPivotGrid");
pivotGrid.model.dataSource.data = pivot_dataset_refresh;
pivotGrid._populatePivotGrid();

RG Ramesh Govindaraj Syncfusion Team May 10, 2016 04:30 AM UTC

Hi Jeffrey, 

Thanks for the update. 

Please let us know if you have any query. 

Regards,
Ramesh G. 


NO Nestor Ochoa February 18, 2019 05:29 AM UTC

Hi All, This is really helpful solution, but if we have a query to update as well, where should we set this query?

gridObj.query = newQuery

gridObj.dataSource(formatData);

VN Vignesh Natarajan Syncfusion Team February 19, 2019 11:33 AM UTC

Hi Nestor, 
Thanks for the update. 
  
Query: “but if we have a query to update as well, where should we set this query?” 
 
We have checked your requirement and if you want to pass the query in addition to dataSource method you can define the query using ej.Query() of ejDataManager with executeLocal method. 
 
Please refer to the code example:- 
 
<script> 
    function btnClick(args) { 
        $.ajax({ 
            type: "POST", 
            url: "/Grid/Data", 
            contentType: "application/json; charset=utf-8", 
            dataType: "json", 
            success: function (data) { 
                var formatData = ej.parseJSON(data);  
                var gridObj = $("#FlatGrid").ejGrid("instance"); 
                var query = ej.Query().sortBy("ShipCity", ej.sortOrder.Ascending, false); 
                var dataManager = ej.DataManager(formatData).executeLocal(query); 
                gridObj.dataSource(formatData);//dataSource method 
             
            } 
        }); 
    } 
</script> 
 
Refer to the API link:- 
 
Note:- executeLocal method works only for LocalData. If you want to use remoteData, you can use executeQuery method.  
 
Refer to the documentation Link:- 
 
 
Please get back to us if you need any further assistance. 
 
Regards, 
Vignesh Natarajan. 
 


Loader.
Live Chat Icon For mobile
Up arrow icon