Reporting Services Subscriptions through SQL Agent

Reporting Services subscriptions are useful (obviously) and can be configured adhoc or driven from config tables. The schedule that the subscription runs on can be run one of two ways.

1. At a time/date specified by you for that run - with the option of repeating

2. On a shared schedule

An option 3 might be

"when a particular job or activity has finished". 

If you could achieve this then you can reuse all the configuration you already have for the subscriptions. But how to do it? I found a hacky way that works. There may be other (and better) methods.

1. Create your subscription as normal, configuring your email receipients, delivery types etc.

2. Create an individual schedule that runs at an odd time (one you will recognise later) and make the end date for the schedule today.

3. Once you have created the schedule switch over to SQL Agent and try to identify the SQL Agent job that has been created by your subscription. You can either use the "created date" of the job or the "next run time". It will more than likely appear at the top of your job list and have an incomprehensible name containing characters and numbers.

4. Once you have located the job, open it up and it will contain a single step calling an sp with the name [AddEvent]. For example:

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='ABCDEF'

5. Now, open up the job you want to embed this in and create a new T_SQL step. Now just pop the line of code from Step 4 into the "Command" section

The reason for setting an expired EndDate in Step 2 is so that it does not actually run. Instead, the job will now be kicked off by your new job. In fact, it can be added to any job you like.

 

Reporting Services Active Subscriptions Query

If you are in an environment where a number of people have the capability to create subscriptions then you may want to keep tabs on them. The ones you have created are available under "My subscriptions" and depending on your own role you can also tab through all the Report folders looking for active subscriptions. I quickly got bored/tired of this so we created an SSRS report that shows all the active subscriptions. The query is as follows and gives you the basis of a useful straighforward report.



SELECT   u.UserName,
         c.Name AS TheReport,
         s.SubscriptionID,
         s.OwnerID,
         s.Report_OID,
         s.Locale,
         s.InactiveFlags,
         s.ExtensionSettings,
         s.ModifiedByID,
         s.ModifiedDate,
         s.Description,
         s.LastStatus,
         s.EventType,
         s.MatchData,
         s.LastRunTime,
         s.Parameters,
         s.DataSettings,
         s.DeliveryExtension,
         s.Version
FROM     Catalog AS c
         INNER JOIN
         Subscriptions AS s
         ON s.Report_OID = c.ItemID
         INNER JOIN
         Users AS u
         ON u.UserID = s.OwnerID
WHERE    (s.LastRunTime BETWEEN @pStartDate AND @pEndDate)
ORDER BY TheReport;

Displaying Multi Valued Parameters in Reporting Services

Its quite normal to want to display the value of the parameters you have used to run a report within the report body. For one thing, once the report has been printed out, the person reading it can tell how it was run. For single values parameters you can simply create a text box and put the expression

=Parameters!pTeam.Label

to display your single team parameter. But if its a multi-valued parameter you have to work a tiny bit harder. For starters you'll need to add a bit of custom code to your report. You get to this through

Report -> Report properties -> Code

and you then enter this little code unit:


Public Shared Function PrintArray(ByVal Value As Array, ByVal Count as Integer) As String


dim i as Integer
PrintArray = ""
For i = 0 to Count -1
PrintArray = PrintArray & Iif(i>0,", ","") & LTrim(Value(i))
Next


'PrintArray = LTrim(Value)


End Function 

To pick this up and make use of it you just create a text box as before but enter the expression:

=Code.PrintArray(Parameters!pTeam.Label, Parameters!pTeam.Count)

This will print out the values you have entered as comma separated list.

Multi Select parameter from SSRS into an Oracle package

When using a SQL Server stored procedure for a dataset you can supply parameters that have 'Multi-select' property but you need some way to handle them in the SQL Server stored procedure (SP). The general approach is to unravel the comma separated string into a temp table within the  SP. Once you've done using loops and various string handling functions then you can join the resulting table to you main table to limit the rows.

In Oracle it is a bit more difficult but the principle is the same. The step by step process I came up with (please correct me if there is a simpler method...)

1. Define a Global type in your Oracle database.

This needs to be an Nested table that can hold the parameters once they have been unravelled.

CREATE TYPE GlobalKeyTableType AS TABLE OF NUMBER;

You need this because the code you are about to write will depend on this - and note it must be a global type.

2.  Write a routine to split up the keys and load them into your global table


PROCEDURE spSplit_Up_Key (
   pParameterString IN VARCHAR2, pIdTable IN OUT  GlobalKeyTableType)
AS
 
   lCommaPos          SMALLINT;
   lParameterString   VARCHAR2(2000) := pParameterString;
   lLoopCounter NUMBER:=0;
   lBreak BOOLEAN := FALSE;
   l_err_num NUMBER;
   l_err_msg VARCHAR2(100);
