dbaSQL
asked on
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?
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?
ASKER
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?
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,get date()) = 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_NUMB ER.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.
Then create a new dataset GET_RUN_NUMBER:
SELECT count(*) as RUN_NUMBER from ExecutionLog
WHERE ReportID = <yourReportID>
and datediff(day,TimeStart,get
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_NUMB
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
a cpl things in front of it right now, but it will be today, i will let you know. thanks very much
ASKER
excellent hillwaaa. works perfectly. and...very easy.
i don't know why i hadn't thought about that.
thanks very much
i don't know why i hadn't thought about that.
thanks very much
ASKER
option 2 is what i did (multiple subscriptions....custom header where needed)
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.