Bunch's Blog

One day I'll have a catchy subtitle, one day
posts - 87 , comments - 126 , trackbacks - 0

Data into a SSRS Header

This was an issue for me a while back. I was making a report using SQL Server Reporting Services (SSRS) in SQL 2005 and I needed to put data into a textbox in the header. After searching the internet fairly thoroughly I found many posts and articles that pointed me in the right direction but they just didn’t work for me.  

 

The solution I used was to place a textbox on the body of the report and make it hidden. In the textbox I would add in the data I needed (i.e. =Fields or =Countrows). I would then set that textbox to RepeatWith the table in the body.

 

In the header I would place a textbox and have it set to =ReportItems!hidden_textbox.Value.

 

Just doing that would give me the data in the header but only on the last page of the report (note if your report is only one page you might not experience this frustration). To get the data to show in the header for each page I needed to move the textbox so it was positioned on top of the table. The easiest way to do this was to change the Left and Top Location properties of the textbox. It makes it a little hard to see parts of the table since the textbox covers up parts of it but it gets the data in the header.

 

Technorati Tags:

Print | posted on Wednesday, March 5, 2008 10:01 AM |

Feedback

Gravatar

# re: Data into a SSRS Header

Excellent post!!!
thanks.
This works for bulk invoices, with lengthy lists of invoicelines.

We have pre-printed paper, with logo and company details on the top of the page and bank details in the bottom.
On our invoice we needed to print customer details next to our logo, and subsequent pages needed to start with the (remainder of) invoice lines below our logo.
I created a list, grouped by customer. In the top of the list I created the hidden textboxes with header data (address details, invoicenr etc). Below the hidden textboxes a table with invoice lines.
The header was big enough to cover the logo area of the paper.
The result was just what we needed!
8/26/2008 3:50 AM | Mick L
Gravatar

# re: Data into a SSRS Header

Thanks for the post!

If your report is more than a page, the header is showing the correct information in the first page, but header is not showing the correct data in the second page.

I have worked out a way to solve this.

I assumed that you use a table to display all the data (in body area). If you added another header row in the table and put the information you want to display in the header of every page inside the text boxes of this header row (in the table). Then, select all the textboxes in the header row; change the visibility property to true. (You can adjust the height of the row to fix the page.)

* Don’t high line the whole header row and change the visibility property to true. It won’t work.
* Make sure you change the repeat on new page in the property of the table row to true.




9/10/2008 6:48 AM | Jess L.L.
Gravatar

# re: Data into a SSRS Header

I stumbled across another option - where the header field
was displaying in preview - but not printing.

The formula in the header references a column in the data set

=First(ReportItems!ColReportGroup.Value)

and by entering the dataset name in the Sort target under User Sort the problem was fixed
12/2/2008 11:27 AM | Chris Jones
Gravatar

# re: Data into a SSRS Header

we use the two table and if we can display the first table on every page what can i do.
1/13/2009 7:38 AM | Sanjay Barai
Gravatar

# re: Data into a SSRS Header

I followed the instructions here to get a live field into a page header. The field value show in Preview, but when printed the live field disappears. i read the post above about putting "=First(xxxx)"...

=First(ReportItems!ColReportGroup.Value)

that didn't seem to fix it alone. And then the next instruction was this....

"and by entering the dataset name in the Sort target under User Sort the problem was fixed "

what the heck does this mean?
3/19/2009 12:01 PM | Justin
Gravatar

# re: Data into a SSRS Header

What's up guys? I have a solution for you. The methods above work okay, but you do run into issues with multiple pages.

What you need to do is the following:
1) go to the report parameters under the report menu
2) click the Add btn to add a new parameter
3) name your new parameter ex: rpInternalStrID
4) check the Internal checkbox option (this will disable the prompt)
5) go down to the Default Values section, and choose the From query radio btn
6) Choose the associated dataset for the field you want to display
7) choose a value field from the dataset (your column)
8) press okay
9) drop a textbox in the header or footer of your report and proceed to the Expression builder area for that textbox
10) enter the following ex:
=Parameters!rpInternalStrID.Value
11) test your report
4/9/2009 1:33 PM | K2
Gravatar

