Archiving database for reporting

Hi
SSIS can be a good choice.
Also
>also keep about 1 year
> of data in archive database.
DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())

> I must keep data in production database about
> two months, b
Create a job and schedule it on day period
I must keep data in production database about
INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
dt<=DATEADD(month,-2,GETDATE())

>Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database.
Here, I'd suggest you to create a trigger or (take a look at OUTPUT clause)
and flag or whatever modified data
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.
On Feb 7, 9:03Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> SSIS can be a good choice.
> Also
>
> DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
>
> X X Create a job and schedule it on day period
> XI must keep data in production database about
> INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
> dt<=DATEADD(month,-2,GETDATE())
>
> Here, I'd suggest Xyou to create a trigger or (take a look at OUTPUT clause)
> and flag or whatever modified data
> "OgnjenT" <Ognj...@.gmail.com> wrote in message
> news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
>
>
> - Show quoted text -
Problem with triger is when I clean data older then two month it will
delete data in archive database too. Also I must then put the triger
on the other tables because of the referential integrity in the
archive database.

>INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
>dt<=DATEADD(month,-2,GETDATE())
I can't do that because I must have to synchronize production and
archive database every day because every night I mast have a dozen of
reports.
I decided to have archive database because of different way of queries
for normal processing and for reporting and I don't wont reporting
have so mutch influence on normaln work. So it is not only archiving
data but creating separate database for reporting so I can put some
more indexes, indexed views and in the same time my insert and updates
will still be fast in the production database. Also production server
doesn't have to hold data older than two months so it is better to
clean it so my queries woold be faster.
Is it maybe ok to delete data older than two months from archive
database every night and insert that data from production database. It
seems to me that it will be faster than check every row in production
database and then update archive database if row is updated, delete if
deleted. The esiest of course is to insert new rows.
Maybe before that bulk delete and insert it wood be smart to drop all
indexes, indexed views and constraint and recreate it after. But I
must do all that for about 15 minutes.

Archiving database for reporting

HI,
I have a database on sql server 2005 ent edition with about 300 000
new records every day. I must keep data in production database about
two months, but there are a lot of reporting activities which are
interrupting normal functioning of production server and there is also
requirement to query historical data(about 1 year) with reports. I
must add new data to the archive database every day. Additionally two
months old data in the production database can change (delete, update)
and that also must be reflected in archive database. So basically I
must insert new data, update changed date and delete deleted data from
production database into archive database but also keep about 1 year
of data in archive database. Then I can separately optimize archive
database for reporting. What is the best way to satisfy all that
requirements. I am thinking about SSIS.SSIS will work. You could also consider a small trigger on the main table
that will put the key values into a holding table when
inserts/updates/deletes occur so you can very quickly make the necessary
changes to the historical records when <2mth old data is modified.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||Hi
SSIS can be a good choice.
Also
>also keep about 1 year
> of data in archive database.
DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> I must keep data in production database about
> two months, b
Create a job and schedule it on day period
I must keep data in production database about
INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
dt<=DATEADD(month,-2,GETDATE())
>Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database.
Here, I'd suggest you to create a trigger or (take a look at OUTPUT clause)
and flag or whatever modified data
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.|||On Feb 7, 9:03=A0am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> SSIS can be a good choice.
> Also
> >also keep about 1 year
> > of data in archive database.
> DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
> > I must keep data in production database about
> > two months, b
> =A0 =A0 Create a job and schedule it on day period
> =A0I must keep data in production database about
> INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AN=D
> dt<=3DDATEADD(month,-2,GETDATE())
> >Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database.
> Here, I'd suggest =A0you to create a trigger or (take a look at OUTPUT cla=use)
> and flag or whatever modified data
> "OgnjenT" <Ognj...@.gmail.com> wrote in message
> news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
>
> > HI,
> > I have a database on sql server 2005 ent edition with about 300 000
> > new records every day. I must keep data in production database about
> > two months, but there are a lot of reporting activities which are
> > interrupting normal functioning of production server and there is also
> > requirement to query historical data(about 1 year) with reports. I
> > must add new data to the archive database every day. Additionally two
> > months old data in the production database can change (delete, update)
> > and that also must be reflected in archive database. So basically I
> > must insert new data, update changed date and delete deleted data from
> > production database into archive database but also keep about 1 year
> > of data in archive database. Then I can separately optimize archive
> > database for reporting. What is the best way to satisfy all that
> > requirements. I am thinking about SSIS.- Hide quoted text -
> - Show quoted text -
Problem with triger is when I clean data older then two month it will
delete data in archive database too. Also I must then put the triger
on the other tables because of the referential integrity in the
archive database.
>INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >=3D'19000101' AND=
>dt<=3DDATEADD(month,-2,GETDATE())
I can't do that because I must have to synchronize production and
archive database every day because every night I mast have a dozen of
reports.
I decided to have archive database because of different way of queries
for normal processing and for reporting and I don't wont reporting
have so mutch influence on normaln work. So it is not only archiving
data but creating separate database for reporting so I can put some
more indexes, indexed views and in the same time my insert and updates
will still be fast in the production database. Also production server
doesn't have to hold data older than two months so it is better to
clean it so my queries woold be faster.
Is it maybe ok to delete data older than two months from archive
database every night and insert that data from production database. It
seems to me that it will be faster than check every row in production
database and then update archive database if row is updated, delete if
deleted. The esiest of course is to insert new rows.
Maybe before that bulk delete and insert it wood be smart to drop all
indexes, indexed views and constraint and recreate it after. But I
must do all that for about 15 minutes.

