Displaying Barchart dynamically based on particular grouping in asp.net core razor pages

Question

I have a requirement like, should display no:of employees in particular group in bar chart.At first i need to group the employees into two groups, after that based on the function i need to display the bar chart.

Currently i am not able to display the Supporting Group barchart.Please find my code below.

Report.cshtml

<div class="row mt-3 mb-3">
        @for (int i = 0; i < Model.GetDistinctEmpGrp.Count(); i++)
        {
            <div class="col-6">
                <div class="card" style="background: rgba(0,0,0,0.6);">
                    <div class="card-header-pills rounded-3 p-2  text-white" style="position:absolute;width:300px;margin-left:20px;margin-top:-20px;background-image:linear-gradient(to right,#f1b52c,#ce6d28)">
                        @Model.GetDistinctEmpGrp[i].EmpGrp
                    </div>
                    <div class="card-body">                   
                        <div class="row mt-5">
                            <div class="col-12">
                                <canvas id="barChart" style="width:100%;max-width:700px"></canvas>
                            </div>
                        </div>
                    </div>                
                </div>            
            </div>
        }
    </div>
    <script>    
        function getRandomColor(n) {
            var letters = '0123456789ABCDEF'.split('');
            var color = '#';
            var colors = [];
            for (var j = 0; j < n; j++) {
                for (var i = 0; i < 6; i++) {
                    color += letters[Math.floor(Math.random() * 16)];
                }
                colors.push(color);
                color = '#';
            }
            return colors;
        }
    
        var xValues = [@Html.Raw("'" + String.Join("','", (Model.GetDistinctFunction.Select(x => x.FuncName).ToList())) + "'")];
        var yValues = [@Html.Raw("'" + String.Join("','", (Model.GetDistinctFunction.Select(x => x.EmpCount).ToList())) + "'")];
        var barColors = getRandomColor([@Html.Raw(Model.GetDistinctFunction.Count)])
        new Chart("barChart", {
            type: "bar",
            data: {
                labels: xValues,
                datasets: [{
                    backgroundColor: barColors,
                    data: yValues
                }]
            },
            options: {
                skipNull: true,
                legend: { position: 'right' },
                title: {
                    display: true,
                    text: "TOTAL NUMBER OF EMPLOYEES"
                }
            }
        });
    </script>

Report.cshtml.cs

 public async Task OnGetAsync(int CustomerId)
            {
                var CustName = _context.Customer.Where(e => e.Id == CustomerId).ToList();
                CustomerName = CustName[0].CustomerName;
                GetDistinctEmpGrp = _context.EmployeeGroup.FromSqlRaw("sp_DistinctEmployeeGroup {0}", CustomerName).ToList();
                EmployeeGroupName = GetDistinctEmpGrp[0].EmpGrp;
    
                List<SqlParameter> parms = new List<SqlParameter>
                { 
                    // Create parameters    
                    new SqlParameter { ParameterName = "@CName", Value = CustomerName },
                    new SqlParameter { ParameterName = "@EGName", Value = EmployeeGroupName }
                };
                GetDistinctFunction = _context.DistinctFunction.FromSqlRaw("sp_DistinctFunction @CName,{1}", parms.ToArray()).ToList();
            }

Do i need two stored procedures to handle this? If not how can i handle with a single stored procedure? Appreciate help on this.

Thanks,

Teena John

Answer 1

Hi @Blooming Developer

Currently i am not able to display the Supporting Group barchart.Please find my code below. <canvas id="barChart" style="width:100%;max-width:700px"></canvas>

This issue might relate the id attribute.

In your view page, you are using for-each statement to display multiple canvases, but they have the same id attribute. So, when display the bar chart to the canvas (it seems that you are using JavaScript to find the canvas based on the id attribute), the javascript script just find the first canvas, and only the first canvas displays the chart.

To solve this issue, the canvas id attribute should be dynamic, try to use the following code:

@for (int i = 0; i < Model.GetDistinctEmpGrp.Count(); i++)
{
    <div class="col-6">
        <div class="card" style="background: rgba(0,0,0,0.6);">
            <div class="card-header-pills rounded-3 p-2  text-white" style="position:absolute;width:300px;margin-left:20px;margin-top:-20px;background-image:linear-gradient(to right,#f1b52c,#ce6d28)">
                @Model.GetDistinctEmpGrp[i].EmpGrp
            </div>
            <div class="card-body">
                <div class="row mt-5">
                    <div class="col-12">
                        @{
                            var dynamicid = $"barChart{i}";
                        }
                        <canvas id="@dynamicid" style="width:100%;max-width:700px"></canvas>
                    </div>
                </div>
            </div>
        </div>
    </div>
}

