How to save the parameters to a RS report

I have several RS reports which have a number of parameters like
Company Code, Plant, Movement type, GL Accounts, Period, Material type,
Material,etc.
Sometimes each user runs the report with the same selection except that
they change something like the period for which the report is run. Is
there a way we can provide a mechanism to the users to save the
parameters for the reports and retrieve the parameter settings for a
report and modify and run them. We can do this in Business objects.
Thanks
Kareni think you are looking for the subscriptions feature in Report Manager.
Click on a report. Next click "subscriptions" near the top. You can now
setup a subscription with different parameters.
Taz
"KarenM" <karenmiddleol@.yahoo.com> wrote in message
news:1158489967.873086.53010@.d34g2000cwd.googlegroups.com...
>I have several RS reports which have a number of parameters like
> Company Code, Plant, Movement type, GL Accounts, Period, Material type,
> Material,etc.
> Sometimes each user runs the report with the same selection except that
> they change something like the period for which the report is run. Is
> there a way we can provide a mechanism to the users to save the
> parameters for the reports and retrieve the parameter settings for a
> report and modify and run them. We can do this in Business objects.
> Thanks
> Karen
>|||Hi Tarun,
No I know of subscriptions that is when I expect the report to run in
background. What I am looking for is if users run the same report many
times with a specific parameters it is not convenient for them to type
them everytime they would like to save the parameters and retrieve and
use them
Thanks
Karen
Tarun Mistry wrote:
> i think you are looking for the subscriptions feature in Report Manager.
> Click on a report. Next click "subscriptions" near the top. You can now
> setup a subscription with different parameters.
> Taz
>
> "KarenM" <karenmiddleol@.yahoo.com> wrote in message
> news:1158489967.873086.53010@.d34g2000cwd.googlegroups.com...
> >I have several RS reports which have a number of parameters like
> > Company Code, Plant, Movement type, GL Accounts, Period, Material type,
> > Material,etc.
> >
> > Sometimes each user runs the report with the same selection except that
> > they change something like the period for which the report is run. Is
> > there a way we can provide a mechanism to the users to save the
> > parameters for the reports and retrieve the parameter settings for a
> > report and modify and run them. We can do this in Business objects.
> >
> > Thanks
> > Karen
> >|||This is not a feature that is included in the product.
We created a system that did this. The first step is that you have to
create your own Visual Studio application that you will use for
reporting, probably including a ReportViewer control.
You would create your own fields on the screen to accept these
parameters.
Then you would have to create a system for saving the reports.
We collected all the paramters into an xml string and saved it to a
table, along with a userID and a name the user gave to the particular
parameter set.
KarenM wrote:
> Hi Tarun,
> No I know of subscriptions that is when I expect the report to run in
> background. What I am looking for is if users run the same report many
> times with a specific parameters it is not convenient for them to type
> them everytime they would like to save the parameters and retrieve and
> use them
> Thanks
> Karen
> Tarun Mistry wrote:
> > i think you are looking for the subscriptions feature in Report Manager.
> >
> > Click on a report. Next click "subscriptions" near the top. You can now
> > setup a subscription with different parameters.
> >
> > Taz
> >
> >
> > "KarenM" <karenmiddleol@.yahoo.com> wrote in message
> > news:1158489967.873086.53010@.d34g2000cwd.googlegroups.com...
> > >I have several RS reports which have a number of parameters like
> > > Company Code, Plant, Movement type, GL Accounts, Period, Material type,
> > > Material,etc.
> > >
> > > Sometimes each user runs the report with the same selection except that
> > > they change something like the period for which the report is run. Is
> > > there a way we can provide a mechanism to the users to save the
> > > parameters for the reports and retrieve the parameter settings for a
> > > report and modify and run them. We can do this in Business objects.
> > >
> > > Thanks
> > > Karen
> > >|||That is exactly what we are after. Could you kindly share the code for
the same. It is such a vital requirement for users I am surprised that
Microsoft missed including this in one of the RS support packs.
Thanks
Karen
cowznofsky wrote:
> This is not a feature that is included in the product.
> We created a system that did this. The first step is that you have to
> create your own Visual Studio application that you will use for
> reporting, probably including a ReportViewer control.
> You would create your own fields on the screen to accept these
> parameters.
> Then you would have to create a system for saving the reports.
> We collected all the paramters into an xml string and saved it to a
> table, along with a userID and a name the user gave to the particular
> parameter set.
> KarenM wrote:
> > Hi Tarun,
> >
> > No I know of subscriptions that is when I expect the report to run in
> > background. What I am looking for is if users run the same report many
> > times with a specific parameters it is not convenient for them to type
> > them everytime they would like to save the parameters and retrieve and
> > use them
> >
> > Thanks
> > Karen
> > Tarun Mistry wrote:
> > > i think you are looking for the subscriptions feature in Report Manager.
> > >
> > > Click on a report. Next click "subscriptions" near the top. You can now
> > > setup a subscription with different parameters.
> > >
> > > Taz
> > >
> > >
> > > "KarenM" <karenmiddleol@.yahoo.com> wrote in message
> > > news:1158489967.873086.53010@.d34g2000cwd.googlegroups.com...
> > > >I have several RS reports which have a number of parameters like
> > > > Company Code, Plant, Movement type, GL Accounts, Period, Material type,
> > > > Material,etc.
> > > >
> > > > Sometimes each user runs the report with the same selection except that
> > > > they change something like the period for which the report is run. Is
> > > > there a way we can provide a mechanism to the users to save the
> > > > parameters for the reports and retrieve the parameter settings for a
> > > > report and modify and run them. We can do this in Business objects.
> > > >
> > > > Thanks
> > > > Karen
> > > >

