Small and medium business | Business Central, N...

How to Group and then Combine Rows in AL or in SSRS?

(0) Share

Report

Posted on by jdrco

481

Say I have a table with columns: Item | Cost | Quantity | Total

How would I group rows by items, and THEN calculate the combined Cost and Quantity?

Example:
row1: Bike, $100, 1, $100
row2: Bike, $100, 2, $200
row3: Tire, $30, 1, $30

Would be combined like this (since there are two Bike items, we combine them and get the new total):
row1: Bike, $100, 3, $300
row2: Tire, $30, 1, $30

Is this easier to implement in AL or in an SSRS report builder? I am ultimately trying to display this table in a report.

Thanks!

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Inge M. Bruvik 1,105 Moderator on at

Like (0)

Report

You can do it in AL. But i really think that what you should do is to look at Power BI to solve this. Especially if you are running BC Online.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,331 Super User 2025 Season 2 on at

Like (0)

Report

Hi, it can be done in SSRS, but it looks more complicated. Use Bike(Item No?) to create a new Group, but the Unit Price looks like an average, and the Quantity and Amount look like a total.

So, I think the easiest way is to handle it in AL.

A simple practice: create a temporary table and add the fields you need. Then use this new table to create a new Report. Then insert data into the temporary table in AL, and the SSRS just displays all the data and does not require additional processing.

Jus an idea, hope this helps.

Thanks.

ZHU

Was this reply helpful? Yes No
Suggested answer

Nitin Verma 21,698 Moderator on at

Like (0)

Report

Hi jdrco ,

You can go with the SSRS reporting, please refer below link, if it help you.

docs.microsoft.com/.../lesson-6-adding-grouping-and-totals-reporting-services

Was this reply helpful? Yes No
Dixonsamuel 60 on at

Like (0)

Report

I would use SSRS to do the grouping instead.

You can use AL to form up the data set, then use SSRS to group the figures.

Was this reply helpful? Yes No