Manual / Conditional email report from reporting services
-
I've got a bit of a problem. Our company has consolidating reports, and intranet servers. One of the reports that is used quite a bit is a report delivered to warehouse management, and executives detailing orders that have been printed on a given day.
I'd origionally written this report in ASP using an ASP graphing component. The SQL has been optimized considerably, but, the method of generating graphs that I used was a major slow-down (took 15-20 seconds to generate). I've rewritten the report using reporting services and 1 query instead of 4. The report now generates in less then a second, and I'd like to depoly the report, BUT, here's where our problems start.
Our sending practices for the report are to send every time we finish a print run. There are some cases where I could schedule for these, but, for exception runs, and the like, there is no way to set up a strict schedule of when to send the report.
So far, we've been able to thin of two options.
1) use a piece of data or flag to feed to the generating stored procedure, and cause the report to fail if the 'send' flag isn't set. Then set up a schedule for the report to run every 5-10 min.
2) create a seperate application to scrape the report from reporting services, and then e-mail the scraped results.
I don't really like either of these options. It just feels like there should be an option in reporting services to manage this type of situation.
Any ideas?
thanks,
Kevin
-
This was removed by the editor as SPAM
-
A bit more information. I think I've figured out how to do this... except one small problem. My thought was to use a data driven subscription, and a query to return a list of people to send to if it should be sent, or blank if it shouldn't, and used logic similar to this :
set nocount on
declare @date varchar(8)
declare @time varchar(6)
declare @dynSQL varchar(4000)
declare @QTY int
create table #QTYTemp (QTY int)
set @Date = convert(varchar,convert(int,convert(varchar,getdate(),112))-19000000)
set @time = replace(convert(varchar, dateadd(minute,-15,getdate()), 108), ':', '')
set @QTY = 0
set @dynSQL = '
insert into
#QTYTEMP
select
QTY
from
OPENROWSET(
''MSDASQL'',
''DSN=*****;UID=******;PWD=*****;'',
''SELECT
count(*) QTY
FROM
Table t1
WHERE
t1.date = ' + @Date + ' and
t1.time >= ' + @Time + '
''
 
'
exec(@dynSQL)
select @QTY = QTY from #QTYTEMP
drop table #QTYTEMP
select
ToUsers = case @QTY
when 0 then ''
else 'distributionlist@ourcompany.com'
end
end
The stored procedure works great on SQL server, but reporting services complains about the temporary table. The only other option would be to use a linked server, but we've found that the ODBC driver we use to connect to our AS400 can be a bit buggy when it comes to SQL server linked servers (it's prone to crash after a number of queries use it).
As far as I know, I don't have a case type function available on the version of DB2 we are using, or I could proably work around this.
anyone have any ideas?
-
I just answered my own question... All I needed to do was create a permanent table instead of a temporary table...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply