Monday, 22 January 2018

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

And if XL Color option is selected,

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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

output.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

The final query screen is displayed below,

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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:

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

Output from SOST t-code.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

Excel option tool will be available for instant use.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

Map Background Excel Process


SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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  SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materialsoption, a new pop-up window will open to insert or update existing mail content information.


SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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, useSAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials 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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

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.

SAP ABAP Development, SAP ABAP Certifications, SAP ABAP Guides, SAP ABAP Tutorials and Materials

No comments:

Post a Comment