I'm using SQL Server Reporting Services and need to generate a report listing
addresses. I have to accomodate two address lines. How can I set it up so
that if there is no second address no blank line appears on the reportOn Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report
I'm assuming you are using a table data region. Set the Hidden
property for the second line/row to somthing like this:
=IIF(addressline2 = Nothing, True, False)
Make sure you set the property for the row and not individual cells
otherwise it will not close the white space left by the hidden row.
HTH
toolman|||I'm VERY NEW to this product. How do you set up a table data region?
Otherwise your reply makes perfect sense.
Thanks!
"toolman" wrote:
> On Oct 18, 11:14 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report
> I'm assuming you are using a table data region. Set the Hidden
> property for the second line/row to somthing like this:
> =IIF(addressline2 = Nothing, True, False)
> Make sure you set the property for the row and not individual cells
> otherwise it will not close the white space left by the hidden row.
> HTH
> toolman
>|||I have figured out how to set up a data table, and placed the iif statement
on the address2 line. But when I preview the report I get this message: The
table is in a list that has no group expressions defined for it. The ous a
data region in a list, the list must have group expressions".
Can you advise me which property sets the group expressions?
Thanks!
"Jeannie" wrote:
> I'm using SQL Server Reporting Services and need to generate a report listing
> addresses. I have to accomodate two address lines. How can I set it up so
> that if there is no second address no blank line appears on the report|||On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
wrote:
> I have figured out how to set up a data table, and placed the iif statement
> on the address2 line. But when I preview the report I get this message: The
> table is in a list that has no group expressions defined for it. The ous a
> data region in a list, the list must have group expressions".
> Can you advise me which property sets the group expressions?
> Thanks!
>
> "Jeannie" wrote:
> > I'm using SQL Server Reporting Services and need to generate a report listing
> > addresses. I have to accomodate two address lines. How can I set it up so
> > that if there is no second address no blank line appears on the report- Hide quoted text -
> - Show quoted text -
Jeanie,
This would be much easier if you based your entire report on a table
as opposed to a bunch of text boxes in a list. In my experience with
reporting, tables are the way to go. If you're not sure whether you
can do this, post an example of how you want the report to look and
maybe I can help.
Assuming we can do this in just a table:
Let's start from a blank slate.
In Layout mode, delete the table, then delete the list so that your
report body is empty.
Now, instead of placing your table inside a list, just place a table
directly into the report body.
Drag and drop your fields from the Datasets pane into the table. You
can add additional rows and columns as necessary.
Once you've got all the fields in place, right click the detail row
handle (gray box with three horizontal lines in it on the far left
side of the table) that contains your second address.
>From the drop down, select Properties to open the Properties pane.
Find and expand the Visibility property by clicking the plus sign
box.
Now click the right side of the Hidden property where it says False.
Then click the drop down arrow and select the <Expression> option.
In the text window of the resulting Edit Expression dialog, overwrite
'False' with the IIF statement. Make sure to include the = sign.
Click OK and then preview the report.
With any kind of luck it should perform as we hope.|||I've managed to create the table, but the blank address lines are still
printing.
Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
True, False). I've attached this to the row property, not the field.
I want the report to look like this
ABC Recycling
555 Adams Drive
Marietta, GA 30303 USA
Since there is no partneraddress2, the line is hidden( There is actually a
partneraddress3, but I assume I would follow the same procedure as address2)
Thanks for your help
Jeannie
"toolman" wrote:
> On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> wrote:
> > I have figured out how to set up a data table, and placed the iif statement
> > on the address2 line. But when I preview the report I get this message: The
> > table is in a list that has no group expressions defined for it. The ous a
> > data region in a list, the list must have group expressions".
> >
> > Can you advise me which property sets the group expressions?
> > Thanks!
> >
> >
> >
> > "Jeannie" wrote:
> > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > addresses. I have to accomodate two address lines. How can I set it up so
> > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > - Show quoted text -
> Jeanie,
> This would be much easier if you based your entire report on a table
> as opposed to a bunch of text boxes in a list. In my experience with
> reporting, tables are the way to go. If you're not sure whether you
> can do this, post an example of how you want the report to look and
> maybe I can help.
> Assuming we can do this in just a table:
> Let's start from a blank slate.
> In Layout mode, delete the table, then delete the list so that your
> report body is empty.
> Now, instead of placing your table inside a list, just place a table
> directly into the report body.
> Drag and drop your fields from the Datasets pane into the table. You
> can add additional rows and columns as necessary.
> Once you've got all the fields in place, right click the detail row
> handle (gray box with three horizontal lines in it on the far left
> side of the table) that contains your second address.
> >From the drop down, select Properties to open the Properties pane.
> Find and expand the Visibility property by clicking the plus sign
> box.
> Now click the right side of the Hidden property where it says False.
> Then click the drop down arrow and select the <Expression> option.
> In the text window of the resulting Edit Expression dialog, overwrite
> 'False' with the IIF statement. Make sure to include the = sign.
> Click OK and then preview the report.
> With any kind of luck it should perform as we hope.
>|||On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> I've managed to create the table, but the blank address lines are still
> printing.
> Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> True, False). I've attached this to the row property, not the field.
> I want the report to look like this
> ABC Recycling
> 555 Adams Drive
> Marietta, GA 30303 USA
> Since there is no partneraddress2, the line is hidden( There is actually a
> partneraddress3, but I assume I would follow the same procedure as address2)
> Thanks for your help
> Jeannie
>
> "toolman" wrote:
> > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > wrote:
> > > I have figured out how to set up a data table, and placed the iif statement
> > > on the address2 line. But when I preview the report I get this message: The
> > > table is in a list that has no group expressions defined for it. The ous a
> > > data region in a list, the list must have group expressions".
> > > Can you advise me which property sets the group expressions?
> > > Thanks!
> > > "Jeannie" wrote:
> > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> > > - Show quoted text -
> > Jeanie,
> > This would be much easier if you based your entire report on a table
> > as opposed to a bunch of text boxes in a list. In my experience with
> > reporting, tables are the way to go. If you're not sure whether you
> > can do this, post an example of how you want the report to look and
> > maybe I can help.
> > Assuming we can do this in just a table:
> > Let's start from a blank slate.
> > In Layout mode, delete the table, then delete the list so that your
> > report body is empty.
> > Now, instead of placing your table inside a list, just place a table
> > directly into the report body.
> > Drag and drop your fields from the Datasets pane into the table. You
> > can add additional rows and columns as necessary.
> > Once you've got all the fields in place, right click the detail row
> > handle (gray box with three horizontal lines in it on the far left
> > side of the table) that contains your second address.
> > >From the drop down, select Properties to open the Properties pane.
> > Find and expand the Visibility property by clicking the plus sign
> > box.
> > Now click the right side of the Hidden property where it says False.
> > Then click the drop down arrow and select the <Expression> option.
> > In the text window of the resulting Edit Expression dialog, overwrite
> > 'False' with the IIF statement. Make sure to include the = sign.
> > Click OK and then preview the report.
> > With any kind of luck it should perform as we hope.- Hide quoted text -
> - Show quoted text -
Now I'm perplexed. I know it works, having done it in most of my
address list reports. I even copied and pasted your expression
directly into one of my existing reports and it worked (all I changed
was the field name). You might try ="" instead of =Nothing but if
your field is truly empty, this shouldn't make a difference. Beyond
that I'm stumped. Sorry|||Sory about the delay in responding. But i didn't return to the client's site
until today. I am happy to say that with your help, I figured it out. The
reason it didn't work is because the cells, that appeared to be null,
actually had 40 spaces in them! Today I ran a query and looked at the data
and noticed that all the 'empty" cells had a block highlighted when I
positioned my cursor on the cell. So I changed the Iif statement to
acknowledge the spaces and it worked.
Thank you so much for your help!
"toolman" wrote:
> On Oct 23, 1:04 pm, Jeannie <Jean...@.discussions.microsoft.com> wrote:
> > I've managed to create the table, but the blank address lines are still
> > printing.
> >
> > Here is the formula I've used: =IIF(Fields!partneraddress2.Value = Nothing,
> > True, False). I've attached this to the row property, not the field.
> >
> > I want the report to look like this
> > ABC Recycling
> > 555 Adams Drive
> > Marietta, GA 30303 USA
> >
> > Since there is no partneraddress2, the line is hidden( There is actually a
> > partneraddress3, but I assume I would follow the same procedure as address2)
> >
> > Thanks for your help
> > Jeannie
> >
> >
> >
> > "toolman" wrote:
> > > On Oct 22, 10:34 am, Jeannie <Jean...@.discussions.microsoft.com>
> > > wrote:
> > > > I have figured out how to set up a data table, and placed the iif statement
> > > > on the address2 line. But when I preview the report I get this message: The
> > > > table is in a list that has no group expressions defined for it. The ous a
> > > > data region in a list, the list must have group expressions".
> >
> > > > Can you advise me which property sets the group expressions?
> > > > Thanks!
> >
> > > > "Jeannie" wrote:
> > > > > I'm using SQL Server Reporting Services and need to generate a report listing
> > > > > addresses. I have to accomodate two address lines. How can I set it up so
> > > > > that if there is no second address no blank line appears on the report- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Jeanie,
> > > This would be much easier if you based your entire report on a table
> > > as opposed to a bunch of text boxes in a list. In my experience with
> > > reporting, tables are the way to go. If you're not sure whether you
> > > can do this, post an example of how you want the report to look and
> > > maybe I can help.
> > > Assuming we can do this in just a table:
> > > Let's start from a blank slate.
> > > In Layout mode, delete the table, then delete the list so that your
> > > report body is empty.
> > > Now, instead of placing your table inside a list, just place a table
> > > directly into the report body.
> > > Drag and drop your fields from the Datasets pane into the table. You
> > > can add additional rows and columns as necessary.
> > > Once you've got all the fields in place, right click the detail row
> > > handle (gray box with three horizontal lines in it on the far left
> > > side of the table) that contains your second address.
> > > >From the drop down, select Properties to open the Properties pane.
> > > Find and expand the Visibility property by clicking the plus sign
> > > box.
> > > Now click the right side of the Hidden property where it says False.
> > > Then click the drop down arrow and select the <Expression> option.
> > > In the text window of the resulting Edit Expression dialog, overwrite
> > > 'False' with the IIF statement. Make sure to include the = sign.
> > > Click OK and then preview the report.
> > > With any kind of luck it should perform as we hope.- Hide quoted text -
> >
> > - Show quoted text -
> Now I'm perplexed. I know it works, having done it in most of my
> address list reports. I even copied and pasted your expression
> directly into one of my existing reports and it worked (all I changed
> was the field name). You might try ="" instead of =Nothing but if
> your field is truly empty, this shouldn't make a difference. Beyond
> that I'm stumped. Sorry
>
