Solved

Reporting Services -- custom email headers

Posted on 2006-11-06
7
251 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 16

Accepted Solution

by:
Hillwaaa earned 300 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tableau dashboard drill-though 2 3,978
Sum Distinct Records not duplicate Records 6 171
Update mapping table using VBA 3 80
Conditionally Hide SSRS Gauge Pointer 2 64
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…
How to increase the row limit in Jasper Server.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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