?
Solved

Reporting Services -- custom email headers

Posted on 2006-11-06
7
Medium Priority
?
264 Views
Last Modified: 2012-08-14
sql/rs v2k, vs .net 2003 -- i have this one report, working just fine, but, it has a custom schedule.  i created one subscription, then added to three more schedule times for the associated sql agent job.  so, one subscription, the job runs 4 times throughout the day, each sending it's own variation of the email

does anybody know if it's possible at all, to head two of those emails reports with a custom message?
like 'this is job one, three more coming...'
that nature?
ok, not actually that statement, but i think maybe you know what i mean

can i do this at all?
0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17885319
Hi dbaSQL,

I don't know of any way to do this from the schedule (not to say that there isn't though) but I'm thinking that the easiest way to achieve this would be to add the custom message to the report - making it conditional depending on the run number.

The run number you could work out in a couple of ways (there's probably more) -

(1) by hard-coding into the report the times that it is run
(2) by looking into the ExecutionLog table (assuming that it only runs on a schedule and isn't run by users as well)

Let me know if this won't work for your needs, or if you would like any more detail.

Cheers,
Hillwaaa.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17888588
Oh Hillwaaa, I should have prefaced my inquiry by saying I am a bit of an RS newbie.  I'm getting there...I've got a lot up and running...but still, it takes me some time to get there.  That said, I'm not sure how to approach your suggestion.  Can you provide any other direction?
0
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 17894284
What I was thinking was to create a new textbox at the top of the report, and add the appropriate text for run 2 into it.

Then create a new dataset GET_RUN_NUMBER:

SELECT count(*) as RUN_NUMBER from ExecutionLog
WHERE ReportID = <yourReportID>
and datediff(day,TimeStart,getdate()) = 0

This will return your run number for each day.

Then within the visibility property of the text box, add an expression:

=IIF(First(Fields!RUN_NUMBER.Value, "GET_RUN_NUMBER") = 2, True, False)

This will set the textbox to display when the report is run for the second time that day.

Let me know if you have any problems.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 16

Accepted Solution

by:
Hillwaaa earned 1200 total points
ID: 17894289
Actually - I just thought of a potentially easier way.  

In RS2005 you can modify the text for the subject - would it be easier to create 4 subscriptions an put the extra text into the subject line for the appropriate two?

It's a little messier to maintain I know - but probably the simplest solution (assuming that you can do this in rs2K).

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17897256
ok....lemme give this a shot, hillwaaa
a cpl things in front of it right now, but it will be today, i will let you know.  thanks very much
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17898406
excellent hillwaaa.  works perfectly.  and...very easy.
i don't know why i hadn't thought about that.
thanks very much
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17898413
option 2 is what i did  (multiple subscriptions....custom header where needed)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question