SSRS create summary by group without using subreport?

Question

I am building a report where I need to show summaries by group. The report shows data by product by restaurant. It also needs show data by product by district (the totals of all restaurants in a district):

Restaurant 1
  Pepperoni Pizzas: 10
  Cheese Pizzas: 12
  Ham Pizzas: 15

Restaurant 2
  Pepperoni Pizzas: 5
  Cheese Pizzas: 12
  Ham Pizzas: 20

District Totals
  Pepperoni Pizzas: 15
  Cheese Pizzas: 24
  Ham Pizzas: 25

The approach I've taken is to build a sub-report that what will show data for a store, district, etc, depending on the parameters that the master report passes it. This is combersome from a design perspective and slow at run time. Is there a way to create the district totals without using a subreport?

Answer 1

This can be done with one dataset/report and as many different food types as required. I've created some basic data:

create table Pizza
(
  district varchar(100)
  , restaurant varchar(100)
  , foodItem varchar(100)
  , quantitySold int
)

insert into Pizza
select 'District 1', 'Restaurant 1', 'Pepperoni Pizzas', 10
union all
select 'District 1', 'Restaurant 1', 'Cheese Pizzas', 12
union all
select 'District 1', 'Restaurant 1', 'Ham Pizzas', 15
union all
select 'District 1', 'Restaurant 2', 'Pepperoni Pizzas', 5
union all
select 'District 1', 'Restaurant 2', 'Cheese Pizzas', 12
union all
select 'District 1', 'Restaurant 2', 'Ham Pizzas', 20

For the report create a basic Tablix with District group (if required) and Restaurant group:

The trick here is that I merged all the cells in the last row and inserted another table into the cell, this time based on a Food group:

Because this is in the District scope it's underlying data will be based on all the rows for a particular district. End result:

This will repeat for multiple Districts. Adjust appearance as required!

Answer 2

As I said in my comment you're looking for a simple group with static content or group inside group.

To avoid using subreports you just need to group by restaurant on your first two examples, which I think you are already doing.

If your District Totals is a static content just add the rows and on the data cell put a simple sum for all your pizzas, like: [Sum(PepperoniPizzas)].

If it's grouped you should do the same, but inside your grouped data. From your question this is the second scenario you could've meant, that your whole data is grouped by district and then by restaurants, if so this is how what you do:

Have an internal group by restaurants and an external one by district with footer. In this footer you should do the same as explained above. It would look like this:

|--Group by District
|----Group by Restaurant
|------Details or another group
|----End of group by restaurant
|----
|----District Totals
|----Pepperoni Pizzas - [Sum(PepperoniPizzas)]
|----Cheese Pizzas - [Sum(CheesePizzas)]
|----Ham Pizzas - [Sum(HamPizzas)]
|--End of district group

I'm at home now so I can't show you the report, but I guess the above example is pretty understandable. Let me know if this is what you needed, if not ellaborate a little bit more. When I get to work tomorrow I'll update this answer with how the report would look like.

UPDATE

As I said, here's how your report should look like:

• The groups

• The report itself

Answer 3

I would just add a second dataset if you want the data contained seperately.

However if you want the code in the report to display a 'totals' on a 'grouping' I would add a grouping column 'Total'

EG: I have a small dataset similar to yours:

declare @Pizza table ( restaurantid int identity, pizzas int)

insert into @Pizza values (10),(15),(5);

select * from @Pizza

I create a table where the only values are: restaurantid, pizzas in the 'detail' row and they generate similar names in the header.

If you wish a totals you need to look in 'Design' view in the lower left for 'Row Groups'. Click the down arrow>Add Total>After. You now added another line that will total everything in your table thus far. You can remove cell values for the restaurant id's as they won't matter and put in something useful like 'Totals' instead.