Data Driven Report Export

#1

Hi,

I have a report which I would like to export when the data is updated (currently it is scheduled but sometimes goes out with old data). I was considering using a data driven notification for this but it doesn’t appear to be supported for Reports.

I was wondering if it might be possible to set up a dashboard visualization using the same data cube that the report is built from, set up a data driven notification to fire when the data changes and have this trigger the export of the report? But I don’t know how to go about picking up notification occurrences? Any ideas would be appreciated!

Kind regards,
Shane

#2

Hi @shane.o.sullivan,

There’s isn’t really a mechanism to ‘detect new data’ but there are a couple of techniques that you could try.

  • If you know when your data is being updated, simply sync the notification to be a reasonable time after the loading of new data. This of course assumes that you know when you’re data is updating.

  • If data is not being removed from your database as part of the load process, you could try to create a notification based the record count in a specific table. (Data increasing condition)

  • You can always create a metric set that is looking for something specific like a number being greater than 0 (or a count) for a specific date token. (maybe last hour?). Create a notification based on the number being greater than 0.

  • Not my favorite idea on this list but you can also modify an existing notification via a script and force it to run when you’re ready. Basically edit an existing notification and change the next run date to be something in the very near future to trigger it. You’d still need some kind of condition for your script to know to fire though…

Hopefully this gives you some ideas.

#3

Hi Jeff,

Thanks for looking at this,

Unfortunately the time in which the data is updated fluctuates. As an interim I’ve moved the daily process that updates the underlying table in our data warehouse to being earlier and scheduled the report later however due it being considered as going out late and if the data warehouse process falls over the report may go out with old data.

I like the sound of creating a conditional notification by looking at a metric set or table however I haven’t come across any examples of scripts for this. Do you have any lying around?

#4

Hi @shane.o.sullivan,

I don’t have a script sample handy but the concept that I use in this video should help you figure out how to get it. (Network trace to API mapping idea)

#5

Dont get me wrong here Dundas is a great tool.
Yes it is a Swiss army knife / Multi tool but that doesn’t mean it can do everything.
SQL servers (at least Microsoft) can send email and you can create data checking scripts that run via jobs and send email based on a condition. I know you want the report to be emailed but from just inside SQL you can do an HTML email and include a link to the report.

Some else I would try is to write a scripted that changes the value of a column in a table (made just for being monitored by a Dundas data driven notification for a dashboard) to increase the value of that Integer column. that way Dundas will see the increase and fire off a notification. I think it is possible to embed a report in a dashboard. or is it the other way around, i am looking in help now hold on…i might have that backwards.
so I would go with emailing form SQL server with a link to the report.

You can also do all this in C# and possible even get the report in the email because of Dundas open API calls that can be used in C#. This is a lot more technical and complex solution.

#6

Thanks Jeff, I’ll see what I can do with it! :grinning:

#7

Hi James, thanks for the Advice. We do use SQL to schedule HTML emails however I was hoping to take advantage of Dundas’s ability to nest groups, apply rags etc (the report has a lot going on!). Emailing a link to the report could be a great alternative however the report may still not have up to data when the link goes out,

You can embed a report but you can’t use a data driven notification on embedded views. I suppose what I was hoping for is some means of triggering the job through SQL server agent or a db trigger of some kind.

image