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

479

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!

All responses (4)

Answers (0)

Dixonsamuel 60 on at

Like (0)

Report

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

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.
Suggested answer

Nitin Verma 21,569 Moderator on at

Like (0)

Report

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

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
Suggested answer

YUN ZHU 85,674 Super User 2025 Season 1 on at

Like (0)

Report

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

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
Suggested answer

Inge M. Bruvik 1,029 Moderator on at

Like (0)

Report

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

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.