Embeded case in where clause - causing problems.

Hi.
I have a sp that allows the user to search based on a variable set of parame
ters for example just a home phone or just a buss. phone however when search
ing by last name the user also has to supply the ZIP
optionally he can filter that search by first name or address. I am trying t
o accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does not
work - I am sure that are fields with those values in the db
(note: everying worked as expected before I added the embeded cases (and wil
dcard stuff))
the following is the SP - (sorry for the long snippet)
ALTER procedure GetDupCheckResults
(
@.HomePhone varchar(50),
@.BussPhone varchar(50),
@.Email varchar(50),
@.LastName varchar(50),
@.FirstName varchar(50),
@.Address varchar(50),
@.FirmZip varchar(50),
@.PersonZip varchar(50),
@.FirmName varchar(50)
)
as
begin
SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype +
' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
FROM
(
select v_nmf_naf_npf_linked.* from
v_nmf_naf_npf_linked inner join
(
Select distinct npfseq from
(
select
npfseq,'HomePhone' FoundSource
from
v_nmf_naf_npf_Linked
where
npfArea + '-' + NPFPhone = @.HomePhone
and
naftype = 'HOME'
and
(
@.HomePhone <> ''
and
@.HomePhone <> '%%'
)
union
select
npfseq,'BussPhone'
from
v_nmf_naf_npf_Linked
where
npfArea+'-'+NPFPhone = @.BussPhone
and
naftype = 'BF'
and
@.BussPhone <> ''
union
select
npfseq,'NameAddress'
from
V_nmf_naf_npf_Linked
where
@.PersonZIP like nafzip
and
@.LastName like nmflast
and
-- optionally allow filter on address and last name
1 =
Case @.Address
When '' then 1
When '%%' then 1 -- when wild cards are in use
Else
(
Case
when @.Address like nafAdd1 then 1
when @.Address like nafAdd2 then 1
else
0
end
)
end
and
1 =
Case @.LastName
When '' then 1
When '%%' then 1
Else
(
Case
when @.Lastname like nmfLast then 1
else 0
end
)
end
and not
(
(@.LastName = '' or @.PersonZip = '')
or
(@.LastName = '%%' or @.PersonZip = '%%')
)
union
select
npfseq,'Company'
from
V_nmf_naf_npf_Linked
where
nafZip like @.FirmZip
and
nafCompany like @.FirmName
and not @.FirmName = ''
and not @.FirmZip = ''
and not @.FirmName = '%%'
and not @.FirmZip = '%%'
)Temptab
)
unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
) dd
end
thank you for slogging thru it.You are sure you have people with the last name '%s%' and zip '%1%'?
What country is this?
Most likely you don't have such data in your database, but that is exactly
what this code will try to find, since your condition (on last name, for
example) is
@.Lastname like nmfLast
While this stored procedure looks far more complicated than it needs to
be, my guess is that you want
nmfLast LIKE @.Lastname
instead of the other way around.
Steve Kass
Drew University
Madler wrote:

>Hi.
>I have a sp that allows the user to search based on a variable set of param
eters for example just a home phone or just a buss. phone however when searc
hing by last name the user also has to supply the ZIP
>optionally he can filter that search by first name or address. I am trying
to accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
>For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does no
t work - I am sure that are fields with those values in the db
>(note: everying worked as expected before I added the embeded cases (and wi
ldcard stuff))
>the following is the SP - (sorry for the long snippet)
>ALTER procedure GetDupCheckResults
>(
> @.HomePhone varchar(50),
> @.BussPhone varchar(50),
> @.Email varchar(50),
> @.LastName varchar(50),
> @.FirstName varchar(50),
> @.Address varchar(50),
> @.FirmZip varchar(50),
> @.PersonZip varchar(50),
> @.FirmName varchar(50)
> )
>as
>begin
>SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype
+ ' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
>FROM
>(
>select v_nmf_naf_npf_linked.* from
> v_nmf_naf_npf_linked inner join
> (
> Select distinct npfseq from
> (
> select
> npfseq,'HomePhone' FoundSource
> from
> v_nmf_naf_npf_Linked
> where
> npfArea + '-' + NPFPhone = @.HomePhone
> and
> naftype = 'HOME'
> and
> (
> @.HomePhone <> ''
> and
> @.HomePhone <> '%%'
> )
> union
> select
> npfseq,'BussPhone'
> from
> v_nmf_naf_npf_Linked
> where
> npfArea+'-'+NPFPhone = @.BussPhone
> and
> naftype = 'BF'
> and
> @.BussPhone <> ''
> union
> select
> npfseq,'NameAddress'
> from
> V_nmf_naf_npf_Linked
> where
> @.PersonZIP like nafzip
> and
> @.LastName like nmflast
> and
> -- optionally allow filter on address and last name
> 1 =
> Case @.Address
> When '' then 1
> When '%%' then 1 -- when wild cards are in use
> Else
> (
> Case
> when @.Address like nafAdd1 then 1
> when @.Address like nafAdd2 then 1
> else
> 0
> end
> )
> end
> and
> 1 =
> Case @.LastName
> When '' then 1
> When '%%' then 1
> Else
> (
> Case
> when @.Lastname like nmfLast then 1
> else 0
> end
> )
> end
> and not
> (
> (@.LastName = '' or @.PersonZip = '')
> or
> (@.LastName = '%%' or @.PersonZip = '%%')
> )
>
> union
> select
> npfseq,'Company'
> from
> V_nmf_naf_npf_Linked
> where
> nafZip like @.FirmZip
> and
> nafCompany like @.FirmName
> and not @.FirmName = ''
> and not @.FirmZip = ''
> and not @.FirmName = '%%'
> and not @.FirmZip = '%%'
>
> )Temptab
> )
> unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
> ) dd
>end
>
>thank you for slogging thru it.
>
>

