Create header in cross tab - Webi

Create header in cross tab - WEBI

Usually for cross tabs if we put required text above the crosstab data part it would replicate (i.e. it wont merge) the same header in all columns.

So here's solution to get single header across cross tab data

1) Create a variable by right clicking on Variables in left pane and  New with required text that needs to go in the header and set its qualification to Dimension. 

2) Right-click on the headings row and choose Insert -> Insert Row above. Now drag and drop the variable to the header portion of cross tab

3) Right-click on the object in the cross table heading and choose Break -> Add Break

4) Right-click on this object again and choose Break -> Manage Breaks. Within the Manage Breaks dialog box, unselect "Break Footer" and change the Duplicate Values behavior to "Merge".

Automation of BW scheduled Jobs linked to BO Webi reprts

AUTOMATION OF SCHEDULED JOBS LINKING TO BO REPORTS

 

CURRENT SCENARIO:

If BOBJ reports are scheduled to run on daily recurrence and for some reason if BW loads get delayed or failed, BOBJ report will not show the latest data.

 

PROPOSED SOLUTION:

To overcome the current scenario we are going to have BO reports scheduled based on file events. The file will be created based on the successful completion of BW data loads/process chains. This would make sure the report is generated and sent to users only if the process chain completes successfully.

 

In BW, we have created an ABAP program to create a test file on application server. This directory, where the file is created, is mounted on both BW server as well as BOBJ server. BOBJ reports will be scheduled prior to this file creation in BW. In BOBJ once the schedules starts, Event will continuously search for the file. In case the file is available prior to BOBJ report schedule time, Event doesn’t process. It will internally check with timestamp of BOBJ report schedule and file creation.  So, we are deleting the old file and replacing it with a new file after BW data loads.

 

Below is high level summary of the steps involved:

• Create a share drive in BW and mount it to BOBJ server so that it can be accessed by both BW and BOBJ server. This is required so that file can be created in BW side and read in BOBJ side.
• Create an ABAP Program to delete the previously created file
• Create an ABAP program to create a sample test file on Application server
• Create the File based Event on the BOBJ side and assign this event to the desired Report for scheduling

BENEFITS:

• Accuracy in data since the report generates only after BW data load finishes
• Incorrect data Reports aren’t sent to users if BW loads fail (especially useful on weekends wherein there is no monitoring)

DETAILED STEPS:

Creating a shared file path at OS level:

Create a share drive in BW and mount it to BOBJ server so that can be accessed by BW system and BOBJ system with full rights with an admin user.  For example, \\AZSXX2120\data\BOBJ\ is directory that we created in BW where test file will be written. This file can be read from the BOBJ side.  This step is done by our BASIS team.

BW Steps

We have created two ABAP program to delete and create the test file. We included these two program in a process chain and attached the process chain with our transactional data load process chain. This helped to create the file only at the end of all transactional data load.

File deletion ABAP code:
*&---------------------------------------------------------------------*
*& Report ZBO_ORDERS_DELETE_TEST
*&
*&---------------------------------------------------------------------*
*&
*& Sample Testing. This program will delete a file from Application *& Server
*&---------------------------------------------------------------------*

REPORT ZBO_ORDERS_DELETE_TEST.

data: p_s_path type pathextern. " Complete filepath
data: p_s_file type pathextern." File name
data: p_s_dir type pathextern. " Directory

p_s_file ='Orders.csv'. "file name to be deleted

p_s_dir = '\AZSXX2120\data\BOBJ\'. " Path from where file will be deleted

concatenate p_s_dir p_s_file into p_s_path.
condense p_s_path.

open dataset p_s_path for input in text mode encoding default.


if sy-subrc = 0.
close dataset p_s_path.
delete dataset p_s_path.

if sy-subrc eq 0.
write: 'File Deleted Succesfully', p_s_path.
else.
write: 'No Authorization to delete or no file exists', p_s_path.
endif.
else.

write: p_s_path, 'File does not exist'.
endif.

File Creation ABAP code:
*&---------------------------------------------------------------------*
*& Report ZBO_ORDERS_CREATE_TEST
*& Program will create a file and place it at application server
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZBO_ORDERS_CREATE_TEST.
tables: rlgrap.
data: lv_file_str type string . "file Path For Application server file
data: lv_msg_text(50),
lv_filename type localfile.


data l_lines type i.
data ls_split type string.
data lt_split like table of ls_split.
data lp_file type localfile.
data lp_fil2 like rlgrap.
lp_file = 'Orders.csv'. "file name to be created
lp_fil2 = '\AZSXX2120\data\BOBJ'. " Path where file will be created


at line-selection.

call transaction 'AL11' .


*———————————————————————-*
* Start of selection
*———————————————————————-

start-of-selection.
split lp_file at '.' into lv_file_str lv_filename .

translate lv_filename to upper case .

if lv_filename = 'CSV' .

clear : lv_filename .



split lv_file_str at '\' into table lt_split.
describe table lt_split lines l_lines.
* file name

loop at lt_split into ls_split.
if sy-tabix eq l_lines.
lv_filename = ls_split.
endif.
endloop.

lv_file_str = lp_file .
if lv_file_str is not initial.
perform write_file .
else.
message 'File Path Needed .' type 'W'.
endif.
else.
message '.CSV File Needed .' type 'W'.
endif.


free :lv_filename ,lt_split ,l_lines ,lv_file_str.
*&———————————————————————*
*& Form write_file – This will create a file at Application Server
*&———————————————————————

form write_file .

concatenate lp_fil2 lv_filename '.csv' into lv_filename .
condense lv_filename no-gaps .
open dataset lv_filename for output in text mode
encoding default message lv_msg_text.

if sy-subrc eq 0.
write: 'File' ,lv_filename ,'created Successfully at Application server.'.
exit.
else.
write: 'File' ,lv_filename ,' cannot get created at Application server.'.
exit.
endif.
endform.

The above two program in the process chain are included in our transactional data load chain. This ensures that file would be created only after BW transactional load is completed. After all sequences, BW system will write file on /data/BOBJ mount or share drive.

BOBJ Steps:

From the Business Objects side, we will be creating file-based event and attaching the event to the report. Once the recurrence schedule starts, the event will continuously search for the files specified in the path. If the file occurs prior to schedule time, event will not trigger, internally it will check with time stamp of file creation and schedule time. In case if the file occurs then need to delete it and write the file. Once the file placed, event will get triggered and schedule process starts to run the report.

Transport / Migrate BO Webi Reports to Production (Admin)

The below article provides information on how to use Promotion Management tool in SAP Business Objects BI Platform 4.0 or above to transport various objects (Webi, universe, dashboards etc.) from one BO environment to other.

Prerequisites –

• Access to CMC (Central Management Console)
• Administrative Rights to access Promotion Management Tool from CMC.
• Server Details and Credentials of both the BO environment between whom transfer needs to be done.
• If you are transferring directly between the BO Servers than both the server should be on same network or can talk to each other.

Steps-

1) Login to CMC

2) Select Promotion Management from the Manage column

3) Promotion Management window will get open , you will be able to see the jobs created for Objects Transport. Click on New Job

Provide the Job details like Job Name, Description, Source (CMS) and Destination(CMS/LCMBIAR)

4)


Source will be the CMS from where you want to transport the Objects, select Login to New CMS and provide the credentials. Destination can be either a CMS system where you directly want to transport the objects or LCMBIAR file which can be imported later.

In my case I have selected CMS(Destination- CMS)

5)

A new window will get open which will provide you option to select all the objects you need to transport. Select the objects from the list and click Add & Close.

If you are selecting objects from different folders than select object one by one and click Add. After adding all the objects click Add & Close.

You will be able to see the objects selected by you in the list that will be transported.

6) Now, here an important point to be noted-always check Manage Dependencies before transporting the objects as this option will check the important dependent objects that needs to be transferred along with the objects selected by user. E.g. Reports along with connection and universe.

As you click Manage Dependencies a new window will get open and will display all the dependent objects. In my case I don’t have any so it’s not displayed below. If you get any dependent objects than select all of them and click Apply & Close.

Now, select all the objects that need to be transported and Click Promote.

Promotion window will get open .You will be able to see the summary, security settings and Test Promote.

7) In Security Settings you can transfer the security attached to the objects along with it. Select appropriate option as per your requirements.

Always use the Test Promote before actually transporting the objects as it will give you a rough idea and errors that may come. Click Save.

Below you can see the results of Test Promote. You can check the status and errors if any.

Now, you will be able to see your Job created for objects transport, wait for some time and check the status. It can be Failure, Partial Success and Success. If it’s successfully done you will be able to check your objects in the destination BO Server now.