Formatted Excel Process

The dictionary defines automation as “the technique of making an apparatus, a process, or a system operates automatically.”

We define automation as “the creation and application of technology to monitor and control the
production and delivery of products and services.”

The automation profession includes “everyone involved in the creation and application of technology to monitor and control the production and delivery of products and services”; and the automation
professional is “any individual involved in the creation and application of technology to monitor and
control the production and delivery of products and services.”

My new development or tool is a process which tries to automate the data download option in such a
way that would reduce the post download work.

Basic scenario:

A custom program used to develop in system with the logic provided by the functional people. After the development is moved to production system, final user either executes the same in foreground to get the data or if it takes more time, then based on the input they execute the same in background.

Later they download the data in excel file and do different kind of color formatting before sharing the
same with higher managements based on different conditions.

This new tool imbibed with the auto color formatting process which minimizes the earlier manual effort in excel file.

Means, user now doesn’t need to download the as reported data as excel file in their local desktop /laptop to do color formatting.

Process Output and Information:

By using this custom tool in custom reports, a new option “XL Option’s” will display in tool bar.

Which contains below functionality

1. Condition Color

2. Download

3. Mail

4. Save Variant

5. Apply Variant

Except above options, there are three more options added in context menu.

By using any of these options, a color can be add in selected row, column or cell.

The added color can be changed to another color or can be removed from respective selected cell, row or column.

Color option selection

First time when color selection pop-up window will open, then one will have option to choose either

from excel or ALV.

SAP ALV only supports 7 different colors to apply in output cells.

Here this new tool provides an option to user to select whether use given 7 colors to apply in output ALV for further process or default a color will apply in cells, but internally the selected color will update which will apply only when download or send the output as file.

This is one-time selection, once any of the respective option selected, that will applicable for all color selection processes.

For example, if 7 Colors of ALV option is selected for further process.

After selection of the color, system will update selected color in ALV output.

And if XL Color option is selected,

Then after selection of any color, system will update a default color in ALV

output.

Note: The default color can be changed with the help of developer while programming of the respective method from class ZCL_PRV_OLE_PRO.

Condition Color

By using this option user can set colors on particular cells or rows based on the conditions. It’s same as query option on displaying output.

The below pop-up window will open once this option is selected.

Above window contains output field information, respective values with F4 help and applying color

condition on cell or rows.

For example, select “Max. Data Length” column from fields drop down

And select greater then and less then information from Condition button.

In value field, by using F4 help request, system will display a pop-up screen, which will only contain the values from current output based on selected condition field.

The final query screen is displayed below,

By using below option, user needs to select a color option to apply color on build query.

On Selection of OK, build query will execute and apply color on respective rows or cells.

Download

By using this option, displayed output will get downloaded in local system with formatted excel file

based on applied color.

Mail

This option provides an instant mail to single or multiple mail ids which can also includes condition

information into mail body.

Above pop-up window will open to provide additional information with mails ids to send current output as attachment in mail.

Here mail Subject and attachment name is mandatory fields.

By selection of “Send Color Condition” option, received mail will contain condition information in mail

body to provide more details on applied color on attached excel file.

For example:

Output from SOST t-code.

Save Variant

By using this option, user can save applied condition with selected color information as variant for future use for respective program output.

Below pop-up window will open, which contains the applied condition information on current output.

A valid variant name has to be enter to save the applied condition for future use.

Apply Variant

By using this option, already saved variant can be applied on current output based on executed program.

Displayed above pop-up screen will open for selection of any of the existing variant.

Note: Some reports may have different output based on conditions, in such scenarios, if selected variant information is different than current output. Then an error message will display for selecting valid variant based on current output.

Color would be applied based on selected variant, means if saved variant color used by 7 Colors of ALV, then while applying variant condition, system will update the colors from cells or rows based on 7 colors of ALV and the output will be same as while saving the variant.

But if saved variant is based on another color option (Excel), then system will display the default color in ALV output.

Configuration for Background Jobs and mail

Here some more options has been provided to use formatted excel file for background process.

Developed a program to display executed background report in ALV report, user can use T-Code

ZPRV_BG_SETTING.

To Display background information user needs to enter date and user id.

Program and job name is optional input. On execution of the process below output will display.

On selection of any of the finished background job the outcome will display as output in ALV format.

Excel option tool will be available for instant use.

Map Background Excel Process

By using program ZPRV_BG_MAPPING (Create T-Code as per your choice), user can map the background job information with saved variant and other information like map content and mail ids.

To add or update mail content information, select  option, a new pop-up window will open to insert or update existing mail content information.

Added information will be as mail body while sending output of mapped background job with selected variant.

By selection of “Send Condition” option, mail body will display information with color code based on applied conditions on fields.

To add new or modify existing mail id, use option. A new pop-up window will open to add or modify existing mail id. The formatted excel file, will send based on maintained mail id for the respective mapped information.

Activate / De-Activate Background Excel Process

By activating the process, a program will set in background process based on the entered time on a daily basis.

It will check the Mapping information for background process, program and other information. If any of the background job information is found for the execution date, then it will fetch the report data, apply the selected variant and send to maintained mail id’s with related information.