Embedding a reporting Services object

Hi,
Can I embedde a reporting services object in my web based application so the
clients can use the reporting seemlessly.
Thanks in advance,
BenjaminYes, you can. Take a look at the ReportViewer sample that Microsoft
includes.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BenJ" <BenJ@.netvu.com> wrote in message
news:O3XVHB4HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Can I embedde a reporting services object in my web based application so
> the
> clients can use the reporting seemlessly.
> Thanks in advance,
> Benjamin
>|||Check this example
http://www.rdlcomponents.com/ASPExamples/default.aspx
"Jeff A. Stucker" wrote:
> Yes, you can. Take a look at the ReportViewer sample that Microsoft
> includes.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BenJ" <BenJ@.netvu.com> wrote in message
> news:O3XVHB4HFHA.2456@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > Can I embedde a reporting services object in my web based application so
> > the
> > clients can use the reporting seemlessly.
> >
> > Thanks in advance,
> > Benjamin
> >
> >
>
>

Embedded images based on a boolean

I'm making a table, and based on a variable in a specific row, I want to display one of two embedded images, icon_good or icon_bad. I'm currently using IIf (Fields!IsCompleted.Value, icon_good, icon_bad) as BackgroundImage.Value, but it gives me errors, and the type field refuses to fill itself in no matter what. I've tried google searching and searching here on the forums, and I can find info about embedding images, but not how to selectively show them. Hopefully this is just a simple trick and I'll be able to get some quick help here.

Thanks in advance

Amdrew

Hi,
Did you try with "".
IIf (Fields!IsCompleted.Value, "icon_good", "icon_bad") as BackgroundImage.Value
Regards
Ayzan

Email subscriptions: changing "from" address for different subscriptions on same s

I doubt this is possible, but can someone think of a way to change the email address used for sending report subscriptions based on the report or subscription?

It's a need that I've heard from a number of different clients. Scenario: a company has one reporting services server with reports running from numerous departments. Report subscriptions are sent to internal and external email addresses and there's a business need to use different "from" addresses based on the report (or audience).

Unfortunately this isn't really possible without doing some messing around. You can make a call to the RS WMI provider to change the SenderEmailAddress value, but you would have to synchronize the code making the WMI calls with the subscription times. Definitely a hack :-(.

Email Subscription Problem

I have an install of Reporting Services which when trying to send an email based subscription, returns the following error;

Failure sending mail: Retrieving the COM class factory for component with CLSID {CD000001-8B95-11D1-82DB-00C04FB1625D} failed due to the following error: 80040154.

No errors are shown in the RS logs or the events logs.

Does anybody have any ideas what could be causing this?

I don't know what version you are using but when it first came out it was hard for most people to install it but I did not get a hard time because I ignored all Windows questions. So I remember it asking for Exchange server you will use with it, if you did not configure Exchange, SMTP or SQL Server Agent to use for your mail that could be the reason for the error. Hope this helps.|||Thanks for your reply. I've double-checked all the configuration and everything appears fine. :(sql

Email reports based on a condition

Hi,

Data is fed to our database from 10 different places. We massage the data and then send out reports via email with rs subscriptions.

Everything works fine except when at least one of the data feeds does not load properly on time. The reports go out but with uncomplete data generating undesired effect in management.

I can create a "Loaded" flag on the database. Is there a way to use this or other method to send out the reports based on a condition?

Thank you.

One more thing, we only have the standard edition of rs, not the enterprise edition where you can use data-driven subscription.

In the meantime, we thought of a possible solution: Create a status table and new dataset in rs. Based on the status of the day, hide or make visible detail and header rows of the report. The same idea apply to a text box stating the status of the loads.

|||

I did some research on that subject for SQL2000 RPS.

Peter Blackburn had a solution, here is his message as well as my comments.

It should still work with 2005

For the condition, you can set a step to test your "Status" table ad success only when your data is ready and fire the report job.

Have fun,

Philippe

-

Hi,

Here the trick is to use SP_Start_Job to start the report from a sp or a dts.

You can also use something like exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

in your sp or DTS.

As Peter says, this breaks when you edit the subscription schedule from ReportServer but still, you can do it and you can also set a test like

DECLARE @.JobID BINARY(16)

SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'EF3028CF-D22A-4EA7-B197-D9018D6BA262')
IF (@.JobID IS NULL)
BEGIN

-- send some sort of alert to the developer so he recreates the correct calls....

EXEC msdb.dbo.sp_send_dbmail

@.recipients = 'someone@.someplace.com',

@.body = 'The report BlahBlah EF3028CF-D22A-4EA7-B197-D9018D6BA262 could not run, it may have been updated on the report server, sorry.',

@.subject = 'Report could not run';

else

-- run the report

exec ReportServer.dbo.AddEvent @.EventType='TimedSubscription', @.EventData='6273f0b2-3899-416c-ada7-29c801a2c16f'

end

This is not optimal however this may be very handful for reports where you absolutely need it.

When you set your initial schedule in the past, set a time easy to find in the job list.

Phil

Subject: Re: Scheduling a Report based on an event

11/9/2004 10:38 PM PST

By:

Peter Blackburn (www.sqlreportingservice

In:

microsoft.public.sqlserver.reportingsvcs

Was this post helpful to you?

Sure this is real easy to do.Create a schedule that has completed in the past - so effectively it will never fire. Associate this schedule with a Report.Now what happens is that a SQL Agent Job is created - that maps to the schedule. You can run SQL Agent Jobs from the SQL Agent Management interface by hand - or you can cause that job to run through T-SQL.All that the SQL Agent Job does is create an entry in the Report Server's Event table at the scheduled time. The Report Server Windows Service is polling the Event table every 10 seconds or so - and if there are any events to process it gets on and processes them.So what you do is either include in your long running stored procedure a call that will create the required entry in the Event table directly - or a call that fires the SQL Agent Job.- One word of warning though if you start editing the schedule in the Report Manager, then the Report Manager can end up re-creating the SQL Agent Jobs - and you lose reference to the actual Job.However if you are disciplined enough then this approach works fine - (Schedule in the past, have your own process force the SQL Agent Job to run)Peter BlackburnHitchhiker's Guide to SQL Server 2000 Reporting Serviceshttp://www.sqlreportingservices.net"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message news:OWMxVAoxEHA.3080@.TK2MSFTNGP14.phx.gbl...> Is it possible to schedule a report based on a flag or stored procedure > completing. Currently have an overnight load process which must complete > before the report starts. Any suggestions would be most appreciated>> > Posted using Wimdows.net NntpNews Component -

|||

Thank you for your response.

We got the results we wanted by creating a load_status table. In my report I created a new dataset that looks up for the flag value on this table.

Then we condition the visibility of each row of the table based on this flag. We added another header row with the "sorry, not all data was loaded, report will be sent later" with the opposite condition and it works great.

Good luck!

Email Report in Outlook 2007 not the same as Outlook 2003

I have a Reporting Services 2005 report that is emailed to users. I created/formatted the report based on how it looked in Outlook 2003 which all of our users had at that time. The report is wide, but just fit in the email window when it was full size. Now I have a user who has been converted to Outlook 2007 and he emailed me a copy of the report he received in Outlook 2007 and it looks like it reduced the column sizes; it does not fill the entire email window and wraps the wording on most lines because the column sizes have been reduced.

The report is:

Delivered by: Report Server E-mail

Include Report is checked

Render Format is Web archive

Any ideas on how I can fix this?

Thanks in advance.

I still haven't found a fix for this, however, the user has found that opening the email message in a browser window displays it as expected. I wouldn't consider this a fix, unless all Outlook 2007 users are supposed to open all email messages in a browser window. If not, then it still would be nice to send an email report that is formatted correctly when the message is opened as a regular email message as it used to do in earlier Outlook versions.

|||

I am having the same issue.

Has anyone found a solution to this?

I have an embedded image in my header that is not showing either.

Any help would be greatly appreciated.

Thanks, Adrian

|||I am still waiting for a solution. My report does not have an embedded image so I can't help you there. If I ever find a fix, I will post it here.|||We are having the same issue. Just getting my name on this so when anyone updates it i'll get emailed. :-)|||Ditto for me!|||Also having this issue, and have been trying to hack around it for a while. This really should be fixed as a lot of reports in outlook 2007 become almost unreadable when the columns get squished to be so narrow.|||Yet another with the same problem.|||

What are the target platforms?

Xp with Outlook 2003 & IE6 is okay?

How about Xp with Outlook 2003 & IE7?

I don't think there is an easy fix for this problem as I reacall reading somewhere that Outlook 2007 uses Word2007, and not IE to render html emails.

|||

