...
Create the Sales by Branch report by utilizing the data in the downloaded Excel file.
Let’ start by understanding the role of each sheet in this report.
Sheet V : V_SalesByBranch
When multiple sheets exist in Excel, the sheet to be displayed on the web must follow the format ‘V_(letters)’ or ‘V(number)’.
- Sheet D : D1
This sheet allows you to enter data or retrieve it by connecting to a database.
Sheet P : P1
Create variables to be used when querying data. You can define cell values as variables by using Excel’s ‘Name Manager’ feature.
In this exercise, we’ll use ‘Year’ as a variable to view the income statement by year.
참고사항 title Excel Sheet Naming Convention The naming convention for Excel sheet is as follows:
...
Set the ‘Year’ variable in the P Sheet using Excel’s ‘Name Manager’ feature.
The variable can be used as a filter condition on the web screen. Once the condition is set, only data matching the variable’s value will be retrieved.
To aggregate data in the V sheet, create a derived column that separates the year and month using the Date column.
Use Excel’s SUMIFS function to calculate the ‘Target Amount’ and ‘Actual Amount’ corresponding to the selected year for each item.
* Year : VS_YYYY
* Branch : NYC, LA, WA …
* Value : Target Amount, Actual Amount
Example) Cell C13 : =SUMIFS('D1'!$C:$C, 'D1'!$E:$E, VS_YYYY, 'D1'!$B:$B, 'V_Sales Performance Report'!$B$13)
When you enter 'Target Amount' and 'Actual Amount', the related charts, Variance, and achievement rate are displayed automatically.
The table at the bottom showing monthly performance by branch also calculates each branch’s monthly values using Excel’s SUMIFSS SUMIFS function.
* Year : VS_YYYY
* Month : Jan, Feb ...
* Branch : NYC, LA, WA …
* Value : Actual Amount- Write the SUMIFS function by referencing the derived column.
Example) Cell C21 : =SUMIFS('D1'!$D:$D, 'D1'!$E:$E, VS_YYYY, 'D1'!$B:$B, 'V_Sales Performance Report'!C$21, 'D1'!$F:$F, 'V_Sales Performance Report'!$B22)
Once you have finished entering the data, the Sales by Branch report is complete. Save the completed Excel file.
...
- Next, add and configure the 'Year' filter to view the Sales by Branch report by year on the web.
- Place a ‘Year’ label and a calendar so that users can select the year to filter, and add a ‘Search’ button to refresh data when clicked.
- From the UI Bot menu at the top, click Label and place it on the designer. In the Properties pane, change the Text propertyto ‘Year’, or press F2 on your keyboard. You can also customize the label’s font, color, and other style properties in Style.
- Next, add the calendar.
- From UI Bot > Calendar, click Year and place it next to the label.
- Set the calendar’s Name property to VS_YYYY, the same variable you defined in the Excel’s ‘Name Manager’. This ensures that the value selected in the calendar is used as the filter condition.
Optionally, set InitDate to define the default value when the report is first opened.
- Finally, add the ‘Search’ button to the designer.
- From the UI Bot menu at the top, click Button and place it in the desired location on the designer.
- Similar to the label, change the button’s Text property to ‘Search’, and adjust its formatting in Style.
You can use BoxStyle to quickly apply a saved style to the button.
- Set up an event so that when the ‘Search’ button is clicked, the report data is refreshed using the value from the Year variable.
- Click Process Bot at the top. In the Objects panel, double-click Button with a name ‘Search’.
In the Activities panel, double-click ‘Refresh’ module.
In the Process Bot window, select the 'Button' placed on the window and drag it to the 'Refresh' action link them, so that clicking the 'Button' triggers the Refresh action.
In the Refresh module details, select the MXGrid component to refresh, and drag a connection line from the Button to Refresh Module.
(Note: Dragging and dropping the Excel file onto the web screen automatically creates the MXGrid component.)
...