Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reporting Services -- custom email headers

Posted on 2006-11-06
7
Medium Priority
?
270 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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