Calculated field in dataset using Previous() function

Hi,
Iam trying to add a calculated field to my report dataset which based on
value of a field in the previous row. The calculated field is defined as
=IIf(Previous(Fields!Col1.Value)=Fields!Col1.Value,0,CDbl(Fields!Col2.Value)+CDbl(Fields!Col3.Value))
. Iam basically checking the value of Col1 in previous row with value in
current row. Iam getting the following error when tried to build the report
"An internal error occurred on the report server. See the error log for more
details.". I could not find anything in error log.
Can anyone help in resolving this problem ?
Thanks,
RKOn Dec 3, 1:35 am, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> Iam trying to add a calculated field to my report dataset which based on
> value of a field in the previous row. The calculated field is defined as
> =IIf(Previous(Fields!Col1.Value)=Fields!Col1.Value,0,CDbl(Fields!Col2.Value-)+CDbl(Fields!Col3.Value))
> . Iam basically checking the value of Col1 in previous row with value in
> current row. Iam getting the following error when tried to build the report
> "An internal error occurred on the report server. See the error log for more
> details.". I could not find anything in error log.
> Can anyone help in resolving this problem ?
> Thanks,
> RK
I found that the Previous(Fields!XYZ.Value) function only works in the
Table and Matrix expressions -- expresions that are evaluated at
render time. Although it would make sense to have them in the DataSet
as a calculated field, I've not been able to get this to work.
Oracle has LAG and LEAD functions that return the previous/next value
of a Field as part of the query results. Microsoft T-SQL does not
have an equivalent.
-- Scottsql

Calculate/create Row Number without identity

How do I output a row number for a table solely for the purpose of
querying for a unique row?

In my problem, the table from a legacy system does not have a primary
key, so it limits various querying I'd like to do that identifies
uniqueness in the table.

The problem is that since I'm using DTS to simply copy the table to
SQL, I don't want to create identity rows.chrispycrunch (chrispycrunch@.gmail.com) writes:
> How do I output a row number for a table solely for the purpose of
> querying for a unique row?
> In my problem, the table from a legacy system does not have a primary
> key, so it limits various querying I'd like to do that identifies
> uniqueness in the table.
> The problem is that since I'm using DTS to simply copy the table to
> SQL, I don't want to create identity rows.

Not sure why the use of DTS would preclude the use of an IDENTITY row,
but then again I have no experience of DTS. After all, IDENTITY seems
perfect in this case.

If there really is a problem for DTS, you make a two-stepper and have
DTS to park the data in a transient table, and insert from there into
the target table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi. Are you sure that the original table has no duplicates? If there
are any
duplicate, you do not have unique rows. However, assuming you want to
establish an identity for future use, you could extract the old data
row-by-row
and set a unique identity of your own construction, and maintain that
with
any new rows that get added (and make sure to have a unique index on
that
value.
All in all, the identity column is made for this.
Joe Weinstein at BEA

Calculate row size

Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that '
Thanks
Richard_SQLTable size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that '
> Thanks
> Richard_SQL|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
--
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> > Hello everybody:
> >
> > How can I do to calculate the record size in bytes, and the table size
in
> > bytes? Does exist any stored procedure to do that '
> >
> > Thanks
> >
> > Richard_SQL
>|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:
> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> > Table size:
> >
> > EXEC sp_spaceused 'table_name'
> >
> > For row size, you can average by the above result / SELECT COUNT(*) FROM
> > table_name
> >
> > For individual rows, this gets a little trickier because there is overhead
> > for certain datatypes, and whether the data is nullable and/or is null.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> > news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> > > Hello everybody:
> > >
> > > How can I do to calculate the record size in bytes, and the table size
> in
> > > bytes? Does exist any stored procedure to do that '
> > >
> > > Thanks
> > >
> > > Richard_SQL
> >
> >
>
>

Calculate row size

Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that '
Thanks
Richard_SQLTable size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that '
> Thanks
> Richard_SQL|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
--
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in[vbcol=seagreen]
>|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:

> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There ar
e
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>

Calculate row size

Hello everybody:
How can I do to calculate the record size in bytes, and the table size in
bytes? Does exist any stored procedure to do that ?
Thanks
Richard_SQL
Table size:
EXEC sp_spaceused 'table_name'
For row size, you can average by the above result / SELECT COUNT(*) FROM
table_name
For individual rows, this gets a little trickier because there is overhead
for certain datatypes, and whether the data is nullable and/or is null.
http://www.aspfaq.com/
(Reverse address to reply.)
"Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
> Hello everybody:
> How can I do to calculate the record size in bytes, and the table size in
> bytes? Does exist any stored procedure to do that ?
> Thanks
> Richard_SQL
|||Richard,
Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
Also, for more exact calculations concerning table and row size, see
"Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
related topics for calculating table size for a heap table and a clustered
table.
Ron
Ron Talmage
SQL Server MVP
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Table size:
> EXEC sp_spaceused 'table_name'
> For row size, you can average by the above result / SELECT COUNT(*) FROM
> table_name
> For individual rows, this gets a little trickier because there is overhead
> for certain datatypes, and whether the data is nullable and/or is null.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Richard_SQL" <Richard_SQL@.discussions.microsoft.com> wrote in message
> news:ED295DB2-396D-4E5D-B42B-CAF858473F04@.microsoft.com...
in
>
|||Thanks a lot for your help.
Richard
"Ron Talmage" wrote:

> Richard,
> Be sure to use @.updateusage = 'TRUE' when invoking sp_spaceused.
> Also, for more exact calculations concerning table and row size, see
> "Estimating the Size of a Table" in SQL Server 2000 Books Online. There are
> related topics for calculating table size for a heap table and a clustered
> table.
> Ron
> --
> Ron Talmage
> SQL Server MVP
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OSjvpDB9EHA.4004@.tk2msftngp13.phx.gbl...
> in
>
>

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance

|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance

|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

|||Hi,

I am also facing same problem...do u got any solution....
plz help mesql

Calculate difference between two rows

Hi,

i have a matrix, and in that matrix i need to have one column which calculates the percentage change between a value on the current row and the same value on the previous row.

Is this possible? The RunningValue() function isn't of help as it can't help me calculate the change between two rows, and Previous() doesn't work in a matrix (why?!!!!!). Also calculating this as part of the query isn't possible as there is a single row group on the matrix, and the query is MDX.*

Thanks,

sluggy

*for those who are curious, the matrix is showing data an a per week basis, the row group is snapshot date, i am trying to measure the change in sales at each snapshot.

Hi sluggy,

I Have the same problem now.

Have you found a solution?

Thanks

|||

I sure did. I used some custom code, on each row i called the function with the value from that row, and the function kept track of the previous values in an array.

If you can wait for about 24 hours from the time of this post, i will be able to post the function code for you.

|||Yes please send me the code.|||

Here you go....

think of this as measuring the change in booked theatre seats - i have the amount booked, and the capacity of the theatre. In the matrix field that contains the calculation, i have this as an expression:

=FormatPercent(

Code.CalcChangeInFill( Sum(Fields!Booked.Value), Sum(Fields!Capacity.Value), RowNumber("MyMatrix") ),

2,

true,

false,

true

)

and then in the code i have this:

Dim bookedVals() As Decimal

Dim capacityVals() As Decimal

Function CalcChangeInFill(ByVal bookedAmt As Decimal, ByVal capacityAmt As Decimal, ByVal rowNum as Integer) As Decimal

Dim upper As Integer

upper = 0

On Error Resume Next

If rowNum > 1 Then

upper = UBound(bookedVals) + 1

End If

ReDim Preserve bookedVals(upper)

bookedVals(upper) = bookedAmt

ReDim Preserve capacityVals(upper)

capacityVals(upper) = capacityAmt

If upper > 0 Then

If capacityVals(upper) = capacityVals(upper - 1) Then

If capacityVals(upper) = 0 Then

CalcChangeInFill = 0

Else

CalcChangeInFill = (bookedVals(upper) - bookedVals(upper - 1)) / capacityVals(upper)

End If

Else

If capacityVals(upper) = 0 Then

CalcChangeInFill = -100

ElseIf capacityVals(upper - 1) = 0 Then

CalcChangeInFill = 100

Else

CalcChangeInFill = (bookedVals(upper) / capacityVals(upper)) - (bookedVals(upper - 1) / capacityVals(upper - 1))

End If

End If

Else

CalcChangeInFill = 0

End If

End Function

This calculation got a little complicated because the capacity of the theatre could change (i.e. extra seats were added). I tracked the rowNum because this would indicate the start of a matrix when i had multiple matrices (i.e. i had a list control grouping by Week, so each matrix would have a week's worth of data in it, each row would represent one day of bookings) - at the start of each matrix the row number is 1, so i would know to "restart" the arrays and the calculation.

I hope this helps :)

|||any ideas on getting this to work on columns?

the columns in my matrix are calendar year with a sub-group that sums a couple of values. I need the percentage increase/decrease in these sums per year.

thnx in advance|||

Hi

i've just done this by creating a function that takes the current value in
the column and puts it into an array. The columns populate from left to right and
top to bottom.

So you simply need to put put in a check depending on the number of columns that
are there. I know its dynamic but I mean IE:

GROUP YEAR

InvoiceQty SupplyQty

xxxxx xxxxxxxx

When doing the calculation put a table next to matrix with the same groupings
and call a function to get the values from the array and do a calculation on them.

If i'm unclear just tell me what u need..

Gerhard Davids

|||

Hi Everyone,

I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.

=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))

