Ok, I missed the boat somewhere.
I prepared the query below to go though all the columns and tables in
the database and return the count of distinct values for each column and
label them with the table and column names
When I run the query, I receive the following error from the part
labeled #1:
Server: Msg 137, Level 15, State 2, Line 44
Must declare the variable '@.tbl_name'.
What I dont understand is why this error is occurring. I defined the
variable and populated it. I commented out part #1 and tried PRINT
@.tbl_name @.col_name which returned appropriate values.
I have a workaround of commenting out the part labeled #1 and instead
insert the following which produced a list of queries that I copied and
pasted into a new QA window and executed.
PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
'.' + @.col_name + '"' + ' from ' + @.tbl_name
I dont understand why I cannot substitute the variables in a query as I
wish to.
I am also considering building a command string and using exec
sp_executesql.
I welcome comments and suggestions on this matter.
-- -- --
DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
DECLARE CURS_sys_tables_and_cols CURSOR FOR
select sysobjects.name, syscolumns.name from syscolumns, sysobjects
where sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
and sysobjects.name NOT like 'SYS%'
and sysobjects.name NOT IN ( LIST OF TABLES I DONT WANT)
order by sysobjects.name
OPEN CURS_sys_tables_and_cols
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- #1
PRINT @.tbl_name + '.' + @.col_name
Select count(distinct @.col_name) from @.tbl_name
PRINT '--'
FETCH NEXT FROM CURS_sys_tables_and_cols
INTO @.tbl_name, @.col_name
END
CLOSE CURS_sys_tables_and_cols
DEALLOCATE CURS_sys_tables_and_cols
*** Sent via Developersdex http://www.examnotes.net ***SJM,
I think you'll need to use Dynamic SQL to use a variable for the table name
in your query i.e., EXEC or sp_executesql.
Check it out in the SQL BOL and at Erland's article:
http://www.sommarskog.se/dynamic_sql.html
HTH
Jerry
"SJM" <nospam@.devdex.com> wrote in message
news:eIAwXiRxFHA.624@.TK2MSFTNGP11.phx.gbl...
> Ok, I missed the boat somewhere.
> I prepared the query below to go though all the columns and tables in
> the database and return the count of distinct values for each column and
> label them with the table and column names
> When I run the query, I receive the following error from the part
> labeled #1:
> Server: Msg 137, Level 15, State 2, Line 44
> Must declare the variable '@.tbl_name'.
> What I don't understand is why this error is occurring. I defined the
> variable and populated it. I commented out part #1 and tried PRINT
> @.tbl_name @.col_name which returned appropriate values.
> I have a workaround of commenting out the part labeled #1 and instead
> insert the following which produced a list of queries that I copied and
> pasted into a new QA window and executed.
> PRINT 'select count(distinct ' + @.col_name + ') ' + '"' + @.tbl_name +
> '.' + @.col_name + '"' + ' from ' + @.tbl_name
> I don't understand why I cannot substitute the variables in a query as I
> wish to.
> I am also considering building a command string and using exec
> sp_executesql.
> I welcome comments and suggestions on this matter.
> -- -- --
> DECLARE @.tbl_name varchar(255), @.col_name varchar(255)
> DECLARE CURS_sys_tables_and_cols CURSOR FOR
> select sysobjects.name, syscolumns.name from syscolumns, sysobjects
> where sysobjects.id = syscolumns.id
> and (sysobjects.xtype='U' or sysobjects.xtype='S')
> and sysobjects.name NOT like 'SYS%'
> and sysobjects.name NOT IN ( LIST OF TABLES I DON'T WANT)
> order by sysobjects.name
> OPEN CURS_sys_tables_and_cols
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
>
> -- #1
> PRINT @.tbl_name + '.' + @.col_name
> Select count(distinct @.col_name) from @.tbl_name
> PRINT '--'
>
> FETCH NEXT FROM CURS_sys_tables_and_cols
> INTO @.tbl_name, @.col_name
> END
> CLOSE CURS_sys_tables_and_cols
> DEALLOCATE CURS_sys_tables_and_cols
>
> *** Sent via Developersdex http://www.examnotes.net ***|||
Indeed, I thought I might need to build strings and use sp_executesql.
Thanks for the pointer to the article, I missed it in my google
searches.
*** Sent via Developersdex http://www.examnotes.net ***
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Thursday, March 29, 2012
Cursor to populate a table
Hi!
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursor
On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegr oups.com...
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,
|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursor
On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegr oups.com...
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,
|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
Cursor to populate a table
Hi!
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
>
>
>
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:[vbcol=seagreen]
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
>
>
>
>
>
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
>
>
>
>
>
>
>
>
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
Cursor to populate a table
Hi!
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > Hi!
> > I am trying to create a cursor (please see the code below) that will
> > populate client_all table that has two columns clientid and cid.
> > The first value for that table is drawn from TClient table (@.clientid)
> > and the second one is drawn from get_client_all function (cid).
> > I have to populate client_all table until I reach the end of Tclient
> > table. Tclient table has about 16000 rows. Each of @.clientID might
> > have multiple cids. This cursor runs a long time and doesn't
> > complete.
> > Anybody has any idea?
> > Thanks,
> > declare @.rownumber int
> > declare @.rowcount int
> > select @.rowcount = count(*) from tciclien
> > Declare PopulateTable_cursor Cursor for
> > select idnumber from TClient
> > open PopulateTable_cursor
> > declare @.clientid int
> > Fetch Next from PopulateTable_cursor
> > Into @.clientid
> > WHILE @.@.FETCH_STATUS <> -1
> > Begin
> > while @.rownumber < = @.rowcount
> > Begin
> > insert into Client_all
> > select @.clientid, cd.cid
> > from get_client_all(@.clientid, -1, 0) cd
> > End
> > End
> > close PopulateTable_cursor
> > deallocate PopulateTable_cursor
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>> What does get_client_all do? Can you move away from using cursors and
>> instead just join TClient against the equivalent of whatever this
>> function
>> returns?
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
> > What does get_client_all do? Can you move away from using cursors and
> > instead just join TClient against the equivalent of whatever this function
> > returns?
> > "tolcis" <nytolly...@.gmail.com> wrote in message
> >news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > > Hi!
> > > I am trying to create a cursor (please see the code below) that will
> > > populate client_all table that has two columns clientid and cid.
> > > The first value for that table is drawn from TClient table (@.clientid)
> > > and the second one is drawn from get_client_all function (cid).
> > > I have to populate client_all table until I reach the end of Tclient
> > > table. Tclient table has about 16000 rows. Each of @.clientID might
> > > have multiple cids. This cursor runs a long time and doesn't
> > > complete.
> > > Anybody has any idea?
> > > Thanks,
> > > declare @.rownumber int
> > > declare @.rowcount int
> > > select @.rowcount = count(*) from tciclien
> > > Declare PopulateTable_cursor Cursor for
> > > select idnumber from TClient
> > > open PopulateTable_cursor
> > > declare @.clientid int
> > > Fetch Next from PopulateTable_cursor
> > > Into @.clientid
> > > WHILE @.@.FETCH_STATUS <> -1
> > > Begin
> > > while @.rownumber < = @.rowcount
> > > Begin
> > > insert into Client_all
> > > select @.clientid, cd.cid
> > > from get_client_all(@.clientid, -1, 0) cd
> > > End
> > > End
> > > close PopulateTable_cursor
> > > deallocate PopulateTable_cursor
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
I am trying to create a cursor (please see the code below) that will
populate client_all table that has two columns clientid and cid.
The first value for that table is drawn from TClient table (@.clientid)
and the second one is drawn from get_client_all function (cid).
I have to populate client_all table until I reach the end of Tclient
table. Tclient table has about 16000 rows. Each of @.clientID might
have multiple cids. This cursor runs a long time and doesn't
complete.
Anybody has any idea?
Thanks,
declare @.rownumber int
declare @.rowcount int
select @.rowcount = count(*) from tciclien
Declare PopulateTable_cursor Cursor for
select idnumber from TClient
open PopulateTable_cursor
declare @.clientid int
Fetch Next from PopulateTable_cursor
Into @.clientid
WHILE @.@.FETCH_STATUS <> -1
Begin
while @.rownumber < = @.rowcount
Begin
insert into Client_all
select @.clientid, cd.cid
from get_client_all(@.clientid, -1, 0) cd
End
End
close PopulateTable_cursor
deallocate PopulateTable_cursorWhat does get_client_all do? Can you move away from using cursors and
instead just join TClient against the equivalent of whatever this function
returns?
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> Hi!
> I am trying to create a cursor (please see the code below) that will
> populate client_all table that has two columns clientid and cid.
> The first value for that table is drawn from TClient table (@.clientid)
> and the second one is drawn from get_client_all function (cid).
> I have to populate client_all table until I reach the end of Tclient
> table. Tclient table has about 16000 rows. Each of @.clientID might
> have multiple cids. This cursor runs a long time and doesn't
> complete.
> Anybody has any idea?
> Thanks,
>
> declare @.rownumber int
> declare @.rowcount int
> select @.rowcount = count(*) from tciclien
> Declare PopulateTable_cursor Cursor for
> select idnumber from TClient
> open PopulateTable_cursor
> declare @.clientid int
> Fetch Next from PopulateTable_cursor
> Into @.clientid
> WHILE @.@.FETCH_STATUS <> -1
> Begin
> while @.rownumber < = @.rowcount
> Begin
> insert into Client_all
> select @.clientid, cd.cid
> from get_client_all(@.clientid, -1, 0) cd
> End
> End
> close PopulateTable_cursor
> deallocate PopulateTable_cursor
>|||On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
> What does get_client_all do? Can you move away from using cursors and
> instead just join TClient against the equivalent of whatever this function
> returns?
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > Hi!
> > I am trying to create a cursor (please see the code below) that will
> > populate client_all table that has two columns clientid and cid.
> > The first value for that table is drawn from TClient table (@.clientid)
> > and the second one is drawn from get_client_all function (cid).
> > I have to populate client_all table until I reach the end of Tclient
> > table. Tclient table has about 16000 rows. Each of @.clientID might
> > have multiple cids. This cursor runs a long time and doesn't
> > complete.
> > Anybody has any idea?
> > Thanks,
> > declare @.rownumber int
> > declare @.rowcount int
> > select @.rowcount = count(*) from tciclien
> > Declare PopulateTable_cursor Cursor for
> > select idnumber from TClient
> > open PopulateTable_cursor
> > declare @.clientid int
> > Fetch Next from PopulateTable_cursor
> > Into @.clientid
> > WHILE @.@.FETCH_STATUS <> -1
> > Begin
> > while @.rownumber < = @.rowcount
> > Begin
> > insert into Client_all
> > select @.clientid, cd.cid
> > from get_client_all(@.clientid, -1, 0) cd
> > End
> > End
> > close PopulateTable_cursor
> > deallocate PopulateTable_cursor
That function loop through different table to gather sub clients. The
function returns a temp table.
Thanks,|||"tolcis" <nytollydba@.gmail.com> wrote in message
news:1183678877.263066.236090@.o61g2000hsh.googlegroups.com...
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>> What does get_client_all do? Can you move away from using cursors and
>> instead just join TClient against the equivalent of whatever this
>> function
>> returns?
> That function loop through different table to gather sub clients. The
> function returns a temp table.
>
Then I can only say that this looks like a classic case of how NOT to write
SQL. You should start with a set-based approach to every problem. Only
resort to cursors and loops in very exceptional cases. If you aren't sure
you can do that then get into the habit of seeking a second opinion before
you write a cursor. Your code will be much simpler and more efficient that
way.
If you need more help, please post DDL, sample data and show your required
end result.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On Jul 6, 5:41 am, tolcis <nytolly...@.gmail.com> wrote:
> On Jul 5, 6:26 pm, "Will Alber" <j...@.crazy-pug.co.uk> wrote:
>
>
> > What does get_client_all do? Can you move away from using cursors and
> > instead just join TClient against the equivalent of whatever this function
> > returns?
> > "tolcis" <nytolly...@.gmail.com> wrote in message
> >news:1183670562.499319.150460@.n60g2000hse.googlegroups.com...
> > > Hi!
> > > I am trying to create a cursor (please see the code below) that will
> > > populate client_all table that has two columns clientid and cid.
> > > The first value for that table is drawn from TClient table (@.clientid)
> > > and the second one is drawn from get_client_all function (cid).
> > > I have to populate client_all table until I reach the end of Tclient
> > > table. Tclient table has about 16000 rows. Each of @.clientID might
> > > have multiple cids. This cursor runs a long time and doesn't
> > > complete.
> > > Anybody has any idea?
> > > Thanks,
> > > declare @.rownumber int
> > > declare @.rowcount int
> > > select @.rowcount = count(*) from tciclien
> > > Declare PopulateTable_cursor Cursor for
> > > select idnumber from TClient
> > > open PopulateTable_cursor
> > > declare @.clientid int
> > > Fetch Next from PopulateTable_cursor
> > > Into @.clientid
> > > WHILE @.@.FETCH_STATUS <> -1
> > > Begin
> > > while @.rownumber < = @.rowcount
> > > Begin
> > > insert into Client_all
> > > select @.clientid, cd.cid
> > > from get_client_all(@.clientid, -1, 0) cd
> > > End
> > > End
> > > close PopulateTable_cursor
> > > deallocate PopulateTable_cursor
> That function loop through different table to gather sub clients. The
> function returns a temp table.
> Thanks,- Hide quoted text -
> - Show quoted text -
Can you post the code for get_client_all? How exactly are you
'looping' through? More cursors?
cursor to alter table, add columns
Friends,
I am trying to use a cursor inside a stored procedure to add columns to a
temp table. Can anyone tell me how to fix the following code so the cursor
will feed new column names to the alter table statement?
The code fails when I try to use a variable as a column name in the alter
table statement.
Thanks for your help ...
DECLARE @.strVar varchar(7)
If object_id('tempdb..#temptbl') is not null
begin
drop table #temptbl
end
Create Table #temptbl
(sku varchar(15) null)
DECLARE mycursor CURSOR
FOR
SELECT colA
FROM PermTable
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVAr varchar(20) null
WHILE @.@.fetch_status = 0
BEGIN
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVar varchar(20) null
END
CLOSE mycursor
DEALLOCATE mycursorWhy in the world would you ever want to do this'''?
nevermind.......
CREATE TABLE #thisisstupid(colname VARCHAR(256))
GO
INSERT #thisisstupid(colname)
SELECT 'col1' UNION ALL
SELECT 'col2' UNION ALL
SELECT 'col3' UNION ALL
SELECT 'col4'
DECLARE @.thisisstupider VARCHAR(4000)
SELECT @.thisisstupider = ''
SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
FROM #thisisstupid
SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupider
SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
EXEC(@.thisisstupider)
SELECT * FROM #thisisstupid
DROP TABLE #thisisstupid
GO
"bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
> Friends,
> I am trying to use a cursor inside a stored procedure to add columns to a
> temp table. Can anyone tell me how to fix the following code so the cursor
> will feed new column names to the alter table statement?
> The code fails when I try to use a variable as a column name in the alter
> table statement.
> Thanks for your help ...
>
> DECLARE @.strVar varchar(7)
> If object_id('tempdb..#temptbl') is not null
> begin
> drop table #temptbl
> end
> Create Table #temptbl
> (sku varchar(15) null)
> DECLARE mycursor CURSOR
> FOR
> SELECT colA
> FROM PermTable
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVAr varchar(20) null
> WHILE @.@.fetch_status = 0
> BEGIN
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVar varchar(20) null
> END
> CLOSE mycursor
> DEALLOCATE mycursor
>|||If you are so disgusted, why even reply? Keep your "stupid" remarks to
yourself.|||You may need to do this when you want the procedure to return a "pivot table
"
- i.e., the values in one column need to become column headers in the
returned records -in the meantime I was able to find a guy at a local compan
y
who is familiar with this technique, using a cursor. It does involve storin
g
the entire ALTER TABLE statement ( + a variable for the new column value)
inside a variable, as you've done below.
Inside each loop the new column value is updated and the sp_executesql is
executed to run the ALTER TABLE string.
Thanks for your reply ...
bill morgan
"Derrick Leggett" wrote:
> Why in the world would you ever want to do this'''?
> nevermind.......
>
> CREATE TABLE #thisisstupid(colname VARCHAR(256))
> GO
> INSERT #thisisstupid(colname)
> SELECT 'col1' UNION ALL
> SELECT 'col2' UNION ALL
> SELECT 'col3' UNION ALL
> SELECT 'col4'
> DECLARE @.thisisstupider VARCHAR(4000)
> SELECT @.thisisstupider = ''
> SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
> FROM #thisisstupid
> SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupide
r
> SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
> EXEC(@.thisisstupider)
> SELECT * FROM #thisisstupid
> DROP TABLE #thisisstupid
> GO
> "bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
> message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
>
>|||Because normally people do this for a stupid reason. :) He actually has an
interesting reason for doing it. Calm down guy. It's not the end of the
world. He took it a little better than you. And, it is stupid that you
would have to do this for a PIVOT table in SQL Server. It's also
unfortunate in 2005 that they haven't fixed this. You still need to
hardcode the values for the columns, which IS STUPID!!!!! And, I won't keep
my stupid remarks to myself. People need to think about what they are
doing.
The MS reason for the pivot table in 2005 being written in that format is
because "it would cause problems with the optimizer otherwise". That really
doesn't cut it. The pivot is a great idea. The way they implemented it
though forces the everyday user to resort to dynamic SQL for it to be truly
useful. That's a shame. They should have a warning in Books Online about
the optimizer and plan generator having some issues with a dynamic comic
list, not just exclude the functionality completely.
"bd" <bryce_dooley123@.yahoo.com> wrote in message
news:1110478752.721077.32400@.o13g2000cwo.googlegroups.com...
> If you are so disgusted, why even reply? Keep your "stupid" remarks to
> yourself.
>sql
I am trying to use a cursor inside a stored procedure to add columns to a
temp table. Can anyone tell me how to fix the following code so the cursor
will feed new column names to the alter table statement?
The code fails when I try to use a variable as a column name in the alter
table statement.
Thanks for your help ...
DECLARE @.strVar varchar(7)
If object_id('tempdb..#temptbl') is not null
begin
drop table #temptbl
end
Create Table #temptbl
(sku varchar(15) null)
DECLARE mycursor CURSOR
FOR
SELECT colA
FROM PermTable
OPEN mycursor
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVAr varchar(20) null
WHILE @.@.fetch_status = 0
BEGIN
FETCH NEXT
FROM mycursor
INTO @.strVar
alter table #temptbl
add
@.strVar varchar(20) null
END
CLOSE mycursor
DEALLOCATE mycursorWhy in the world would you ever want to do this'''?
nevermind.......
CREATE TABLE #thisisstupid(colname VARCHAR(256))
GO
INSERT #thisisstupid(colname)
SELECT 'col1' UNION ALL
SELECT 'col2' UNION ALL
SELECT 'col3' UNION ALL
SELECT 'col4'
DECLARE @.thisisstupider VARCHAR(4000)
SELECT @.thisisstupider = ''
SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
FROM #thisisstupid
SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupider
SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
EXEC(@.thisisstupider)
SELECT * FROM #thisisstupid
DROP TABLE #thisisstupid
GO
"bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
> Friends,
> I am trying to use a cursor inside a stored procedure to add columns to a
> temp table. Can anyone tell me how to fix the following code so the cursor
> will feed new column names to the alter table statement?
> The code fails when I try to use a variable as a column name in the alter
> table statement.
> Thanks for your help ...
>
> DECLARE @.strVar varchar(7)
> If object_id('tempdb..#temptbl') is not null
> begin
> drop table #temptbl
> end
> Create Table #temptbl
> (sku varchar(15) null)
> DECLARE mycursor CURSOR
> FOR
> SELECT colA
> FROM PermTable
> OPEN mycursor
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVAr varchar(20) null
> WHILE @.@.fetch_status = 0
> BEGIN
> FETCH NEXT
> FROM mycursor
> INTO @.strVar
> alter table #temptbl
> add
> @.strVar varchar(20) null
> END
> CLOSE mycursor
> DEALLOCATE mycursor
>|||If you are so disgusted, why even reply? Keep your "stupid" remarks to
yourself.|||You may need to do this when you want the procedure to return a "pivot table
"
- i.e., the values in one column need to become column headers in the
returned records -in the meantime I was able to find a guy at a local compan
y
who is familiar with this technique, using a cursor. It does involve storin
g
the entire ALTER TABLE statement ( + a variable for the new column value)
inside a variable, as you've done below.
Inside each loop the new column value is updated and the sp_executesql is
executed to run the ALTER TABLE string.
Thanks for your reply ...
bill morgan
"Derrick Leggett" wrote:
> Why in the world would you ever want to do this'''?
> nevermind.......
>
> CREATE TABLE #thisisstupid(colname VARCHAR(256))
> GO
> INSERT #thisisstupid(colname)
> SELECT 'col1' UNION ALL
> SELECT 'col2' UNION ALL
> SELECT 'col3' UNION ALL
> SELECT 'col4'
> DECLARE @.thisisstupider VARCHAR(4000)
> SELECT @.thisisstupider = ''
> SELECT @.thisisstupider = @.thisisstupider + colname + ' VARCHAR(20), '
> FROM #thisisstupid
> SELECT @.thisisstupider = 'ALTER TABLE #thisisstupid ADD ' + @.thisisstupide
r
> SELECT @.thisisstupider = LEFT(@.thisisstupider,LEN(@.thisisstupider
)-1)
> EXEC(@.thisisstupider)
> SELECT * FROM #thisisstupid
> DROP TABLE #thisisstupid
> GO
> "bill_morgan_3333" <billmorgan3333@.discussions.microsoft.com> wrote in
> message news:50997181-3CC0-45EE-97FE-7C389EDB6909@.microsoft.com...
>
>|||Because normally people do this for a stupid reason. :) He actually has an
interesting reason for doing it. Calm down guy. It's not the end of the
world. He took it a little better than you. And, it is stupid that you
would have to do this for a PIVOT table in SQL Server. It's also
unfortunate in 2005 that they haven't fixed this. You still need to
hardcode the values for the columns, which IS STUPID!!!!! And, I won't keep
my stupid remarks to myself. People need to think about what they are
doing.
The MS reason for the pivot table in 2005 being written in that format is
because "it would cause problems with the optimizer otherwise". That really
doesn't cut it. The pivot is a great idea. The way they implemented it
though forces the everyday user to resort to dynamic SQL for it to be truly
useful. That's a shame. They should have a warning in Books Online about
the optimizer and plan generator having some issues with a dynamic comic
list, not just exclude the functionality completely.
"bd" <bryce_dooley123@.yahoo.com> wrote in message
news:1110478752.721077.32400@.o13g2000cwo.googlegroups.com...
> If you are so disgusted, why even reply? Keep your "stupid" remarks to
> yourself.
>sql
Cursor question
I have a table with the following columns Parent_Part (PP), Child_part (CP),
Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in stock
that has been reserved for the parent part and is to be allocated to the
child based on the need date per PP/CP group. The sample table looks like
this after it's sorted based on allocation criteria. Your help will be
greated appreciated. TIA.
NOTE: I have not been using my hotmail account and I may have to activate
it so for now please post your reply here.
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 ?
A A1 4/1/05 5 7 ?
A A4 3/10/03 1 0 ?
B B3 3/1/05 10 6 ?
B B3 5/15/05 2 6 ?
B B3 6/1/05 2 6 ?
Result should look like this
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 5
A A1 4/1/05 5 7 2
A A4 3/10/03 1 0 0
B B3 3/1/05 10 6 6
B B3 5/15/05 2 6 0
B B3 6/1/05 2 6 0Hi
See http://www.aspfaq.com/etiquett__e.asp?id=5006 on how to make more
useful posts.
This assume that all PP, CP and ND combinations are unique!
SELECT q.PP, q.CP, q.ND, q.RQ, q.QOH,
CASE WHEN q.QOH - r.rq > 0 THEN
CASE WHEN q.QOH - r.rq - q.rq < 0 THEN q.QOH - r.rq
ELSE q.rq
END
ELSE 0
END AS ALLOC
from #qry q
JOIN (
SELECT b.pp, b.cp, b.nd, ISNULL(SUM(a.RQ),0) AS RQ
FROM #qry a
RIGHT JOIN #qry b ON b.pp = a.pp and b.cp = a.cp and a.nd < b.nd
GROUP BY b.pp, b.cp, b.nd
) r ON q.pp = r.pp and q.cp = r.cp and q.nd = r.nd
ORDER BY q.pp, q.cp, q.nd
John
"danlin" wrote:
> I have a table with the following columns Parent_Part (PP), Child_part (CP
),
> Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in sto
ck
> that has been reserved for the parent part and is to be allocated to the
> child based on the need date per PP/CP group. The sample table looks like
> this after it's sorted based on allocation criteria. Your help will be
> greated appreciated. TIA.
> NOTE: I have not been using my hotmail account and I may have to activate
> it so for now please post your reply here.
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 ?
> A A1 4/1/05 5 7 ?
> A A4 3/10/03 1 0 ?
> B B3 3/1/05 10 6 ?
> B B3 5/15/05 2 6 ?
> B B3 6/1/05 2 6 ?
> Result should look like this
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 5
> A A1 4/1/05 5 7 2
> A A4 3/10/03 1 0 0
> B B3 3/1/05 10 6 6
> B B3 5/15/05 2 6 0
> B B3 6/1/05 2 6 0
>
Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in stock
that has been reserved for the parent part and is to be allocated to the
child based on the need date per PP/CP group. The sample table looks like
this after it's sorted based on allocation criteria. Your help will be
greated appreciated. TIA.
NOTE: I have not been using my hotmail account and I may have to activate
it so for now please post your reply here.
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 ?
A A1 4/1/05 5 7 ?
A A4 3/10/03 1 0 ?
B B3 3/1/05 10 6 ?
B B3 5/15/05 2 6 ?
B B3 6/1/05 2 6 ?
Result should look like this
PP CP ND RQ QOH ALLOC
A A1 3/1/05 5 7 5
A A1 4/1/05 5 7 2
A A4 3/10/03 1 0 0
B B3 3/1/05 10 6 6
B B3 5/15/05 2 6 0
B B3 6/1/05 2 6 0Hi
See http://www.aspfaq.com/etiquett__e.asp?id=5006 on how to make more
useful posts.
This assume that all PP, CP and ND combinations are unique!
SELECT q.PP, q.CP, q.ND, q.RQ, q.QOH,
CASE WHEN q.QOH - r.rq > 0 THEN
CASE WHEN q.QOH - r.rq - q.rq < 0 THEN q.QOH - r.rq
ELSE q.rq
END
ELSE 0
END AS ALLOC
from #qry q
JOIN (
SELECT b.pp, b.cp, b.nd, ISNULL(SUM(a.RQ),0) AS RQ
FROM #qry a
RIGHT JOIN #qry b ON b.pp = a.pp and b.cp = a.cp and a.nd < b.nd
GROUP BY b.pp, b.cp, b.nd
) r ON q.pp = r.pp and q.cp = r.cp and q.nd = r.nd
ORDER BY q.pp, q.cp, q.nd
John
"danlin" wrote:
> I have a table with the following columns Parent_Part (PP), Child_part (CP
),
> Need_Date (ND), Reqd_qty (RQ), QTY_On_Hand (QOH). QOH is total qty in sto
ck
> that has been reserved for the parent part and is to be allocated to the
> child based on the need date per PP/CP group. The sample table looks like
> this after it's sorted based on allocation criteria. Your help will be
> greated appreciated. TIA.
> NOTE: I have not been using my hotmail account and I may have to activate
> it so for now please post your reply here.
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 ?
> A A1 4/1/05 5 7 ?
> A A4 3/10/03 1 0 ?
> B B3 3/1/05 10 6 ?
> B B3 5/15/05 2 6 ?
> B B3 6/1/05 2 6 ?
> Result should look like this
> PP CP ND RQ QOH ALLOC
> A A1 3/1/05 5 7 5
> A A1 4/1/05 5 7 2
> A A4 3/10/03 1 0 0
> B B3 3/1/05 10 6 6
> B B3 5/15/05 2 6 0
> B B3 6/1/05 2 6 0
>
Monday, March 19, 2012
Currency Variables?
Starting in my control flow, I execute a data flow that populates a recordset via SQL 2005 Stored Proc. One of the columns in source table is a currency type.
Back to the control flow, I have a for each container that includes an execute sql task that updates or inserts records into another table. I get precision or data type issues since I can not assign the package variable to a currency data type. The only way I can get this to work is if I convert the currency column in my data flow to a string and then cast the variable in my update/insert sql task. Any suggestions?
Thanks....Gary
One suggestion would be to map the currency type in the recordset to a variable of type Object in the ForEach container's Variable mappings tab.Then, when running the Execute SQL task in the ForEach container, use that Object variable in the parameter mapping tab with a DataType of CURRENCY.
No casting is needed.|||That works as described...Thanks!
Currency Fromatting
Hi Everyone,
I am a bit of a newbie to SQL, I have created a table to hold supplier
invoice details which has three columns to hold currency values i.e.
Nett Value
VAT Value
Gross Value
The columns for VAT and gross calculate automatically from the Nett value,
however the values in the fields have more than 2 digits after the decimal
place i.e.
Nett Value = 275.00
VAT Value = 48.125
Gross Value = 323.125
How do I force the two calculating fields to format the contents to standard
currency format i.e. ￡xxx.xx with only 2 digits after the decimal point?
Hope that makes sense. Any help would be greatly appreciatedNote that in the UK (AFAIK same as the rest of the EU), accounting
regulations require that VAT be calculated on an invoice sub-total, not
on individual items (there may be a difference due to rounding) so what
you are doing will not represent the correct total for accounting
purposes.
Storing both Net and Gross columns in a table is poor design. This is
redundant data and most systems will store just the Net value at detail
level, plus a VAT code.
Formatting is probably best left to your client app or middle tier. If
you must do it in SQL then look up the style parameter of the CONVERT
function. Avoid using MONEY / SMALLMONEY columns in your tables though
as these have some problems with precision in division operations.
David Portas
SQL Server MVP
--|||Thanks for your reply David. I am aware that for official accounting it
would need to be line by line calculated, but my DB if purely for reporting
supplier turnover and not for accounting so I am not overly bothered about
the roundings, but thanks for the pointers I'll go and have aplay and see
what I get.
Cheers
Jonathan
"David Portas" wrote:
> Note that in the UK (AFAIK same as the rest of the EU), accounting
> regulations require that VAT be calculated on an invoice sub-total, not
> on individual items (there may be a difference due to rounding) so what
> you are doing will not represent the correct total for accounting
> purposes.
> Storing both Net and Gross columns in a table is poor design. This is
> redundant data and most systems will store just the Net value at detail
> level, plus a VAT code.
> Formatting is probably best left to your client app or middle tier. If
> you must do it in SQL then look up the style parameter of the CONVERT
> function. Avoid using MONEY / SMALLMONEY columns in your tables though
> as these have some problems with precision in division operations.
> --
> David Portas
> SQL Server MVP
> --
>
I am a bit of a newbie to SQL, I have created a table to hold supplier
invoice details which has three columns to hold currency values i.e.
Nett Value
VAT Value
Gross Value
The columns for VAT and gross calculate automatically from the Nett value,
however the values in the fields have more than 2 digits after the decimal
place i.e.
Nett Value = 275.00
VAT Value = 48.125
Gross Value = 323.125
How do I force the two calculating fields to format the contents to standard
currency format i.e. ￡xxx.xx with only 2 digits after the decimal point?
Hope that makes sense. Any help would be greatly appreciatedNote that in the UK (AFAIK same as the rest of the EU), accounting
regulations require that VAT be calculated on an invoice sub-total, not
on individual items (there may be a difference due to rounding) so what
you are doing will not represent the correct total for accounting
purposes.
Storing both Net and Gross columns in a table is poor design. This is
redundant data and most systems will store just the Net value at detail
level, plus a VAT code.
Formatting is probably best left to your client app or middle tier. If
you must do it in SQL then look up the style parameter of the CONVERT
function. Avoid using MONEY / SMALLMONEY columns in your tables though
as these have some problems with precision in division operations.
David Portas
SQL Server MVP
--|||Thanks for your reply David. I am aware that for official accounting it
would need to be line by line calculated, but my DB if purely for reporting
supplier turnover and not for accounting so I am not overly bothered about
the roundings, but thanks for the pointers I'll go and have aplay and see
what I get.
Cheers
Jonathan
"David Portas" wrote:
> Note that in the UK (AFAIK same as the rest of the EU), accounting
> regulations require that VAT be calculated on an invoice sub-total, not
> on individual items (there may be a difference due to rounding) so what
> you are doing will not represent the correct total for accounting
> purposes.
> Storing both Net and Gross columns in a table is poor design. This is
> redundant data and most systems will store just the Net value at detail
> level, plus a VAT code.
> Formatting is probably best left to your client app or middle tier. If
> you must do it in SQL then look up the style parameter of the CONVERT
> function. Avoid using MONEY / SMALLMONEY columns in your tables though
> as these have some problems with precision in division operations.
> --
> David Portas
> SQL Server MVP
> --
>
Sunday, March 11, 2012
Cumulative total in matrix
Hi,
For columns in a matrix, I need to add the row value to the row value in
previous column. Is there a way to do this?
Here is what I want the matrix results to look like:
year1 year2 year3 year4
Amount 1 3 4 7
Total 1 4 8 15
Year is the column group value and amount is the row group value. Total is
the field I am looking for how to calculate.
Thanks!Initially I thought you were after a running total, but looking at your
example that doesn't appear to be the case.
You will have to do this in the underlying query by including the two
consecutive year's values on the same row; i.e.
Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
B.Year = A.Year-1)
From MyTable A
Group By A.Year
(I'm not sure this is syntactically correct, but you get my drift)
Having said that, mathematically what you're showing would appear
incorrect, effectively the year2 total is included in the year4 total
twice. What are you trying to show?
Chris
Me wrote:
> Hi,
> For columns in a matrix, I need to add the row value to the row value
> in previous column. Is there a way to do this?
> Here is what I want the matrix results to look like:
> year1 year2 year3 year4
> Amount 1 3 4 7
> Total 1 4 8 15
> Year is the column group value and amount is the row group value.
> Total is the field I am looking for how to calculate.
> Thanks!|||Hi Chris,
Thanks for replying.
What I wrote is a little unclear but my example is correct. They want to
add the current year to the running total of the previous years. Does that
make sense?
Thanks,
Melissa
"Chris McGuigan" wrote:
> Initially I thought you were after a running total, but looking at your
> example that doesn't appear to be the case.
> You will have to do this in the underlying query by including the two
> consecutive year's values on the same row; i.e.
> Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
> B.Year = A.Year-1)
> From MyTable A
> Group By A.Year
> (I'm not sure this is syntactically correct, but you get my drift)
> Having said that, mathematically what you're showing would appear
> incorrect, effectively the year2 total is included in the year4 total
> twice. What are you trying to show?
> Chris
>
> Me wrote:
> > Hi,
> >
> > For columns in a matrix, I need to add the row value to the row value
> > in previous column. Is there a way to do this?
> >
> > Here is what I want the matrix results to look like:
> >
> > year1 year2 year3 year4
> > Amount 1 3 4 7
> > Total 1 4 8 15
> >
> > Year is the column group value and amount is the row group value.
> > Total is the field I am looking for how to calculate.
> >
> > Thanks!
>|||Melissa, Yes that makes sense (logically not statistically), but I
think you may have mis-interpreted it.
In the example you have given, year 3 is neither a running total nor a
grand total. It is year1 + year2 + year1 + year3, that's the effect of
what you are doing.
So they want the current year added to each past year? I still can't
see a reason for that, but hey! the customers always right!
You are better off doing this in the query. Matrix controls like fairly
simply structured data, so hide the complexity in the query, on the
lines of my original post.
The more I look at this, the more I think you probably just need a
running total. If that is the case use =RunningValue( ... ) in the cell.
Chris
Me wrote:
> Hi Chris,
> Thanks for replying.
> What I wrote is a little unclear but my example is correct. They
> want to add the current year to the running total of the previous
> years. Does that make sense?
> Thanks,
> Melissa
>
> "Chris McGuigan" wrote:
> > Initially I thought you were after a running total, but looking at
> > your example that doesn't appear to be the case.
> >
> > You will have to do this in the underlying query by including the
> > two consecutive year's values on the same row; i.e.
> > Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B
> > Where B.Year = A.Year-1)
> > From MyTable A
> > Group By A.Year
> > (I'm not sure this is syntactically correct, but you get my drift)
> >
> > Having said that, mathematically what you're showing would appear
> > incorrect, effectively the year2 total is included in the year4
> > total twice. What are you trying to show?
> >
> > Chris
> >
> >
> >
> > Me wrote:
> >
> > > Hi,
> > >
> > > For columns in a matrix, I need to add the row value to the row
> > > value in previous column. Is there a way to do this?
> > >
> > > Here is what I want the matrix results to look like:
> > >
> > > year1 year2 year3 year4
> > > Amount 1 3 4 7
> > > Total 1 4 8 15
> > >
> > > Year is the column group value and amount is the row group value.
> > > Total is the field I am looking for how to calculate.
> > >
> > > Thanks!
> >
> >
For columns in a matrix, I need to add the row value to the row value in
previous column. Is there a way to do this?
Here is what I want the matrix results to look like:
year1 year2 year3 year4
Amount 1 3 4 7
Total 1 4 8 15
Year is the column group value and amount is the row group value. Total is
the field I am looking for how to calculate.
Thanks!Initially I thought you were after a running total, but looking at your
example that doesn't appear to be the case.
You will have to do this in the underlying query by including the two
consecutive year's values on the same row; i.e.
Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
B.Year = A.Year-1)
From MyTable A
Group By A.Year
(I'm not sure this is syntactically correct, but you get my drift)
Having said that, mathematically what you're showing would appear
incorrect, effectively the year2 total is included in the year4 total
twice. What are you trying to show?
Chris
Me wrote:
> Hi,
> For columns in a matrix, I need to add the row value to the row value
> in previous column. Is there a way to do this?
> Here is what I want the matrix results to look like:
> year1 year2 year3 year4
> Amount 1 3 4 7
> Total 1 4 8 15
> Year is the column group value and amount is the row group value.
> Total is the field I am looking for how to calculate.
> Thanks!|||Hi Chris,
Thanks for replying.
What I wrote is a little unclear but my example is correct. They want to
add the current year to the running total of the previous years. Does that
make sense?
Thanks,
Melissa
"Chris McGuigan" wrote:
> Initially I thought you were after a running total, but looking at your
> example that doesn't appear to be the case.
> You will have to do this in the underlying query by including the two
> consecutive year's values on the same row; i.e.
> Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B Where
> B.Year = A.Year-1)
> From MyTable A
> Group By A.Year
> (I'm not sure this is syntactically correct, but you get my drift)
> Having said that, mathematically what you're showing would appear
> incorrect, effectively the year2 total is included in the year4 total
> twice. What are you trying to show?
> Chris
>
> Me wrote:
> > Hi,
> >
> > For columns in a matrix, I need to add the row value to the row value
> > in previous column. Is there a way to do this?
> >
> > Here is what I want the matrix results to look like:
> >
> > year1 year2 year3 year4
> > Amount 1 3 4 7
> > Total 1 4 8 15
> >
> > Year is the column group value and amount is the row group value.
> > Total is the field I am looking for how to calculate.
> >
> > Thanks!
>|||Melissa, Yes that makes sense (logically not statistically), but I
think you may have mis-interpreted it.
In the example you have given, year 3 is neither a running total nor a
grand total. It is year1 + year2 + year1 + year3, that's the effect of
what you are doing.
So they want the current year added to each past year? I still can't
see a reason for that, but hey! the customers always right!
You are better off doing this in the query. Matrix controls like fairly
simply structured data, so hide the complexity in the query, on the
lines of my original post.
The more I look at this, the more I think you probably just need a
running total. If that is the case use =RunningValue( ... ) in the cell.
Chris
Me wrote:
> Hi Chris,
> Thanks for replying.
> What I wrote is a little unclear but my example is correct. They
> want to add the current year to the running total of the previous
> years. Does that make sense?
> Thanks,
> Melissa
>
> "Chris McGuigan" wrote:
> > Initially I thought you were after a running total, but looking at
> > your example that doesn't appear to be the case.
> >
> > You will have to do this in the underlying query by including the
> > two consecutive year's values on the same row; i.e.
> > Select A.Year, Sum(A.Value), (Select Sum(B.Value) From MyTable B
> > Where B.Year = A.Year-1)
> > From MyTable A
> > Group By A.Year
> > (I'm not sure this is syntactically correct, but you get my drift)
> >
> > Having said that, mathematically what you're showing would appear
> > incorrect, effectively the year2 total is included in the year4
> > total twice. What are you trying to show?
> >
> > Chris
> >
> >
> >
> > Me wrote:
> >
> > > Hi,
> > >
> > > For columns in a matrix, I need to add the row value to the row
> > > value in previous column. Is there a way to do this?
> > >
> > > Here is what I want the matrix results to look like:
> > >
> > > year1 year2 year3 year4
> > > Amount 1 3 4 7
> > > Total 1 4 8 15
> > >
> > > Year is the column group value and amount is the row group value.
> > > Total is the field I am looking for how to calculate.
> > >
> > > Thanks!
> >
> >
Thursday, March 8, 2012
Cumulative max-length for index on SQL servers... please help
Folks,
What is the cumulative max-length of columns in both a clustered
and/or nonclustered index for:
a. SQL Server 2000
b. SQL Server 2005
Any comments much, much appreciated...
Thank you,
Al.Look for "maximum capacity specifications" in Books Online, this info
and more can be found there.
almurph@.altavista.com wrote:
> Folks,
> What is the cumulative max-length of columns in both a clustered
> and/or nonclustered index for:
> a. SQL Server 2000
> b. SQL Server 2005
>
> Any comments much, much appreciated...
> Thank you,
> Al.|||Tracy McKibben wrote:
> Look for "maximum capacity specifications" in Books Online, this info
> and more can be found there.
>
> almurph@.altavista.com wrote:
> > Folks,
> >
> > What is the cumulative max-length of columns in both a clustered
> > and/or nonclustered index for:
> >
> > a. SQL Server 2000
> > b. SQL Server 2005
> >
> >
> > Any comments much, much appreciated...
> >
> > Thank you,
> > Al.
Number of columns should not be more than 16 and cumulative length
should not be more than 900. But you can use included column opation in
SQL Server 2005 to add more columns.
More help you can find in Create index statement in BOL and Maximum
Capacity Specifications for SQL Server 2005
Regards
Amish Shah
What is the cumulative max-length of columns in both a clustered
and/or nonclustered index for:
a. SQL Server 2000
b. SQL Server 2005
Any comments much, much appreciated...
Thank you,
Al.Look for "maximum capacity specifications" in Books Online, this info
and more can be found there.
almurph@.altavista.com wrote:
> Folks,
> What is the cumulative max-length of columns in both a clustered
> and/or nonclustered index for:
> a. SQL Server 2000
> b. SQL Server 2005
>
> Any comments much, much appreciated...
> Thank you,
> Al.|||Tracy McKibben wrote:
> Look for "maximum capacity specifications" in Books Online, this info
> and more can be found there.
>
> almurph@.altavista.com wrote:
> > Folks,
> >
> > What is the cumulative max-length of columns in both a clustered
> > and/or nonclustered index for:
> >
> > a. SQL Server 2000
> > b. SQL Server 2005
> >
> >
> > Any comments much, much appreciated...
> >
> > Thank you,
> > Al.
Number of columns should not be more than 16 and cumulative length
should not be more than 900. But you can use included column opation in
SQL Server 2005 to add more columns.
More help you can find in Create index statement in BOL and Maximum
Capacity Specifications for SQL Server 2005
Regards
Amish Shah
Cumulative Field
I have four columns in a table. One called person, days, hours and cumulative. Every day the person works and as a result creates a new entry and thus an entire new row. From this, the day increases e.g. 1 - 2 - 3 etc in each row. The hours that they work is different each day. What i am hoping to achieve is have the forth column showing the total hours that person has work this the first day they started. Any ideas...Hi,
Since you posted your question in the SQL forum I presume that you're looking for a query. Her I've got one:
SELECT person, days, hours,
(SELECT sum(hours)
FROM table b
WHERE b.person = a.person
AND b.days <= a.days) AS cumulative
FROM table a
This should work, at least it does on an Informix database. I'm not shure whether this is standard SQL or not...
If it's not a query you want but a way of dynamicly filling the fourth column at every insert you could use the subquery inside a trigger to calculate the value to be inserted in the last column.
Regards|||Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean; for example:CREATE OR REPLACE TRIGGER trg_hours
BEFORE INSERT ON HOURS_CUM
FOR EACH ROW
BEGIN
SELECT
NVL(MAX(t.days) + 1, 1),
NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
INTO :NEW.days, :NEW.cumulative
FROM HOURS_CUM t
WHERE person = :NEW.person;
END;Direct table insert won't be possible (in Oracle) because table is mutating and records can't be accessed (for example, entering 2 hours for person number 1)INSERT INTO HOURS_CUM
(person, days, HOURS_worked, cumulative)
(SELECT
1,
NVL(MAX(t.days) + 1, 1),
2,
NVL(SUM(t.HOURS_worked), 0) + 2
FROM HOURS_CUM t
WHERE t.person = 1
);I guess the same goes for another databases; if you can't use triggers, you'll need to figure out how to bypass such a limitation (if it exists).|||On Oracle, you could create a database trigger
The same holds for DB2.
But replace NVL by COALESCE then. (Also works on Oracle.)|||... have the forth column showing the total hours ...
An other solution consists of redefining your table:
- Have a differently named table with just the first three columns.
- Create a VIEW (with the name of the old table): CREATE VIEW mytable AS
SELECT person, days, hours,
(SELECT SUM(hours)
FROM oldtable
WHERE person = t.person AND days <= t.days) AS cumulative
FROM oldtable AS aThis way, you may insert into "oldtable", and read from "mytable".|||Can anyone tell me how to size the "Code" section so it won't appear with a fixed size and a scrollbar? I've been trying some things now, without the wanted result...
Thanks,
Hans
BTW: With Informix it's possible to dynamically store a value in a column of an inserting row. It has to be done with an insert trigger that a invokes a stored function which returns it's value INTO the specified column.|||Don't think this is possible...
It's a feature of the dBforums lay-out, I'm afraid.|||Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean;
Unfortunatrely this trigger solution posted may produce a "mutating table" error. :rolleyes:|||It may, but - on the other hand - it doesn't have to. Let me try:SQL> create table hours_cum
2 (person number,
3 days number,
4 hours_worked number,
5 cumulative number);
Table created.
SQL> CREATE OR REPLACE TRIGGER trg_hours
2 BEFORE INSERT ON HOURS_CUM
3 FOR EACH ROW
4 BEGIN
5 SELECT
6 NVL(MAX(t.days) + 1, 1),
7 NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
8 INTO :NEW.days, :NEW.cumulative
9 FROM HOURS_CUM t
10 WHERE person = :NEW.person;
11 END;
12 /
Trigger created.
SQL> insert into hours_cum (person, hours_worked) values (1, 8);
1 row created.
SQL> select * from hours_cum;
PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
SQL> insert into hours_cum (person, hours_worked) values (1, 7);
1 row created.
SQL> select * from hours_cum;
PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
1 2 7 15
SQL> insert into hours_cum (person, hours_worked) values (2, 5);
1 row created.
SQL> select * from hours_cum;
PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
1 2 7 15
2 1 5 5
SQL>It seems quite OK to me ... did you have something else in mind, what I don't see at the moment?|||In summary, there's not much difference between the "trigger" solution and the "view" solution -- i.e., you insert into the first three columns, and you may read the four columns.
Advantages of the "trigger" solution:
- only need one table in all SQL
- 4th column is calculated once, and stored; no recalculation on read
Advantages of the "view" solution:
- less danger of attempt to insert into 4th column, since the table to be inserted only has 3 columns, and an attempt to insert into the view will tell you it's a view
- a trigger is invisible for SQL; with the view it is clearer for the SQL user that the 4th column is a calculated one.|||For what it's worth, I would prefer the view-method. Next to the advantages Peter summarized it complies with the first normalform to!
BTW: Did anyone notice the initial asker, Divardo, never replied to this thread?
Bye|||Never mind; we had a nice little chat here :)
Since you posted your question in the SQL forum I presume that you're looking for a query. Her I've got one:
SELECT person, days, hours,
(SELECT sum(hours)
FROM table b
WHERE b.person = a.person
AND b.days <= a.days) AS cumulative
FROM table a
This should work, at least it does on an Informix database. I'm not shure whether this is standard SQL or not...
If it's not a query you want but a way of dynamicly filling the fourth column at every insert you could use the subquery inside a trigger to calculate the value to be inserted in the last column.
Regards|||Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean; for example:CREATE OR REPLACE TRIGGER trg_hours
BEFORE INSERT ON HOURS_CUM
FOR EACH ROW
BEGIN
SELECT
NVL(MAX(t.days) + 1, 1),
NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
INTO :NEW.days, :NEW.cumulative
FROM HOURS_CUM t
WHERE person = :NEW.person;
END;Direct table insert won't be possible (in Oracle) because table is mutating and records can't be accessed (for example, entering 2 hours for person number 1)INSERT INTO HOURS_CUM
(person, days, HOURS_worked, cumulative)
(SELECT
1,
NVL(MAX(t.days) + 1, 1),
2,
NVL(SUM(t.HOURS_worked), 0) + 2
FROM HOURS_CUM t
WHERE t.person = 1
);I guess the same goes for another databases; if you can't use triggers, you'll need to figure out how to bypass such a limitation (if it exists).|||On Oracle, you could create a database trigger
The same holds for DB2.
But replace NVL by COALESCE then. (Also works on Oracle.)|||... have the forth column showing the total hours ...
An other solution consists of redefining your table:
- Have a differently named table with just the first three columns.
- Create a VIEW (with the name of the old table): CREATE VIEW mytable AS
SELECT person, days, hours,
(SELECT SUM(hours)
FROM oldtable
WHERE person = t.person AND days <= t.days) AS cumulative
FROM oldtable AS aThis way, you may insert into "oldtable", and read from "mytable".|||Can anyone tell me how to size the "Code" section so it won't appear with a fixed size and a scrollbar? I've been trying some things now, without the wanted result...
Thanks,
Hans
BTW: With Informix it's possible to dynamically store a value in a column of an inserting row. It has to be done with an insert trigger that a invokes a stored function which returns it's value INTO the specified column.|||Don't think this is possible...
It's a feature of the dBforums lay-out, I'm afraid.|||Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean;
Unfortunatrely this trigger solution posted may produce a "mutating table" error. :rolleyes:|||It may, but - on the other hand - it doesn't have to. Let me try:SQL> create table hours_cum
2 (person number,
3 days number,
4 hours_worked number,
5 cumulative number);
Table created.
SQL> CREATE OR REPLACE TRIGGER trg_hours
2 BEFORE INSERT ON HOURS_CUM
3 FOR EACH ROW
4 BEGIN
5 SELECT
6 NVL(MAX(t.days) + 1, 1),
7 NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
8 INTO :NEW.days, :NEW.cumulative
9 FROM HOURS_CUM t
10 WHERE person = :NEW.person;
11 END;
12 /
Trigger created.
SQL> insert into hours_cum (person, hours_worked) values (1, 8);
1 row created.
SQL> select * from hours_cum;
PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
SQL> insert into hours_cum (person, hours_worked) values (1, 7);
1 row created.
SQL> select * from hours_cum;
PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
1 2 7 15
SQL> insert into hours_cum (person, hours_worked) values (2, 5);
1 row created.
SQL> select * from hours_cum;
PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
1 2 7 15
2 1 5 5
SQL>It seems quite OK to me ... did you have something else in mind, what I don't see at the moment?|||In summary, there's not much difference between the "trigger" solution and the "view" solution -- i.e., you insert into the first three columns, and you may read the four columns.
Advantages of the "trigger" solution:
- only need one table in all SQL
- 4th column is calculated once, and stored; no recalculation on read
Advantages of the "view" solution:
- less danger of attempt to insert into 4th column, since the table to be inserted only has 3 columns, and an attempt to insert into the view will tell you it's a view
- a trigger is invisible for SQL; with the view it is clearer for the SQL user that the 4th column is a calculated one.|||For what it's worth, I would prefer the view-method. Next to the advantages Peter summarized it complies with the first normalform to!
BTW: Did anyone notice the initial asker, Divardo, never replied to this thread?
Bye|||Never mind; we had a nice little chat here :)
Cubes and Cost-Forecast
Hi,
I'm experiencing trouble with the following requirement:
We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table:
projekt, date_of_forecast, date, costs
PR-A, 2007-06-01, 2007-07-01, 2000
PR-A, 2007-06-05, 2007-07-01, 3000
PR-A, 2007-06-10, 2007-07-01, 2500
PR-A, 2007-06-10, 2007-07-15, 2000
For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15.
The customer wants a cube wich allows an answer to the following question:
Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] ?
So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ...
How to accomplish the using SQL Server 2005 STANDARD-EDITION ?
Do I need two Time-Dimensions or just one?
Is there a way to create a Cube-Skript for this requirement?
Thanks and best whishes
Manfred
Dear Friend,
You must have a Time dimension and a Project Dimension and you FactTable that could be as you wrote...
Look for the follow post in my blog that could help you... (see the MDX query... I think you can apply to your case, but only seing more carefully)
http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html
I hope this helped you!
regards!
|||Hi,
I've tried this. But SSAS seems to get into an infinity-recursion ...
How to work around this issue?
Best Whishes,
Manfred
|||Can you post the calculation code? The usual problem with an infinite recursion is a missing measure reference somewhere.|||Here it is:
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
Btw: I also use currency conversions created by the wizzard. The code for the currency comes first; then this calculation comes ...
Best wishes,
Manfred
|||
You did not include the CREATE MEASURE statement. Is this the code for the [Gewinn] measure or the [Geplanter Gewinn]. I am guessing that it is for the [Gewinn] measure as this would cause an infinite recursion. If I have guessed correctly it is simply that you have transposed the two measures from Pedro's example, try the following
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn]),
[Measures].[Geplanter Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Gewinn], [Time].[Date].PrevMember)
)
)
In fact we should be able to simplify the whole thing, removing the "if null return null" section and ending up with the following:
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn])
,([Measures].[Geplanter Gewinn])
,([Time].[Date].PrevMember)
)
Which says
1. If Geplanter Gewinn is not empty return that
2. Else return the value of this calculation for the previous time member (which is where the recursion comes in). So this calculation will keep searching back until it finds a nonEmpty value of Geplanter Gewinn.
|||Hi,
I think, there is a missunderstanding.
[Gewinn] is the Measure in the Cube and has a value for some days.
[Geplanter Gewinn] should return the last non empty value of [Gewinn]
So, the right syntax should be:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Geplanter Gewinn]
AS
iif ( not isEmpty([Measures].[Gewinn]), -- If there is a [Gewinn] for the current day
[Measures].[Gewinn], -- return it
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember) -- Go back one day and retry it
)
)
But this ends up in an infinity-recursion ...
Wishes,
Manfred
|||Dear ManfredSteyer,
The statment in my blog works perfectly... are you sure you saw it right?
Check this:
Code Snippet
'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)'
I hope you get it!!!
Regards!
|||Sorry, without the CREATE MEMBER clause I could not tell which was the measure in the cube. In that case it looks OK. I'm wondering if there is something else in your calculation script that might be conflicting.
Are you able to use this type of calculation in an MDX query using the "WITH MEMBER" clause?
|||for you both:
Code Snippet
CREATE MEMBER CURRENTCUBE.[MEASURES].CM_PRM_ENT_Racio
AS 'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),
[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)',
VISIBLE = 1;
Helped?
This for me works!
Regards!
|||Hi,
I exactly used this pattern - I also looked up "MDX Solutions" (Wiley) ...
And if I use it directly within a mdx-query, it works too:
with member [Measures].[Geplanter Gewinn]
as
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
select [Measures].[Geplanter Gewinn] on 0
from [Kostenrechnung Sample Db]
where [Time].[Date].&[2007-06-15T00:00:00]
But when I used it as calculated member or within a cube-script, I get an inifinity-recursion ...
Have you used this pattern within cube-script/ as calc. meber or "just" as mdx-query ?
Regards,
Manfred
|||
I use it in a CM...
|||I can't see anything wrong with the implementation of this CM. I think the fact that it works inline in a query proves that there is nothing with it on it's own. There must be a circular dependancy somewhere in the calculation script.
In order to figure this out you could either set a breakpoint in the MDX Script and use the debugger. Stepping through the script until the calc does not work. If it does not work as soon as you hit it with the debugger then there must be something earlier in the script that is upsetting it, but my guess is that it might be something after it in the script. The other approach which you could either use on it's own or in conjunction with the debugger would be to comment out blocks of the script until you isolate what is causing the issue.
|||Hi,
Now, I figured out, that there is not an inifinity-recursion but a realy time consuming recursion. But I can not imagine why this takes that long, cause it's just a proof-of-conecpt project with very few data (~ 15 rows) and a small time-dimension (Jan/2006 - Dec/2007).
Best Whishes,
Manfred
ps.: Perhaps I sould try enterprise edition ...
|||Yeah, sometimes depending in the projectsm could take lot of time! :-(
mark your answer to resolved!
Kind Regards!
Cubes and Cost-Forecast
Hi,
I'm experiencing trouble with the following requirement:
We have a fact-table containing data about cost-forecasts. This table includes columns for the project-name, for the date the forecast was made, the date the forecast is for and the estimated costs. This could be an example for this table:
projekt, date_of_forecast, date, costs
PR-A, 2007-06-01, 2007-07-01, 2000
PR-A, 2007-06-05, 2007-07-01, 3000
PR-A, 2007-06-10, 2007-07-01, 2500
PR-A, 2007-06-10, 2007-07-15, 2000
For instance, the last row says: We estimated on 2007-06-10 that the costs for Project PR-A will be 2000 at 2007-07-15.
The customer wants a cube wich allows an answer to the following question:
Wich cost did we expect for [Projekt] .... at [date] on [date_of_forecast] ?
So, it's not possible to just sum up all the measures, but I have to look up the last cost behind [date_of_forecast] and [date] ...
How to accomplish the using SQL Server 2005 STANDARD-EDITION ?
Do I need two Time-Dimensions or just one?
Is there a way to create a Cube-Skript for this requirement?
Thanks and best whishes
Manfred
Dear Friend,
You must have a Time dimension and a Project Dimension and you FactTable that could be as you wrote...
Look for the follow post in my blog that could help you... (see the MDX query... I think you can apply to your case, but only seing more carefully)
http://pedrocgd.blogspot.com/2007/07/ssas-slowly-changing-values.html
I hope this helped you!
regards!
|||Hi,
I've tried this. But SSAS seems to get into an infinity-recursion ...
How to work around this issue?
Best Whishes,
Manfred
|||Can you post the calculation code? The usual problem with an infinite recursion is a missing measure reference somewhere.|||Here it is:
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
Btw: I also use currency conversions created by the wizzard. The code for the currency comes first; then this calculation comes ...
Best wishes,
Manfred
|||
You did not include the CREATE MEASURE statement. Is this the code for the [Gewinn] measure or the [Geplanter Gewinn]. I am guessing that it is for the [Gewinn] measure as this would cause an infinite recursion. If I have guessed correctly it is simply that you have transposed the two measures from Pedro's example, try the following
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn]),
[Measures].[Geplanter Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Gewinn], [Time].[Date].PrevMember)
)
)
In fact we should be able to simplify the whole thing, removing the "if null return null" section and ending up with the following:
Code Snippet
iif ( not isEmpty([Measures].[Geplanter Gewinn])
,([Measures].[Geplanter Gewinn])
,([Time].[Date].PrevMember)
)
Which says
1. If Geplanter Gewinn is not empty return that
2. Else return the value of this calculation for the previous time member (which is where the recursion comes in). So this calculation will keep searching back until it finds a nonEmpty value of Geplanter Gewinn.
|||Hi,
I think, there is a missunderstanding.
[Gewinn] is the Measure in the Cube and has a value for some days.
[Geplanter Gewinn] should return the last non empty value of [Gewinn]
So, the right syntax should be:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Geplanter Gewinn]
AS
iif ( not isEmpty([Measures].[Gewinn]), -- If there is a [Gewinn] for the current day
[Measures].[Gewinn], -- return it
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember) -- Go back one day and retry it
)
)
But this ends up in an infinity-recursion ...
Wishes,
Manfred
|||Dear ManfredSteyer,
The statment in my blog works perfectly... are you sure you saw it right?
Check this:
Code Snippet
'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)'
I hope you get it!!!
Regards!
|||Sorry, without the CREATE MEMBER clause I could not tell which was the measure in the cube. In that case it looks OK. I'm wondering if there is something else in your calculation script that might be conflicting.
Are you able to use this type of calculation in an MDX query using the "WITH MEMBER" clause?
|||for you both:
Code Snippet
CREATE MEMBER CURRENTCUBE.[MEASURES].CM_PRM_ENT_Racio
AS 'IIF(NOT IsEmpty ([Measures].[ENT_Racio]),
[Measures].[ENT_Racio]
,IIF ([DimTime].[Dia].PrevMember IS NULL, NULL, ([Measures].[CM_PRM_ENT_Racio]
,[DimTime].[Dia].PrevMember)
)
)',
VISIBLE = 1;
Helped?
This for me works!
Regards!
|||Hi,
I exactly used this pattern - I also looked up "MDX Solutions" (Wiley) ...
And if I use it directly within a mdx-query, it works too:
with member [Measures].[Geplanter Gewinn]
as
iif ( not isEmpty([Measures].[Gewinn]),
[Measures].[Gewinn],
iif ( [Time].[Date].PrevMember IS NULL,
null,
([Measures].[Geplanter Gewinn], [Time].[Date].PrevMember)
)
)
select [Measures].[Geplanter Gewinn] on 0
from [Kostenrechnung Sample Db]
where [Time].[Date].&[2007-06-15T00:00:00]
But when I used it as calculated member or within a cube-script, I get an inifinity-recursion ...
Have you used this pattern within cube-script/ as calc. meber or "just" as mdx-query ?
Regards,
Manfred
|||
I use it in a CM...
|||I can't see anything wrong with the implementation of this CM. I think the fact that it works inline in a query proves that there is nothing with it on it's own. There must be a circular dependancy somewhere in the calculation script.
In order to figure this out you could either set a breakpoint in the MDX Script and use the debugger. Stepping through the script until the calc does not work. If it does not work as soon as you hit it with the debugger then there must be something earlier in the script that is upsetting it, but my guess is that it might be something after it in the script. The other approach which you could either use on it's own or in conjunction with the debugger would be to comment out blocks of the script until you isolate what is causing the issue.
|||Hi,
Now, I figured out, that there is not an inifinity-recursion but a realy time consuming recursion. But I can not imagine why this takes that long, cause it's just a proof-of-conecpt project with very few data (~ 15 rows) and a small time-dimension (Jan/2006 - Dec/2007).
Best Whishes,
Manfred
ps.: Perhaps I sould try enterprise edition ...
|||Yeah, sometimes depending in the projectsm could take lot of time! :-(
mark your answer to resolved!
Kind Regards!
Saturday, February 25, 2012
Cube Operator
When we are using more than 10 columns in cube operator it is throwing error that maxiimum limit is 10.
but it is mandatory for my project to use more than 10 columns.
So could any one tell how to do this.
If not at all possible by using cube operator how else can this be done to get the same result
but it is mandatory for my project to use more than 10 columns.
So could any one tell how to do this.
If not at all possible by using cube operator how else can this be done to get the same result
The only way to do this is to generate the query with the necessary GROUP BY clauses yourself. The sheer number of combinations that you need to take care of will be huge. You could write a stored procedure or client side script that can generate a query for each combination of GROUP BY and UNION them together to get the same results. It almost seems like this is more suited for OLAP.
cube measures on rows in reporting services....
Is there anyway for reporting services to display measures on rows instead
of columns as in Excel?
I'm trying to format my data in rows but this doesn't seems possible in
reporting services... does anyone has any examples of how this can be done?Create a shared datasource to Foodmart 200 and try this RDL. HEre is a sample
report that i created with measures on rows.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>Report20</Value>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.72in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Measures_Unit_Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Measures_Profit.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>FoodMart_2000</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_Customers_Country">
<GroupExpressions>
<GroupExpression>=Fields!Customers_Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!Customers_Country.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="Customers_Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>Customers_Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Customers_Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.24in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.33in</Top>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Units</Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Profit</Value>
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>2.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="FoodMart 2000">
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="FoodMart_2000">
<Fields>
<Field Name="Customers_Country">
<DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Unit_Sales">
<DataField>[Measures].[Unit Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Profit">
<DataField>[Measures].[Profit]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>select
{{[Customers].[Country].Members}}on rows,
{ [Measures].[Unit Sales],[Measures].[Profit]} on columns
from Sales</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
"Nestor" wrote:
> Is there anyway for reporting services to display measures on rows instead
> of columns as in Excel?
> I'm trying to format my data in rows but this doesn't seems possible in
> reporting services... does anyone has any examples of how this can be done?
>
>
of columns as in Excel?
I'm trying to format my data in rows but this doesn't seems possible in
reporting services... does anyone has any examples of how this can be done?Create a shared datasource to Foodmart 200 and try this RDL. HEre is a sample
report that i created with measures on rows.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>Report20</Value>
</Textbox>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.72in</Height>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Measures_Unit_Sales.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!Measures_Profit.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.24in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>FoodMart_2000</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix1_Customers_Country">
<GroupExpressions>
<GroupExpression>=Fields!Customers_Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!Customers_Country.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<ReportItems>
<Textbox Name="Customers_Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>Customers_Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Customers_Country.Value</Value>
</Textbox>
</ReportItems>
</DynamicColumns>
<Height>0.24in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>2in</Width>
<Top>0.33in</Top>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<StaticRows>
<StaticRow>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Units</Value>
</Textbox>
</ReportItems>
</StaticRow>
<StaticRow>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<FontSize>12pt</FontSize>
<Color>DarkRed</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Profit</Value>
</Textbox>
</ReportItems>
</StaticRow>
</StaticRows>
</RowGrouping>
</RowGroupings>
</Matrix>
</ReportItems>
<Style />
<Height>2.25in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="FoodMart 2000">
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="FoodMart_2000">
<Fields>
<Field Name="Customers_Country">
<DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Unit_Sales">
<DataField>[Measures].[Unit Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Profit">
<DataField>[Measures].[Profit]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>select
{{[Customers].[Country].Members}}on rows,
{ [Measures].[Unit Sales],[Measures].[Profit]} on columns
from Sales</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
"Nestor" wrote:
> Is there anyway for reporting services to display measures on rows instead
> of columns as in Excel?
> I'm trying to format my data in rows but this doesn't seems possible in
> reporting services... does anyone has any examples of how this can be done?
>
>
Friday, February 17, 2012
csv import into ms sql 2000
I've got a csv file (75000 records) with 5 columns, one is a date of birth column which shows as an 8 digit long integer (yyyymmdd). I've tried sql dts but I just don't know how to show this column in sql as a date field in dd/mm/yyyy format. Can anyone please help?Using Excel you can change the cell format as you required & upload data in the SQL database but in Excel maximum row limit is 65536.
You can divide this data in two excel sheet & can proceed.
If you are not going to do this task frequently than it is fine, else find some better solution.|||Thanks Rajesh - i've tried that, the problem is that the date of birth field has no date separators, and being in number format, a excel just fills the cell with "###############". Today (2nd sept 2006) shows like an integer 20060902.
Thanks again for your quick reply.
Arthur|||Still Excel helps you lot. Check the enclosed file, it is self explanatory.
Add four columns next to your date column & add formulas same as enclosed file.
At the last check data then Copy -> Paste Special -> Value. Remove unwanted columns & upload data.
Little hard work but fruitful.|||Brilliant - all works well. Thanks a milliuon for all your help.
You can divide this data in two excel sheet & can proceed.
If you are not going to do this task frequently than it is fine, else find some better solution.|||Thanks Rajesh - i've tried that, the problem is that the date of birth field has no date separators, and being in number format, a excel just fills the cell with "###############". Today (2nd sept 2006) shows like an integer 20060902.
Thanks again for your quick reply.
Arthur|||Still Excel helps you lot. Check the enclosed file, it is self explanatory.
Add four columns next to your date column & add formulas same as enclosed file.
At the last check data then Copy -> Paste Special -> Value. Remove unwanted columns & upload data.
Little hard work but fruitful.|||Brilliant - all works well. Thanks a milliuon for all your help.
CSV Exports are not as expected, columns are missing
I use SQL2005. I have a hard time understanding the reason why the CSV exports are not exporting the report with all the columns. Only the first column of data is exported.
example, I have report like this
Product Customer Jan Feb Mar Apr Jun
p1 C1 12 3 13 7 20
After csv export, I get a bunch of label down the rows, then I see my Product and Customer columns but I can only see Jan. Feb Mar Apr and Jun are NOT exported.
All these columns have exactly the same settings and the report is not a matrix report.
When I export to Excel or HTML, the result is perfect.
Any idea?
Thanks
Philippe
Make sure that Output property is set to Auto or Yes for those textboxes (Textbox Properties dialog -> Data Output tab)|||Hi,
All these text boxes have the output property set to Auto.
|||Would you like to e-mail your report to me?|||OK, thanks for your help. I greatly appreciate it. The rdl of one of these reports is on its way.
Thanks,
Philippe
|||There is a bug here : when Output is set Auto it is translated to No for table columns with Hidden set to an expression.
The workaround is to set Output to Yes for textboxes which should be exported.
CSV exporting issue
Hi,
I have a simple report with a table and 3 columns. I have used only the detail row of the table to display data from a simple dataset that has a stored procedure as the data source.
When I export it to CSV format, the names of the textboxes that are in the table column also gets exported in the first row. Is there a way to suppress this?
I have already tried setting DataElementOutput = NoOutput for the table and the textboxes but nothing works. Please do not give me the link of an earlier thread on the same topic because that doesnt provide the answer.
I have also tried setting DeviceInfo parameters in RSReportDesigner.config and rsreportserver.config (NoHeader=true) but none of them work even after restarting the machine and SQL Reporting Service.
If there is a way or an alternative, please let me know.
Thanks,
Shyam
There is another issue with CSV exporting.
I want every field to be wrapped around double quotes, so I have handled that in the SQL query itself. When I export to CSV, it adds 4 more double quotes to every field which means every field has 6 double quotes around it (3 to the left and 3 to the right). I also tried handling it in the report by appending Chr(34) and an explicit double quote in the field expression but all of them seem to give the same stupid result (6 double quotes).
Is there a way to work around this problem in SQL Reporting services?
It's very frustrating.
Shyam
|||Thanks everybody for not responding. Hope it works on another machine.
Shyam
CSV exporting issue
Hi,
I have a simple report with a table and 3 columns. I have used only the detail row of the table to display data from a simple dataset that has a stored procedure as the data source.
When I export it to CSV format, the names of the textboxes that are in the table column also gets exported in the first row. Is there a way to suppress this?
I have already tried setting DataElementOutput = NoOutput for the table and the textboxes but nothing works. Please do not give me the link of an earlier thread on the same topic because that doesnt provide the answer.
I have also tried setting DeviceInfo parameters in RSReportDesigner.config and rsreportserver.config (NoHeader=true) but none of them work even after restarting the machine and SQL Reporting Service.
If there is a way or an alternative, please let me know.
Thanks,
Shyam
There is another issue with CSV exporting.
I want every field to be wrapped around double quotes, so I have handled that in the SQL query itself. When I export to CSV, it adds 4 more double quotes to every field which means every field has 6 double quotes around it (3 to the left and 3 to the right). I also tried handling it in the report by appending Chr(34) and an explicit double quote in the field expression but all of them seem to give the same stupid result (6 double quotes).
Is there a way to work around this problem in SQL Reporting services?
It's very frustrating.
Shyam
|||Thanks everybody for not responding. Hope it works on another machine.
Shyam
Tuesday, February 14, 2012
CSV DeviceSettings in ReportServer.config dont take effect
I am using MS Reporting Services 2000 SP2 and I need help very urgently. my issue is that the user needs to view Text datatype columns exported to csv (basically, customer service support notes), but with the text fields in one cell. I changed the configuration settings of the CSV rendering extension as below, but but it didnt seem to make any difference. The report continues to export the csv in a haphazard format, with the newline characters in the notes spilling onto the next line, etc.
I also noticed that when I used ReportManager, the url request didnt have the rc:Encoding or rc:SuppressLineBreaks settings when I try to export the report to csv. I added these as a test in the url request directly and it worked just fine. I tried changing various settings, but none of them seem to take any effect. What am I missing here ?
I tried restarting the web server AND the ReportServer service. My config settings are as below. Also, my config file is located at:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config
<Render>
... ... ...
... ... ...
<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<SuppressLineBreaks>True</SuppressLineBreaks>
</DeviceInfo>
</Configuration>
</Extension>
</Render>
Any advice would be greatly appreciated. Thanks in advance.
I found the below information on another post.. Specifically, the thread "Export to an ASCII CSV" by Bruce L-C (MVP) on 4/19/2006. According to him, the settings (He speaks of only the encoding setting) work in RS 2000 only when passed in the URL explicitly. Setting it in the Config file does not have any effect in RS 2000, they are new for RS 2005.http://msdn.microsoft.com/newsgroups/Default.aspx?query=device+settings+reporting+services&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
I hope someone can prove me wrong here.... I would love to figure out how to do this in RS 2000. Any advice would be highly appreciated. Thanks in advance.
Subscribe to:
Posts (Atom)