Architecture changes from RS2000 and RS2005

Where can I find information on the architecture changes from Reporting
Services 2000 and Reporting Services 2005?Not sure what you are looking for. You can check the pre-release
documentation of RS 2005 here:
http://msdn2.microsoft.com/en-us/library/ms228038(en-US,SQL.90).aspx
Generally, reports that work on RS 2000 will also work on RS 2005 (they get
upgrade on-the-fly). For SOAP applications interacting with RS
specificially, the old RS 2000 SOAP namespace is still supported on RS 2005.
However, if you want to take advantage of some of the new functionality, you
will need to use the new RS 2005 SOAP end point.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <David@.discussions.microsoft.com> wrote in message
news:DA305369-16C6-4FFA-B636-914AD510C87F@.microsoft.com...
> Where can I find information on the architecture changes from Reporting
> Services 2000 and Reporting Services 2005?|||Thanks! This helps!
"Robert Bruckner [MSFT]" wrote:
> Not sure what you are looking for. You can check the pre-release
> documentation of RS 2005 here:
> http://msdn2.microsoft.com/en-us/library/ms228038(en-US,SQL.90).aspx
> Generally, reports that work on RS 2000 will also work on RS 2005 (they get
> upgrade on-the-fly). For SOAP applications interacting with RS
> specificially, the old RS 2000 SOAP namespace is still supported on RS 2005.
> However, if you want to take advantage of some of the new functionality, you
> will need to use the new RS 2005 SOAP end point.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:DA305369-16C6-4FFA-B636-914AD510C87F@.microsoft.com...
> > Where can I find information on the architecture changes from Reporting
> > Services 2000 and Reporting Services 2005?
>
>

Approx date of next release

Does anyone have an approximate date of the next release of Reporting
Services?
Thanks in advance.
MarkReporting Services will be shipping along with Yukon. This is sometime in
the second half of next year most likely.
--
| From: "Mark" <mfield@.idonotlikespam.cce.umn.edu>
| Subject: Approx date of next release
| Date: Mon, 25 Oct 2004 08:55:27 -0500
| Lines: 8
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.181
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.181
| Message-ID: <e#K3FppuEHA.2016@.TK2MSFTNGP15.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: x15-238.cce.umn.edu 134.84.15.238
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP0
8.phx.gbl!TK2MSFTNGP15.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:33045
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Does anyone have an approximate date of the next release of Reporting
| Services?
|
| Thanks in advance.
|
| Mark
|
|
|

applying sum or count funtions to a group

