Dynamic Report Subscriptions

I'm using Reporting Services 2003 and I'm trying to set up a Subscription
whereby the report is kicked off and sent on email when and only when data is
updated on the server. Any suggestions?Chad,
I believe you may be able to do this using data driven subscriptions.
See:
http://msdn2.microsoft.com/en-us/library/ms169673.aspx
o
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_92ex.asp?frame=true
p.s. I am assuming you meant Reporting Services 2005!
regards
Wozza
"Chad Webber" wrote:
> I'm using Reporting Services 2003 and I'm trying to set up a Subscription
> whereby the report is kicked off and sent on email when and only when data is
> updated on the server. Any suggestions?|||I did mean 2005 and thanks, the information did help. I was able to get my
subscription set up and working the way I wanted.
"Wozza" wrote:
> Chad,
> I believe you may be able to do this using data driven subscriptions.
> See:
> http://msdn2.microsoft.com/en-us/library/ms169673.aspx
> or
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_92ex.asp?frame=true
> p.s. I am assuming you meant Reporting Services 2005!
> regards
> Wozza
> "Chad Webber" wrote:
> > I'm using Reporting Services 2003 and I'm trying to set up a Subscription
> > whereby the report is kicked off and sent on email when and only when data is
> > updated on the server. Any suggestions?

Dynamic Email Attachment Name

