Create professional, branded Excel reports with custom layouts, formatting, and calculations.

Excel Template Design allows you to create sophisticated reports that maintain consistent formatting, include your organization's branding, and present data in exactly the layout you need. This is perfect for official reports, government submissions, and professional presentations where appearance matters as much as content.

Table of contents

What Excel Templates Can Do

Key Capabilities

• Custom Layouts - Design reports exactly as you want them to appear

• Branding - Include logos, colors, and organizational styling

• Calculations - Pre-built formulas that work with your data

• Charts & Graphs - Visual representations that update automatically

• Multiple Sheets - Complex reports with different data views

• Repeating Sections - Handle variable numbers of rows or columns

• Professional Formatting - Consistent appearance every time

Common Use Cases

• Government Reports - Standardized formats required by health ministries

• Monthly Dashboards - Executive summaries with charts and KPIs

• Patient Lists - Formatted lists with conditional formatting

• Financial Reports - Budget reports with calculations and summaries

• Quality Indicators - Performance monitoring with visual indicators

Creating Excel Templates

Step 1: Design Your Template

Planning Your Layout

1. Sketch the report - Draw what you want the final output to look like

2. Identify data sections - Where will dynamic data appear?

3. Plan for variability - How many rows/columns might you need?

4. Consider branding - Where will logos and headers go?

Creating the Excel File

1. Open Excel and create a new workbook

2. Add static content:

   • Headers, logos, organizational information

   • Column labels and section titles

   • Any fixed text or formatting

3. Design the layout:

   • Apply fonts, colors, and styling

   • Set column widths and row heights

   • Add borders and cell formatting

4. Save as .xls format (not .xlsx)

Step 2: Add Data References

Basic Data References

Replace actual data with placeholders using the format #dataReference#:

Parameter References

Access report parameters with specific formatting:

Indicator Data References

For Period Indicator Reports:

For multiple data sets, prefix with data set name:

Step 3: Configure Repeating Sections

When to Use Repeating Sections

• Row-per-patient reports with variable numbers of patients

• Location breakdowns with different numbers of facilities

• Time series data with varying periods

Row Repeating Example

For a patient list that might have 10 or 100 patients:

1. Design one row with patient data references:

   Row 5: #patientId# | #givenName# | #familyName# | #age# | #gender#

2. Configure repeating section:

   • Sheet: 1

   • Row: 5 (the row to repeat)

   • Dataset: patientList (name of your data set)

Column Repeating Example

For indicators broken down by multiple locations:

1. Design one column with location data

2. Configure repeating section:

   • Sheet: 1

   • Column: C (the column to repeat)

   • Dataset: locationBreakdown

Multi-Row Repeating Example

For complex patient records spanning multiple rows:

1. Design a block (e.g., rows 10-12 for each patient)

2. Configure repeating section:

   • Sheet: 1

   • Row: 10-12 (range of rows to repeat)

   • Dataset: patientDetails

Advanced Template Features

Charts and Graphs

Creating Dynamic Charts

1. Use sample data to create your chart in Excel

2. Replace sample data with data references

3. The chart will update automatically when the report runs

Example:

• Create a pie chart with sample values: 50, 30, 20

• Replace with: #1#, #2#, #3# (your indicator values)

• Chart updates with real data each time

Chart Best Practices

• Use simple charts - Complex charts may not render properly

• Test with various data ranges - Ensure charts work with different values

• Provide fallback data - Charts may break if all values are zero

Conditional Formatting

Static Formatting

Apply formatting that will persist in the template:

• Cell colors based on fixed rules

• Font formatting for emphasis

• Borders and styles for professional appearance

Dynamic Formatting Ideas

• Red/yellow/green indicators for performance metrics

• Bold formatting for totals and summaries

• Different colors for different programs or locations

Multiple Sheets

Organizing Complex Reports

• Sheet 1: Executive summary with key indicators

• Sheet 2: Detailed patient lists

• Sheet 3: Trend analysis and charts

• Sheet 4: Raw data for reference

Sheet Repeating

For reports that need one sheet per location:

1. Design a template sheet with location-specific data

2. Configure sheet repeating:

   • Sheet: 2 (the sheet to repeat)

   • Dataset: locationData

Practical Examples

Example 1: Monthly HIV Program Report

Template Structure

Configuration

• Data Set Name: hivIndicators

• No repeating sections needed (fixed number of indicators)

• Formulas in Status column to compare values with targets

Example 2: Patient List with Demographics

Template Structure

Configuration

• Repeating Section: Sheet 1, Row 5, Dataset: patientList

• Result: Row 5 repeats for each patient in the list

Example 3: Multi-Location Comparison

Template Structure

Configuration

• Column Repeating: If number of locations varies

• Data Set: locationComparison with location dimensions

Configuration and Setup

Adding Templates to Reports

Through Report Design Manager

1. Navigate: Administration → Report Design Manager

2. Add New Design:

   • Name: Descriptive name for the output option

   • Report Definition: Select your report

   • Renderer Type: Excel Template Renderer

   • Upload Template: Your .xls file

Template Properties Configuration

Expression Settings

You can change these if your template needs to include # characters:

Repeating Sections

Configure through the graphical interface:

• Sheet Number: Which sheet contains the repeating section

• Row/Column: Which rows or columns to repeat

• Dataset Name: Name of the data set driving the repetition

Design Properties

Add custom properties accessible in your template:

Access in template: #property.reportTitle#

Testing Your Template

Test with Sample Data

1. Run your report with a known data set

2. Verify all placeholders are replaced with actual data

3. Check formatting is preserved

4. Test edge cases (empty data, large data sets)

Common Testing Scenarios

• No data found: Ensure template handles empty results gracefully

• Large data sets: Verify performance with many rows

• Missing parameters: Check behavior when optional parameters are empty

• Different user roles: Test with various user permissions

Troubleshooting

Common Issues

Template Not Uploading

• Check file format: Must be .xls, not .xlsx

• Check file size: Large templates may timeout

• Check permissions: Ensure you have design management rights

Data Not Appearing

• Verify data set names: Must match exactly between report and template

• Check expression syntax: Ensure proper #parameter.name# format

• Test indicator numbers: Verify indicators exist in your report

Formatting Issues

• Charts not updating: May need simpler chart types

• Formulas broken: Excel formulas may not transfer properly

• Styling lost: Some advanced Excel features may not be supported

Performance Problems

• Large templates: Consider breaking into smaller reports

• Complex formulas: Simplify calculations where possible

• Many repeating sections: May impact generation speed

Debugging Tips

Test Step by Step

1. Start simple: Begin with basic data references

2. Add complexity gradually: Add formatting, then charts, then repeating sections

3. Test frequently: Don't wait until the template is complete

Use Sample Data

• Create test data with known values

• Use simple parameters for initial testing

• Verify each data reference works before adding more

Check the Logs

• OpenMRS logs may contain error details

• Look for template processing errors

• Check for data set evaluation failures

Best Practices

Template Design

• Keep it simple: Complex layouts are harder to maintain

• Use consistent styling: Establish and follow formatting standards

• Plan for growth: Design templates that can handle more data over time

• Document your template: Include comments about data sources and calculations

Data Organization

• Use meaningful names: Clear data set and parameter names

• Group related data: Organize indicators and dimensions logically

• Consider the audience: Design for the people who will use the report

Performance Optimization

• Limit repeating sections: Use only when necessary

• Optimize data sets: Ensure underlying queries are efficient

• Test with realistic data: Use actual data volumes for testing

• Monitor generation time: Track how long reports take to run

Maintenance

• Version control: Keep track of template changes

• Backup templates: Store copies of working templates

• Document dependencies: Note which data sets and parameters are required

• Test after updates: Verify templates work after system changes

Advanced Techniques

Custom Calculations

Include Excel formulas that work with your data:

Multiple Data Set Integration

Combine data from different sources:

Dynamic Sheet Names

Name sheets based on parameters:

Quick Reference

Essential Data References

Repeating Section Configuration

Next Steps:

• 🎨 Text Template Design - Create custom HTML and text outputs

• 🔧 Report Design Manager - Configure and manage your templates

• ⚙️ Advanced Features - SQL data sets and complex scenarios