Well, last week I was asked to do bursting of reports in such a manner that each of the split report gets delivered to specified Delivery channel. For those, who are not getting what I am talking about- Bursting is a option in BI Publisher in which we write a query such that the report gets split up into sub reports based on split key. For example, if I have 100000 customers in my Database then there are two ways:
1. I can generate reports for each of them separately by passing their customer id[or primary key] as a parameter. What it will do, it will simply pick up all the data for that particular customer from widely separated several tables , aggregate everything finally in a report. [Normal way of generating report will work for it.]. It will generate report for only those customers whose customer id, I will pass[Not for all customers]
2. I don't pass any parameters, I just want to get the reports automatically generated for all the customers existing in my Database. So for this "Bursting" comes for rescue. A normal bursting query allows us to split all the reports at 1 particular delivery channel at a time. For e.g. at one time I can get all the reports generated at my File system or get all the reports sent to Email or get all the reports delivered to Fax or deliver all the reports to Printer etc. If I am asked to deliver to one destination at a time, it was simple as per BI Publisher docs.
But as usual, you never get such simple straight forward work to do :|. We always need to scratch our heads a bit. I was asked to generate[of course using Bursting] all the reports in such a manner that each of them gets delivered to separate destination based on the customer's preference mode. In simple words, if Customer with customer id as C1 has set his/her report's delivery mode as 'FILE' then his report should be saved as File only. If Customer with id as C2 has set his/her delivery mode as 'EMAIL' then his report should be sent as an email. If C3 has set delivery mode as 'FAX' , it should be delivered to 'FAX'
So for this problem, I talked to Oracle people on their forums also but they said this facility will be given in next release and all and we can't wait till their next releases right?So somehow, I managed to solve this problem in 3 ways [Best is one only as always :) ]
1. I burst the reports and first save all the reports in a File system using DEL_CHANNEL as 'FILE' and then making a for loop which will fetch each of the customer's id and check his report delivery mode preference and then fire up the report to that destination.
Drawback : Who will use unnecessarily a "for" loop until or unless you have no other option left right? So I opted this option only if I couldn't find any better method.
2. Because of having my habit of tweaking with the things always in wrong direction, I tried something funny. FYI, Normal bursting query for DEL CHANNEL AS 'FILE' is something like below
Now what funny thing I tried was I combined both the queries and planned to use the where condition so that reports are delivered to appropriate customers according to their preferences. See
My problem was still unresolved so I approached to 3rd way
3. Now what I did, I made a customized REF_REPORT_DEL_CHANNEL table which will be having all the parameters set in it.
And changed the bursting query like following:
So finally this query would work for all types of delivery modes just I need to sacrifice a "join' statement' and an additional table REF_REPORT_DEL_CHANNEL. It works like magic.
Hope can be of some use to people looking for same problem. :)
Good bye !
[Note: Discussing Customer's problem but giving solution for employee is obviously clearly shown in above post but because of confidential reasons, queries are being changed. Assume employee as customer or whatever , it rarely matters for brainy ones]
