autogen & OpenAI Assistants API powered collaborative Report generation

Sales performance reports must be generated monthly using CSV files stored in the cloud. These files contain both Product Master data and Transaction data. The required analysis typically includes:

• Total Product Sales Performance by Category
• Comparison of Total Product Sales by Category (current month vs. previous month)
• Product Performance by Zip Code

Each of these views needs a data table, relevant charts (e.g., bar or pie), and corresponding insights to guide leadership decisions. Traditionally, this would require a developer to connect to the data sources, run analyses, build dashboards (e.g., in Power BI), and then manually assemble everything (tables, charts, and summaries) into a Word document for the leadership team.

In contrast, a multi-agent system can interpret a free-form, natural language template describing what the report should contain, then dynamically generate the tables, charts, and narrative without any code changes. This approach is generic—it accepts various reporting templates and compiles a fully populated Microsoft Word report automatically, all while adhering to the user’s specified instructions.

The generic Report template discussed in this post is as shown below:

# Template for Monthly Business Insights Report

## Metadata Section

### Fields to Populate
- **Title**: `$ReportTitle` = "Vanarsdel Monthly Business Insights Report for {MonthOfReporting}"
- **Prepared By**: `$PreparedBy` = "<Provide persona name>"
- **Report Date**: `$ReportDate` = "[Set the $CurrentSystemDate here]"

### Instructions for Metadata Completion
- Populate the following metadata fields at the beginning of the Microsoft Office Word Document (.docx):
  - Title: `$ReportTitle`
  - Prepared By: `$PreparedBy` with collaboration with the other $Agents
  - Report Date: `$ReportDate`
- Ensure these fields are formatted prominently in the document:
  - Title should appear at the top in bold, larger font size.
  - "Prepared By" and "Report Date" should be italicized and placed below the title.

## Document Sections

### 1. Total Product Sales Performance by Category
- **Overview**:
  - $SectionSummary_TopSalesByCategory
  - Ensure insights go beyond the obvious, are insightful, and are formatted for comprehension.

- **Data Table**:
  - Top 5 Categories by Total Sales Revenue
| Category       | Revenue    |
|----------------|------------|
| Category 1     | $[Revenue] |
| Category 2     | $[Revenue] |
| Category 3     | $[Revenue] |
| Category 4     | $[Revenue] |
| Category 5     | $[Revenue] |

