0

I have a SSRS report that looks like the following:

Report Header:
Page X of Total Page Y (based on change of Customer ID)
Customer ID (Referring to ReportItem!CustomerID)

Report Body:
A tablix page break by change of group Customer ID

Tablix Header:        Date    |  Price | Quantities | Description | Hidden Column Customer ID
Tablix Details:    YYYY-MM-DD |  1.00  | 500        | Blah Blah   | Customer ID (used for page header referring)
Row outside group: Empty
Row outside Group: Sum of Price
Row outside Group: Sum of Quantities
Row outside group: Empty
Row outside group: *** END OF RECORDS ***

Report Footer:
Company address

Occasionally, the Row outside group will go to next page if the report body for the tablix details are full, which causing in the next page, only some of the rows outside group will be displayed (e.g. * END OF RECORDS *).

Then, in the report header, the Customer ID field cannot get the value of ReportItem!CustomerID which leave it blank.

Is there anyway to solve the blank issue? Or make the row outside group must attach to the tablix details in the same page? For some reason, I cannot limit the number of rows display per page. Thanks.

TinySimonH
  • 29
  • 2
  • 9

3 Answers3

0

Maybe you can try throwing in another hidden copy of the final "Customer ID" somewhere on the "END OF RECORDS" group row and then use an IIF to call that one if the original hidden "Customer ID" you have isn't present on the final page.

Hoonerbean
  • 367
  • 1
  • 4
  • 12
  • This approach does not work either. I created another hidden textbox right next to the "END OF RECORDS". Then in the page header use the below IIF to detect the CustomerID: `=IIF(IsNothing(ReportItem!CustomerID.Value),ReportItem!CustomerID_TABLIX_FOOTER.Value,ReportItems!CustomerID.Value)` But this will throw another errror: **An expression in a page header or footer can refer to only one report item.** – TinySimonH Jan 12 '17 at 01:20
  • @TinySimonH. Okay. I have another idea. If your output is a PDF file, this might work. Keep that 2nd hidden 'CustomerID' field that you created in the "END OF RECORDS" row. In your header, use 2 textboxes overlayed right on top of each other. One calling the original 'CustomerID' field and the other calling the new 'CustomID' field. If both 'CustomerID' fields are present on the same page at the same time, then the header textboxes will just overlap each other with the same data and, theoretically, you'll never know that there are 2 separate textboxes in the header area. :-) – Hoonerbean Jan 20 '17 at 00:51
  • Thanks! Although your method can display the Customer ID, it will make the font bolder if both field overlapped. Anyway, I will mark this valid answer as it is already the best way to present the report. Thanks. – TinySimonH Jan 23 '17 at 06:03
  • @TinySimonH. I thought of another thing to try. Keep the two textboxes in the header as they are, but try implementing an IIF in the hidden 'CustomerID' field you added to the "END OF RECORDS" row. See if you can set up the expression so that it looks to see if the original hidden 'CustomerID' field is empty, or not. If the original hidden 'CustomerID' field is empty, then bring in the 'CustomerID' to this "END OF RECORDS" row field. Otherwise, do the opposite. This might prevent the two textboxes in the header from both displaying text and causing the bolding from doubled up text. – Hoonerbean Jan 26 '17 at 19:56
  • Thanks. Finally solve the issue. Add the following expression for the Visibility: For the textbox with ReportItem!Custo‌​merID_TABLIX_FOOTER: =IIF(Globals!PageNumber = Globals!TotalPages, FALSE, TRUE) For the textbox with ReportItem!CustomerID.Value: =IIF(Globals!PageNumber <> Globals!TotalPages, FALSE, TRUE) – TinySimonH Feb 03 '17 at 12:12
0

You can control when the page breaks occur and keep the elements together, but it's not very intuitive. First, don't use actual Headers and Footers. Just use textboxes that will appear at the beginning and end of each Customer's data. Second, you need to wrap everything in a grouped rectangle. This will be grouped by customer with page breaks set to happen between instances.

See my answer here for more details on how to do this.

Community
  • 1
  • 1
StevenWhite
  • 5,907
  • 3
  • 21
  • 46
  • It sounds like @TinySimonH is okay with his row group page breaks, but if I understand correctly, what's happening is that on the page break group, there's just enough customer data that it's causing an additional, final page to be created, which doesn't contain any customer data, but just his Tablix footer group rows. And since these Tablix footer group rows don't hold the hidden 'Customer ID' data that his header field is relying upon, the logic for his Header field isn't working on this final page because he's calling upon data from a Body field that doesn't exist on the final page. – Hoonerbean Jan 11 '17 at 22:32
  • @Rashiki, you are completely right and are fully understanding my question. I am trying your method mentioned above now. – TinySimonH Jan 12 '17 at 01:01
  • I see. I would still suggest you let SSRS filter values based on the current grouping. Trying to cross-reference values in other objects is not as reliable. Just like when you do a Sum in various levels of a pivot. There's no point telling it which textboxes to Sum when it's already aware of its scope. – StevenWhite Jan 12 '17 at 03:11
0

If I was going to display "Body" object values in the report header, I would create a hidden expression text object somewhere in the "Body" that uses a Code call ( Report Properties, Code ) to put the value into a shared variable. Then, that gives you a set variable, get variable, type of situation. The variable is updated as the "Body" object appears throughout the report, and the variable 'sticks' across pages where the object does not necessarily occur.

M Ramazoodle
  • 3
  • 6