Reporting Services -- custom email headers

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?
LVL 17
dbaSQLAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
HillwaaaConnect With a Mentor Commented:
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
 
HillwaaaCommented:
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
 
dbaSQLAuthor Commented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
HillwaaaCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
excellent hillwaaa.  works perfectly.  and...very easy.
i don't know why i hadn't thought about that.
thanks very much
0
 
dbaSQLAuthor Commented:
option 2 is what i did  (multiple subscriptions....custom header where needed)
0
All Courses

From novice to tech pro — start learning today.