[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Reporting Services -- custom email headers

Posted on 2006-11-06
7
Medium Priority
?
273 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

872 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