BEGIN

   IF (LENGTH (lParameterString) <= 0)
   THEN
      RETURN;
   END IF;

   lCommaPos := INSTR ( RTRIM (LTRIM (lParameterString)),',');

   IF lCommaPos = 0
   THEN
             
        lLoopCounter:=lLoopCounter +1;
       pIdTable(lLoopCounter) :=TO_NUMBER ( RTRIM (LTRIM (lParameterString)));
   ELSE
 
         WHILE LENGTH (lParameterString) > 1 AND NOT lBreak
         LOOP
       
            lCommaPos := INSTR ( RTRIM (LTRIM (lParameterString)),',');
             lLoopCounter:=lLoopCounter +1;
             -- Extend if bigger than 1
             IF lLoopCounter > 1 THEN
                pidTable.EXTEND;
            END IF;
            pIdTable(lLoopCounter) :=          
                    TO_NUMBER (SUBSTR (RTRIM (LTRIM (lParameterString)),1,lCommaPos - 1));
                   
            lParameterString :=
               SUBSTR (RTRIM (LTRIM (lParameterString)),
                          lCommaPos + 1,
                          LENGTH (RTRIM (LTRIM (lParameterString))));
            lCommaPos := INSTR ( RTRIM (LTRIM (lParameterString)),',');

            IF lCommaPos = 0
            THEN
           
             lLoopCounter:=lLoopCounter +1;
             -- Extend if bigger than 1
             IF lLoopCounter > 1 THEN
                pidTable.EXTEND;
            END IF;
       
                  pIdTable(lLoopCounter) :=  TO_NUMBER (RTRIM (LTRIM (lParameterString)));

                  lBREAK:=TRUE;
               END IF;
            END LOOP;
           END IF;
         
           EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       l_err_num := SQLCODE;
      l_err_msg := SUBSTR(SQLERRM, 1, 100);
     
       DBMS_OUTPUT.put_line( TO_CHAR(L_err_num) || ' : ' || l_err_msg);
       RAISE;
     
     
           END spSplit_Up_Key;

This SP is best stored centrally in its own package accessible by various other routines. I actually have another one for splitting up VARCHARs but the principle is the same.

3. Call the SP and use the returned table

In you main SP you'll need to call the spSplit_Up_Key and capture the returned NESTED TABLE.

Note you'll have to declare (and initialise) the table in your calling routine - here is an example of where I've used it to capture a list of teams and am now using it in the query.


AND teamid  IN
 (  select a.column_value  val
    from THE ( select cast( lTeamKeyTable as GlobalKeyTableType )
                               from dual ) a)

You may need to define more than 1 depending on how many multi values parameters you are passing in.

4. Hook up Reporting services to call the Oracle SP


Refer to my previous post for this but remember when passing the parameter to pass the joined string using

JOIN(pTeams,",")

Conclusions...
This took me longer than it should have done! There are several small gotchas which I haven't included here as would make the post to long but this is enough to get it working.

Reporting Services calling an Oracle Stored Procedure

Most of the recent SSRS work I have done has been off a SQL Server datasource. And, for anything other than the simplest queries you'd write a Stored Procedure (SP) in the database which returned the rows you want. This is achieved nicely in T-SQL as you can do whatever you want in the body of the SP e.g set flags, create temporary tables, build objects and then end your SP by just writing a SELECT statement. The rows returned get picked up by the SSRS Report in the datasource and once you've mapped your fields then you are away.

In Oracle I didn't find it as simple. Maybe a combination of letting my PLSQL get rusty but also I think that PLSQL is just not geared up to do this in as simple a way.

In any case, I did get it working, and once you know how it is straightforward! The process is to create a Procedure in your Oracle database whose last parameter is and OUT parameter of type SYS_REF_CURSOR. Here is a short example of one I wrote:

 PROCEDURE spPickOrderStatus(pOrderStatuses OUT SYS_REFCURSOR) IS
  BEGIN


   OPEN pOrderStatuses
   FOR
   SELECT
      oid.order_ind, 
      order_ind_desc
   FROM
      mm_order_indicators_dim;


   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END;


Change the datasource in you report to be Oracle and choose the query type to be "Stored Procedure". The field should be mapped as smoothly as with a SQL Server datasource.

Case Sensitivity

I'm sure this can be changed by your friendly SQL Server DBA but worth noting that, by default, SQL Server databases are case insensitive whereas with Oracle you do need to distinguish between your P's and your p's!

For example when searching for the word 'Tower', the following line

SELECT *
FROM MyTable
WHERE name LIKE '%ToWeR%'

will bring back the row in SQL Server but NOT in Oracle.