How can I make reporting services to calculate the sum of a value that is
displayed in a group? When I insert the funtion =Sum(Fields!nuAmount.Value)
in the footer of a group, I get the sum considering all values included in a
detail. I only want the sum to be calculated from the group header values
diaplyeddid you get any answer on this quest?
reg.
Kjell Brandes
"FedericAguirre" <FedericAguirre@.discussions.microsoft.com> wrote in message
news:C518AE1F-FFE3-4F16-A007-165420532AB1@.microsoft.com...
> How can I make reporting services to calculate the sum of a value that is
> displayed in a group? When I insert the funtion
=Sum(Fields!nuAmount.Value)
> in the footer of a group, I get the sum considering all values included in
a
> detail. I only want the sum to be calculated from the group header values
> diaplyed|||do some of the calculations on the query side. If you want a count of orders
group by order# then do a count of them on the report.
"FedericAguirre" wrote:
> How can I make reporting services to calculate the sum of a value that is
> displayed in a group? When I insert the funtion =Sum(Fields!nuAmount.Value)
> in the footer of a group, I get the sum considering all values included in a
> detail. I only want the sum to be calculated from the group header values
> diaplyed

Applying Stylesheet in Reports (e.g. Font, Header)

I'm using Reporting Service with Service Pack3 and I want to Apply my own stylesheet in reports. I want to give my own style class to Fonts, Headers etc. Can anyone help me ? Is it possible or not ?SQL Server 2000 Reporting Services has somewhat limited style template
support. Improving support in this area is being looked at for inclusion in
a future release.
Until then you have the following options:
1. You can approximate this behavior by creating the specific report you
want and then placing it in C:\ProgramFiles\Microsoft SQL
Server\80\Tools\Report Designer\ProjectItems\ReportProject directory. These
reports "templates" will show up in Report Designer's Add New Item dialog
along with the Report Wizard, Report, and Data Source templates when
you add a new report to a project.
2. For simple wizard-created reports, you can control the style via
modifying the wizard style templates (there's an XML config file for this
documented in the help)
3. If you're wanting to modify the style dynamically after the reports are
created, you could store style information in your database and then query
for that information in your report, setting style properties based on the
query results (e.g. FontFamily
=First(Fields!DetailRowFontFamily.Value,"StyleDataSet"))
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gaurav Shah" <Gaurav Shah@.discussions.microsoft.com> wrote in message
news:ACC0AD53-1693-46DA-B929-9DA55A51D373@.microsoft.com...
> I'm using Reporting Service with Service Pack3 and I want to Apply my own
stylesheet in reports. I want to give my own style class to Fonts, Headers
etc. Can anyone help me ? Is it possible or not ?

Applying Encryption Keys in a Web Farm

I wonder if someone can help clarify this one..?
If Iâ've implemented Reporting Services across a Web Farm, and saved a copy
of the encryption key for each node â' If encryption is broken, which key do I
apply?
Thanks,
MarkThe key is the same for each node. There is only one symmetric key for a
farm. The key is just encrypted differently for each node.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> I wonder if someone can help clarify this one..?
> If I've implemented Reporting Services across a Web Farm, and saved a copy
> of the encryption key for each node - If encryption is broken, which key
do I
> apply?
> Thanks,
> Mark
>|||Thanks Daniel
Does that mean that if I want to recover I run â'rskeymgmt â'a..â' on one (any)
node â' or on all nodes in the Farm?
"Daniel Reib [MSFT]" wrote:
> The key is the same for each node. There is only one symmetric key for a
> farm. The key is just encrypted differently for each node.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > I wonder if someone can help clarify this one..?
> >
> > If I've implemented Reporting Services across a Web Farm, and saved a copy
> > of the encryption key for each node - If encryption is broken, which key
> do I
> > apply?
> >
> > Thanks,
> > Mark
> >
>
>|||You would need to do that to each node that was failing with an error about
not be able to unencrypt data. You should rarely have to use this tool.
Only if any passwords get reset or users that the service are running as
change.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
news:6865252C-DD9F-425F-8F4A-498139C9BDDC@.microsoft.com...
> Thanks Daniel
> Does that mean that if I want to recover I run 'rskeymgmt -a..' on one
(any)
> node - or on all nodes in the Farm?
>
> "Daniel Reib [MSFT]" wrote:
> > The key is the same for each node. There is only one symmetric key for
a
> > farm. The key is just encrypted differently for each node.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> > news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > > I wonder if someone can help clarify this one..?
> > >
> > > If I've implemented Reporting Services across a Web Farm, and saved a
copy
> > > of the encryption key for each node - If encryption is broken, which
key
> > do I
> > > apply?
> > >
> > > Thanks,
> > > Mark
> > >
> >
> >
> >|||OK - Thanks Daniel - Appreciate your swift response.
"Daniel Reib [MSFT]" wrote:
> You would need to do that to each node that was failing with an error about
> not be able to unencrypt data. You should rarely have to use this tool.
> Only if any passwords get reset or users that the service are running as
> change.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> news:6865252C-DD9F-425F-8F4A-498139C9BDDC@.microsoft.com...
> > Thanks Daniel
> >
> > Does that mean that if I want to recover I run 'rskeymgmt -a..' on one
> (any)
> > node - or on all nodes in the Farm?
> >
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > The key is the same for each node. There is only one symmetric key for
> a
> > > farm. The key is just encrypted differently for each node.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "Mark Barker" <MarkBarker@.discussions.microsoft.com> wrote in message
> > > news:70EE7611-00DA-40E8-8B0C-31F5D48EE39E@.microsoft.com...
> > > > I wonder if someone can help clarify this one..?
> > > >
> > > > If I've implemented Reporting Services across a Web Farm, and saved a
> copy
> > > > of the encryption key for each node - If encryption is broken, which
> key
> > > do I
> > > > apply?
> > > >
> > > > Thanks,
> > > > Mark
> > > >
> > >
> > >
> > >
>
>