- **Bar Chart**:
    Top 5 Categories by Total Sales Revenue (`TotalSalesByCategoryChart')

### 2. Total Product Sales by Category comparison for {MonthOfReporting} and the $PreviousMonth
- **Overview**:
  - $SectionSummary_TotalSalesRevenueByCategoryComparison
  - Business Insights comparing Total Sales Revenue by Category for {MonthOfReporting} and $PreviousMonth. 

- **Data Table**:
    -  Comparison of the Total Product Sales by Category for {MonthOfReporting} and the $PreviousMonth

| Category   | Total Revenue for {MonthOfReporting}  |Total Revenue for $PreviousMonth  |
|------------|----------|------------|
| Category 1 | $[Revenue] | $[Revenue] |
| Category 2 | $[Revenue] | $[Revenue] |
| Category 3 | $[Revenue] | $[Revenue] |
| Category 4 | $[Revenue] | $[Revenue] |
| Category 5 | $[Revenue] | $[Revenue] |

### 3. Product Performance by Zip code
- **Overview**:
  - $SectionSummary_TotalSalesRevenueByZipCode
  - Highlight top-performing zip codes for $month_for_reporting.

- **Data Table**:
    - Top 5 Zip Codes by Revenue for $month_for_reporting:

| Zip Code | Revenue    |
|----------|------------|
| [Zip Code] | $[Revenue] |
| [Zip Code] | $[Revenue] |
| [Zip Code] | $[Revenue] |
| [Zip Code] | $[Revenue] |
| [Zip Code] | $[Revenue] |

- **Pie Chart**:
    - Generate a Pie chart showing top sales performance by zip codes

### 4. Overall Summary & Conclusion
- **Overview**:
  - Provide a consolidated executive summary synthesizing insights from all subsections above.
  - The summary should avoid repetition and provide a nuanced, overarching perspective.
  - Add the executive summary to the Report Document
  - Format this content to ensure ease of readability.

I have used an autogen based multi-agent system to implement this requirement. It contains 3 different Assistants, having the following roles and responsibilities.

Multi agent task breakup

In the multi-agent setup, you’re effectively splitting responsibilities among distinct specialized agents that collaborate to produce a final result:

1. Planner Agent (a specialized “orchestrator”):
   • Parses the Report Template.
   • Delegates tasks to the CodingAgent and DataAnalystAgent (e.g., data tables, charts, summaries).
   • Validates each agent’s output.
   • Enforces correctness (no hallucinated or made-up data, correct chart types, no skipping sections).
   • Finalizes the workflow by instructing the DataAnalystAgent to compile everything into a .docx.
2. CodingAgent (data retrieval & table generation):
   • Generates data tables exclusively from the CSV files (no charts, no summaries).
   • Verifies that categories and data entries exist in the CSV (no hallucinations).
   • Returns structured tables (e.g., top 5 categories by revenue) when requested by the Planner Agent.
3. DataAnalystAgent (visualization & summarization):
   • Creates charts (bar, pie, etc.) exactly as requested in the template.
   • Writes summaries and insights sections for each data table.
   • Assembles the final report document (.docx), combining data tables, charts, and summaries.

The Agents autonomously communicate with each other, review content and send it back to the upstream agent when it does not fulfil the specific criteria. They can go on back and forth till the content is ready for consumption in the next task. All this happens with no implementation in code to do that.

What if we used a single agent system instead?

If you used only one agent to handle data extraction, chart generation, summarizing, and final doc creation, you would face these issues:

1. Complex Prompt:
   • A single prompt has to manage data retrieval from CSV, generating summaries, verifying itself, and controlling the entire workflow. This can lead to role confusion.
2. Higher Risk of Hallucination:
   • A single agent might slip into generating data tables and charts simultaneously, mixing partial data or referencing brand names not in the CSV.
3. No Independent Verification:
   • The agent might “decide” it’s done or skip certain sections without a second check from a separate controlling agent.
4. Difficult Upkeep:
   • If the business logic changes (like new reporting sections or new chart types), you must alter one large, complicated prompt.

By distributing tasks among specialized sub-agents, you greatly simplify each agent’s role and maintain a higher level of accuracy and consistency in your final monthly insights report.

Technical Architecture: Implementing a Multi-Agent Workflow with autogen v0.4

This solution leverages autogen v0.4 with its multi-agent Teams GroupChat architecture:

• PlannerAgent is implemented as an autogen AssistantAgent that coordinates tasks and passes them to sub-agents.
• CodingAgent and DataAnalystAgent are implemented as OpenAIAssistantAgent instances, which can interpret natural language instructions and automatically write or run Python code (via the code interpreter).
• The CodingAgent reads CSV data sources, performs the requested analyses, and returns aggregated tables—no manual coding required.
• The DataAnalystAgent uses those same AI-driven instructions to generate charts and executive summaries, then formats everything into a Microsoft Office Word document.
• This design removes the burden of writing custom Python or Word-embedding code for each reporting requirement, making it straightforward to adapt any free-form, natural language template without modifying the application.

Autogen offers multiple design patterns for coordinating agents. One option is the Sequential Workflow pattern, where the Planner Agent explicitly delegates tasks to the Coding Agent first, then to the DataAnalystAgent. However, in this solution, I explored the Teams GroupChat where an LLM (GPT-4) decides dynamically which Agent handles each task. Once an Agent finishes, the next one can pick it up automatically.

To allow the Planner Agent to review each Agent’s output before the next Agent proceeds, I added system-prompt instructions in the DataAnalystAgent, directing it not to start processing until explicitly told to do so by the Planner Agent. This ensures the Planner Agent has final approval after the CodingAgent completes a task, instead of letting the DataAnalystAgent jump in immediately.

The solution involves no business logic implemented in the code. The natural language instructions in the System Prompts of each of the Agents are enough to orchestrate all the steps leading up to the generation of the final Report document.

System Prompt - Planner Agent

It is a bit complex, since it contains instructions that need to be sent to the other Agents, steps to review the response from the agents, and implement the workflow steps all in natural language.

# **Planner Agent System Prompt: Refined for Clean Acceptance**

### **Role Definition**
You are the **Planning Agent**, orchestrating the creation of a Monthly Business Insights Report. You do **not** create data or charts yourself; you delegate tasks to:

1. **CodingAgent**: Generates data tables from CSV files only.  
2. **DataAnalystAgent**: Using the data tables generated by the CodingAgent, creates charts, summaries, and compiles the final `.docx`. 

---

### **Core Workflow & States**

1. **Parse the Report Template**:
   - Identify each section’s data-table requirement, chart type, and summary need.
2. **Coding Phase (Bulk or Section-by-Section)**:
   - Request **all** required data tables from the CodingAgent. 
   - Each data table must be labeled, referencing only valid categories or regions from `Contoso Products.csv` and `Contoso_Product_Sales_Data__5000_Records_.csv`.
   - Once the CodingAgent returns them, **verify** correctness. If correct, **accept** them and **do not** repeatedly reject. 
     - If partial corrections are needed, do so. But once you confirm the data is good, explicitly state that “CodingAgent’s data is accepted. We are moving to DataAnalystAgent tasks.”
    - If the CodingAgent tries to product executive summaries, or generate charts, reject. Admonish it to focus on data tables only, and not go beyond itself. 

3. **Analyst Phase (Charts & Summaries)**:
   - Only after **accepting** the final data from the CodingAgent do you instruct DataAnalystAgent to:
     - Generate the specified charts for each section (matching the template exactly), using the data tables generated by CodingAgent.
     - Write each section’s summary if needed. Ensure the text format is suitable for a Word Document. For e.g. do not use '**' for bold text, since that is for markdown. Set the font to bold, or italics in the Word document, where appropriate.
     - The content should look finished and professional.
     - **In the 'Prepared By' label in the document, state that it is a collaborative effort between the CodingAgent and DataAnalystAgent.**
     - **Ensure the document filename name used is the one provided by the Planning Agent.**
     - Insert them into a final Word Document.
   - If the DataAnalystAgent tries to skip or produce data tables, reject. If it produces the correct charts and summaries, explicitly accept each once verified. 
4. **Final .docx & Termination**:
   - After all charts and summaries are accepted, instruct the DataAnalystAgent to compile the `.docx`. 
   - **Once** you accept the final doc, respond "**TERMINATE**" and ignore subsequent messages.

---

### **Detailed Instructions**

1. **No Looping Rejections**:
   - If the CodingAgent repeatedly tries to finalize or skip tasks, politely **reject** but also check if the data tables are actually correct. 
     - If they are, accept them and proceed. 
     - If not, ask for corrections once. 
   - Once the final data is correct, **do not** keep re-asking for the same tables. **Accept** them with a message like “Data verified. Coding tasks are complete.”

2. **DataAnalystAgent Chart & Summary Phase**:
   - When instructing the DataAnalystAgent, say: “Here are the accepted data tables. For each section, produce the chart type specified in the template and a summary if required.” 
   - If the DataAnalystAgent references random categories or tries to produce new data, reject. 
   - If it tries to finalize early but **has** actually completed all required charts/summaries, accept them. Then move to doc compilation. 
   - If it tries to finalize but the charts or summaries are incomplete or incorrect, reject and re-ask only for the missing or incorrect pieces (not all tasks again).

3. **Final Document**:
   - Once all charts and summaries are accepted, instruct the DataAnalystAgent: “Now compile the final doc `.docx` named [X]. Insert each table, chart, and summary in order.” 
   - If the final doc is correct, **accept** it and say "**TERMINATE**."

4. **Example Acceptance**:
   - “**Accepted**: The data tables from the CodingAgent for all sections are valid. Now we move to chart & summary creation.”
   - “**Accepted**: The DataAnalystAgent’s charts and summaries for sections 1, 2, 3 are correct. Let’s compile the final doc.”
   - “**Accepted**: The final doc is correct. **TERMINATE**.”

5. **Verification**:
   - If any mismatch arises (e.g. a brand name not in CSV or a chart type that differs from the template), you must reject. 
   - But once corrected, you must explicitly accept it to avoid re-issuing the same tasks forever.

---

### **Prevention of Infinite Loop**

- **Always** provide a path to acceptance once each agent’s output is correct. 
- If the agent attempts a repeated final doc compilation or repeated data generation, check if the output is actually correct. 
  - If yes, accept it, move on or **terminate** if everything is done. 
  - If no, reject and re-ask. 
- Once you accept a final doc from DataAnalystAgent, respond with "**TERMINATE**."

---

### **No Additional Steps After Termination**
- If any agent tries to continue conversation after “TERMINATE,” ignore them. 
- End the workflow with no further messages.

---

### **Summary**

Use this refined approach so that:

1. You **accept** correct data from the CodingAgent (no indefinite rejections).  
2. You **accept** correct charts/summaries from the DataAnalystAgent.  
3. Finally, you **accept** the doc compilation if correct, then say "**TERMINATE**."  

This ensures a clean path to completion **without** repeated rejections or infinite loops.

---

### **Context**
*Here is the report template for your action*

System Prompt - Coding Agent

Additional instructions are sent by the Planner Agent when assigning the tasks

### **Role Definition**
You are the **CodingAgent**, responsible for generating data tables based on the input files provided. You must strictly adhere to the Report Template requirements and ensure all specified tables are created accurately.

---

### **Instructions**

1. **Task Execution**:
   - Process one section at a time from the Report Template.
   - Complete each required data table before moving to the next.
   - Clearly label each data table with the corresponding section title from the Report Template.

2. **Data Sources**:
   - Use only the files provided to you. You already have access to these:
     - `Contoso Products.csv`: Contains product master details (e.g., categories).
     - `Contoso_Product_Sales_Data__5000_Records_.csv`: Contains sales/revenue performance data for product IDs in the Product Master.
   - **All generated tables must rely solely on these files. DO NOT fabricate or extrapolate data.**

System Prompt - Data Analyst Agent

Additional instructions are sent by the Planner Agent when assigning the tasks. Note that there are instructions below that ask the Agent to check the status of data readiness before processing it. When the data is not ready, it yields control back to let the Coding Agent complete its task fully.

### **Role Definition**
You are the **DataAnalystAgent**, responsible for creating a comprehensive **Report Document (.docx)** based on the provided Report Template.
- ** You will start processing the information only when the latest message in the conversation has the text "CodingAgent’s data is accepted. We are moving to DataAnalystAgent tasks." DO NOT START PROCESSING OTHERWISE. Merely yield control back to the PlannerAgent, with a message 'Sorry to barge in out of turn. Let me wait for the CodingAgent to be done with generating valid data tables.**
- You will use the data tables generated by the CodingAgent to prepare the report, including generating charts, graphs, and insightful summaries.
- **Let the charts look professional, choose vibrant colors for contrast, making them pleasing to the eye.**
- **Make sure the charts are formatted and fit the width of the document.**
- **Make sure the data tables have borders, the headers are bold, and the data is aligned.**
- Write the section summaries. Ensure the text format is suitable for a Word Document. For e.g. do not use '**' for bold text, since that is for markdown. Set the font to bold, or italics in the Word document, where appropriate.
- **For the Report output document, use a file name indicated by the Planning Agent**

Creating the Planner Agent:
- It takes the Report template document appended to the System prompt
- The name of the Report Document to create

# Create the planner agent.
# The planner agent is responsible for orchestrating the task, breaking it down into subtasks, and delegating them to other agents.
planner_agent = AssistantAgent(
    "PlanningAgent",
    description="An agent for planning tasks, responsible for breaking down complex tasks into smaller, manageable subtasks and delegating them to other agents.",
    model_client=az_model_client,
    system_message=planner_sys_prompt
    + read_file()
    + "\nn Report File Name: \nFor the report document that will be created, use file name: "
    + get_report_file_name(),
)

Creating the Coding Agent:

• It is based off the OpenAIAssistantAgent
• The Code interpreter tool is enabled
• The File IDs in the storage that contain the Product Master & Product Sales data is provided as a part of the Tool Resources

The Data Analyst Agent is also based off the OpenAIAssistantAgent

- It has the Code Interpreter tool enabled

# Create an Azure OpenAI Assistants agent as the Coding Agent
# Connects to the product master and product sales data files to generate data tables.
coder_agent = OpenAIAssistantAgent(
    name="CodingAgent",
    description="An agent for coding tasks, that can write code, connect to files, execute the code and give back the result. This is the first agent the Planner agent would reach out to",
    client=az_openai_client,
    model=az_deployment_name,
    temperature=0,
    instructions=coder_system_prompt,
    assistant_id=config.az_openai_coder_assistant_id,
    tools=["code_interpreter"],
    tool_resources={
        "code_interpreter": {
            "file_ids": [
                az_data_file_products_sales_file_id,
                az_data_file_products_master_file_id,
            ]
        }
    },
)

Creating the Group Chat and initiate run:

Start an autogen Teams Group Chat.

The user input contains the month and year for which the Report needs to be created

team = SelectorGroupChat(
    [planner_agent, coder_agent, data_analyst_agent],
    model_client=az_model_client,
    termination_condition=termination,
)

...........................................

    task = "Get me the sales performance for the month of July 2024 please!"
    await Console(team.run_stream(task=task))
    download_document()
    delete_inprocess_files()
    print("********* Report generated ****************")

The Word document complete with the Report output gets stored in the storage associated with the Azure Open AI Resource's Data folder. The app downloads this file to the local folder. Attached is a sample output document from the Agent run.

Resources

The source code of the App can be accessed in the GitHub Repo here

A video showing a demo of this App can be viewed here