reporting-services ssrs-tablix dynamics-ax-2012-r3

Getting a subtotal in an SSRS report

I have what ought to be a very simple issue, but I can't seem to solve it.

I have a master/detail report. It's actually two reports. One for the master records and then a subreport for the details. This is actually in Dynamics AX (which is why this setup and some of the limitations I'll describe may seem a bit odd). But the issue I'm having is an SSRS issue.

Because of the way the Dynamics AX stuff is set up, my reports are based on 2 tables. Table 1 is a 'select * from table1' and Table 2 is 'select * from table2'. For reasons of the way it's setup in AX, there is no where clause. It's simply reporting off of the full contents of these tables.

So this is my setup for the first table. The products list and then the row below has the subreport.

The second table is as follows:

Table 2 has the details, which are orders that have that product on them. What we're trying to do is see how many cases we have orders for, versus what we have in stock.

Table 2 has all the data for all the products. The query that the subreport is based on is select * from table2. There is no where clause (this is based on how it's set up in AX. I can't filter in the where clause, in this case).

So to handle the filtering, I create the group on ProductCode and then add this filter:

Up to this point, everything in my report works perfectly. It displays everything exactly the way I want. My details list all the orders associated with each product.

The trick now is that I want a sub-total in the details of all the cases ordered for that product. But nothing I do seems to work right.

If I simply right click on the OrderQty field for the details and click "Add Total", it gives me the total of all products from all orders, in each subreport. So, for example, a detail list for a product might show 2 order with quantities of 100 and 20 and should show a subtotal of 120, but instead shows a subtotal of say 40,000, which would be the sum of all the details for all the products in the report. And it will show that same 40,000 subtotal in every detail list for every product.

Alternatively, if I add a row inside the group, after, and add a Sum() for the CaseCount, after each individual detail row, it gives me a subtotal that matches the count of the detail row. So, for example, I might have Product 130 with 2 orders, for 120 and 20. Each row will have a subtotal. The first, with 100, and the second with 20, instead of a single subtotal after both rows, of 120.

This seems like it ought to be ridiculously simple, but I'm stumped.

I would ask that you try to provide a solution that doesn't require re-architecting the report as a whole, if possible. We're on a time crunch here and that would cause some problems.

No doubt I'm missing some key info, so please let me know what else you might need to know to help me solve this.

Update

Chance Finley below suggested Sum(field, groupname), and I tried that. It actually does the sum properly, but it displays it improperly.

Here's what it looks like in Visual Studio...

Update 2

If I add the total outside the group, as shown here:

I get the following results:

Which puts my sum right where I want it, but it's counting all the products.

Update 3

And here's another one. This one gives the right results, but then throws in other stuff at the end that I don't want. Here's the setup in the SSRS designer:

And here's how it comes out: After showing all the subtotals, it then shows second product (0227) and then shows a list of all the subtotals that follow it (except missing 0130). The third shows all subtotals except 0130 and 0227.. And so on...

Update 4

Following the steps provided by CuriousKid, I got closer...

The setup following his steps (not all fields filled in yet):

And the results:

Problem is that it's all product results shown in details under each product.

Solution

Here is what you need to do. Please check if any of the config is missing in your design.(I feel there is some issue in your grouping config but cannot verify without the row group details provided in the Question)

Step 1:

Add the columns: