Skip to content

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

  1. Clear Hierarchy: Use font sizes and colors to establish visual hierarchy
  2. Consistent Styling: Apply consistent color schemes and formatting
  3. White Space: Use adequate spacing between dashboard elements
  4. Responsive Layout: Design for different screen sizes and print formats

Performance Considerations

  1. Optimize Data Sources: Use efficient data retrieval methods
  2. Limit Chart Complexity: Avoid overly complex charts that slow rendering
  3. Use Appropriate Writers: Choose the right writer for your data volume
  4. Cache Calculations: Store calculated values rather than complex formulas

User Experience

  1. Navigation: Provide clear navigation between dashboard sections
  2. Filters: Add interactive filters for data exploration
  3. Documentation: Include instructions or legends where needed
  4. Error Handling: Gracefully handle missing or invalid data