Creating Excel Dashboards
Guide for creating interactive Excel dashboards with charts, tables, and formatting.
Dashboard Components
Basic Dashboard Structure
abap
" Create dashboard with multiple worksheets
DATA: lo_excel TYPE REF TO zcl_excel,
lo_summary TYPE REF TO zcl_excel_worksheet,
lo_data TYPE REF TO zcl_excel_worksheet,
lo_charts TYPE REF TO zcl_excel_worksheet.
CREATE OBJECT lo_excel.
" Create worksheets for different dashboard sections
lo_summary = lo_excel->add_new_worksheet( ).
lo_summary->set_title( 'Dashboard' ).
lo_data = lo_excel->add_new_worksheet( ).
lo_data->set_title( 'Data' ).
lo_charts = lo_excel->add_new_worksheet( ).
lo_charts->set_title( 'Charts' ).
Key Performance Indicators (KPIs)
abap
" Create KPI section with formatting
DATA: lo_kpi_style TYPE REF TO zcl_excel_style,
lo_header_style TYPE REF TO zcl_excel_style.
" Header style
lo_header_style = lo_excel->add_new_style( ).
lo_header_style->font->bold = abap_true.
lo_header_style->font->size = 14.
lo_header_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
lo_header_style->fill->fgcolor->set_rgb( 'E6E6FA' ).
" KPI value style
lo_kpi_style = lo_excel->add_new_style( ).
lo_kpi_style->font->bold = abap_true.
lo_kpi_style->font->size = 18.
lo_kpi_style->font->color->set_rgb( '0066CC' ).
" Add KPI headers and values
lo_summary->set_cell( ip_column = 'B' ip_row = 2 ip_value = 'Total Sales' ip_style = lo_header_style ).
lo_summary->set_cell( ip_column = 'B' ip_row = 3 ip_value = '€1,234,567' ip_style = lo_kpi_style ).
lo_summary->set_cell( ip_column = 'D' ip_row = 2 ip_value = 'Growth %' ip_style = lo_header_style ).
lo_summary->set_cell( ip_column = 'D' ip_row = 3 ip_value = '+15.3%' ip_style = lo_kpi_style ).
Data Tables with Formatting
abap
" Create formatted data table
DATA: lt_sales_data TYPE TABLE OF zsales_data,
lo_table_style TYPE REF TO zcl_excel_style.
" Table header style
lo_table_style = lo_excel->add_new_style( ).
lo_table_style->font->bold = abap_true.
lo_table_style->fill->filltype = zcl_excel_style_fill=>c_fill_solid.
lo_table_style->fill->fgcolor->set_rgb( 'D3D3D3' ).
" Bind data table with styling
lo_data->bind_table(
ip_table = lt_sales_data
is_table_settings = VALUE #(
table_style = zcl_excel_table=>builtinstyle_medium2
top_left_column = 'A'
top_left_row = 1
show_row_stripes = abap_true
)
).
" Add autofilter
DATA(lo_autofilter) = lo_data->add_new_autofilter( ).
lo_autofilter->set_filter_area( 'A1:F100' ).
Charts Integration
abap
" Add chart to dashboard
DATA: lo_chart TYPE REF TO zcl_excel_drawing,
lo_chart_data TYPE REF TO zcl_excel_chart_data.
" Create bar chart
lo_chart = lo_excel->add_new_drawing( ).
lo_chart->set_type( zcl_excel_drawing=>type_chart ).
lo_chart->set_position(
ip_from_row = 6
ip_from_col = 'B'
ip_to_row = 16
ip_to_col = 'G'
).
" Configure chart data
lo_chart_data = lo_chart->get_chart_data( ).
lo_chart_data->set_chart_type( zcl_excel_chart=>c_chart_type_column ).
lo_chart_data->set_data_range( 'Data!A2:B10' ).
lo_chart_data->set_title( 'Monthly Sales Trend' ).
lo_summary->add_drawing( lo_chart ).
Advanced Dashboard Features
Conditional Formatting for Visual Indicators
abap
" Add traffic light indicators
DATA: lo_cond_format TYPE REF TO zcl_excel_style_cond.
" Green for values > 100
lo_cond_format = lo_summary->add_new_style_cond( ).
lo_cond_format->set_range( 'F5:F15' ).
lo_cond_format->set_rule_type( zcl_excel_style_cond=>c_rule_cellis ).
lo_cond_format->set_operator( zcl_excel_style_cond=>c_operator_greaterthan ).
lo_cond_format->set_formula( '100' ).
lo_cond_format->set_color( zcl_excel_style_color=>c_green ).
" Red for values < 50
lo_cond_format = lo_summary->add_new_style_cond( ).
lo_cond_format->set_range( 'F5:F15' ).
lo_cond_format->set_rule_type( zcl_excel_style_cond=>c_rule_cellis ).
lo_cond_format->set_operator( zcl_excel_style_cond=>c_operator_lessthan ).
lo_cond_format->set_formula( '50' ).
lo_cond_format->set_color( zcl_excel_style_color=>c_red ).
Interactive Elements
abap
" Add data validation for dropdown filters
DATA: lo_validation TYPE REF TO zcl_excel_data_validation.
lo_validation = lo_summary->add_new_data_validation( ).
lo_validation->set_range( 'B1' ).
lo_validation->set_type( zcl_excel_data_validation=>c_type_list ).
lo_validation->set_formula1( 'Q1,Q2,Q3,Q4' ).
lo_validation->set_allow_blank( abap_false ).
lo_validation->set_show_dropdown( abap_true ).
Dashboard Layout and Navigation
abap
" Create navigation buttons using hyperlinks
lo_summary->set_cell(
ip_column = 'A'
ip_row = 1
ip_value = 'Go to Data'
).
" Add hyperlink to data sheet
DATA(lo_hyperlink) = lo_summary->add_new_hyperlink( ).
lo_hyperlink->set_url( '#Data!A1' ).
lo_hyperlink->set_location( 'A1' ).
" Freeze panes for better navigation
lo_summary->freeze_panes(
ip_num_rows = 4
ip_num_columns = 1
).
Complete Dashboard Example
Sales Performance Dashboard
abap
CLASS zcl_sales_dashboard DEFINITION.
PUBLIC SECTION.
METHODS: create_dashboard
RETURNING VALUE(rv_excel) TYPE xstring.
PRIVATE SECTION.
METHODS: add_kpi_section
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel,
add_trend_chart
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel,
add_regional_breakdown
IMPORTING io_worksheet TYPE REF TO zcl_excel_worksheet
io_excel TYPE REF TO zcl_excel.
ENDCLASS.
CLASS zcl_sales_dashboard IMPLEMENTATION.
METHOD create_dashboard.
DATA: lo_excel TYPE REF TO zcl_excel,
lo_dashboard TYPE REF TO zcl_excel_worksheet,
lo_writer TYPE REF TO zif_excel_writer.
CREATE OBJECT lo_excel.
lo_dashboard = lo_excel->add_new_worksheet( ).
lo_dashboard->set_title( 'Sales Dashboard' ).
" Build dashboard sections
add_kpi_section(
io_worksheet = lo_dashboard
io_excel = lo_excel
).
add_trend_chart(
io_worksheet = lo_dashboard
io_excel = lo_excel
).
add_regional_breakdown(
io_worksheet = lo_dashboard
io_excel = lo_excel
).
" Generate Excel file
CREATE OBJECT lo_writer TYPE zcl_excel_writer_2007.
rv_excel = lo_writer->write_file( lo_excel ).
ENDMETHOD.
METHOD add_kpi_section.
" Implementation for KPI section
" Add key metrics with formatting
ENDMETHOD.
METHOD add_trend_chart.
" Implementation for trend chart
" Create line chart showing sales over time
ENDMETHOD.
METHOD add_regional_breakdown.
" Implementation for regional data
" Add pie chart and data table
ENDMETHOD.
ENDCLASS.
Best Practices for Dashboards
Design Principles
- Clear Hierarchy: Use font sizes and colors to establish visual hierarchy
- Consistent Styling: Apply consistent color schemes and formatting
- White Space: Use adequate spacing between dashboard elements
- Responsive Layout: Design for different screen sizes and print formats
Performance Considerations
- Optimize Data Sources: Use efficient data retrieval methods
- Limit Chart Complexity: Avoid overly complex charts that slow rendering
- Use Appropriate Writers: Choose the right writer for your data volume
- Cache Calculations: Store calculated values rather than complex formulas
User Experience
- Navigation: Provide clear navigation between dashboard sections
- Filters: Add interactive filters for data exploration
- Documentation: Include instructions or legends where needed
- Error Handling: Gracefully handle missing or invalid data