Applying a .css to a .rdl file

Hello all,

I'm using Reporting Services 2005 and have been developing reports using Visual Studio 2005. There will eventually be a large number of reports, all of which will need to adhere to my company's standard look and feel.

To this end I was wondering if it were possible to apply a .css stylesheet to the contents of a .rdl report?

I've been digging round the web and can't seem to come across a definitive answer either way.

I am fairly new to Reporting Services so apologies in advance if this has been asked before.

Thanks

I believe there is no way of doing this

Appending a custom sql where clause

Hello,
I am in the process of evaluating SSRS 2005 to replace an home grown
reporting tool. In my reporting application all users have access all
the tables\fields in the database. In the home grown tool, data
security is implemented by the following mechanism. When users run
reports, a standard sql where clause is appended to the sql generated
by the reproting tool. This standard where clause has @.userID as the
parameter.
Now is there a way in SSRS 2005 I can append a standard where clause to
every report just before it is run? Does it have an event model, I can
hook into?
Thanks
_Gigi JKIn most cases query parameters are mapped to report parameters but they do
not have to be, they can be mapped to expressions. When mapping a query
parameter to an expression you can map the query parameter to a global
variable. One of the global variables available is User!UserID. This
variable has the user (and their domain). If you don't want the domain then
you would strip the domain off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<gigijk@.gmail.com> wrote in message
news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> Hello,
> I am in the process of evaluating SSRS 2005 to replace an home grown
> reporting tool. In my reporting application all users have access all
> the tables\fields in the database. In the home grown tool, data
> security is implemented by the following mechanism. When users run
> reports, a standard sql where clause is appended to the sql generated
> by the reproting tool. This standard where clause has @.userID as the
> parameter.
> Now is there a way in SSRS 2005 I can append a standard where clause to
> every report just before it is run? Does it have an event model, I can
> hook into?
> Thanks
> _Gigi JK
>|||Bruce,
Thanks for the reply. Sorry I sent the same question to you directly from my
gmail address as well.
In my case, reporting application will be intergrated into another
application which does not use NT auth. Is there a way I could pass in userID
to reporting tool?
On the original issue, what I want to do is to automatically append a where
clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
user_id=@.userID') to every query generated by the reporting tool withou the
user intervention. Would this be posssible.
Once again thank you.
_GJK
"Bruce L-C [MVP]" wrote:
> In most cases query parameters are mapped to report parameters but they do
> not have to be, they can be mapped to expressions. When mapping a query
> parameter to an expression you can map the query parameter to a global
> variable. One of the global variables available is User!UserID. This
> variable has the user (and their domain). If you don't want the domain then
> you would strip the domain off.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <gigijk@.gmail.com> wrote in message
> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> > Hello,
> > I am in the process of evaluating SSRS 2005 to replace an home grown
> > reporting tool. In my reporting application all users have access all
> > the tables\fields in the database. In the home grown tool, data
> > security is implemented by the following mechanism. When users run
> > reports, a standard sql where clause is appended to the sql generated
> > by the reproting tool. This standard where clause has @.userID as the
> > parameter.
> >
> > Now is there a way in SSRS 2005 I can append a standard where clause to
> > every report just before it is run? Does it have an event model, I can
> > hook into?
> >
> > Thanks
> > _Gigi JK
> >
>
>|||You can have a hidden parameter (i.e. it does not prompt for it but you can
include it when running the report).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GJK" <GJK@.discussions.microsoft.com> wrote in message
news:D37B86F8-AA55-4FF1-BC01-C426D806AF02@.microsoft.com...
> Bruce,
> Thanks for the reply. Sorry I sent the same question to you directly from
> my
> gmail address as well.
> In my case, reporting application will be intergrated into another
> application which does not use NT auth. Is there a way I could pass in
> userID
> to reporting tool?
> On the original issue, what I want to do is to automatically append a
> where
> clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
> user_id=@.userID') to every query generated by the reporting tool withou
> the
> user intervention. Would this be posssible.
> Once again thank you.
> _GJK
> "Bruce L-C [MVP]" wrote:
>> In most cases query parameters are mapped to report parameters but they
>> do
>> not have to be, they can be mapped to expressions. When mapping a query
>> parameter to an expression you can map the query parameter to a global
>> variable. One of the global variables available is User!UserID. This
>> variable has the user (and their domain). If you don't want the domain
>> then
>> you would strip the domain off.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <gigijk@.gmail.com> wrote in message
>> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
>> > Hello,
>> > I am in the process of evaluating SSRS 2005 to replace an home grown
>> > reporting tool. In my reporting application all users have access all
>> > the tables\fields in the database. In the home grown tool, data
>> > security is implemented by the following mechanism. When users run
>> > reports, a standard sql where clause is appended to the sql generated
>> > by the reproting tool. This standard where clause has @.userID as the
>> > parameter.
>> >
>> > Now is there a way in SSRS 2005 I can append a standard where clause to
>> > every report just before it is run? Does it have an event model, I can
>> > hook into?
>> >
>> > Thanks
>> > _Gigi JK
>> >
>>

