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

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:
>

Adding the Same Column to a Model More Than One Time

I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.

Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.

For background on why I would want to add the same column to a model more than once please read this post.

Thanks.

It should be fairly straightforward, is this not working?

CREATE MINING STRUCTURE Foo
(
CustID LONG KEY,
Age LONG CONTINUOUS,
AgeDisc LONG DISCRETIZED)

INSERT INTO Foo(CustID, Age, AgeDisc)
OPENQUERY(MyDataSource,"SELECT CustID, Age, Age as Age2 FROM MyTable")

|||Sorry Jamie. I was actually about to delete the post as I realized my obvious oversight.

Adding the Same Column to a Model More Than One Time

I know that I can add a column to a mining structure and/or model more than once using XMLA. However, I am having a problem doing the same using DMX. I now that DMX does not support data binding and that it relies on the INSERT INTO... construct to essentially map the structure columns to the incoming comluns from the data source. I tried to use "AS" like I would in SQL but it doesn't seem to like it.

Can anyone confirm that this can only be done using XMLA and not DMX? If I am mistaken, it would be greatly appreciated if you could provide an example on how to do this using DMX.

For background on why I would want to add the same column to a model more than once please read this post.

Thanks.

It should be fairly straightforward, is this not working?

CREATE MINING STRUCTURE Foo
(
CustID LONG KEY,
Age LONG CONTINUOUS,
AgeDisc LONG DISCRETIZED)

INSERT INTO Foo(CustID, Age, AgeDisc)
OPENQUERY(MyDataSource,"SELECT CustID, Age, Age as Age2 FROM MyTable")

|||Sorry Jamie. I was actually about to delete the post as I realized my obvious oversight.

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1|||

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1|||

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding SubTotol of a Group to group

Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000

For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000

2 a 100 2000 0
Team Total 100 0

Total 3400 3000

BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)

I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount

And the finial total it turns out 2400 (3400 - 1000)

I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :

Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0

2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)

this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ?
thank youAre you using "InScope"?|||

Not Really

Now the Problems should be on "Team Total of Exceed "

The Reporting service Cannot just sum up the Exceed for each customer in a Team

I want a solution for it thank you

|||Ok either you are using Inscope or not.

'Not really' doesn't tell me this.|||

adolf garlic wrote:

Ok either you are using Inscope or not.

'Not really' doesn't tell me this.

Sorry,

I 'm not using "Inscope"

|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1884435&SiteID=1|||

What is the expression that you use in Exceed column? Is it

" iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0) " as mentioned in your post?

Why are you using First(Creditlimt) in the expression? First(Creditlimt) will always return the first value in the group.

Try using this expression.

iif (Sum(amount)>Sum(Creditlimt) , Sum(amount)-Sum(Creditlimt), 0)

|||

Sorry this Creditlimit is per customer at a period of time. therefore It may not sum up the Creditlimit. since I grouped from the customer, frist( Credit limit ) will be get the one of the value of creditlimt by each customer comparing with the sum of amount.

thank you I may try this expression tomorrow

|||

Even if you can't change the source query, you can actually add calculated fields to the dataset.

Go to the data tab, then from the dataset window (next to toolbox on the left, display this by choosing View Menu -> Datasets)

Right Click Dataset and choose Add

Select Calculated Field and give it a name

Use the following as the expression:
=Iif(Fields!amount.Value > Fields!Creditlimit.Value, Fields!amount.Value - Fields!Creditlimit.Value, 0)

Adding folders in the solution explorer?

Hi..
Isn′t it possible to create sub folders in the solution explorer?
I have it hard time to create a decent structure inside the SSIS Package folder, since I have like 30 packages in my project. Is it just me being silly or cannot this be done?
Have a nice day
/Erik

nope...

You can add folders to a solution but withing a project, everything is either in packages of misc.

|||ok, thanks for the input though