Hide Header and Footer when no data returned on report

Any suggestions on how to hide the header and footer information if there isn't any data returned on the report?

Hi Kimberly,

There may be a better suggestion, but this is what I came up with.

Place a textbox in the body of the report. Set the visibility of this textbox to hidden = true. Set the value of this textbox to the expression = countrows("datasetname"). For each item in the header and footer you would like to hide, add the following expression to the visibility - hidden property: =iif(reportitems!textbox.Value = "0",true,false).

This should hide all items when there are no records in the dataset.

Good luck,

Simone

|||You can use an expression to determine visibility. For example.

=IIF(Countrows("Scope") = 0, True, False)

HTH...

Joe|||This would generate an error when placed in the header or the footer. CountRows cannot be used in page headers or footers.|||Agreed. I didn't read the original posting as page header/footer. Perhaps I misunderstood.|||I may have misunderstood as well. I made the assumption that it was referencing the page header/footer. If this is not the case, then Joe's explanation is the way to go.|||Thank you Simone and Joe. I appreciate all of your help. Adding the text box and then referencing it to determine the visibility sounds like it should do the trick.

Hide Header and Footer when no data returned on report

Any suggestions on how to hide the header and footer information if there isn't any data returned on the report?

Hi Kimberly,

There may be a better suggestion, but this is what I came up with.

Place a textbox in the body of the report. Set the visibility of this textbox to hidden = true. Set the value of this textbox to the expression = countrows("datasetname"). For each item in the header and footer you would like to hide, add the following expression to the visibility - hidden property: =iif(reportitems!textbox.Value = "0",true,false).

This should hide all items when there are no records in the dataset.

Good luck,

Simone

|||You can use an expression to determine visibility. For example.

=IIF(Countrows("Scope") = 0, True, False)

HTH...

Joe|||This would generate an error when placed in the header or the footer. CountRows cannot be used in page headers or footers.|||Agreed. I didn't read the original posting as page header/footer. Perhaps I misunderstood.|||I may have misunderstood as well. I made the assumption that it was referencing the page header/footer. If this is not the case, then Joe's explanation is the way to go.|||Thank you Simone and Joe. I appreciate all of your help. Adding the text box and then referencing it to determine the visibility sounds like it should do the trick.

Hidding rows disappearing in Excel export

I have a report that looks fine in Report Server, but when I export to
Excel, I lose data.
I have a group that I am repeating the group footer 3 times. The
original footer is the toggle. when I hit the + in Rep Services, it
expands with no problem, showing all of my hidden rows.
when I export it to excel and hit the toggle, it only shows the first
hidden row and loses the other two.
Any ideas?What version of Report Server and Office do you have?
daw
"M@." wrote:
> I have a report that looks fine in Report Server, but when I export to
> Excel, I lose data.
> I have a group that I am repeating the group footer 3 times. The
> original footer is the toggle. when I hit the + in Rep Services, it
> expands with no problem, showing all of my hidden rows.
> when I export it to excel and hit the toggle, it only shows the first
> hidden row and loses the other two.
> Any ideas?
>|||Microsoft SQL Server Reporting Services Version 8.00.1038.00
Excel 2003
thx,
M@.
On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> What version of Report Server and Office do you have?
> daw
> "M@." wrote:
> > I have a report that looks fine in Report Server, but when I export to
> > Excel, I lose data.
> > I have a group that I am repeating the group footer 3 times. The
> > original footer is the toggle. when I hit the + in Rep Services, it
> > expands with no problem, showing all of my hidden rows.
> > when I export it to excel and hit the toggle, it only shows the first
> > hidden row and loses the other two.
> > Any ideas?|||Are you sure that the rows are not just hidden rows in Excel? Are the row
numbers consecutive?
"M@." wrote:
> Microsoft SQL Server Reporting Services Version 8.00.1038.00
> Excel 2003
> thx,
> M@.
> On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> > What version of Report Server and Office do you have?
> >
> > daw
> >
> > "M@." wrote:
> > > I have a report that looks fine in Report Server, but when I export to
> > > Excel, I lose data.
> >
> > > I have a group that I am repeating the group footer 3 times. The
> > > original footer is the toggle. when I hit the + in Rep Services, it
> > > expands with no problem, showing all of my hidden rows.
> >
> > > when I export it to excel and hit the toggle, it only shows the first
> > > hidden row and loses the other two.
> >
> > > Any ideas?
>
>|||I have a group footer (group1) that I repeat. The first line is the
toggle, lines 2-5 are details. I'm using running values. Row 1 is
the sum. Row2 is a sum when a field = PRV, Row3 is a sum when a field
= MCD and so on.
It looks fine in Reporting Services, but if I export it to excel and
hit the toggle, only the first hidden row pops in.
Yeah, the row numbers are consecutive.
On Mar 21, 4:32 pm, daw <d...@.discussions.microsoft.com> wrote:
> Are you sure that the rows are not just hidden rows in Excel? Are the row
> numbers consecutive?
>
> "M@." wrote:
> > Microsoft SQL Server Reporting Services Version 8.00.1038.00
> > Excel 2003
> > thx,
> > M@.
> > On Mar 21, 3:52 pm, daw <d...@.discussions.microsoft.com> wrote:
> > > What version of Report Server and Office do you have?
> > > daw
> > > "M@." wrote:
> > > > I have a report that looks fine in Report Server, but when I export to
> > > > Excel, I lose data.
> > > > I have a group that I am repeating the group footer 3 times. The
> > > > original footer is the toggle. when I hit the + in Rep Services, it
> > > > expands with no problem, showing all of my hidden rows.
> > > > when I export it to excel and hit the toggle, it only shows the first
> > > > hidden row and loses the other two.
> > > > Any ideas... Hide quoted text -
> - Show quoted text -

hidden field populating header/footer trick not working

I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here?
Well, just switched the conditional visibility so that rect1 would go on page 2 and rect2 on page one, and guess what? Now these fields on invisible on rect1 and visible on rect2! So it has to do with the page number! Wow, how weird! Anyone see this before and is there any workaround?
|||

Expressions in the page header/footer can only reference report items on the current page. One thing to check is whether the hidden textbox in the report body is repeated on both pages? If the textbox only appears on the first page, expressions in the header/footer for the second page would always get a null value for this textbox.

sql

hidden field populating header/footer trick not working

I'm a bit stumped at this. I have done this before a bunch of times. The difference is that this time I have two rectangles in the footer. For the each, the visibility is set to an expression which checks whether the page is page 1 or 2. If it is 1, the first rectangle is visible, if 2, the second. When I reference the hidden fields on the main area of the report via ReportItems, they come through fine on the first rectangle, but are invisible on the second. I've tried the same exact fields and every time they are visible in rectangle1, and invisible in rectangle2. What is going on here?
Well, just switched the conditional visibility so that rect1 would go on page 2 and rect2 on page one, and guess what? Now these fields on invisible on rect1 and visible on rect2! So it has to do with the page number! Wow, how weird! Anyone see this before and is there any workaround?
|||

Expressions in the page header/footer can only reference report items on the current page. One thing to check is whether the hidden textbox in the report body is repeated on both pages? If the textbox only appears on the first page, expressions in the header/footer for the second page would always get a null value for this textbox.