SSRS ReportItems!TxtBox.Value causing grouping/page break to fail

Epicor ERP 10
1

Not quite sure how to describe this issue, but I’ll try…

I have an SSRS report containing sales for all sales people. The report is grouped, with page breaks, by the sales person name. It worked fine… UNTIL I added a header field that displays the group variable.

Once I place a text box in the header to display the Salesperson who’s data appears on the page(s), all of a sudden, the breaking no longer (reliably) works.

For example, sales person A sales data fills the first two pages and half of page 3. The breaking occurs and the next salesperson (B) starts on page 4.

However, once I add the ReportItems!TxtBox.Value expression in the header, page 3 no longer breaks.
Instead, sales person A fills the first half of the page, as before, but it’s immediately followed by sales person B - no page break.

As for the Header expression, sales person A is properly displayed for the first 2 pages. However, since the sales person changes mid-page w/no break, the header for page 3 shows sales person B.

It seems that the ReportItems expression is grabbing the last group name that WOULD appear on the page had there been no break. I thought it could it be that I should NOT use a group name in the ReportItems expression, however, I tried using a different field in the ReportItems expression as a test. It still suppresses the break JUST for this single sales person. I verified the data is good.

It makes no sense that the break occurs perfectly without the header field, and once I put it back in, the break is suppressed.

As stated above, the break fails JUST for page 3. There are other pages where a single sales person’s data does not fill the page, and the break works. And the header field displays properly! Vexing!

Any thoughts will be appreciated.

2

You need to go into Advanced Mode.

image
image692×97 12.6 KB

Once in it, it will look like this.

image

Each (Static) represents a row in your grid. You are going to have to play around with the Properties of these rows to get the result you want. Specifically, KeepWithGroup and RepeatOnNewPage. You will probably get some errors when you try to save as SSRS has specific rules when using these fields. Play around until you get what you want.

image

3

Hmmm… Thanks @jkane. That’s probably the only area I haven’t yet spent countless, fruitless, hours. I will start that slog now, however.
When/If I hit the right combination of property settings, I’ll update this thread.

4

What is the expression of the “TxtBox” that you are trying to reference in the header via ReportItems?

5

Hello @Jonathan, the info is below…

=ReportItems!ShipTo_SalesRepCode.Value

image

ShipTo_SalesRepCode is the abbreviated name of the salesperson and is the grouping field.

6

ShipTo_SalesRepCode is a textbox within the page right? So what is the expression of that textbox?

7

Oh. Sorry. No, that is not a textbox. It is the name of the group. As I indicated in my earlier dissertation, I DID try referencing a textbox. I created Textbox162 within the Tablix that included the field ShipTo_SalesRepCode. The textbox displayed the info I want in my header.

For that test, I then created the expression in the header = ReportItems!Textbox162.Value
The result was identical to when I referenced the group name; Unreliable page breaking.

8

Hey @jkane , I now see that I did spend significant time adjusting those static row properties in order to get the report to function exactly as we want. It does function that way - the grouping, the report breaks, the repeating header information, all of it appears as designed.

There is something else at play here, and I can’t figure it out. It will likely end up being one of those undocumented traps that we will eventually stumble onto, like, don’t use underscores or limit the referenced fieldname to 8 unique characters, or some other oddity.

9

It sounds like you might have some bad data in Epicor. I would run a quick query pulling in all IDs and Names for the sales reps and group on both. Then see if something was changed in a name somewhere.

10

Its a possibility, I guess. I keep looking at the data, but see nothing wrong. The report is based on a BAQ, which I created and am very familiar with.

11

Another interesting set of results from two tests…

a) I changed the header field to reference some other, totally uninteresting field in the report tablix - Textbox80.Value. The problem still occurs.
b) I changed the header field to reference the text “Text”. The problem disappeared.

It seems that using the ReportItems function to look into the Tablix (at ANY field or text box) is causing the page break to fail.
Cue the Twilight Zone music…

12

I was thinking that maybe because you were trying to evaluate the Group condition at the header, that this is executed before the actual page break happens, so as you said the last group that would print on that page without breaks is the result. But using a textbox within the group should be different than accessing the group directly, and that still fails.

Is the grouping on a single field? what if you try adding one or two more unique fields to the grouping? Hopefully that will let you display the SalesRepCode while maintaining the breaks.

13

Tried adding a second field to the grouping - the email address of the sales person. This changes along with the SalesRepCode field, so the break positions should be the same. No change in the result.

Tried replacing the grouping with JUST the email address. No change in the result.

Now looking back at @jkane’s posts, I’m closely inspecting the static row properties and I may have a pagination issue. Stay tuned.
Still no idea why it works properly without the header text, though…

14

Wow. It appears that the report wasn’t necessarily breaking at the “end” of an 8.5 x 11 sheet of paper, landscape.

I significantly increased the bottom margin, to create an easily observable break line, and only then was I able to see that the pagination properties (the static row properties - the “keep-togethers”) were not set properly.

Once I got the pages breaking accurately and the headings appearing properly with the breaks, everything else fell in line.

Bottom line, it wasn’t the expression in the header at fault, it was just as @jkane posited. The pagination properties were off.

Whew! Thank you both for the assist.