Target Screen


Download Practice Template

To study the practice example above, use the following report by saving it with a new name.

  • How to Use the Template:
    1. Launch the report from the portal.
      Template Path : [PPDM] > [Templates] > [1. Reporting] > [Dashboards] > Template_Monthly Sales Analysis

    2. Click the Save As button in the top right to save it to your working folder.

    3. Run the saved report.

    4. Click the Edit Report button in the top right to launch the Designer window.


Step 1. Design the Excel Interface

         Download and open the practice Excel file.

         Verify the V1, V2, and V3 sheets that will be converted into web interface.

 

1.1 V1 Sheet Key Metrics



1.  Move the indicators from V1 to the T1 data sheet and link each cell to the corresponding metric value.



(Note) Data Sheets - T1 and T2 Sheets 

 


1.2 V2 Sheet Hourly Sales Trends

  1. Establish hourly sales trends in V2, and aggregate data using SUMIFS function and get time-dimension using Data Derivation Method.
  2. Navigate tot he T3 data sheet and parse the time items using a function to enable SUMIF aggregation. 
    Time Derived Function  =LEFT(C2,2)&" hour"


  3. Connect to the T3 data sheet using SUMIFS int he V2 sheet. 


  4. Assign the _D_ reserved keyword to generate the hourly sales visualization chart.  _D_예약어 ☜ Refer to the detailed guide


  5. Apply Conditional Formatting to the product-specific sales information to add visual depth, ensuring higher vales are displayed in darker colors.


  6. Format all cells based on their values > 2-Color Scale > Select colors and click OK.

          

 1.3 V3 Sheet Product Sales Performance

  1. V3 sheet displays sales performance by product and uses Conditional Formatting to show data bars according to the average unit price.
  2. Select the Average Unit Price range > Conditional Formatting > New Rule > Format all cells based on their values > Format Style: Data Bar > Select Fill Color

         

Step 2. Web Interface Conversion

        

Step 3. Data Connection

          Connect live data to the T1, T2, and T3 data sheets (which locates in MX-Grid) using DB Bot. 

         Right-click MX-Grid> Navigate to Design Mode.

         

3.1 T1 Sheet Data Connection

     1. Navigate to T1, click cell [G1], and define the name as VS_YM to load data based on specified inquiry criteria.

       

     2.  Click the [DB Bot] icon in the [i-MATRIX] tools.

       

     

   2. Path : PPDM > Samples > 3. i-META > Sales Analysis

        Query Items: YearMonth, Revenue Amount

        Query Conditions: YearMonth :VS_YM

       

   3. Click the [Yes] button. 
         

3.2 T2 Sheet Data Connection

  1. Navigate to T2, click cell [A1], and click [DB Bot]. 


  2. Path : PPDM > Samples > 3. i-META > Sales Analysis
     Query Items: YearMonth, Target Amount
     Query Conditions: YearMonth :VS_YM



  3. Click the [Yes] button. 

          

3.3 T3 Sheet Data Connection

  1. Navigate to T3, click cell [A1], and click [DB Bot].


  2. PPDM > Sample > 3. i-META, select Sales Analysis
    Query Items: Product Category, Product Subcategory, Sales Time, Quantity, Revenue Amount
    Query Conditions: YearMonth :VS_YM


  3. Click the [Yes] button.


Step 4. Utilizing MX-Grid Reserved Keywords.

_EXPAND_?__?? : Expansion/Collapse functionality for specific rows/columns (Expanded state)

  1.  V3 Sheet Product Category Name Definiiton

Step 5. Utilizing MX-Grid Specific Functions (AUD_xxx)

AUD_IMAGE : Feature to output images into cells.

  1. Reference images in the V_image sheet to output different images to cells depending on whether the cell value is greater or less than 0. (Verify image name definitions)


  2. Enter the function into a specific cell in the V1 sheet.
    Formula : =IF(L2 > 0, AUD_IMAGE(":차이상"), AUD_IMAGE(":차이하"))


  3. Click the Save button in the [i-MATRIX] tools and close the window.

Step 6. Layout MX-Grid Screens via ActiveSheet

Split V1, V2 and V3 sheets within MX-Grid and arrange them in the desired areas. 

  1. Position the V1 sheet in the top sales summary section to display key metric ranges.  
  2. Click MX-Grid control and copy the [i-MATRIX] address to place the V2 and V3 sheets on the screen. 


  3. Create a new MX-Grid component via UI Bot to arrange the V2 sheet.


  4. Paste the copied [i-Matrix] address into the property pane of the new MX-Grid and enter V2 in ActiveSheet and MXGrid in ParentGrid.


  5. Arrange V3 using a new MX-Grid in the same manner.


  6. Remove MX-Grid Background Color : Set the Background A(Alpha) value to 0 in the Style tab of the property pane.


  7. Remove MX-Grid Blue Lines : Select Line Type: None in the Border area of the style tab.

Step 6.  Create a Chart

      Visualize hourly data using a Chart.

  1. Click UI Bot> Chart> Chart.


  2. Enter TIME in the Name property of the generated chart.


  3. The chart will display data bound via _D_ reserved keyword from the V2 sheet.
    The _D_ControlName keyword allows MX-GRID data to be output to i-AUD controls.  _D_예약어 활용하기 ☜ Refer to the detailed guide for _D_ keyword usage.

      


Step 7. Assign Events via Process Bot 

  1. Search for required modules in the Process Bot > Activities tab and double-click to place them.


  2. Refresh the MXGrid when the Refresh button is clicked.
    Process Bot 사용하기 ☜ Refer tot he detailed guide for Process Bot usage.


  3. Apply distinct colors for the MAX value in the TIME chart.
    Default Color value in HEX code : #2797FA
    MAX Color value in HEX code : #EA5A40


  4. Place the Refresh Button and the generated bar chart (TIME) control into the Process Bot and link them to the module.

        


Step 8. Configure Chart Properties

Modify the chart units to '1K'.

  1. Right-click the Chart > Select Design.


  2. Enter 1000 in the Series > Others > Unit area of the chart properties.