How to check the example data source for pivot grid?

[Grace]

Hi,

I am following this https://github.com/telerik/kendo-ui-core/blob/master/docs/api/javascript/data/pivotdatasource.md to build a pivot grid. 

How can I check the data source that is used in the following read method? I want to take a look at the structure and build my own olap data source. But when I click the link, it gives 500 error.

<script>
var dataSource = new kendo.data.PivotDataSource({
  type: "xmla",
  measures: {
      values: ["[Measures].[Internet Order Lines Count]", "[Measures].[Days Current Quarter to Date]"],
      axis: "rows"
  },
  transport: {
    connection: {
        catalog: "Adventure Works DW 2008R2",
        cube: "Adventure Works"
    },
    read: "https://demos.telerik.com/olap/msmdpump.dll"
  },
  schema: {
    type: "xmla"
  }
});
dataSource.fetch();
</script>

[Alex Hajigeorgieva]

Hello, Grace,

The service that we have accepts XMLA formatted requests and it is expected that it cannot be accesses directly from the browser.

The hosted service is a compiled dll along with some web.config files. I am attaching it for your reference, however to build your own cube, I would suggest checking the forum post that includes the ADOMD project that may be more useful to you:

https://www.telerik.com/forums/securing-access-to-msmdpump-dll

Together with the documentation article we have:

https://docs.telerik.com/kendo-ui/controls/data-management/pivotgrid/binding/olap-cube-setup

Let me know in case I may assist you further.

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Grace]

Thanks a lot. Those are very helpful resources.

In my situation, I also need to pass parameters to the cube to filter the data based on some conditions so that only certain data will be shown in the pivot grid. How should I achieve this using Kendo jQuery pivot grid with olap data source? Can you give me an example?

[Alex Hajigeorgieva]

Hi, Grace,

To display a subset of the data, you can use the PivotDataSource  with filters:

https://docs.telerik.com/kendo-ui/controls/data-management/pivotgrid/filtering

Regards,
Alex Hajigeorgieva
Progress Telerik

[Grace]

Could you please give me an example of using kendo jQuery pivotGrid to bind flat dataSource?

I am checking this jQury pivotGrid here https://dojo.telerik.com/ATUtOKay but it is OLAP binding, and I couldn't find an example of jQuery pivotGrid binding flat data. 

[Alex Hajigeorgieva]

Hello,

You can find the example in in the demos under local binding:

https://demos.telerik.com/kendo-ui/pivotgrid/local-flat-data-binding

Kind Regards,
Alex Hajigeorgieva
Progress Telerik

[Grace]

Hi Alex,

How should I sort the data based on both columns and rows when binding Ajax data using MVC pivotGrid?

I tried the below option, but it doesn't seem to be sorting based on two dimensions at the same time. 

public class TransactionViewModel
{
    public string Category { get; set; }
    public string Product { get; set; }
    public string Country { get; set; }
    public string State { get; set; }
    public decimal? Quantity { get; set; }
    public int CategorySortOrder { get; set; }
    public int ProductSortOrder { get; set; }
    public int CountrySortOrder { get; set; }
    public int StateSortOrder { get; set; }

}

 

@(Html.Kendo().PivotGrid<MyProject.Web.ViewModels.TransactionViewModel>()
    .Name("pivotgrid")
    .Filterable(true)
    .ColumnWidth(65)
    .Height(585)
    .DataSource(dataSource => dataSource
        .Ajax()
        .Transport(transport => transport.Read(read => read.Action("Transaction_Read", "Home").Data("SearchValues")))
        .Schema(schema => schema
            .Cube(cube => cube
                .Dimensions(dimensions =>
                {
                    dimensions.Add(model => model.Category).Caption("Categories");
                    dimensions.Add(model => model.Product).Caption("Products");
                    dimensions.Add(model => model.Country).Caption("Countries");
                    dimensions.Add(model => model.State).Caption("States");
                })
                .Measures(measures => measures.Add("Quantity Sum").Field(model => model.Quantity).AggregateName("sum").Format("{0:n2}"))
            ))
        .Columns(columns =>
        {
            columns.Add("Category").Expand(true);
            columns.Add("Product");

        })
        .Rows(rows =>
        {
            rows.Add("Country").Expand(true);
            rows.Add("State");
        })
        .Measures(measures => measures.Values("Quantity Sum"))
        .Sort(sort =>
            {
                //We have a specific order requirement for all of them. Ideally, it would sort based on CategorySortOrder, then by ProductSortOrder for rows
                // and sort based on CountrySortOrder, then by StateSortOrder for columns
                sort.Add("CategorySortOrder").Ascending();
                sort.Add("ProductSortOrder").Ascending();
                sort.Add("CountrySortOrder").Ascending();
                sort.Add("StateSortOrder").Ascending();
            }
        )
    )
)

[Preslav]

Hello Grace,

Let's keep the thread regarding the MVC PivotGrid sorting in the following forum - https://www.telerik.com/forums/column-sorting-a-z

 

Regards,
Preslav
Progress Telerik