Using SSRS 2008R2.
Currently, I have a select statement that queries a few tables and returns a bunch of hierarchical data.
1 Store-> 1+ Sections -> 1+ Items
The returned dataset resembles something like this(plus 20 other columns):
Store_num | Section_num | Section_Name | Year_Calc | Item_name | Item_Num
4542 | 32 | Fruits | 1 | Apple | 1
4542 | 32 | Fruits | 2 | Apple | 1
4542 | 32 | Fruits | 3 | Apple | 1
4542 | 32 | Fruits | 4 | Apple | 1
4542 | 32 | Fruits | 5 | Apple | 1
4542 | 32 | Fruits | 1 | Berry | 2
4542 | 32 | Fruits | 2 | Berry | 2
4542 | 32 | Fruits | 3 | Berry | 2
4542 | 32 | Fruits | 4 | Berry | 2
4542 | 32 | Fruits | 5 | Berry | 2
4542 | 32 | Fruits | 1 | Orange | 3
4542 | 32 | Fruits | 2 | Orange | 3
4542 | 32 | Fruits | 3 | Orange | 3
4542 | 32 | Fruits | 4 | Orange | 3
4542 | 32 | Fruits | 5 | Orange | 3
4542 | 32 | Fruits | 1 | Banana | 4
4542 | 32 | Fruits | 2 | Banana | 4
4542 | 32 | Fruits | 3 | Banana | 4
4542 | 32 | Fruits | 4 | Banana | 4
4542 | 32 | Fruits | 5 | Banana | 4
4542 | 32 | Fruits | 1 | Watermelon | 5
4542 | 32 | Fruits | 2 | Watermelon | 5
4542 | 32 | Fruits | 3 | Watermelon | 5
4542 | 32 | Fruits | 4 | Watermelon | 5
4542 | 32 | Fruits | 5 | Watermelon | 5
4542 | 33 | Vegetables | 1 | Esparagus | 12
4542 | 33 | Vegetables | 2 | Esparagus | 12
4542 | 33 | Vegetables | 3 | Esparagus | 12
4542 | 33 | Vegetables | 4 | Esparagus | 12
4542 | 33 | Vegetables | 5 | Esparagus | 12
4542 | 33 | Vegetables | 1 | Lettuce | 13
4542 | 33 | Vegetables | 2 | Lettuce | 13
4542 | 33 | Vegetables | 3 | Lettuce | 13
4542 | 33 | Vegetables | 4 | Lettuce | 13
4542 | 33 | Vegetables | 5 | Lettuce | 13
4542 | 33 | Vegetables | 1 | Mushroom | 14
4542 | 33 | Vegetables | 2 | Mushroom | 14
4542 | 33 | Vegetables | 3 | Mushroom | 14
4542 | 33 | Vegetables | 4 | Mushroom | 14
4542 | 33 | Vegetables | 5 | Mushroom | 14
4542 | 33 | Vegetables | 1 | Tomato | 15
4542 | 33 | Vegetables | 2 | Tomato | 15
4542 | 33 | Vegetables | 3 | Tomato | 15
4542 | 33 | Vegetables | 4 | Tomato | 15
4542 | 33 | Vegetables | 5 | Tomato | 15
4542 | 33 | Vegetables | 1 | Spinach | 16
4542 | 33 | Vegetables | 2 | Spinach | 16
4542 | 33 | Vegetables | 3 | Spinach | 16
4542 | 33 | Vegetables | 4 | Spinach | 16
4542 | 33 | Vegetables | 5 | Spinach | 16
In my tablix, I'm currently grouping on Items_num, and Section_num so that I repeat my entire tablix for each Item for each Section.
It wasn't a difficult problem at first, as I just created two tablix's and the one with multiple years had a column group. Problem solved.
However, as with any job, it wasn't good enough. I need to have all the informational data repeated for each "5 year calculation".
Here's what the tablix sort of looks like, and you'll understand why I can't figure out a good solution for this.
+--------+--------+----------------+-------------+---------+---------+----------------+
|Store |4542 |Store Name: | We Sell Groceries! |
+--------+--------+----------------+-------------+---------+---------+----------------+
|Section |32 |Section Name:| Fruits |
+--------+--------+----------------+-------------+---------+---------+----------------+
|Item |1 |Item Name: | Apple |
+--------+--------+----------------+-------------+---------+---------+----------------+
| |First(Year) |2nd(Year) |3rd(Year)|4th(Year)|Last(Year) |
+-----------------+----------------+-------------+---------+---------+----------------+
|Calculation 1 |First(Column_10)| | | |Last(Column_10) |
+-----------------+----------------+-------------+---------+---------+----------------+
|Calculation 2 |First(Column_11)| | | |Last(Column_11) |
+-----------------+----------------+-------------+---------+---------+----------------+
|Calculation 3 |First(Column_12)| | | |Last(Column_12) |
+-----------------+----------------+-------------+---------+---------+----------------+
|Calculation 4 |First(Column_13)| | | |Last(Column_13) |
+-----------------+----------------+-------------+---------+---------+----------------+
|Calculation 5 |First(Column_14)| | | |Last(Column_14) |
+-----------------+----------------+-------------+---------+---------+----------------+
|Calculation 6 |First(Column_15)| | | |Last(Column_15) |
+-----------------+----------------+-------------+---------+---------+----------------+
|Calculation 7 |First(Column_16)| | | |Last(Column_16) |
+-----------------+----------------+-------------+---------+---------+----------------+
I have 5 static columns and I cannot figure out a way to get the "2nd, 3rd and 4th" values from my dataset.
using the Lookup function doesn't work as it's on the entire dataset, and doesn't adhere to the row groups that I currently have applied (To the entire tablix)
Any ideas?
Since I had the report working in a previous attempt for 1 section and 1 Item. I reverted back to that design and created a new report with a subreport tucked inside a Tablix with the grouping done there.
In the actual subreport, I created two tablixs. One with all the data above the Calculations, and one below with the calculations (Which would grow based on a column group)
ANSWER: Used a sub report