Skip to main content

Create an expense tracker using MS Office connectors

5 Tasks

30 mins

Pega Robotic Automation 22.1
Visible to all users
Beginner Pega Robotic Automation 22.1 Robotic Process Automation English

Scenario

The Robotic Banking company provides an expense tracker functionality that collects all spending for predefined categories, creates an Excel report, formats it, and saves it to a PDF file. 

Complete the following tasks:

  • Create an automation that handles a user request for expense tracking.
  • Fill in an Excel spreadsheet with data for pre-defined categories.
  • Format the spreadsheet using the Excel Connector.
  • Save the report as a PDF file.
Note: Some automation and elements of the Globals tab have already been created in the starting project or included in automations for this exercise. The Windows Form was also created, to trigger the RequestExpenseReport automation.

You must initiate your own Pega instance to complete this Challenge.

Initialization may take up to 5 minutes so please be patient.

Challenge Walkthrough

Detailed Tasks

1 Create a RequestExpenseReport automation that handles an operator request for expense tracking

  1. Download the Expense Tracker Challenge project: 
  2. Save the file to your desktop and unzip the file to extract the project folder to your C:\Users\<username>\Documents\Pega Robot Studio\Projects\.
  3. On the Home screen in Pega Robot Studio, click Open Project, and then navigate to the projects folder and open RoboticBanking.pegaproject
Note: The starter project includes a configured BankerInsight application. The value of the Path field must match the location of BankerInsight on your local computer for the project to function properly. In the BankerInsight application, change the value of the Path to the location of your BankerInsight application.
  1. In the Project Explorer, in the Expense tracker folder, open the RequestExpenseReport automation.
  2. On the automation surface, click and drag the gray dot that represents the output automation link to open the Toolbox dialog box. 
  3. In the Toolbox dialog box, in the search field, enter InputBox.
    Screenshow showing the addition of an InputBox to the automation surface.
  4. In the Toolbox dialog box, in the Accelerators section, double-click the ShowInputBox method to add it to the automation surface.
    Note:  The ShowInputBox method is also listed in the Utilities section of the toolbox. This method is the same in both locations and is listed in multiple locations for convenience.
  5. Configure the InputBox variables with the following values by clicking each parameter name on the design block:
    Variable Value
    Message

    Insert a customer ID

    Title

    Expense tracking request

    Default value

    1234500078963456

     
    Screenshot showing the InputBox parameters filled out.
  6. On the Palette, in the Automations section, drag the PrepareAppAndData automation to the design surface to open a Select action dialog box.
  7. In the Select action dialog box, select the Run method and then click Add to add a sub-automation to the automation surface. 
    Note: The PrepareAppAndData automation logs into the BankerInsight application, searches for a customer with a given customerId, and prepares default expense categories. Open the automation in Project Explorer to see more details.
  8. On the Palette, in the Globals section, drag the Categories Lookup Table to the automation surface to open a Select action dialog box.
  9. In the Select action dialog box, select the GetKeys method and then click Add to add it to the automation surface. The GetKeys method returns all tracing categories saved in the Lookup Table.
  10. In the Toolbox dialog box, in the search field, enter ListLoop.
  11. In the list of filtered results, drag List Loop to the automation surface. 
  12. On the automation surface, connect the links as shown in the following example:
    Screenshot showing the connected design blocks in the RequestExpenseReport automation.
  13.  Save the automation. 

2 Add an Excel Connector to the project.

  1. Click the Project tab to open the Project Explorer.
  2. In the Project Explorer, click and open the Globals tab.
  3. In the Toolbox dialog box, in the search field, enter Excel to find ExcelConnector
    Adding an ExcelConnector to the project
  4. Drag ExcelConnector to the Globals tab to add it to the project. 
  5. On the Property Grid, change the name of ExcelConnector to ExcelConnector_ExpenseTracker
  6. On the Property Grid, in the FileName property, click the More icon to select a template Excel file. 
    Changing an ExcelConnector FileName property
  7. in the Open dialog box, navigate to: C:\Users\<username>\Desktop\RobotStudioChallengeAssetsand select SpendingsReport file.  
  8. On the Property Grid, in the SaveAsName property, click the More icon to select the Excel file that stores the report.
  9. In the Open dialog box, navigate to: C:\Users\<username>\Desktop\RobotStudioChallengeAssetsand select SpendingsReport file.
  10. On the Property Grid, set the SheetName property to Report template.
    ExcelConnector Property Grid
  11. Save the changes on the Globals tab.

