I need help building a fairly simple report, SQL 2008R2, Report Builder 3.0

Question

My data schema is very simple. Each week a store puts in their name, the week number, the year, and their total sales. We use this data in a number of reports / spreadsheets and it has served us well for a few years now.

But today someone asked for a new report and I'm stumped on how to build it. I filter the data for the year I want (2012). Then each column is the week #. Each row is the store name and their total sales, ranked highest to lowest for that week. Ideally there would be a guide along the left counting 1-total number of stores so you could quickly see "Ahh, store 17 was 5th in sales for week 15"

When I try to put the Week as a Column Group I get the message "If one or more column groups is specified, all fields in the Values field list must have an aggregate function specified."

The closest I've come is putting the week in the row groups and then I can expand each week to see who was tops that week. But that isn't ideal, especially for printing.

And I haven't figured out a way at all to do the list of numbers (1-whatever number of stores there are) for the ranking.

Thanks for your help on this, sorry I'm so clueless :(

Answer 1

I remembered running into this before and how I figured it out --

I created a table with the Week column and the Sum of Sales for the data.

I created another table that had the store name and the sum of sales in the values.

I went back to the first table and deleted the Sum of Sales. I then cut and paste the second table into the value box and created a nested table and this gave me exactly what I wanted -- each week, 1- whatever.

I eventually trimmed down the nested table to a single cell, a single expression

=RowNumber("StoreandSales") & " - " & Fields!StoreName.Value & " - " & FormatCurrency(Sum(Fields!SaleAmount.Value),0)

Where "StoresandSales" was the name of the nested Table and the FormatCurrency got rid of the cents that aren't needed for this report.

Answer 2

Hi Sean,

Glad to hear that you have got the issue resolved. Thanks very much for your sharing. That's very helpful.

Regards,
Mike Yin

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Mike Yin

TechNet Community Support