I have a report subscription set up to email a report via a Data
Driven Subscription. I have been asked to make the file name unique
with a date stamp in the email message. I do not see the FileName
field as one of the options in the email delivery. I know that I can
do this via the FileShare extension, but this report is going to an
outside company that does not have access to our file share.
Is there any way to dynamically set the file name in the email
attachment?On Dec 14, 5:06 pm, dachrist <dachris...@.gmail.com> wrote:
> I have a report subscription set up to email a report via a Data
> Driven Subscription. I have been asked to make the file name unique
> with a date stamp in the email message. I do not see the FileName
> field as one of the options in the email delivery. I know that I can
> do this via the FileShare extension, but this report is going to an
> outside company that does not have access to our file share.
> Is there any way to dynamically set the file name in the email
> attachment?
Probably the best option you have for emailing a report with a dynamic
naming convention is to use the Reporting Services Web Service (http://
msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to call, export and dynamically name the report. Then use the built in
functionality of the .NET framework via a custom ASP.NET/Exe
application to email the reports (using System.Net.Mail). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Dynamic Connectionstring and Email delivery

Hi,
Im new to reporting services.
My requirement is to develop only one set of reports and to pass the
connectionstring dynamically based on the user logs in.I used the
setdatasourcecontents API as it overwrites the connectionstring and Im
passing it just before the retrieval of the reports.Can anyone suggest me
whether it is the best way or is there any other way to acheive this.
Also the user will have an option of subscribing to a report and he can
specify a scheduled time for that.As the report runs just before the delivery
and Im Overwriting the datasource the mail will be sent with wrong report
data.So,please help me in solving this.
Im using standard edition.
Thanks..What are you trying to accomplish with this? Is it a matter of several
databases on the same server and based on the user you are allowing them to
see data from that database?
What you are doing is very problematic (as you have seen) but there might be
some other solution for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> Hi,
> Im new to reporting services.
> My requirement is to develop only one set of reports and to pass the
> connectionstring dynamically based on the user logs in.I used the
> setdatasourcecontents API as it overwrites the connectionstring and Im
> passing it just before the retrieval of the reports.Can anyone suggest me
> whether it is the best way or is there any other way to acheive this.
> Also the user will have an option of subscribing to a report and he can
> specify a scheduled time for that.As the report runs just before the
> delivery
> and Im Overwriting the datasource the mail will be sent with wrong report
> data.So,please help me in solving this.
>
> Im using standard edition.
> Thanks..
>|||Hello Bruce,
Thanks for your relply,I have seen some articles and custom data extension
might be a solution for this,and I don't know how to implement this and
whether it solves our email issue.
Definitely "Setdatasourcecontents" is not a solution as it applies changes
globally.
Our client have different companies wherein the database structure is
same.So I can create the reports for one company and pass the connection
string based on the employee login,so that he can view the reports specific
to his company.
Also when an employee subscribes for a report,how to pass the connection
string at the time of delivery of the report so that he receives his
company's report data.
"Bruce L-C [MVP]" wrote:
> What are you trying to accomplish with this? Is it a matter of several
> databases on the same server and based on the user you are allowing them to
> see data from that database?
> What you are doing is very problematic (as you have seen) but there might be
> some other solution for you.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "GK" <GK@.discussions.microsoft.com> wrote in message
> news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > Hi,
> > Im new to reporting services.
> > My requirement is to develop only one set of reports and to pass the
> > connectionstring dynamically based on the user logs in.I used the
> > setdatasourcecontents API as it overwrites the connectionstring and Im
> > passing it just before the retrieval of the reports.Can anyone suggest me
> > whether it is the best way or is there any other way to acheive this.
> > Also the user will have an option of subscribing to a report and he can
> > specify a scheduled time for that.As the report runs just before the
> > delivery
> > and Im Overwriting the datasource the mail will be sent with wrong report
> > data.So,please help me in solving this.
> >
> >
> > Im using standard edition.
> >
> > Thanks..
> >
> >
>
>|||There are several solutions for this with one exception. The problem is with
subscriptions. There is the User!userid global variable which will work for
everything but a subscription. The subscription will not know who to run as.
I have trouble seeing how to get around that even for a custom data
extension.
Leaving aside subscriptions you can solve the problem without a custom data
extension. Have a table that maps userid to database. Could also use dynamic
sql in Reporting Services but it would be cleaner with Stored Procedures.
Pass the User!Userid to the SP. Look up the database to use and then
assemble your SQL string to use it and execute it. This is clean and cannot
be modified by the user. It does not, however deal with subscriptions. I
don't have a good solution for you for that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GK" <GK@.discussions.microsoft.com> wrote in message
news:F76DFED5-42A9-4351-9284-E82370257FCB@.microsoft.com...
> Hello Bruce,
> Thanks for your relply,I have seen some articles and custom data extension
> might be a solution for this,and I don't know how to implement this and
> whether it solves our email issue.
> Definitely "Setdatasourcecontents" is not a solution as it applies changes
> globally.
> Our client have different companies wherein the database structure is
> same.So I can create the reports for one company and pass the connection
> string based on the employee login,so that he can view the reports
specific
> to his company.
> Also when an employee subscribes for a report,how to pass the connection
> string at the time of delivery of the report so that he receives his
> company's report data.
>
> "Bruce L-C [MVP]" wrote:
> > What are you trying to accomplish with this? Is it a matter of several
> > databases on the same server and based on the user you are allowing them
to
> > see data from that database?
> >
> > What you are doing is very problematic (as you have seen) but there
might be
> > some other solution for you.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> >
> > "GK" <GK@.discussions.microsoft.com> wrote in message
> > news:45299590-B2F5-4FEA-9544-9F92EE2EF028@.microsoft.com...
> > > Hi,
> > > Im new to reporting services.
> > > My requirement is to develop only one set of reports and to pass the
> > > connectionstring dynamically based on the user logs in.I used the
> > > setdatasourcecontents API as it overwrites the connectionstring and Im
> > > passing it just before the retrieval of the reports.Can anyone suggest
me
> > > whether it is the best way or is there any other way to acheive this.
> > > Also the user will have an option of subscribing to a report and he
can
> > > specify a scheduled time for that.As the report runs just before the
> > > delivery
> > > and Im Overwriting the datasource the mail will be sent with wrong
report
> > > data.So,please help me in solving this.
> > >
> > >
> > > Im using standard edition.
> > >
> > > Thanks..
> > >
> > >
> >
> >
> >

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."