NOTE: matrix1_FiscalMonth is my row group name

To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%

Regard,

AA

"Small things done consistently in strategic places create major impact"

Calculate difference between previous row and insert to new column -problem

Hi!

I have an algorithm that uses cursors to calculate difference between row and row-1 in a certain (int-type) column. How could I insert the difference value to this same table (or generate new dynamic table) as a new column?

I need this information to do some reporting with Reporting Services and show the difference there...

Thanks!

-Jukka

hi,

can you post your proc?|||

My knee-jerk reaction would be to suggest self-joining the table. This depends on whether you have a ready-made criteria that you can use either for keying or ordering your table. A simple-minded example might be something like:

declare @.xample table
( rowId integer primary key,
theValue numeric (5,2)
)
insert into @.xample
select number,
33.34*dbo.rand() + 33.33*dbo.rand() + 33.33*dbo.rand()
from master.dbo.spt_values
where name is null
and number <= 5

select a.rowId,
a.theValue as [A Value],
b.theValue as [B Value],
a.theValue - b.theValue as Difference
from @.xample a
join @.xample b
on a.rowId = b.rowId + 1

/*
rowId A Value B Value Difference
-- - - -
1 6.82 37.87 -31.05
2 20.28 6.82 13.46
3 35.15 20.28 14.87
4 56.60 35.15 21.45
5 35.21 56.60 -21.39
*/

Cal Measure % against total

How to define calculated member whose value should be the row value divided by total of all rows. Example, sales of 2005 was 50k and sales for 2006 was 100k. The % 2005 sales is 33% of total (150k) and 2006 is 67%.

Hi

For example to get the percentage of the Unit Sales for one customer of all customers, you write:

[Measures].[Unit Sales] / ([Measures].[Unit Sales], [Customers].[All Customers])

The key is, to divide by a MDX tuple ([Measures].[Unit Sales], [Customers].[All Customers]) which brings the value for all customers.

Hans

|||

Thank Hans

This problem is solved. Please guide me to get the same result with multiple dimensions.

Also, i have a variance calculated member, which is on the date time diminsion [Sale of 2005] - [Sale of 2006] = [Variance 2005]. I would like to see three measures, [Year 2005 Sale], [Year 2006 Sale] and variance. But i get Sales and variance figure under Year 2005 and same under Year 2006. How to get the desired result.

Thanks

Shekhar

|||

Hi Shekhar,

I'm not sure, if I did understand you right, but I think it's because your [Variance] is on the Time Dimension. I do it in my projects so, that I create a calculated member like:

MEMBER [Measures].[Year variance] AS ([Measures].[Unit Sales],[Time].Currentmember.Prevmember) - [Measures].[Unit Sales]

If Currentmember is Year 2007, den Prevmember is Year 2006 and so on.

If you use now all 3 in a select, you can see it "flat"

select

{ [Measures].[Year 2005 Sales], [Measures].[Year 2006 Sale], [Measures].[Year variance]} on columns,

.....

Hans

bytes used per datatype

Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
Thanks
Look here:
http://groups.google.de/groups?q=row...oft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>

bytes used per datatype

Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
http://groups.google.de/groups?q=rowsize+sql+server&hl=de&lr=&selm=udj18LFHAHA.259%40cppssbbsa02.microsoft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>

bytes used per datatype

Trying to calculate the row size and wanted to know if theres a bettter way
to do it than just finding how much each datatype sizes and add accordingly.
I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
the size of these datatypes
ThanksLook here:
%40cppssbbsa02.microsoft.com&rnum=3" target="_blank">http://groups.google.de/groups?q=ro...soft.com&rnum=3
select sum(CHARACTER_MAXIMUM_LENGTH)
from information_schema.columns
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'authors'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:O0J4XGLRFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Trying to calculate the row size and wanted to know if theres a bettter
> way
> to do it than just finding how much each datatype sizes and add
> accordingly.
> I have an int, datetime,Smalldatetime,money,bit ,smallint. Can you tell me
> the size of these datatypes
> Thanks
>

Bypass transaction log

