0

Store Number
Store Zone

In my report query, each Store Number is assigned a Store Zone. I have grouped rows by Store Zone and columns by Store Number.

The report displays the columns for Store Numbers that are not in the Store Zone group. There is no data in these columns other than the Store Number in the header row.

How do I hide a Store Number column if it doesn't belong to the current Store Zone group?

So for example, Store1 & Store3 belong to Store Zone A. Store 2 belongs to Store Zone B. I do not want to see the column for Store2 in the section that groups Store Zone A, and I don't want to see the column for Store1 and Store3 in the Store Zone B group. I have a page break between Store Zone groups.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
devlin carnate
  • 8,309
  • 7
  • 48
  • 82
  • It looks like one way to accomplish this is to create different data sets, and limit each data set to one Store Zone, and have a Tablix for each data set. But this means the query is running multiple times (once for each Store Zone type). Perhaps there is a better way? – devlin carnate Aug 22 '13 at 23:00
  • I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders Aug 23 '13 at 02:54

1 Answers1

0

If you can have the store Zone group and the Store Number groups both on Column or both on Row, then the behavior you are looking for is the default. But when one is on the rows and one is on columns, then it is more difficult to achieve this: A Tablix needs to have the same rows down the whole thing: even across pages.

One way to get this result:

  1. Create a simple report taking Store Zone as a parameter. It should return the rows and columns for a single zone and will be used as a subreport for the following:
  2. Create a parent report which has a dataset returning the list of distinct zones. It should have a table with one or two detail rows, and a single column, no column groups.
  3. Place the subreport into one of the detail cells in the parent report and connect the zone parameter to the Fields!StoreZone.Value.

This will repeat the subreport as needed, and each instance can have different columns.

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • yes, that will work. another method is to have several tablix's on the same report, and filter each to show only one Store Zone. – devlin carnate Aug 23 '13 at 14:39
  • Makes sense. I'd probably still go with the subreport approach. It's a little bit more complicated but more flexible if the store zones change. Authoring reports can be a pain when the .rdl doesn't all fit on the screen at once. – Jamie F Aug 23 '13 at 15:08