I have a requirement to have a mailing label the can have up to 3 Address
fields
Homer Simpson
Address Line 1
Address Line 2
Address Line 3
Springfield, IL 999999
If 2 an 3 are not used I would like the City State and Zip to move up under
Address line 1, right now there are blank lines between Address 1 and
City/State.
I'm using a Table RDL element to display the fields.
JeffExcellent idea! I tried that on one of my reports and it seems to work
well.
--
Keith
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> i am suggesting just after reading your question
> if each address is on a seperate row in the table...why don't u HIDE the
> rows Address2 and Address3 depending on wheather they are nothing or
empty as
> Hidden = iif(isnothing(Address2),True,False)
> let us know if this works...
> "Jeff" wrote:
> > I have a requirement to have a mailing label the can have up to 3
Address
> > fields
> >
> > Homer Simpson
> > Address Line 1
> > Address Line 2
> > Address Line 3
> > Springfield, IL 999999
> >
> > If 2 an 3 are not used I would like the City State and Zip to move up
under
> > Address line 1, right now there are blank lines between Address 1 and
> > City/State.
> >
> > I'm using a Table RDL element to display the fields.
> >
> > Jeff|||Hey Sunny,
Your solution Hides the row (makes invisible) but the rows don't get removed
to bring Address 1 and Address 3, if there is no value in Address 2.
Can you suggest a solution?
It seam like we need a property to "align" the rows "Bring Together"
Jeff
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> i am suggesting just after reading your question
> if each address is on a seperate row in the table...why don't u HIDE the
> rows Address2 and Address3 depending on wheather they are nothing or
empty as
> Hidden = iif(isnothing(Address2),True,False)
> let us know if this works...
> "Jeff" wrote:
> > I have a requirement to have a mailing label the can have up to 3
Address
> > fields
> >
> > Homer Simpson
> > Address Line 1
> > Address Line 2
> > Address Line 3
> > Springfield, IL 999999
> >
> > If 2 an 3 are not used I would like the City State and Zip to move up
under
> > Address line 1, right now there are blank lines between Address 1 and
> > City/State.
> >
> > I'm using a Table RDL element to display the fields.
> >
> > Jeff|||Keith, what other properties (expressions) did you set for the Fields?
Jeff
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:O9mirS9fEHA.704@.TK2MSFTNGP09.phx.gbl...
> Excellent idea! I tried that on one of my reports and it seems to work
> well.
> --
> Keith
>
> "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> > i am suggesting just after reading your question
> >
> > if each address is on a seperate row in the table...why don't u HIDE the
> > rows Address2 and Address3 depending on wheather they are nothing or
> empty as
> > Hidden = iif(isnothing(Address2),True,False)
> >
> > let us know if this works...
> >
> > "Jeff" wrote:
> >
> > > I have a requirement to have a mailing label the can have up to 3
> Address
> > > fields
> > >
> > > Homer Simpson
> > > Address Line 1
> > > Address Line 2
> > > Address Line 3
> > > Springfield, IL 999999
> > >
> > > If 2 an 3 are not used I would like the City State and Zip to move up
> under
> > > Address line 1, right now there are blank lines between Address 1 and
> > > City/State.
> > >
> > > I'm using a Table RDL element to display the fields.
> > >
> > > Jeff
>|||Got it!.. need to set the expression on the Row, I was doing it on the
Field/Cell...
Works perfect...
Thanks guys
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:23A578CF-E937-4A0B-BDAA-CBA2468F24C8@.microsoft.com...
> I have a requirement to have a mailing label the can have up to 3 Address
> fields
> Homer Simpson
> Address Line 1
> Address Line 2
> Address Line 3
> Springfield, IL 999999
> If 2 an 3 are not used I would like the City State and Zip to move up
under
> Address line 1, right now there are blank lines between Address 1 and
> City/State.
> I'm using a Table RDL element to display the fields.
> Jeff|||nothing.
I selected the row (click on the grey box outside [to the left] of the
table) and then navigated to the properties window. Expand visibility. I
added the following expression under Hidden:
=iif(isnothing(Fields!AddlInfo.Value),True,False)
(My column is called AddlInfo)
--
Keith
"Jeff" <ernzddy@.msn.com> wrote in message
news:ukGHiV9fEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Keith, what other properties (expressions) did you set for the Fields?
> Jeff
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:O9mirS9fEHA.704@.TK2MSFTNGP09.phx.gbl...
> > Excellent idea! I tried that on one of my reports and it seems to work
> > well.
> >
> > --
> > Keith
> >
> >
> > "Sunny" <Sunny@.discussions.microsoft.com> wrote in message
> > news:8ABAF59D-0CFB-4FC3-8221-9FD7EA7927A5@.microsoft.com...
> > > i am suggesting just after reading your question
> > >
> > > if each address is on a seperate row in the table...why don't u HIDE
the
> > > rows Address2 and Address3 depending on wheather they are nothing or
> > empty as
> > > Hidden = iif(isnothing(Address2),True,False)
> > >
> > > let us know if this works...
> > >
> > > "Jeff" wrote:
> > >
> > > > I have a requirement to have a mailing label the can have up to 3
> > Address
> > > > fields
> > > >
> > > > Homer Simpson
> > > > Address Line 1
> > > > Address Line 2
> > > > Address Line 3
> > > > Springfield, IL 999999
> > > >
> > > > If 2 an 3 are not used I would like the City State and Zip to move
up
> > under
> > > > Address line 1, right now there are blank lines between Address 1
and
> > > > City/State.
> > > >
> > > > I'm using a Table RDL element to display the fields.
> > > >
> > > > Jeff
> >
>
Showing posts with label address. Show all posts
Showing posts with label address. Show all posts
Tuesday, March 27, 2012
Address table structure
Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country.
> State is specific to US, in Canada they have provinces, other contries have
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> > Is the table structure to store addresses somewhat standardized?
> >
> > Address1
> > Address2
> > City
> > State
> > ZipCode
> > Country
> >
> > I ask because I am working with international addresses for the first time
> > and I don't know what to expect in terms of what my users will want to
> > enter.
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>> Not really, it varies from database to database. The best thing you can
>> do
>> is make it somewhat generic and not make any assumptions about any
>> country.
>> State is specific to US, in Canada they have provinces, other contries
>> have
>> regions, districts, you name it. I think this structure might be more
>> generic and applicable to many countries:
>> Address1
>> Address2
>> Address3
>> City
>> PostalCode
>> Region
>> Country
>> Of course, address formatting varies from country to country.
>> Roman
>> --
>> Roman Rehak
>> http://sqlblog.com/blogs/roman_rehak
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>> > Is the table structure to store addresses somewhat standardized?
>> >
>> > Address1
>> > Address2
>> > City
>> > State
>> > ZipCode
>> > Country
>> >
>> > I ask because I am working with international addresses for the first
>> > time
>> > and I don't know what to expect in terms of what my users will want to
>> > enter.
>>sql
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
--
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country.
> State is specific to US, in Canada they have provinces, other contries have
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> > Is the table structure to store addresses somewhat standardized?
> >
> > Address1
> > Address2
> > City
> > State
> > ZipCode
> > Country
> >
> > I ask because I am working with international addresses for the first time
> > and I don't know what to expect in terms of what my users will want to
> > enter.
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>> Not really, it varies from database to database. The best thing you can
>> do
>> is make it somewhat generic and not make any assumptions about any
>> country.
>> State is specific to US, in Canada they have provinces, other contries
>> have
>> regions, districts, you name it. I think this structure might be more
>> generic and applicable to many countries:
>> Address1
>> Address2
>> Address3
>> City
>> PostalCode
>> Region
>> Country
>> Of course, address formatting varies from country to country.
>> Roman
>> --
>> Roman Rehak
>> http://sqlblog.com/blogs/roman_rehak
>>
>> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
>> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>> > Is the table structure to store addresses somewhat standardized?
>> >
>> > Address1
>> > Address2
>> > City
>> > State
>> > ZipCode
>> > Country
>> >
>> > I ask because I am working with international addresses for the first
>> > time
>> > and I don't know what to expect in terms of what my users will want to
>> > enter.
>>sql
Address table structure
Is the table structure to store addresses somewhat standardized?
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter
.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country
.
> State is specific to US, in Canada they have provinces, other contries hav
e
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...[vbcol=seagreen]
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>
Address1
Address2
City
State
ZipCode
Country
I ask because I am working with international addresses for the first time
and I don't know what to expect in terms of what my users will want to enter
.Not really, it varies from database to database. The best thing you can do
is make it somewhat generic and not make any assumptions about any country.
State is specific to US, in Canada they have provinces, other contries have
regions, districts, you name it. I think this structure might be more
generic and applicable to many countries:
Address1
Address2
Address3
City
PostalCode
Region
Country
Of course, address formatting varies from country to country.
Roman
Roman Rehak
http://sqlblog.com/blogs/roman_rehak
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
> Is the table structure to store addresses somewhat standardized?
> Address1
> Address2
> City
> State
> ZipCode
> Country
> I ask because I am working with international addresses for the first time
> and I don't know what to expect in terms of what my users will want to
> enter.|||What are some best practices for data types? Is it best practice to store a
list of countries and states in a separate table and reference the id in the
main address table?
"Roman Rehak" wrote:
> Not really, it varies from database to database. The best thing you can do
> is make it somewhat generic and not make any assumptions about any country
.
> State is specific to US, in Canada they have provinces, other contries hav
e
> regions, districts, you name it. I think this structure might be more
> generic and applicable to many countries:
> Address1
> Address2
> Address3
> City
> PostalCode
> Region
> Country
> Of course, address formatting varies from country to country.
> Roman
> --
> Roman Rehak
> http://sqlblog.com/blogs/roman_rehak
>
> "Curtis" <Curtis@.discussions.microsoft.com> wrote in message
> news:91008C72-8ADB-475D-A64D-7714F5A41124@.microsoft.com...
>
>|||Hi
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
Sure
"Curtis" <Curtis@.discussions.microsoft.com> wrote in message
news:2F00010D-EE81-438E-A0F8-8BA573B57985@.microsoft.com...[vbcol=seagreen]
> What are some best practices for data types? Is it best practice to store
> a
> list of countries and states in a separate table and reference the id in
> the
> main address table?
> "Roman Rehak" wrote:
>
Address Searching (sp?)
I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave
,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave
,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>
Address Searching (sp?)
I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
James
The problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
James
The problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>
Address Searching (sp?)
I have a form that inputs an address (city/state/zip/etc.). Basically I
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>
need to search our database for this address, prior to it being entered.
Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
etc. Anyone have an affective algorithm for searching for comparable
addresses? Just curious if someone has run into/solved this problem
effectively.
Thanks,
JamesThe problem has been solved quite effectively by address correction software
vendors. It might be quite a task to write a proprietary set of functions to
do the same thing.
Don't know if this is feasible for you but you could have a separate field
to store the street type and then provide a drop-down list so that the user
doesn't have the option to input a variant (such as Ave. instead of Avenue)
"James" wrote:
> I have a form that inputs an address (city/state/zip/etc.). Basically I
> need to search our database for this address, prior to it being entered.
> Problem in that people type St. vs. Street vs. St, Ave. vs. Avenue vs. Ave,
> etc. Anyone have an affective algorithm for searching for comparable
> addresses? Just curious if someone has run into/solved this problem
> effectively.
> Thanks,
> James
>
>
address management software
Does anyone know of any address management software that is compliant with sql server 2000, i.e. for record duplication and address cleansing etc...
Thanks!any suggestions appreciated!sql
Thanks!any suggestions appreciated!sql
Address layout-no blank line
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
>
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
>
Address field problem.
I have a number of text boxes (one on top of the other) to display the addresses in my report. I have Address_name, Address1, Address2, Address_city, Address_State, Address_PostalCode - Each with its own text box except for the city, state, and postal code for which I just combined the fields. However, I have many who do not have any data for Address2 and don't want an empty line in the address. Is there an expression I can use to display the next line or move/shift up the remaining parts of the address? Would the iif and isnothing expressions be helpful, and if so, how do I write it correctly?
If it is ok to put them all into one textbox, you could do this:
=Fields!Address_name.Value
& chr(10) & Fields!Address1.Value
& IIf(Len(Fields!Address2.Value) > 0, chr(10) & Fields!Address2.Value, "")
& chr(10) & Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value
If they have to have their own textbox and you just want to move it up if the Address2 is empty, then you could try something like this.
In the expression for the Address2 textbox:
=IIf(Len(Fields!Address2.Value) > 0, Fields!Address2.Value, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value)
Then, in the expression for the City/State/Postal Code:
=IIf(Len(Fields!Address2.Value) > 0, Fields!Address_city.Value & ", " & Fields!Address_State.Value & " " & Fields!Address_PostalCode.Value, "")
Hope this helps.
Jarret
|||Thanks mate, I'll give it a try.!
What does "Len" do?
|||Len gives you then length of the object you pass in. In this case, if the Address2 is NULL or an empty string, Len will return 0 and it will be skipped with the logic in the code.
Let me know if that fixes your issue.
Jarret
|||I see, thanks for the info. I will try this in about an hour or so, I'll post back and give you an update.
Thanks again Jarret,
Bill
Friday, February 24, 2012
Adding Group Subscriptions
Hi,
Can anyone let me know how to add a group in the domain to reporting
services.I tried adding email address and could easily subscribe to reports.
but when i tried to add a group by spacifying domainname\group name, it did
not work. Can anyone please help!!!
SachinI got a solution for this. All i did was to add a group email in our email
server. Reporting services lets us enter only email address .
"Sachin" wrote:
> Hi,
> Can anyone let me know how to add a group in the domain to reporting
> services.I tried adding email address and could easily subscribe to reports.
> but when i tried to add a group by spacifying domainname\group name, it did
> not work. Can anyone please help!!!
> Sachin
Can anyone let me know how to add a group in the domain to reporting
services.I tried adding email address and could easily subscribe to reports.
but when i tried to add a group by spacifying domainname\group name, it did
not work. Can anyone please help!!!
SachinI got a solution for this. All i did was to add a group email in our email
server. Reporting services lets us enter only email address .
"Sachin" wrote:
> Hi,
> Can anyone let me know how to add a group in the domain to reporting
> services.I tried adding email address and could easily subscribe to reports.
> but when i tried to add a group by spacifying domainname\group name, it did
> not work. Can anyone please help!!!
> Sachin
Sunday, February 19, 2012
Adding different columns from different table
I have three tables.
Member(name, address, ID)
Loan(ID, startdate, amount)
Deposite(ID, startdate, amount)
I wanna create a report which look like this.
ID MembersName startdate address etc
Member can be either borrower or a depositor.
I'm thinking of using inner join. Can anyone help me to write the query?
Thanks
*If* ID links all three tables then the query would look like this:
This query is based on a member being *Either* a borrower OR a depositor.
Code Snippet
Select
m.ID,
M.Name,
Coalesce(L.StartDate, D.StartDate) as StartDate,
m.Address
From Member m (nolock)
left outer join Loan L (nolock)
on m.ID = L.ID
left outer join Deposit D (nolock)
on m.ID = D.ID
If a member can be a borrower AND/OR a depositor, the query would look like this:
Code Snippet
Select
m.ID,
S.AccountType,
m.Name,
S.StartDate,
m.Address
From
Member m (nolock),
inner join
(Select
ID,
StartDate,
'Loan' as AccountType
From
Loan l (nolock)
union
Select
ID,
StartDate,
'Deposit' as AccountType
From
Deposit d (nolock)
) S
on
m.ID = S.ID
Order by ID
You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.
HtH
BobP
|||Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.
As a example,
If the start date of one perticular member is in borrower table , then he is a borrower.
Can you please help me to write this query too?I really appriciate
Thanks
|||Actually, the 2nd query I have above will show that. I included a column for member type.
BobP
Adding different columns from different table
I have three tables.
Member(name, address, ID)
Loan(ID, startdate, amount)
Deposite(ID, startdate, amount)
I wanna create a report which look like this.
ID MembersName startdate address etc
Member can be either borrower or a depositor.
I'm thinking of using inner join. Can anyone help me to write the query?
Thanks
*If* ID links all three tables then the query would look like this:
This query is based on a member being *Either* a borrower OR a depositor.
Code Snippet
Select
m.ID,
M.Name,
Coalesce(L.StartDate, D.StartDate) as StartDate,
m.Address
From Member m (nolock)
left outer join Loan L (nolock)
on m.ID = L.ID
left outer join Deposit D (nolock)
on m.ID = D.ID
If a member can be a borrower AND/OR a depositor, the query would look like this:
Code Snippet
Select
m.ID,
S.AccountType,
m.Name,
S.StartDate,
m.Address
From
Member m (nolock),
inner join
(Select
ID,
StartDate,
'Loan' as AccountType
From
Loan l (nolock)
union
Select
ID,
StartDate,
'Deposit' as AccountType
From
Deposit d (nolock)
) S
on
m.ID = S.ID
Order by ID
You really cant use an inner join on all 3 tables since it would look for only members that had Deposit and Loan accounts.
HtH
BobP
|||Thanks I will try with this..Also I wanna add another column now. There i want to show whether the member is a depositor or a borrower. we can get to know that from depositor or borrower table.
As a example,
If the start date of one perticular member is in borrower table , then he is a borrower.
Can you please help me to write this query too?I really appriciate
Thanks
|||Actually, the 2nd query I have above will show that. I included a column for member type.
BobP
Subscribe to:
Posts (Atom)