Note: Though i have shown email addresses below those are actually users. You can burst to multiple users who are part of the repository and not email addresses directly. But you can have multiple email addresses as part of a user.
One of the very good features of BI EE is its ability to burst and send the reports to multiple users simultaneously. Lets look at how to go about achieving bursting and also at the same time look at some additional examples of how to send out the reports to multiple users whose email ids are not stored a database column. First lets look at how to burst reports out using Delivers. In order to do bursting, one would have to have a column in the database that would have all the email ids of the end users to whom you would like to do the bursting to. So, the first step in enabling bursting is to create a report containing the email id column. This report can contain all the desired filters that you want to apply on this report. For example, in my case i have 2 of my own email ids in the report.
Once this is done the next step is to create an ibot. While creating the ibot follow these steps below
1. In the conditional request tab enter the report that we just created above. This is what will be used for bursting.
2. Go to the Recipients tab and enable the checkbox wherein you can determine the users from a Conditional Request. Choose the column that contains the email ids.
3. Now go to the Delivery Content Tab and choose the report which you would like to send.
4. Also choose the kind of scheduling that you need along with the desired Destinations (Email, Pager etc).
This would give you the basic bursting capabitlites that you are looking for. But in some cases it so happens that we would like to send the reports to users whom we know but those that are not stored inside a database column. So, in order to configure delivers for this case we shall see 2 approaches
1. Use of Pipelined Functions and Direct Database Requests
2. Use of Writebacks
In this blog entry i shall be covering the first approach. This is not recommended for production machines. This is just for illustration purposes since Direct Database Requests are not recommended (they bypass all of your BI EE security). In order to achieve this lets start with creating a simple Pipelined function as shown below
CREATE OR REPLACE TYPE VAR_ARRAY AS TABLE OF VARCHAR2(100);CREATE OR REPLACE FUNCTION PIPE_EMAIL( p_csv IN VARCHAR2)
RETURN VAR_ARRAY
PIPELINED
AS
v_inCSV varchar2(50);
v_noCSV number;
v_inSTR number;
v_enSTR number;
BEGIN
SELECT LENGTH(p_csv) – LENGTH(REPLACE(p_CSV,’,',”)) INTO v_noCSV FROM DUAL;
v_inSTR := 1;
SELECT DECODE(INSTR(p_CSV, ‘,’, 1,1),0,LENGTH(p_CSV) + 1,INSTR(p_CSV, ‘,’, 1,1)) INTO v_enSTR FROM DUAL;
FOR I in 1..v_noCSV + 1 LOOP
SELECT SUBSTR(p_CSV,v_inSTR,v_enSTR – v_inSTR) INTO v_inCSV FROM DUAL;
PIPE ROW(v_inCSV);
v_inSTR := v_enSTR + 1;
SELECT DECODE(INSTR(p_CSV, ‘,’, v_enSTR + 2,1),0,LENGTH(p_CSV) + 1,INSTR(p_CSV, ‘,’, v_enSTR + 2,1)) INTO v_enSTR FROM DUAL;
END LOOP;
RETURN;
END PIPE_EMAIL;
So what this basically does it accepts comma seperated email ids as its arguments and will convert them into rows of data. For example if you give ‘krisvenky83@gmail.com,abcd@abcd.com’ as its argument then it will print like this
krisvenky83@gmail.com
abcd@abcd.com
So, all you need to do is to create a new report using Direct Database Requests and typein the following
select * from table(pipe_email(‘krisvenky83@gmail.com,abcd@abcd.com’))
In your case, instead of the email ids specified above you can put as many comma seperated email ids as you want. Now save this report.
Now the procedure to burst is same as the above. But now we do not have to have the email ids stored in a database column. In a later blog entry we shall see another approach to the same problem that would use Writebacks.
Posts