# re: Data into a SSRS Header

I have a similar problem with using ReportItems!textboxinbody.Value in a header. It displays that item on the first page, but not on any page heading on pages following it. I followed the last post's instructions, saved the .rdl file, published it and attempted to run it - and I get the following error: "Error in exporting report to disk file: System.Web.ServicesThe report parameter 'rpInternalStrID' is read-only and cannot be modified." I can find no setting for read-only in its definition or properties. Any idea why this is happening and how to fix it?
5/1/2009 9:23 AM | Alan Steffens
Gravatar

# re: Data into a SSRS Header

In response to K2's entry on 4/9/2009...

Your approach is great however it fails if any of those fields do not contain a value returned from the query. Do you have a workaround for this?
5/8/2009 10:23 AM | Matt Thomas
Gravatar

# re: Data into a SSRS Header

I tried 11 steps from K2. It worked for 1 page, but fails if we have multiple pages and values are changing for the parameter field. It repeat the first value on all the page headers.
5/29/2009 8:27 AM | JD
Gravatar

# re: Data into a SSRS Header

Excellent post. Thank you very much. :-)
6/26/2009 12:46 AM | Harish J
Gravatar

# re: Data into a SSRS Header

I have changing values that I need in the page header. The parameter solution puts the same value on all pages. I've used the hidden textbox solution putting my values in the detail section. However, if you have a page that maybe has grouping totals with no detail rows, the values are blank. Has anyone found a workaround for this?
11/20/2009 8:03 AM | SueW
Gravatar

# re: Data into a SSRS Header

can we add styles to header or footer in SSRS?
7/7/2010 5:59 AM | duckyvideos
Gravatar

# re: Data into a SSRS Header

excellent post by k2. it helped.
9/15/2010 11:54 AM | sue
Gravatar

# re: Data into a SSRS Header

Greaet Post! The hidden column solution worked for me. I tried K2's soultion, but got the same results that SueW got, the same value on all pages. There should be an eaiser way to get data to print in the header.

11/2/2010 3:57 PM | Dan
Gravatar

# re: Data into a SSRS Header

how to show month and year in the header. Month and year take from dates column (dd/mm/yyyy)? plz help me..
2/7/2011 2:19 AM | zatie
Gravatar

# re: Data into a SSRS Header

I had a similar issue (I'm using SSRS 2008 R2). I was creating invoices for multiple companies using a subreport (the subreport loops through each company). In the footer I wanted to put the Companies name on every page. In order to accomplish this I had to first upgrade to SQL Server and RS to 10.50.2500.0. Then I added a column to my Subreport Tablix on the main report (my tablix is now 2 columns by 1 row). In the new column cell create the expression that you want to display (in my case it was simply the company name from the looping query). After you have written the expression hide only that column and set the width of that column to .03125in. Now create a textbox in your footer and set that expression to the new column cell using =ReportItems!hidden_newcolumncell.Value.

The appropriate company name repeats on every page.
8/9/2011 10:14 AM | Ph
Gravatar

# re: Data into a SSRS Header

good stuff. Worked for me.

Thanks!!!
10/21/2011 2:51 PM | Anonymous
Gravatar

# re: Data into a SSRS Header

Does not seem to be an answer to SueW post. Same issue I am having. Very frustrating when it should be so simple. I know this thread is old, but did anyone find a solution?
1/18/2012 10:07 PM | Sandy
Gravatar

# re: Data into a SSRS Header

how to set the image in center of the rdlc . that image should be repeat 1 time per 1 page at middle (Example : students record note contain college image at every page)
2/10/2012 12:20 AM | Abdullasha
Gravatar

# re: Data into a SSRS Header

The 11 step solution works absolutely fine.... until you try to print the report. This causes it to revert back to the first values in the header for every single page in the entire report. I've been battling this one for weeks - any help/suggestions are appreciated.
TIA
5/14/2014 10:30 AM | DBLOWERS
Gravatar

# Show report header before parameters list in report viewer using SSRS

I have one more question.

can I show report header above the report parameters (listing ) in report view using SSRS?

I have searched on google but didn't find any solution.

can you please suggest about it.
8/19/2014 9:16 PM | Mukesh Bhagat
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: