MVC Stacked Chart dynamic Series from dynamic data source

[Jon]

I have a chart that tracks website registrations by date, Country and State.  A user selects which country(ies) and state(s) they want to see and the chart should have a column for each country for each date (aggregate by year)

My current View code:

@(Html.Kendo().Chart<DatePoint>()
    .Name("RegistrationDashboard")
    .Title("Total Registrations")
    .Theme("material")
    .DataSource(ds => ds
        .Read(read => read.Action("RegistrationDashboard_Read", "Reports").Data("filterReport"))
        .Group(group =>
            group.Add(grp => grp.Country)
        )
    )
    .Series(series =>
    {
        series.Column(model => model.Value, categoryExpression: model => model.Date).Aggregate(ChartSeriesAggregate.Count).Stack("Country").Name("");
    })
    .CategoryAxis(axis =>
        axis
            .Date()
            .BaseUnit(ChartAxisBaseUnit.Years)
            .MajorGridLines(lines => lines.Visible(false))
    )
    .ValueAxis(axis => axis
        .Numeric()
        .Line(line => line.Visible(false))
    )
    .Tooltip(tooltip => tooltip.Visible(true))
    )

An example of JSON data sent from the datasource (I have a button that refreshes the data source whenever a user chooses new countries/states)

[
  {
    "Date": "/Date(1558584000000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Cundinamarca"
  },
  {
    "Date": "/Date(1562299200000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1572494400000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Antioquia"
  },
  {
    "Date": "/Date(1580533200000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Estado de Mexico"
  },
  {
    "Date": "/Date(1585281600000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Beni"
  },
  {
    "Date": "/Date(1594094400000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1594094400000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1594094400000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1594094400000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1607058000000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Antioquia"
  },
  {
    "Date": "/Date(1608008400000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Jalisco"
  },
  {
    "Date": "/Date(1608008400000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Jalisco"
  },
  {
    "Date": "/Date(1608526800000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Antioquia"
  },
  {
    "Date": "/Date(1626148800000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Queretaro"
  },
  {
    "Date": "/Date(1632801600000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1633924800000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Valle del Cauca"
  },
  {
    "Date": "/Date(1634011200000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Antioquia"
  },
  {
    "Date": "/Date(1640840400000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Aguascalientes"
  },
  {
    "Date": "/Date(1641445200000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Aguascalientes"
  },
  {
    "Date": "/Date(1642741200000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Aguascalientes"
  },
  {
    "Date": "/Date(1643691600000)/",
    "Value": 1,
    "Country": "Mexico",
    "State": "Mexico"
  },
  {
    "Date": "/Date(1650340800000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1650340800000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Santa Cruz"
  },
  {
    "Date": "/Date(1652241600000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Cochabamba"
  },
  {
    "Date": "/Date(1652328000000)/",
    "Value": 1,
    "Country": "Bolivia",
    "State": "Cochabamba"
  },
  {
    "Date": "/Date(1661140800000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Bogota"
  },
  {
    "Date": "/Date(1664856000000)/",
    "Value": 1,
    "Country": "Colombia",
    "State": "Narino"
  }
]

In this scenario, there are 3 countries returned.  For each year (these dates are between 2019 and 2022 but there could be any number of years from as far back as 2010)

I would like to have a series column for each country for each year. Each column would be stacked based on the number of registrations per state.

The user could choose from 1...N countries.  (I'm sure I can set a limit so the chart isn't out of control, but that's not the point)

When I set the datasource group to "State", there is a series column for each year and the legend shows each state. When I group by "Country", there is only 1 series column for each year and the legend shows the countries selected. I have included a screenshot of each grouping

How do I make a series column for each country with the amounts "stacked" for that country's respective states?

[Eyup]

Hi Jon,

 

I have already replied to your formal support ticket on the same matter. I suggest we continue our technical conversation there.

 

Regards,
Eyup
Progress Telerik