Master-Detail Report with Groups

[a8864]

Hi,

I'm trying to create a summary report that have grouping on both Master data and Detail data and I'll need to sum the data of Detail based on Master grouping.

I have try to create a similiar report with stimulsoft demo data where Master data is Order and Detail data is Order Detail, my Master is group by OrderDate and Detail is group by ProductID. On OrderDate 14/09/2008 the report produce 2 sets of grouping because there are 2 orders on that particular date, is there anyway to show only 1 detail grouping?

Thank you.

[Alex K.]

Hello,

Please check the modified report in the attachment.

Thank you.

[a8864]

Hi Aleksey, thank you so much for your reply.

I have tried your modified report and it does only produce 1 set of grouping for Detail data, however it does not sum up correctly. On 14/09/2008 ProductID 24 should only appear once and the Quantity should be 18 and Total should be 64.8, any solution for this?

Thanks again.

[HighAley]

Hello.

We have mode some changes in the report template.
Now the grouping for order details works right.

Thank you.

[a8864]

Hi Aleksey, thanks again for your reply.

The new report that you provide is able to work with Master Data with 1 Detail Data but I can't figure out how to modified it for multiple Detail Data.

I have attached a modified Demo data where I have added a new table which is Order Payment. 1 Order can have multiple detail as well as multiple payment and it's link by OrderID, I have also added 2 new columns in the Order table which is DetailAmount and PaymentAmount and it's the sum of the detail amount and payment amount respectively based on the OrderID.

Finally I have attached a screenshot for my expected result.

Sorry for all the trouble.

Thank you.

[HighAley]

Hello.

To understand the problem with master-detail scheme we make some changes in your report template.

SummaryReport_modified.mrt

(33.12 KiB) Downloaded 267 times

You could see that groups a created by Orders. you need to show details for group but the details are shown for each order. So if there is the same ProductID for different orders they are do not summed.

Could you describe your full task? For your last case the template can not be created in our way.
Here is our next solution for you.

SummaryReport_modified3.mrt

(28.75 KiB) Downloaded 267 times

Thank you.

[a8864]

Hi Aleksey,

The Summary3.png in my previous post is already my desired final result, but please let me elaborate in detail.

Order - Added 2 new column DetailAmount and PaymentAmount which is the sum amount of Order Detail and Order Payment based on Order ID, this 2 columns is use to check whether there are any difference between Master and Detail. Current sample is group by OrderDate but it might group by other column as well such as CustomerID, ShipRegion or ShipCountry.

Order Detail - 1 Order may contain multiple item, need to group by ProductID and also Master Grouping.

Order Payment - 1 Order may contain multiple item, need to group by PaymentID(CASH, CARD, VOUCHER, etc) and also Master Grouping.

So in the end I'll need to create a summary report that has grouping on all Master and Detail data and Detail data should not produce duplicate record and sum up based on Master grouping and also Detail own grouping, please refer the screenshot below and also Summary3.png for clearer picture.

Thank you.

[HighAley]

Hello.

You could move Panels on the Group Footer and add summing to Orders data band.
If you need more detailed help, please, write to support@stimulsoft.com.

Thank you.

[a8864]

Hi Aleksey, thanks for all your help, I'll see what other alternative I have.

Thank you.

[HighAley]

Hello.

We are always glad to help you.
Let us know if you need any additional help.

Thank you.