none
Dynamic header/footer

    Question

  • I have a SSRS report with 2 distinct tablixes of data, I have a page break between them and page numbering resetting like I want, so I'm close.

    I'd like a different header based on which tablix is currenly being displayed, ideas?

     
    Monday, August 05, 2013 8:13 PM

Answers

  • I was able to solve this problem with this as an expression in the page header:

    =IIf(IsNothing(ReportItems!Data1Col1.Value),"Table 1 header", "Table 2 header")

    Data1Col1.Value only exists on pages that have that table displying, worked out perfect and will work with dynamic data...

    • Marked as answer by cwmizner Wednesday, August 07, 2013 6:15 PM
    Wednesday, August 07, 2013 6:15 PM

All replies

  • If you are sure about which table is going to display on which page then you can change value of controls you have placed in header conditionally. Let's assume, you put a textbox in header and you know page 1 will have table 1 and page 2 will have table two. Then you would write expression for textbox as below.

    = Switch(
              Globals!PageNumber=1, "I'm Table one",
              Globals!PageNumber=2, "I'm Table two"   
            )
    

    Monday, August 05, 2013 9:43 PM
  • If you are sure about which table is going to display on which page then you can change value of controls you have placed in header conditionally. Let's assume, you put a textbox in header and you know page 1 will have table 1 and page 2 will have table two. Then you would write expression for textbox as below.

    = Switch(
              Globals!PageNumber=1, "I'm Table one",
              Globals!PageNumber=2, "I'm Table two"   
            )

    This gets me very very close, thank you!

    My issue now is i reset page numbering for the second table, so is there a global for the 'printed' page number vs. the actual page number?

    i.e. page number 1073 is labled page 1 because I want it to be (to seperate the two data sets).

    Monday, August 05, 2013 11:23 PM
  • Yes, there a function called Globals!OverallPageNumber. This will give you actual page number and won't get reset, wherease Globals!PageNumber will get reset with page breaks(ResetPageNumber property of table).

    Please see below short description I copies from SSDT.

    Globals!OverallPageNumber
    The current overall page number. Can be used only in a page header or footer.

    Globals!PageNumber
    The current page number, which can be reset through the use of page breaks. Can be used only in page header or footer.

    Tuesday, August 06, 2013 1:29 AM
  • good to know, but just realized the obvious, my first table has a dynamic number of rows that will grow over time and spill over into more pages making this strategy no good...

    any other suggestions?

    Tuesday, August 06, 2013 3:45 AM
  • In this case, I would recommend not to use header section of report for heading etc. Try to format your tables' top row(Merge cell) and embed heading in tables. If tables get cluttered due to embedding heading row in table then you can drop 1 row 1 column table and make that your heading for each data table.

    For example:

    1. Header Table 1 (Containing Heading) 1 Row 1 Column
    2. Data Table 1(Containing Data)

    3. Table 2 (Containing Heading for Table 2) 1 Row 1 Column (Add page break for this table NOT the below)
    4. Table  2(Containing Data) (Don't add page break before for this)

    Tuesday, August 06, 2013 4:39 AM
  • Hi Cwmizner,

    From your description, you want to reset the page number when the report show the second tablix, right? In Reporting Services, we can reset the page number on each group under a tablix which you can see on the blog: Report Design: Reset Page Number on Group. However we cannot reset the page number for a different tablix.

    As Samiullah said, if you are sure about which table is going to display on which page, then you can reset the page number on the second tablix, and display different page header for different tablix. However, this solution is not work on your scenario because of the row numbers in your tablix is dynamic. And I am afraid there is no other approach to work around this issue.

    If you have any concerns about the feature that reset page number of a new tablix, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product.

    Thank you for your understanding.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    Tuesday, August 06, 2013 8:44 AM
    Moderator
  • In this case, I would recommend not to use header section of report for heading etc. Try to format your tables' top row(Merge cell) and embed heading in tables. If tables get cluttered due to embedding heading row in table then you can drop 1 row 1 column table and make that your heading for each data table.

    For example:

    1. Header Table 1 (Containing Heading) 1 Row 1 Column
    2. Data Table 1(Containing Data)

    3. Table 2 (Containing Heading for Table 2) 1 Row 1 Column (Add page break for this table NOT the below)
    4. Table  2(Containing Data) (Don't add page break before for this)

    Not quite sure what you mean here.

    I get at a high level what you are saying, don't use a header at all, but simulate a header in each of the 2 tablix.  This is a good idea and I've done it when working with 'normal' tables, you can set the static header row to 'RepeatOnNewPage', it works great.

    In this current case, my tables are not 'normal', the have grouping and the grouping seems to not allow the header row to work like I want (have nested tables inside my outer table to make a 3 coulmn table with filtering to display a single dataset in a 'newsletter' or lable style, pretty cool stuff, but I digress: http://blog.hoegaerden.be/2012/11/01/creating-multiple-column-reports/ )

    Is your suggestion to use 4 tables vs 2 with a header table for each data table?  I'll play with that, I'm not sure how the reapeating on every page will work and the transition to table 3 will work, I don't think I understand your method...

    http://www.sqlservercentral.com/Forums/Topic685955-150-1.aspx  seems promising at some level, just not sure how exacty to do it in my case...

    has to be some way to get this implemented...

    Tuesday, August 06, 2013 3:23 PM
  • I was able to solve this problem with this as an expression in the page header:

    =IIf(IsNothing(ReportItems!Data1Col1.Value),"Table 1 header", "Table 2 header")

    Data1Col1.Value only exists on pages that have that table displying, worked out perfect and will work with dynamic data...

    • Marked as answer by cwmizner Wednesday, August 07, 2013 6:15 PM
    Wednesday, August 07, 2013 6:15 PM