3 Update the AddAmountsToExcel automation that finds each category transaction and saves the amount to the excel file.

  1. Click the Project tab to open the Project Explorer.
  2. In the Project Explorer, click and open the AddAmountsToExcel automation. The AddAmountsToExcel automation loops through transaction amounts for a given category.  
  3. On the Palette, in the Globals section, drag ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box. 
  4. In the Select action dialog box, in the search field, enter SetCellValue.
  5. In the list of search results, click the arrow to expand the functions group. 
  6. In the Select action dialog box, select the SetCellValue (3 parameters) method and then click Add to add it to the automation surface.
    SetCellValue method of ExcelConnector
  7. On the Palette, in the Locals section, drag the itemCount variable to the automation surface to open the Select action dialog box. 
  8. In the Select action dialog box, add the Value property to define a row for the amounts in the expense report. 
  9. Repeat steps 7-8 to define the Value property for the Amount variable for the expense report.
  10. On the automation surface, connect the links as shown in the following example, to fill in the expense report:
    Screenshot showing the connected design blocks after the addition of the Excel Connector SetCellValue method.
  11. On the Palette, in the Globals section, drag the ExcelConnector_ExpenseTracker to the automation surface to pen the Select action dialog box.
  12. In the Select action dialog box, select the Save (no parameters) method, and then click Add to add it to the automation surface to save the changes made in the Excel file.
  13. On the automation surface, connect the links as shown in the following example:
Note: Keep in mind the dgvAcctTrans design block contains all of a specific customer’s transactions made using Robotic Banking. 
Screenshot showing the fully connected design blocks in the AddAmountsToExcel automation.
  1. Save the automation. 

4 Create the EditTrackingReport automation to format the expense report.

  1. Click the Project tab to open the Project Explorer.
  2. In the Project Explorer, click and open the EditTrackingReport automation.
  3. On the Palette, in the Globals section, drag ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box. 
  4. In the Select action dialog box, select the SetRowHeight method and then click Add to add it to the automation surface.  
  5. Configure the SetRowHeight variables with the following values to create the report header. Click each parameter name on the design block to change its value.
    Variable Value
    row 1
    height 50
  6. On the Palette, in the Globals section, drag the ExcelConnector_ExpenseTracker item to the automation surface to open the Select action dialog box.
  7. In the Select action dialog box, select the SetCellBackgroundColor (2 parameters) method and then click Add to add it to the automation surface.  
    Adding a SetCellBackgroundColor method
  8. Click inside each of the SetCellBackgroundColor variables to set the values with the following to format the report header:
    Variable Value
    address A1
    color 255, 128, 0 (select orange)
  9. On the Palette, in the Globals section, drag the ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box.
  10. In the Select action dialog box, select the SetCellStringValue (2 parameters) method and click Add to add it to the automation surface.  
  11. Configure the SetCellStringValue variables with the following values to format the report header:
    Variable Value
    address A1
    value Expense tracking report
  12. On the Palette tab, in the Globals section, drag the ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box.
  13. In the Select action dialog box, select the Calculate (no parameters) method and click Add to add it to the automation surface to save the changes made in the Excel file.
  14. Repeat steps: 12-13 to add the Save method to the design surface. 
  15. On the automation surface, connect the links as shown in the following example:  
    Screenshot showing the connected design blocks in the EditTrackingReport automation.
  16. Save the automation.

5 Update the RequestExpenseReport to use EditTrackingReport automation and to save the expense tracking report as PDF.

  1. Click the Project tab to open the Project Explorer.
  2. On Project Explorer, click and open the RequestExpenseReport automation.
  3. On the Palette, in the Automations section, drag the AddAmountsToExcel application to the automation surface to open the Select action dialog box.
  4. In the Select action dialog box, select the Run method and then click Add to add a sub-automation to the automation surface. 
  5. Repeat steps 3-4 to add the EditTrackingReport automation to the automation surface. 
  6. On the Palette, in the Globals section, drag ExcelConnector_ExpenseTracker to the automation surface to open the Select action dialog box. 
  7. In the Select action dialog box, select the SaveToPDF (1 parameter) method and then click Add to add it to the automation surface to save the changes made in the Excel file as a PDF file.
  8. In the fileName parameter of the SaveToPDF method, enter C:\Users\<username>\Desktop\RobotStudioChallengeAssets\ExpenseTracking.pdf
  9. On the automation surface, connect the links as shown in the following example: 
    Screenshot showing RequestExpenseReport automation with equired connections
      
  10. Save the automation. 

Confirm your work

  1. Click the Project tab to open the Project Explorer.
  2. In the Project Explorer, in the Expense tracker folder, open the RequestExpenseReport automation.
  3. Click the green Test button, and the blue Test button on the window to begin running the automation.
  4. On the displayed Design Form, click: Expense Tracker to trigger an automation.
    Design form for the project
  5. In the Expense tracking request dialog box, select OK.
  6. Select the Stop button in the toolbar to stop testing the automation. 
  7. Open the created PDF file: C:\Users\<username>\Desktop\RobotStudioChallengeAssets\ExpenseTracking.pdf
  8. Ensure the report is filled in and formatted like in the example below:
    Expense tracking report
Note:  If you run into issues while executing the project, revert the Excel file to the original version to prevent overwriting issues. 

This Challenge is to practice what you learned in the following Module:


Available in the following mission:

If you are having problems with your training, please review the Pega Academy Support FAQs.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega Academy has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice