SSRS Report Repeating Columns across multiple pages

Question

As a very simplified example, suppose I have the following DataSet I'm trying to create an SSRS report (.rdl file) from:

TabOrdinal  TabName     EndDate     Value
1           First Tab   1/1/2018    141
1           First Tab   1/1/2018    297
1           First Tab   1/1/2018    309
1           First Tab   1/1/2018    705
1           First Tab   1/1/2018    398
1           First Tab   1/2/2018    309
1           First Tab   1/2/2018    753
1           First Tab   1/2/2018    641
1           First Tab   1/2/2018    806
1           First Tab   1/2/2018    177
1           First Tab   1/2/2018    503
1           First Tab   1/3/2018    653
1           First Tab   1/3/2018    717
1           First Tab   1/3/2018    969
1           First Tab   1/3/2018    631
1           First Tab   1/3/2018    614
1           First Tab   1/3/2018    160
1           First Tab   1/4/2018    463
1           First Tab   1/4/2018    997
1           First Tab   1/4/2018    87
1           First Tab   1/4/2018    378
1           First Tab   1/4/2018    972
1           First Tab   1/4/2018    212
2           Second tab  1/4/2018    873
2           Second tab  1/4/2018    525
2           Second tab  1/4/2018    693
2           Second tab  1/4/2018    48
2           Second tab  1/4/2018    20
2           Second tab  1/4/2018    685
2           Second tab  1/4/2018    688

And I have created the following groupings in my Report:

Top-Level Row Group:

Name: Tab
Group On: [TabName]
Page Breaks: Between Each Group

In Code:

<TablixMember>
  <Group Name="Tab">
    <GroupExpressions>
      <GroupExpression>=Fields!TabName.Value</GroupExpression>
    </GroupExpressions>
    <PageBreak>
      <BreakLocation>Between</BreakLocation>
    </PageBreak>
    <PageName>=Fields!TabName.Value</PageName>
  </Group>
  <SortExpressions>
    <SortExpression>
      <Value>=Fields!TabOrdinal.Value</Value>
    </SortExpression>
  </SortExpressions>
  <TablixMembers>
  ...
  </TablixMembers>
</TablixMember>

And a Column Group:

Name: EndDate
Group On: [EndDate]

In Code:

<TablixMember>
  <Group Name="EndDate">
    <GroupExpressions>
      <GroupExpression>=Fields!EndDate.Value</GroupExpression>
    </GroupExpressions>
  </Group>
  <SortExpressions>
    <SortExpression>
      <Value>=Fields!EndDate.Value</Value>
    </SortExpression>
  </SortExpressions>
  <TablixMembers>
    <TablixMember />
  </TablixMembers>
</TablixMember>

Now, what I was HOPING would happen, given the sample data I presented, would be that my report would look somewhat as follows:

Tab1:

1/1/2018       1/2/2018      1/3/2018     1/4/2018
<data>         <data>        <data>       <data>

Tab2:

1/4/2018
<data>

However, I don't know why, but my second tab is including empty columns for all the same dates that existed on the first tab, so the second tab looks as follows:

Tab2:

<empty column>       <empty column>     <empty column>     1/4/2018
<empty column>       <empty column>     <empty column>     <data>

How can I have it so the report knows to reset the date columns with each tab?

I hope this makes sense, but, if not, please let me know and I'll try and explain further.

Answer 1

The reason it is displaying to the right is because you're grouping on the EndDate, but the end date already has three values before 1/4/2018.

Perhaps you can modify your SQL query and add a ROW_NUMBER() like the following:

 ... ROW_NUMBER() OVER(PARTITION BY TabName ORDER BY TabName, EndDate) AS rn

Then in your column group, just group by rn. You may need to modify the ROW_NUMBER portion a little bit to make sure your values are getting set correctly. I've modified to only partition by TabName. I think that's what you want.

In order to hide a textbox without a value, I'm sure there are multiple ways to go about it. One way I was successful was to right-click on the column and go to column visibility. From there I added the expression - =ReportItems!TextBoxName.Value = NOTHING... Of course you will have to use the correct name for your "data".