Append String to Value

I'm using SQL Reporting Services 2000 and I've built a pie chart. I'm having
trouble displaying the values in the chart. What I want to do is append a
string to the value that is displayed in the legend. Why doesn't this work?
On the Values tab of Chart Properties -> Data tab -> I double clicked on an
item in the Values listbox.
In the Series label:
= "Fully Patched = " + Sum(Fields!FullyPatched.Value)
In the Value: label:
= Sum(Fields!FullyPatched.Value)
This displays Category 1 in the Legend. The 1 is correct but I'm not sure
where the word Category came from. Also, how do you create a Constant? If
you go to the Edit Expression dialog, You have a choice to choose from
Constants, Globals, ..., but I cannot figure out where to declare and set
these variables.
Thanks,
-ChadOk, I figured out you need to use & instead of +, so:
= Code.strTest & Sum(Fields!FullyPatched.Value) was what I needed.
Now, what about Edit Expression -> Fields: -> Constants? How do I add/use
them?
Thanks,
--Chad

append date to exporting file name in SQL reporting service

When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?

example : Report name : Testreport

Exported file should be : Testingreport-November-22-2007.pdf

please help me in this

Thanks
sukuTry here:

http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp|||I would suggest placing the timestamp inside of the report.
ie. "Report was generated November 22 2007 11:01am".
This way the client cannot change the "report date".

Because it is a PDF and thus you won't be editing the document you can sort it by "Date Modified" in explorer if you want to sort the files.

append date to exporting file name in SQL reporting service

When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?

example : Report name : Testreport

Exported file should be : Testingreport-November-22-2007.pdf

please help me in this

Thanks
suku

Quote:

Originally Posted by sukumaster

When exporting a report to another format, say excel,PDF; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to pdf, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services ?

