September 10, 2008 at 4:47 pm
I developed a SSRS report that we only want to see if the report contains data. The report has been deployed to Report Manager and is currently scheduled to run at the beginning of each QTR. Currently we are going to receive a email whether the report has data or not.
September 10, 2008 at 7:16 pm
Not sure if it can be done.
My question to you is if you are only running it once a quarter, why do you care if you get an email. That is only 4 emails, and I would personally want one just so I know that it was actually run whether it has data or not.
September 11, 2008 at 4:45 am
If you set up a data driven subscription then the query that returns the email information can perform a check to determine whether report data exists or not. If it does, return email info, if not return nothing and no email will be sent.
September 11, 2008 at 8:42 am
Thanks for your timely answers. My organization just started using SSRS and Report Manager. We are newbies. This report offered a opportunity to learn how to create a data driven email. 🙂
This SQL reflects whether we have an error that needs correction:
select Count(*) cnt
from eis_prod.dbo.WCS_POLICY_PD_CLASS_COVERAGE_AGG a
inner join eis_prod.dbo.wcs_policy_period wpp
on wpp.plcy_pd_id = a.plcy_pd_id
inner join eis_prod.dbo.eis_day d
on d.bds_day_id = exp_dt_id
inner join eis_prod.dbo.wcs_job_class b
on a.jcl_id = b.jcl_id
inner join eis_prod.dbo.bds_cutoff_date bcd
on data_catg_cd = 'BOBP'
LEFT outer join eis_common.dbo.EIS_JCL_YR ejy
ON d.day_dt -1 between ejy.EVAL_BEG_DT and ejy.EVAL_END_DT
AND ejy.JCL_CD = b.jcl_cd
where d.day_dt -1 between dateadd(year,-1,dateadd(day,1,bcd.cutoff_dt)) and bcd.cutoff_dt
and MNL_CLS_PD_PREM_AMT > 0
and ejy.jcl_cd is null
Step 4 of the data driven subscription offers many options. I am not sure how they are used. Which option do I use to check the value CNT I get back?
September 11, 2008 at 9:07 am
Ok so you'd then test the value of count in your sp or query and if you want to email then return a table with any report parameter information and some or all of the following email information:
TO, CC, BCC, ReplyTo, IncludeReport, RenderFormat, Priority, Subject, Comment, IncludeLink, SendEmailToUserAlias.
Then you can match these fields to the settings in step 4.
September 12, 2008 at 3:24 pm
Thanks. I got it to work once I understood the evaluation limitations of Step 4.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply