Setup:

  1. Make sure SQL Server Reporting Services is installed and running. Check that you can access and run reports in Report Manager (http://localhost/Reports)
  2. Download and unzip the Binaries from codeplex. You should unzip them to a location convenient for you to access (to open the gui) and which is accessible from the ETL Jobs (calls to the console app).
  3. (Optional) Create a new database to host the BusyReport tables (4 tables, small). You can also just add the tables to an existing db NewDb.png
  4. Execute the SQL Script found in the downloaded files ExecuteScript.png
  5. Adapt the .config files. BusyReportsConsole.exe.config and BusyReportsGui.exe.config are the same files, edit the settings in both
    1. in <connectionStrings>, look for the BusyReportsEntities line, adapt the data source and initial catalog
    2. in <applicationSettings> adapt the server name for "BusyReportsReportingServiceReportingService2010" and "BusyReportsReportExecutionReportExecutionService"
    3. see the config file settings section lower for explanation about other settings ConfigFiles.png
  6. open the GUI (BusyReportsGui.exe) and create a new Subscription. To edit the settings click the Edit button. NewSubscription.png
  7. Add a call to BusyReportsConsole.exe with the parameters documented bellow, or a "Execute Process Task" in your SSIS Control Flow if you are using SSIS

BusyReportsGui

It is a WPF-Entitiy Framework application which enables you to maintain the configuration tables. From this app you can also run the reports for testing purposes.

BusyReportsConsole:

This is the console app you schedule. It will call the SSRS web services to produce the reports, get the results and email or store them on a file share.
You can call it like this
  • BusyReportsConsole.exe ID
where id is the id of the report (id column in the BusySubscriptions table). this is not recommended beyond testing purposes, as the ID's can change if you delete/recreate subscriptions.
  • BusyReportsConsole.exe PATH
where Path is the path to the report (From the root in SSRS ReportManager, e.g. /AdventureWorks 2012/Customers_Near_Stores)
  • BusyReportsConsole.exe PATH%
Run all the reports matching the PATH% Pattern (e.g. /AdventureWorks 2012/Customers% -> all reports in the /AdventureWorks 2012 folder whose name starts with Customers)

Config File Settings

  • TestEmail
in the GUI there is a test run button. when you click it it will run the report, but replace any emails with this one. ideally this would be your own email, so you can quickly test-run the reports to look at the email.
  • TestPath
this is the same, but for file subscriptions
  • MaxThreads
Set this to a big number if you want trouble ;-) the numbers of subscriptions it can execute in parallel. take into consideration that all the queries will run at the same time on your databases. there are however cases where you can gain some time by producing more than one report at the same time.

Notes
  • it is not uncommon to add subscriptions directly into the config tables via SQL, if there are a lot of similar subscriptions
  • 99% of the parameters can be set using default parameters (and it should be your preferred way), but if you can't, you can set them in the BusySubscriptionsParameters table

Conditional execution or "Alert Emails"

There are reports you want to send only if a certain level is reached (only send the abc report if number y is above 100). for this the easiest is to build a powershell script which will
  • connect to the database
  • check the level with a sql query
  • if the level is above 100, execute BusyReportsConsole.exe, else do nothing.


busyreports.png

Last edited Jan 24, 2015 at 1:11 PM by busytools, version 7