example : Report name : Testreport

Exported file should be : Testingreport-November-22-2007.pdf

please help me in this

Thanks
suku

Please post the over all operation you are trying to perform?
Are you trying to use shell script to ftp the file to any other server?
If Yes, then renaming a file would become easy!!

app.config for DLL

I am calling a C# method/DLL from Reporting Services. In the DLL, I am
referencing ConfigurationSettings.AppSettings["DBConnectionString"].
This should go in app.config in the caller's folder. This is fine when
the caller is a regular .NET application, but I don't know where to put
the app.config for Reporting Services.1. During runtime the report is generated under the Report Server host
process so the <appSetting> section in web.config file should work. Try
rendering a report from the Report which has a textbox with the following
expression:
= System.Configuration.Configuration.AppSettings(<your config value>)
Please note that the web.config file already has a configuration section
so you need to add only the <appSettings> element. You should have the same
result when calling this from your DLL since it will be loaded in the RS
application domain.
2. During design time it is a bit trickier. Unfortunately, the current
configuration handler of the Report Designer doesn't seem to recognize
<appSettings>. However, you can render the report in debug mode by hitting
F5. This renders the report under ReportHost.exe. To get the config settings
working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
Server\80\Tools\Report Designer and place your configuration section there
(the <configuration> element should be spelled with small "c"), e.g.:
<?xml version="1.0" encoding="utf-8" ?><configuration>
<appSettings>
<add key="serverUrl" value="http://localhost/reportserver" />
</appSettings>
</configuration>
b) To get the Preview tab working you could either check for Nothing and
replace that with a default value, or wrap the ConfigSettings.AppSettings
call to default to some default constant values. Once again, this is only
needed during design time. Your runtime report generation shouldn't need
this hack.
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Bucky" <uw_badgers@.mail.com> wrote in message
news:1103320212.226556.252030@.f14g2000cwb.googlegroups.com...
>I am calling a C# method/DLL from Reporting Services. In the DLL, I am
> referencing ConfigurationSettings.AppSettings["DBConnectionString"].
> This should go in app.config in the caller's folder. This is fine when
> the caller is a regular .NET application, but I don't know where to put
> the app.config for Reporting Services.
>|||Just to clarify...by web.config I meant the Report Server web.config file
located in C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer if default setup settings have been accepted.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23wqOlKK5EHA.3728@.TK2MSFTNGP12.phx.gbl...
> 1. During runtime the report is generated under the Report Server host
> process so the <appSetting> section in web.config file should work. Try
> rendering a report from the Report which has a textbox with the following
> expression:
> = System.Configuration.Configuration.AppSettings(<your config value>)
> Please note that the web.config file already has a configuration section
> so you need to add only the <appSettings> element. You should have the
> same result when calling this from your DLL since it will be loaded in the
> RS application domain.
> 2. During design time it is a bit trickier. Unfortunately, the current
> configuration handler of the Report Designer doesn't seem to recognize
> <appSettings>. However, you can render the report in debug mode by hitting
> F5. This renders the report under ReportHost.exe. To get the config
> settings
> working, create a ReportHost.exe.config in C:\Program Files\Microsoft SQL
> Server\80\Tools\Report Designer and place your configuration section there
> (the <configuration> element should be spelled with small "c"), e.g.:
>
> <?xml version="1.0" encoding="utf-8" ?><configuration>
> <appSettings>
> <add key="serverUrl" value="http://localhost/reportserver" />
> </appSettings>
> </configuration>
> b) To get the Preview tab working you could either check for Nothing and
> replace that with a default value, or wrap the ConfigSettings.AppSettings
> call to default to some default constant values. Once again, this is only
> needed during design time. Your runtime report generation shouldn't need
> this hack.
>
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Bucky" <uw_badgers@.mail.com> wrote in message
> news:1103320212.226556.252030@.f14g2000cwb.googlegroups.com...
>>I am calling a C# method/DLL from Reporting Services. In the DLL, I am
>> referencing ConfigurationSettings.AppSettings["DBConnectionString"].
>> This should go in app.config in the caller's folder. This is fine when
>> the caller is a regular .NET application, but I don't know where to put
>> the app.config for Reporting Services.
>

Apache and SQL Reporting Services

Hi Everyone,
I'm pretty new to reporting services so this might be a dumb question.... A client of mine has asked me to use reporting services to create reports for them but they have apache web servers!!!! Is this even possible? I've seen tons of documentation on reporting services by now but none that says you can use it with Apache? The Apache servers all have .NET 1.1 installed and working but I'm not sure if that's enough to get this working.
Any information you could provide me with would be greatly appreciated...... I want to give my client an educated response to this request and I haven't been able to find much!!
THANKS!!!
PJ

Hi PJ,

The simple reply is you will need IIS installed. RS is not compatible to have Apache instead of IIS.

|||Thanks Brad!! I thought I would get this answer (and I like IIS better anyway so I'm not complaining)!
Thanks Again!
PJ

Apache and Reporting Services

Can Reporting Servers run on Apache?No. It is an ASP.Net application.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bryan Avery" <b_avery@.yahoo.com> wrote in message
news:1127394777.321200.26010@.g43g2000cwa.googlegroups.com...
> Can Reporting Servers run on Apache?
>|||Sort of. Not truely as 'on' Apache, but if your front-end web server is
Apache, and you want to forward certain requests to be handled by reporting
services, then yes, you can make it LOOK as if it came from Apache itself
using Apache's Reverse Proxy functionality.
You'll need to have the Proxy module, and the Rewrite module compiled into
Apache, then set up a section in httpd.conf that looks like the following,
assuming your Report Server is named "RepServ" and is running on Port 80:
RewriteRule ^/ReportServer(.*) http://RepServ/ReportServer$1 [P,NC]
ProxyPassReverse /ReportServer http://RepServ/ReportServer
This way, when Apache sees requests for anything going to a 'ReportServer'
directory, it will reverse-proxy the request to the report server (Along
with everything else on the URL string - that's what the weird (.*) and $1
parts are for.), the report server will generate the report, and return it
to the Apache server (Thinking that the Apache server is just another web
browser), then Apache will send the report to the actual user, making it
look as if it came from the Apache server.
It's all smoke and mirrors :)
"Bryan Avery" <b_avery@.yahoo.com> wrote in message
news:1127394777.321200.26010@.g43g2000cwa.googlegroups.com...
> Can Reporting Servers run on Apache?
>|||Hi Bryan,
you might have some trouble getting support for running RS on apache
too.. ;-)
Peter|||I agree, it appears more hassle than it's worth.
--
Regards
<<<Bryan Avery>>
"Peter Nolan" wrote:
> Hi Bryan,
> you might have some trouble getting support for running RS on apache
> too.. ;-)
> Peter
>

Anyone using the SQL Server Reporting Services yet?

If anyone is using the SQL Server Reporting Services already, would you mind posting some comments about your experience with it?

Thanks,

~Bradabout damn time :P

It was a simple install ... very easy to work with once i figured out where the darn designer was hiding.. I think there needs to be better information on where to start the designer up. I spent a few hours looking through documentation. I have to say it will be nice to finally get away from using CR.NET or Access for a reporting solution.

Adminstration aspect is fairly straight forward as long as you read carefully before attempting to expose a report to the public.

Morgan|||i just ordered my copy...waiting to get my hands on it. we have huge reporting needs...not a lot of traffic but lot of different types of reports... :)|||I was using it in beta version & going to use final. My 1st impression could be found onhttp://www.aspalliance.com/339|||How about performance? Does it place a ton of additional load while the reports are running?

thx
bk|||"For eyes" not more than CR reports, but I didn't measure it.|||Just thought I'd post this article I came across at SQLServerCentral (note: you have to have a free membership to read the articles there, and I am in no way affiliatd with the site):Reporting Services Part I: Overview, Architecture and Installation by James Luetkehoelter. He will be following up that article with "Reporting Services Part II: Report Design".

Terri|||Its really bad that SQL Server reporting services needs VS.NET 2003 and not VS.NET 2002...
Is there a work around...|||You need VS.NET 2003 in order to use the Report Designer. The Report Designer is a friendly GUI that creates an report definition file in XML format. You could code this XML file by hand, you could look for a third party's GUI, or you could write one yourself if you are so inclined.

Terri