How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the D
B
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/ ****************************************
***********************************
***********
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
****************************************
************************************
**********/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @.id
while @.@.fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @.id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @.id
select @.pages = sum(dpages)
from sysindexes
where indid < 2
and id = @.id
select @.pages = @.pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @.id
update #spt_space
set data = @.pages
where objid = @.id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
- data
where objid = @.id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
where objid = @.id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @.id
and objid = @.id
fetch next from c_tables
into @.id
end
select --top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased fro
m
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLSer...=12717
7
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This might be useful.
CREATE TABLE #Tables
( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT
[name],
[data]
FROM #Tables
DROP TABLE #Tables
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl
..
>I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony|||Thanks everyone for the advice. I found the table that was causing the issue
.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there ar
e
>no obvious reasons for the increase. We have also tried Shrinking both the
DB
>and file with little reduction in size. Is there a way to query the databas
e
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasibl
e
>due to the number of objects. TIA
>Antony

How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the DB
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
**************************************************************************************/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @.id
while @.@.fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @.id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @.id
select @.pages = sum(dpages)
from sysindexes
where indid < 2
and id = @.id
select @.pages = @.pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @.id
update #spt_space
set data = @.pages
where objid = @.id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
- data
where objid = @.id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
where objid = @.id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @.id
and objid = @.id
fetch next from c_tables
into @.id
end
select --top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there are
> no obvious reasons for the increase. We have also tried Shrinking both the DB
> and file with little reduction in size. Is there a way to query the database
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasible
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0468_01C71468.EBBF5E10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This might be useful.
CREATE TABLE #Tables ( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT [name],
[data]
FROM #Tables
DROP TABLE #Tables
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message =news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl...
>I have a database under SQL2005 that in a 24 hour period has increased =from
> 200MB to 2GB (this is the dbf file size not the log file size) and =there are
> no obvious reasons for the increase. We have also tried Shrinking both =the DB
> and file with little reduction in size. Is there a way to query the =database
> and find out the sizes of tables etc to help with further =investigation?
> Looking at the properties of each in Management studio really isn't =feasible
> due to the number of objects. TIA
> Antony
--=_NextPart_000_0468_01C71468.EBBF5E10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This might be useful.
CREATE TABLE =#Tables ( [name] nvarchar(20), [rows] char(11), [reserved] varchar(18), [data] varchar(18), [index_size] varchar(18), =[unused] varchar(18) )
EXECUTE sp_MSForEachTable ='INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT [name], [data]FROM #Tables
DROP TABLE #Tables
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Antony" =wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl...>I =have a database under SQL2005 that in a 24 hour period has increased from> 200MB =to 2GB (this is the dbf file size not the log file size) and there are> =no obvious reasons for the increase. We have also tried Shrinking both the DB> and file with little reduction in size. Is there a way to =query the database> and find out the sizes of tables etc to help with =further investigation?> Looking at the properties of each in Management =studio really isn't feasible> due to the number of objects. TIA> Antony

---

--=_NextPart_000_0468_01C71468.EBBF5E10--|||Thanks everyone for the advice. I found the table that was causing the issue.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there are
>no obvious reasons for the increase. We have also tried Shrinking both the DB
>and file with little reduction in size. Is there a way to query the database
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasible
>due to the number of objects. TIA
>Antony