How can I select row values that are in between "First(Fields...)" and "Last(Fields..)" that adhere to row groups?

Question

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?

Why your are setting **First(column_name)** you do not need the **First()** method to display your data, can you please remove the first and check the result. Let me know — Nasser Hadjloo, May 31 '12 at 10:21
First returns the value for when "Year" equals 1 (for each Item in a section). Granted, not having it there still returns the same value, but that isn't the problem I'm having. I'm talking about getting the values when Year = 2,3,4. — Christian, May 31 '12 at 13:36

score 0 · Accepted Answer · answered Jun 01 '12 at 16:30

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

How can I select row values that are in between "First(Fields...)" and "Last(Fields..)" that adhere to row groups?

1 Answers1