If you mean which platform correctly displays the reports, then that would be all users who are on XP or newer, with Outlook 2003 and at least IE6. So are you saying that SSRS 2005 reports cannot be displayed inside the email message without opening a browser OR that these reports need to be completely reformatted to display correctly in Outlook 2007? I guess I'm asking if readable reports can even be included in the text of an Outlook 2007 email message? As more users upgrade, this is becoming more of a problem.

|||

Outlook 2003 used IE to render html. Outlook 2007 chose to use Word to render html which does not honor the column widths we specify. Search the web for "outlook 2007 html" and you'll find this problem is definately not specific to Reporting Services.

The good news is there is a fix (http://support.microsoft.com/kb/935399) which is part of the latest SQL Server 2005 SP2 Cumulative Updates available from http://support.microsoft.com/kb/936305.

Thanks.

Email report

Hi Freinds,
SQL2005 beta,
I create a subscription and want to email report based on a schedule. It
doesn't seems working, no error either.
Where can I track it down? Which config files keep the mail SMTP
information?
Thanks,
PatLook in the windows service log file in the LogFiles folder.
The file rsreportserver.config contains the email delivery extension
configuration.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patrick" <patriarck@.gmail.com> wrote in message
news:OQwr7TeHFHA.2704@.tk2msftngp13.phx.gbl...
> Hi Freinds,
> SQL2005 beta,
> I create a subscription and want to email report based on a schedule. It
> doesn't seems working, no error either.
> Where can I track it down? Which config files keep the mail SMTP
> information?
> Thanks,
> Pat
>

email details from SQL Server agent based on query results?

Hello,

I want to remove records from the database automatically, BUT it is very important for me to know (email) if there were records or not and how many are affected by the automatic task...

My job is scheduled to run every day and does something like:

DELETE from RadioactiveMaterials

WHERE (NotExplosive = true)

SET @.rowcount_var = @.@.ROWCOUNT

I would like to get detail on the deleted records, how can I produce the email?

If you are using SQL 2005, using Books Online, refer to the Topic: 'Database Mail How-to Topics'.

If you are using SQL 2000, I suggest that you check out xp_smtp_mail from here. Most folks will recommend NOT to use MAPI mail on a SQL Server.

|||

Arnie Rowland wrote:

If you are using SQL 2005, using Books Online, refer to the Topic: 'Database Mail How-to Topics'.

Database Mail How-to Topics:

http://msdn2.microsoft.com/en-us/library/ms188298.aspx

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

E-mail custom reports to different users

Does anyone know whats the best way to automate the e-mailing of
reports (say at the end of every month) to a list of people based on a
certian criteria for each report.
We have different sales people and we want to automate thier sales
comission report that needs to be generated and e-mailed to them (at
the end of every month) . We use thier "sales id" to query the
datbase and compile the report, so every sales person will have a
dfferent report.
Is there any suggestions to do this in one process instead of running
individual reports and e-mailing it to them seperately.
TIA
ChuckData driven subscriptions are what you are looking for. Information can be
found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPORTAL/HTM/rs_gts_portal_3vqd.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <chuck@.yeshcom.com> wrote in message
news:a4000eeb.0409011253.4429edee@.posting.google.com...
> Does anyone know whats the best way to automate the e-mailing of
> reports (say at the end of every month) to a list of people based on a
> certian criteria for each report.
> We have different sales people and we want to automate thier sales
> comission report that needs to be generated and e-mailed to them (at
> the end of every month) . We use thier "sales id" to query the
> datbase and compile the report, so every sales person will have a
> dfferent report.
> Is there any suggestions to do this in one process instead of running
> individual reports and e-mailing it to them seperately.
> TIA
> Chuck

Email Column

Can we provide an outlook based email column so that as the email is
clicked. directly outlook opens with to mail column. so tha we can directly
send the email as they click corresponding column.
Navin.DHi,
Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
column
on the column u need the link... right click and select properties,->
Advanced
then give an expression for the jump to URL of the navigation tab...
it's real simple...
Chathuranga
NAVIN.D wrote:
> Can we provide an outlook based email column so that as the email is
> clicked. directly outlook opens with to mail column. so tha we can directly
> send the email as they click corresponding column.
> Navin.D|||There are some 10,000 records filled in directly from dbase for that email
column, can you please provide the expression.
"chathurangakw@.gmail.com" wrote:
> Hi,
> Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> column
> on the column u need the link... right click and select properties,->
> Advanced
> then give an expression for the jump to URL of the navigation tab...
> it's real simple...
> Chathuranga
>
> NAVIN.D wrote:
> > Can we provide an outlook based email column so that as the email is
> > clicked. directly outlook opens with to mail column. so tha we can directly
> > send the email as they click corresponding column.
> >
> > Navin.D
>|||Assuming the db field is email... The Action URL might be
="mailto:" & Fields!email.Value
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"NAVIN.D" wrote:
> There are some 10,000 records filled in directly from dbase for that email
> column, can you please provide the expression.
> "chathurangakw@.gmail.com" wrote:
> > Hi,
> >
> > Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> > column
> > on the column u need the link... right click and select properties,->
> > Advanced
> > then give an expression for the jump to URL of the navigation tab...
> >
> > it's real simple...
> >
> > Chathuranga
> >
> >
> > NAVIN.D wrote:
> > > Can we provide an outlook based email column so that as the email is
> > > clicked. directly outlook opens with to mail column. so tha we can directly
> > > send the email as they click corresponding column.
> > >
> > > Navin.D
> >
> >|||the properties for the currently selected item are not valid,please correct
all errors before contuning. Is the error i am getting when i use it
"Wayne Snyder" wrote:
> Assuming the db field is email... The Action URL might be
> ="mailto:" & Fields!email.Value
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NAVIN.D" wrote:
> > There are some 10,000 records filled in directly from dbase for that email
> > column, can you please provide the expression.
> >
> > "chathurangakw@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> > > column
> > > on the column u need the link... right click and select properties,->
> > > Advanced
> > > then give an expression for the jump to URL of the navigation tab...
> > >
> > > it's real simple...
> > >
> > > Chathuranga
> > >
> > >
> > > NAVIN.D wrote:
> > > > Can we provide an outlook based email column so that as the email is
> > > > clicked. directly outlook opens with to mail column. so tha we can directly
> > > > send the email as they click corresponding column.
> > > >
> > > > Navin.D
> > >
> > >|||the properties for the currently selected item are not valid,please correct
all errors before contuning. Is the error i am getting when i use it
Wayne Snyder" wrote:
> Assuming the db field is email... The Action URL might be
> ="mailto:" & Fields!email.Value
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "NAVIN.D" wrote:
> > There are some 10,000 records filled in directly from dbase for that email
> > column, can you please provide the expression.
> >
> > "chathurangakw@.gmail.com" wrote:
> >
> > > Hi,
> > >
> > > Why dont u have a link of "mailto:chathurangaw@.zone24x7.com " for that
> > > column
> > > on the column u need the link... right click and select properties,->
> > > Advanced
> > > then give an expression for the jump to URL of the navigation tab...
> > >
> > > it's real simple...
> > >
> > > Chathuranga
> > >
> > >
> > > NAVIN.D wrote:
> > > > Can we provide an outlook based email column so that as the email is
> > > > clicked. directly outlook opens with to mail column. so tha we can directly
> > > > send the email as they click corresponding column.
> > > >
> > > > Navin.D
> > >
> > >|||Wayne Snyder 's reply should be enough to get it working.... u might
be in a wrong place... I'm talking about the Navigation Tab of a
Properties Dialog-> advanced... for a TextBox|||or a TextBox inside a Table|||tried the same way but get the error as i mentioned
thank you
Navin.D
"chathurangakw@.gmail.com" wrote:
> or a TextBox inside a Table
>

Email based subscription - need to change the from address

I have multiple reports being sent to various clients. I need to change the
from address on each report based on report's category and client. Is there
anyway I can change the "from" attribute of email report? I checked the
RSServerConfig.xml, and looked the Email configuration section, but If I
change there its going to change for all the report, I want a different from
address for each of my report.
Please help.
vipul ShahThere is no way to change the from address per delivery. You can only
change it globally. You can however change the reply-to field.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vipul Shah" <VipulShah@.discussions.microsoft.com> wrote in message
news:3BA19CAC-2614-47AC-8A77-49ED8ACA5270@.microsoft.com...
>I have multiple reports being sent to various clients. I need to change
>the
> from address on each report based on report's category and client. Is
> there
> anyway I can change the "from" attribute of email report? I checked the
> RSServerConfig.xml, and looked the Email configuration section, but If I
> change there its going to change for all the report, I want a different
> from
> address for each of my report.
> Please help.
> vipul Shah|||That is very disappointing. I think it is very important feature to have in
reporting tool. Most of the report requirements would be for departmental
reports and each department would like to have their group email as a from
address. Is there anyway to have this feature in next release of reporting
services?
Also, how can I change "reply-to" address? Please advice.
Thanks
"Vipul Shah" wrote:
> I have multiple reports being sent to various clients. I need to change the
> from address on each report based on report's category and client. Is there
> anyway I can change the "from" attribute of email report? I checked the
> RSServerConfig.xml, and looked the Email configuration section, but If I
> change there its going to change for all the report, I want a different from
> address for each of my report.
> Please help.
> vipul Shah|||If you have Manage all subscription permission you will see the reply-to
field.
Changing the from clause is on the wish list already. Several customers
have asked for it.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Vipul Shah" <VipulShah@.discussions.microsoft.com> wrote in message
news:C0664333-BFD0-498E-B333-D264B61A6C57@.microsoft.com...
> That is very disappointing. I think it is very important feature to have
> in
> reporting tool. Most of the report requirements would be for departmental
> reports and each department would like to have their group email as a from
> address. Is there anyway to have this feature in next release of
> reporting
> services?
> Also, how can I change "reply-to" address? Please advice.
> Thanks
> "Vipul Shah" wrote:
>> I have multiple reports being sent to various clients. I need to change
>> the
>> from address on each report based on report's category and client. Is
>> there
>> anyway I can change the "from" attribute of email report? I checked the
>> RSServerConfig.xml, and looked the Email configuration section, but If I
>> change there its going to change for all the report, I want a different
>> from
>> address for each of my report.
>> Please help.
>> vipul Shah

ELSE IF Question

Maybe my brain is fried from staring at this too long but I have a question on how IF ELSE IF is read based on nesting.

Is it read like this:

IF

ELSE IF

ELSE IF

END

...or is it read like this?

IF

ELSE

IF

ELSE

IF

ELSE

END

Thanks,

Adamus

The second option.

And there is no END associated with an IF

|||

Thank you...

I simply forgot the BEGIN's and associated END's.

I get confused jumping from language to language. (especially late on Friday afternoon)

Adamus

efficency

i have 3 tables that are linked together and i would like to run a search based on criteria in each table.
my tables are
t_location which is the physical location info such as address and phone
t_provider the contact information such as first and last names
t_source the person who refered that person

the only way i know how to do this now would be to query the location table get the locationid and add it to a dataset then do a search with each location id individually and the provider criteria but then i would not know how to get it to a dataset or something

i have seen the join statements but i dont know for sure how they work, would it be possible to use a join statement or something so that i could return the results so that i could later add them to a table.

if this is possible could you give me the basic structure for the join command and if you use a join statement do you use the select command in the same statement or do you run a seperate statement?

thank you for everything that you can giveThere are several types of joins...
inner - only return rows where there is data in all tables
outer - return all the data from the primary table and any linked data from the lookup tables

Here's an example of an inner join using the Northwind database
Say you wanted to know, for each product, what city it was shipped to, how many, and when.

SELECT Products.ProductName, Orders.ShipCity, [Order Details].Quantity, Orders.ShippedDate
FROM Products
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
ORDER BY ProductName

This returns the following...

ProductName ShipCity Quantity ShippedDate
------------ ----- --- ----------------
Alice Mutton Strasbourg 30 1996-08-12 00:00:00.000
Alice Mutton Frankfurt a.M. 15 1996-08-16 00:00:00.000
Alice Mutton Albuquerque 15 1996-09-05 00:00:00.000
Alice Mutton Charleroi 40 1996-10-09 00:00:00.000
.
.
.

If theres a product in the database that hasn't shipped at all, it wont show up in the results at all.
If you want it in the list, you would do an outer join...

SELECT Products.ProductName, Orders.ShipCity, [Order Details].Quantity, Orders.ShippedDate
FROM Products
LEFT OUTER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
LEFT OUTER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
ORDER BY ProductName

In this case, the LEFT OUTER JOIN operator sells SQL that you want all the data from the table on the left and null for any fields in the table on the right that dont have a related record. Like so...

ProductName ShipCity Quantity ShippedDate
------------ ----- --- ----------------
Alice Mutton Strasbourg 30 1996-08-12 00:00:00.000
Alice Mutton Frankfurt a.M. 15 1996-08-16 00:00:00.000
Alice Mutton Albuquerque 15 1996-09-05 00:00:00.000
Alice Mutton Versailles 6 1998-03-26 00:00:00.000
.
.
.
Alice Mutton Rio de Janeiro 12 NULL
Alice Mutton Boise 77 1998-05-04 00:00:00.000
Aniseed Syrup London 30 1996-08-28 00:00:00.000
.
.
.

For your case, you might want to use an outer join since you would want to know if a given location is in the database but doesn't have any provider or source.
For example...

SELECT address, phone, first, last, refereanceperson
FROM t_location
LEFT OUTER JOIN t_provider on t_location.locationid=t_t_provider.locationid
LEFT OUTER JOIN t_source ON t_provider.sourceid=t_source.sourceid

|||thank you this will help out alot|||alright here is an sql statement i am using

SELECT t_Provider.ProviderID
FROM t_Location INNER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID INNER JOIN
 t_Source ON t_Provider.ProviderID = t_Source.ProviderID
WHERE (t_Location.StateID = 25)

and currently i have 2 rows in my location table with the stateID of 25 and 2 of the providers have the same location id so it should bring up 3

providerid location id
112 147
151 147
114 149

but it brings up three and did before i added the center record too
and their values are
112
114
112

why is it bringing up 2 of the same and why does it ignore when i add another record
by the way the location id is the one with the stateID column|||I'm confussed. Can you post the table contents?|||i changed it to left outer join and it got all of the fields but it still duplicates the 112 provider id
the table contents are

t_Provider

ProviderIDDoNotCallInProcessLocationIDProviderContactProviderEmailProviderFNameProviderLName
11200147Natasha SmithJosephStephens@.DentistPro.comJosephStephens
11300148JodiKbroadbridge@.TeNT.comKurtBroadbridge
11400149Casey HuntJon_nedry@.newparadox.comJonathanNedry
11500150CoorsABush@.HHD.comAnheiserBush
11600151Jonathan NedryDSpeed@.AMD.comDoctorSpeed
11700147Bob Stephenshowdy@.me.comMEToo
11800152Mr Wallaceashd@.ld.comGuySmiley

t_Location

LocationIDLocationAddress1LocationAddress2LocationCityLocationDoctorsLocationFaxLocationONameLocationPhoneLocationZipStateID
1474832 Dixie HwySuite 105Waterford1248-313-4038 Dentistry Professionals248-313-4039 48038 25
14894949 Sunshine DriveOthello1Teeth N' Toothpaste234-343-2343 54343 7
149421 East OtisHazel Park1New Paradox Cleaning248-543-1983 48909 25
150148392 Small Back Rd.St. Lous1Happy Hour Dental243-333-2222 83939 26
151123 Fastest CPU Ln.Extreme Performance1AMD Dental239-343-3234 39393 22
152125 Tanglewood Tr.Ortonville1Mr.Dental248-627-46813495025

t_Source

SourceIDAdminIDCompetitorIDGroupIDLeadSourceIDPatientProviderIDSourceDate
15361641123/4/2004
15415131133/4/2004
1551112Dan Guzek1143/4/2004
15615131153/4/2004
15731541163/4/2004
15814131123/4/2004

New Sql Satement

SELECT t_Provider.ProviderID
FROM t_Location LEFT OUTER JOIN
 t_Provider ON t_Location.LocationID = t_Provider.LocationID LEFT OUTER JOIN
 t_Source ON t_Provider.ProviderID = t_Source.ProviderID
WHERE (t_Location.LocationPhone <> '') AND (t_Location.StateID = 25)

New Returned Values

ProviderID
112
112
117
114
118

sorry if the tables dont come out correctly they are a bit bigger than the text field|||Ok it looks like it did this because i had two source records in the database which i would like to have anyway but would not like the providerto show up everytime there is a source for the provider is this possible?|||oh yeah and one more thing

1 to many
location to provider
provider to source
i dont know if this would help a solution at all|||I'm still confused on exactly what you want to get back.|||I would like it to only get back the providersID's that match ONLY once
but it seems to give me everything correctly except if there is more than one source
if there is more than one source for a provider id it returns the provider id more than once|||The providerIDs that match what?
Are you saying you want a list of the providerIDs that have only one source? Or do you want a list of the providerIDs along with their sources but only the first incident of each provider (i.e. provider 112 has two sources (153 and 158), so only return the 112/153 pair and filter out the 112/158 pair?|||in my search i searched for the state 25 and it came back with the provider id of 112 twice because there were two sources but it should not have done that because the source criteria isnt part of the search|||OK. Lets cover the basics of a join.
A join is a cross product.
For example, say you hve the following tables...

AlphaTable
AlphaID AlphaValue
--- ----
 1 A
 2 B
 3 C
PrimayTable
 ID Name AlphaID
--- --- ---
 1 Bill 2
 2 Steve 1
 3 John 3
 4 Bob 2

If you joined them on the AlphaID you would get...

 ID Name AlphaID AlphaValue
--- -- ---- ----
 1 Bill 2 B
 2 Steve 1 A
 3 John 3 C
 4 Bob 2 B

When you select from a join, you are basically selecting from a virtual table containing all the columns and all the joined data. So, if you select just the AlphaValue from this join, B is in there twice. Even though you are just selecting the AlphaValue, and B is only in the AlphaTable once, since you are selecting from the join, B is going to be in there twice.|||so basically then when you complete a join it is like having 3 seperate tables with all the same values where they are using the same records it makes duplicates
where the break occurs.
For instance
my table structure is as follows
location expands to providers expands to source

1 to many
1 to many
1 to many

so anything when searching on the provider table with present duplicates of the source table

so if i searched from the location table and i had 2 providers that used the same location i would have 2 duplicates and then each of those two providers had 2 sources
then i would have 4 duplicate Location ID's i believe if i did the math correctly?
| Location
/ \
/ \
/\ /\ Providers
/ \ / \Sources

if i am correct in assuming this from the facts that you have given me this is going to be a pain.

is there any way to limit the results to non duplicates in the sql command?|||You've got it exactly.
Yes, there are ways to limit the output, but these records are not "duplicates". Yes, the location will be listed multiple times if there are multiple providers at that location, but the rest of the result (i.e. the provider information) is different. The question to answer is "what information do you want?" If you are building a select that returns the location and provider (ignoring sources for the moment), and a given location has two providers, do you want to show the location and the first provider for that location, and filter out all the other providers at that location? Or do you want to show each location and a concatenated string of all the providers at that location? or something else?

Effective Date selection in SQL

Problem: Selecting the most appropriate row based upon the row's effective date (a user defined field of the row) verses the current date and time.

Platform: Microsoft SQL Server 2000

Details:
In a table of products, each record is a product, or a version of a product. These products or versions of products are "Effective Dated", meaning, a row is activated by the date it becomes effective by (do not be confused by an active row, and an active product - active row is the currently effective row, an active product is a flag set by the user as to define whether the product is active when it's effective date is reached). Example:

Product 1 (version 1)
Effective Date: 6/1/2003 12:00:00 AM
Price: $30
Status: Active

Product 1 (version 2)
Effective Date: 7/11/2003 12:00:00 AM
Price: $20
Status: Active

Product 1 (version 3)
Effective Date: 12/25/2003 12:00:00 AM
Price: $15
Status: Active

Product 1 (version 4)
Effective Date: 1/1/2004 12:00:00 AM
Status: Inactive

In the above case, this SINGLE product has four records in the product table representing different versions. Product 1 version 1 has expired since Product 1 version 2 is effective today (7/11). Product 1 version 3 and version 4, however, have not come about yet due to their effective dating in the future. Product versions 1 through 3 are all active, and the product is no longer available after 1/1/2004, when version 4 becomes effective and the status changes to "Inactive".

The problem I'm experiencing with my SQL is that I cannot differentiate between the version effective currently and the future versions.

My current SQL looks as such:

SELECT sProdName
FROM tblProducts
WHERE NOT EXISTS
(
SELECT P.cProdCode, P.dtEffDate
FROM tblProducts AS P
WHERE
tblProducts.cProdCode = P.cProdCode AND
tblProducts.dtEffDate < P.dtEffDate
)
AND bActive = 1
AND cProdCode = 'somecode'
ORDER BY sProdName

In the above SQL, bActive is a flag that declares this product as an active product (verses inactive) and cProdCode is a code that all versions of the Product share. Ex:

iID: 1
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 6/1/2003 12:00:00 AM

iID: 2
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 7/11/2003 12:00:00 AM

iID: 3
sProdName: Product 1
cProdCode: PROD1
bActive: 0
dtEffDate: 1/1/2004 12:00:00 AM

The above SQL is effective in ignoring the expired versions of the product, but returns the current and future versions. I've attempted to massage the SQL to ignore the expired and future versions (leaving me with the single, currently active, version).

Ideally, I'd like to do this without having to create flags for the records that deam them as past, present, future, or having to export versions out into an archive table. This should be able to be determined by strictly SQL, but haven't been able to get the data to return as I'd like it..

In the end, the final result would be a single record of the version of the product which is currently effective, and alertnately, the ability to retrieve a set of records listing all of the currently effective active products.

Any ideas?Now, I should mention that I have a version of this SQL that works, but I suppose my ultimate question is "Is this the most efficient and proper way to do this or is there a better way?"

Here is the SQL that does work, but it nags at me with the use of TOP and ORDER BY. It doesn't seem as though this is the proper elegant solution:

SELECT TOP 1 *
FROM tblProducts AS P
WHERE
P.iProdID IN
(
SELECT iProdID
FROM tblProducts P2
WHERE
P2.dtEffDate < GETDATE()
)
AND P.bActive = 1
AND P.cProdCode = 'somecode'
ORDER BY P.dtEffDAte DESC

If I do not use TOP, it returns every version of a product except the future versions. If I do not use ORDER BY it returns them in an order inconsistant with their effective date. So combined together, the records are sorted most current->least current, then chopped at the top for the most current effective dated product.

Now, this comes into play heavily when we start talking about retrieving the most recent version of all of the products. Sure the above works when getting the most current version of a single product, but (due to the "TOP") does not work properly when attempting to retrieve the most current version of all active products. The above SQL's "TOP" directive is in there to drop off the expired version of the product, and in this case, TOP drops off more then just expired versions of products.. it drops everything except the lucky record at the top of the set.|||i really don't understand your various definitions of active, inactive, flags, effective dates, etc.

your design sounds fairly complicated

however, i did pick up on one thing

you said your sql works, but only for one product at a time, and "does not work properly when attempting to retrieve the most current version of all active products"

okay, i think i can translate your sql so it will work for all products:
select *
from tblProducts AS P
where dtEffDate =
( select max(dtEffDate)
from tblProducts
where cProdCode = P.cProdCode
and dtEffDate < GETDATE()
and bActive = 1
)
and bActive = 1 this is a correlated subquery so it picks the highest date in each group, where the group is defined as all product rows with the same cProdCode (the P inside the subquery is the correlation variable)

let me know if it works

rudy
http://r937.com/|||Hi Rudy,

I apologize for the complexity of the project, but I think you nailed the solution. I've done some cursory testing, and just looking over the SQL, it looks right on. Tonight I'll take some time to do some more testing, but it looks good. I hadn't even thought of using the MAX function. Thanks for your assistance, I appreciate you taking the time to go through all my details (even if they were a bit long and confusing) and coming up with a solid solution.|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!