Hi,
I have a database in full mode, can i issue a delete
command that deletes a single row bypassing the
transaction log?
Thanks in advance
MiguelYou can use TRUNCATE TABLE to delete the contents of the
entire table without logging. I am unaware of any way to
do this at the row level.
>--Original Message--
>Hi,
>I have a database in full mode, can i issue a delete
>command that deletes a single row bypassing the
>transaction log?
>Thanks in advance
>Miguel
>.
>|||That's not possible.
Linchi
>--Original Message--
>Hi,
>I have a database in full mode, can i issue a delete
>command that deletes a single row bypassing the
>transaction log?
>Thanks in advance
>Miguel
>.
>

but...

hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.

>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
auto-[vbcol=seagreen]
places[vbcol=seagreen]
new
>
>.
>
Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:

>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

but...

hi,
thanks for replying me.
increasing (decreasing too?) the WIDTH of a row because of
an update is a new information for me, and i would also
say it is quite shocking for me.
i allways thought table structure is a fixed-width, so the
space for all of the fields is allocated in the same time
and PLACE in advance for each inserted record.
or maybe do you mean that the index may be compuond by
varchar fields so changing the values may change the
lenght of the compound index-value, for example an index-
value coumpound by 2 fields may be changed from "X"+"A"
to "X"+"ABCDEF"
(so if, for example, the table contains integer fields
only, is Fill-factor 100 still be the optimal option even
when updates are concerned)
i guess it's a long story to explain why a clustered index
is actually needs to contain ALL fileds ...?
i probablly missing something (or a lot of things)...
thanks again.
edo.

>--Original Message--
>Not quite true.
>You're on the right track as far as inserts are
concerned, but updates are
>another matter.
>Because clustered indexes contain ALL columns, any
updates that increase the
>width of the row might cause a page split.
>Therefore, whether 100% is optimal or not depends on
whether there are any
>updates to the table.
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"edo" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1c2db01c4528a$671b1890$a601280a@.phx
.gbl...
auto-[vbcol=seagreen]
places[vbcol=seagreen]
new[vbcol=seagreen]
>
>.
>Hi Edo,
On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:

>hi,
>thanks for replying me.
>increasing (decreasing too?) the WIDTH of a row because of
>an update is a new information for me, and i would also
>say it is quite shocking for me.
>i allways thought table structure is a fixed-width, so the
>space for all of the fields is allocated in the same time
>and PLACE in advance for each inserted record.
This is only true if the row contains no varying length columns. Each
table that holds at least one varchar, nvarchar or varbinary column has
rows with varying length.
(snip)
>i guess it's a long story to explain why a clustered index
>is actually needs to contain ALL fileds ...?
Not at all. The clustered index determines the order in which rows are
stored in the data file. Suppose you have a clustered index on an integer
column, there are rows with values 1 and 3 for that column and you then
insert a row with value 2. In that case, the database will store the
entire row between the rows vor key value 1 and 3, not only the key value.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo's answered most of your questions already here but I'll just answer
that qn you put about whether 100% fillfactor is optimal if all columns are
fixed width such as integer. I'd say that that answer to that is yes - if
the columns are fixed width, then the underlying storage requirements will
never grow for a given row, so there should not be any requirement to split
storage pages. Even if there is some obscure cause of page splits in this
scenario, I'd suggest that would be rare & therefore the 100% fillfactor
would still be optimal.
Regards,
Greg Linwood
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aeatc0poos36e86c2tcthsforql0rbs5lk@.
4ax.com...
> Hi Edo,
> On Mon, 14 Jun 2004 21:40:34 -0700, edo wrote:
>
> This is only true if the row contains no varying length columns. Each
> table that holds at least one varchar, nvarchar or varbinary column has
> rows with varying length.
>
> (snip)
> Not at all. The clustered index determines the order in which rows are
> stored in the data file. Suppose you have a clustered index on an integer
> column, there are rows with values 1 and 3 for that column and you then
> insert a row with value 2. In that case, the database will store the
> entire row between the rows vor key value 1 and 3, not only the key value.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