After that, if using F12 developer tools to check the html elements, you can see the id attribute value should like this: barChart0, barChart1...

Then, when create new Chart in the JavaScript, the relate canvas id attribute should like this: barChart0, barChart1... You might also need to use for each statement to loop the group count.

Do i need two stored procedures to handle this? If not how can i handle with a single stored procedure? Appreciate help on this.

As far as I know, EF core doesn't support Multiple Result Sets from Stored Procedure, you might need to use ADO.Net. Or create multiple stored procedures to handle it.

Update:

You can refer the following sample code:

ChatIndex.cshtml.cs: according to the data table, I create a DistinctFunctionViewModel and use the following code to set the initial data.

    public class ChartIndexModel : PageModel
    {
        [BindProperty]
        public List<DistinctFunctionViewModel> DistinctFunction { get; set; }
        public void OnGet()
        {
            DistinctFunction = new List<DistinctFunctionViewModel>()
            {
                new DistinctFunctionViewModel(){ FuncName="Analyzer", EmpGrp="Production", EmpCount=21},
                new DistinctFunctionViewModel(){ FuncName="DRC", EmpGrp="Production", EmpCount=3},
                new DistinctFunctionViewModel(){ FuncName="RGA", EmpGrp="Production", EmpCount=14},
                new DistinctFunctionViewModel(){ FuncName="Simulator", EmpGrp="Production", EmpCount=18},
                new DistinctFunctionViewModel(){ FuncName="Master Tech", EmpGrp="Supporting Group", EmpCount=4},
                new DistinctFunctionViewModel(){ FuncName="Material Controller", EmpGrp="Supporting Group", EmpCount=10},
                new DistinctFunctionViewModel(){ FuncName="SuperMarket", EmpGrp="Supporting Group", EmpCount=12}, 
            };
        }
    }

ChartIndex.cshtml page: In the JavaScript script, after getting the objects from the Page Model, we can use the filter and map method to filter data and get the xValue and yValues.

@page "/chartindex"
@model RazorWebApp.Pages.ChartIndexModel
<div class="row mt-3 mb-3">
    @{
        var groups = Model.DistinctFunction.GroupBy(c => c.EmpGrp).Select(c => c.First().EmpGrp).ToList(); 
    }
    @for (int i = 0; i < groups.Count(); i++)
    {
        <div class="col-6">
            <div class="card" style="background: rgba(0,0,0,0.6);">
                <div class="card-header-pills rounded-3 p-2  text-white" style="position:absolute;width:300px;margin-left:20px;margin-top:-20px;background-image:linear-gradient(to right,#f1b52c,#ce6d28)">
                    @groups[i]
                </div>
                <div class="card-body">
                    <div class="row mt-5">
                        <div class="col-12">
                            @{
                                var groupid = $"barChart{i}";
                            }
                            <canvas id="@groupid" style="width:100%;max-width:700px"></canvas>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    }
</div>

@section scripts{
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.9.4/Chart.js">
    </script>
    <script>
        function getRandomColor(n) {
            var letters = '0123456789ABCDEF'.split('');
            var color = '#';
            var colors = [];
            for (var j = 0; j < n; j++) {
                for (var i = 0; i < 6; i++) {
                    color += letters[Math.floor(Math.random() * 16)];
                }
                colors.push(color);
                color = '#';
            }
            return colors;
        } 
        //get the groupname
        var groupname = [@Html.Raw("'" + String.Join("','", (Model.DistinctFunction.GroupBy(c => c.EmpGrp).Select(c => c.First().EmpGrp).ToList()))+"'" )];
        //get the object list from page model.
        var data = @Html.Raw(System.Text.Json.JsonSerializer.Serialize(Model.DistinctFunction.ToList()));

        for (var i = 0; i < groupname.length; i++) {
            var xValues = data.filter(c => c.EmpGrp == groupname[i]).map(c => c.FuncName);
            var yValues = data.filter(c => c.EmpGrp == groupname[i]).map(c => c.EmpCount);
            var barColors = getRandomColor([@Html.Raw(Model.DistinctFunction.GroupBy(c => c.EmpGrp).Count())]);
            new Chart("barChart" + i.toString(), {
                type: "bar",
                data: {
                    labels: xValues,
                    datasets: [{
                        backgroundColor: barColors,
                        data: yValues
                    }]
                },
                options: {
                    skipNull: true,
                    legend: { position: 'right' },
                    title: {
                        display: true,
                        text: "TOTAL NUMBER OF EMPLOYEES"
                    }
                }
            });
        }
    </script>
}

Then the output as below:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion