SQL Reporting Page Headers

I have a SQL Report in SQL RS 2008, I have tables setup to group on project number and display various information about the project. The report is setup to page break after each group.

I want to have a header that will display pertinant information in the header of each page. My understanding is page headers can only contain static data and not expressions but I can refer to text boxes within the report.

So if I have a text box in my report, for example, Job number, that changes on each page with the grouping how do I reference that in the page header? I'm sure it's a simple syntax but I am still pretty new to SQL.

Thank You
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Megan BrooksSQL Server ConsultantCommented:
You can display data for a given group anywhere within the group, from headers to details to subgroups to footers.. If you define one or more group header table rows and request that they be repeated on each page when the group spans more than one page, the that header data does not need to be static. If you also start each group on a new page, it is easier for the person viewing it to make sense of it.
The "repeat groups header on each page" option is deeply buried in SSRS 2008. If you look on the groups separator bar of Report Designer, at the bottom of the page, you should see something like "Row Groups", "Column Groups," (depending on the type of data region) and a small drop-down arrow near the right-hand edge of the window. Drop it down and select "advanced mode."
Once you do that (assuming that you have already added the group header rows to the tablix) you will be able to see the "Static" header rows in front of each group. If you look at their properties (F4) there is a RepeatOnNewPage property, and that is what you need to set to include the headers at the top of each overflow page.
This is how I most often do it. If this isn't what you need, there may be other possibilities.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bkpierceAuthor Commented:
Thanks for the info, I'm looking for ways to add items to the page header instead of the group header. If I refernce something from the dataset in the page header it just shows info for one job on every single page, it does not change with the page break groups.

Is tried referencing a text box with =Reportitems!textbox1 but when I run the report it just shows #Error
Megan BrooksSQL Server ConsultantCommented:
When the group headers repeat on page overflow, the group header in effect becomes a page header. You can include any information in this header that is available at the group's scope.
I have a report that shows details about organizations and that groups at 3 organizational levels, and has 1 static (outside of any group) and 3 dynamic group header rows, all marked to repeat on each page. At the top of each page you see the static row plus the current information about all three org levels, whether it is starting a new group or whether it is in the middle of outputting detail rows.
It wouldn't be necessary to have three separate group header rows; I just wanted it to look that way. All of the information could have been combined into one row associated with the innermost group.
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

=Reportitems!textbox1.value is that how you have it?
Should work
bkpierceAuthor Commented:
I ended up removing the page header and just putting everything within a list box and doing a page break on that.
Megan BrooksSQL Server ConsultantCommented:
Referencing the ReportItems collection in the page header, mentioned by planocz, should work for a simple, static page header, to fetch values from textboxes that will always be present on the page.. I had actually forgotten about that collection, probably because much of the time I use headers that vary in number of rows depending upon the grouping level of the data.
Repeating the group headers seems to me like a more flexible and predictable approach.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.