BulkLoad Problem

Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Oops! Sorry, I meant:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
/>
<xsd:attribute name="Supplier" type="xsd:string"
sql:field="Supplier" />
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
Your schema doesn't actually describe your XML. Try something like this:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:is-constant="1" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" sql:relation="Invoices" />
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string" sql:field=
"Invoice"/>
<xsd:attribute name="Supplier" type="xsd:string" sql:field=
"Supplier"/>
<xsd:attribute name="NetAmount" type="xsd:string"
sql:field="NetAmount"/>
</xsd:complexType>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Rob C" <RobC@.discussions.microsoft.com> wrote in message
news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
Anyone know why this will not work... when I run the script it just inserts
one blank row... No Data !
The Script...
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
objBL.ConnectionString = "provider=SQLOLEDB;data
source=localhost;database=SqlXmlTest;integrated security=SSPI"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
set objBL=Nothing
The Schema...
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Invoices" sql:relation="Invoices" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Invoice" type="xsd:string" />
<xsd:element name="Supplier" type="xsd:string" />
<xsd:element name="NetAmount" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The Data...
<?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
NetAmount="68.66" /></Invoices>
The Table...
CREATE TABLE [dbo].[Invoices] (
[Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
[Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
[NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
|||Thank you ! I am very grateful for your help.
Cheers,
Rob
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:OomSYYYpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> Oops! Sorry, I meant:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field="Invoice"
> />
> <xsd:attribute name="Supplier" type="xsd:string"
> sql:field="Supplier" />
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
> news:u0GX%23FYpEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Your schema doesn't actually describe your XML. Try something like this:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:is-constant="1" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" sql:relation="Invoices" />
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string" sql:field=
> "Invoice"/>
> <xsd:attribute name="Supplier" type="xsd:string" sql:field=
> "Supplier"/>
> <xsd:attribute name="NetAmount" type="xsd:string"
> sql:field="NetAmount"/>
> </xsd:complexType>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Rob C" <RobC@.discussions.microsoft.com> wrote in message
> news:72538FD0-A75B-483F-A549-37C993C21432@.microsoft.com...
> Anyone know why this will not work... when I run the script it just
> inserts
> one blank row... No Data !
> The Script...
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.3.0")
> objBL.ConnectionString = "provider=SQLOLEDB;data
> source=localhost;database=SqlXmlTest;integrated security=SSPI"
> objBL.ErrorLogFile = "c:\error.log"
> objBL.Execute "C:\Schema1.xml", "C:\Test.xml"
> set objBL=Nothing
> The Schema...
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Invoices" sql:relation="Invoices" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Invoice" type="xsd:string" />
> <xsd:element name="Supplier" type="xsd:string" />
> <xsd:element name="NetAmount" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> The Data...
> <?xml version="1.0"?><Invoices><Invoice id="4F10OKGRR91" Supplier="501122"
> NetAmount="52.17" /><Invoice id="4F10OPPRR41" Supplier="501122"
> NetAmount="100.00" /><Invoice id="4F18LXLDE51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F18LXXCJ72" Supplier="176696"
> NetAmount="1000.00" /><Invoice id="4F18O9TJM31" Supplier="176580"
> NetAmount="593.60" /><Invoice id="4F21JBLHK81" Supplier="100900"
> NetAmount="17990.00" /><Invoice id="4F21K2SPU51" Supplier="100900"
> NetAmount="449.50" /><Invoice id="4F21OXHKI11" Supplier="503972"
> NetAmount="382.10" /><Invoice id="4F22IMTLO61" Supplier="176534"
> NetAmount="669.86" /><Invoice id="4F22L1JCD54" Supplier="176596"
> NetAmount="150.00" /><Invoice id="4F22L1OQF72" Supplier="176542"
> NetAmount="4.66" /><Invoice id="4F22L1UQJ51" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F22L1VSM43" Supplier="176542"
> NetAmount="20.00" /><Invoice id="4F23NIPEO42" Supplier="176696"
> NetAmount="1200.00" /><Invoice id="4F23NIYCG21" Supplier="176542"
> NetAmount="100.00" /><Invoice id="4F28PTYGJ51" Supplier="176696"
> NetAmount="5150.00" /><Invoice id="4F28PUOST42" Supplier="503302"
> NetAmount="105.00" /><Invoice id="4G01LBMTJ81" Supplier="176696"
> NetAmount="" /><Invoice id="4H20KTFGH31" Supplier="948650"
> NetAmount="3083.14" /><Invoice id="4H20KTFVC32" Supplier="948650"
> NetAmount="22536.02" /><Invoice id="4H20NTSPE91" Supplier="503302"
> NetAmount="107.10" /><Invoice id="4H23IVGMK51" Supplier="176157"
> NetAmount="40.54" /><Invoice id="4H30JEIMH21" Supplier="948650"
> NetAmount="68.66" /></Invoices>
> The Table...
> CREATE TABLE [dbo].[Invoices] (
> [Invoice] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [Supplier] [char] (50) COLLATE Latin1_General_BIN NULL ,
> [NetAmount] [char] (50) COLLATE Latin1_General_BIN NULL
> ) ON [PRIMARY]
>
>
>