Foundry Agent not able to perform calculations or aggregations

Question

Foundry Agent not able to perform calculations or aggregations

Nimesh 230

Hi Everyone,

I have created Foundry Agent with 'require_approval="never"' option. I was able to publish it to Teams Group and it works without approval now.

I have Azure AI Search KB linked to Foundry IQ. This KB includes Excel files like monthly dumps and with columns like Ticket No, Date, Notes etc.

When I prompt it to provide tickets for a specific month which includes a keyword, it is able to answer as expected. But, if I ask it to provide the no. of tickets received for a specific month, it is not able to do so.

User's image

Then I tried creating custom Open API connection and linked it. As per https://learn.microsoft.com/en-us/answers/questions/5890288/issue-with-custom-open-api-tool-in-foundry, it is not able to get all the data like Foundry IQ.

SRILAKSHMI C 19,550 Reputation points Microsoft External Staff Moderator

2026-06-12T16:47:37.19+00:00

Hello @Nimesh

Thank you for your patience. We are currently engaging the backend engineering team to investigate this behavior further and review the service-side findings.

At this time, we are awaiting an update from the engineering team regarding the root cause analysis and any potential mitigation or resolution details. Once we receive additional information from them, I will promptly share the findings and any recommended next steps with you.

We appreciate your understanding while we continue coordinating with the appropriate team, and I will keep you informed as soon as there is a meaningful update available.

Thank you!
SRILAKSHMI C 19,550 Reputation points Microsoft External Staff Moderator

2026-06-17T08:11:16.3933333+00:00

Hi @Nimesh

Could you please provide the details asked in private message for further investigation.

Thank you!

1 answer

Your answer

SRILAKSHMI C 19,550 Reputation points Microsoft External Staff Moderator

2026-06-12T16:47:37.19+00:00

Hello @Nimesh

Thank you for your patience. We are currently engaging the backend engineering team to investigate this behavior further and review the service-side findings.

At this time, we are awaiting an update from the engineering team regarding the root cause analysis and any potential mitigation or resolution details. Once we receive additional information from them, I will promptly share the findings and any recommended next steps with you.

We appreciate your understanding while we continue coordinating with the appropriate team, and I will keep you informed as soon as there is a meaningful update available.

Thank you!
SRILAKSHMI C 19,550 Reputation points Microsoft External Staff Moderator

2026-06-17T08:11:16.3933333+00:00

Hi @Nimesh

Could you please provide the details asked in private message for further investigation.

Thank you!

Answer 1

Hi @Nimesh , thanks for posting your issue here.

This is a fundamental limitation of RAG based search and not a bug. When you ask "show me tickets with keyword X for month Y", the KB retrieves relevant text chunks and the model answers from them. But when you ask "how many tickets were received in month Y", the agent would need to count every matching row across your entire Excel file, and RAG only retrieves the top K most semantically relevant chunks, not all records. So the count will almost always be wrong or incomplete.

The right fix here depends on what you need:

Option 1: Code Interpreter (quickest for PoC) Upload the Excel file directly to the agent session and enable Code Interpreter. The agent can then run actual Python/pandas over the full dataset and give you exact counts and aggregations. The downside is files need to be re-uploaded per session, so it's not great for live production data.
Option 2: Custom API Tool (recommended for production) Expose a small endpoint from your backend that queries the data source directly and returns counts. Something like /ticket-count?month=2026-05. Register it as an OpenAPI tool in Foundry and the agent will call it automatically when it detects an aggregation question. This is the cleanest approach and scales well.
Option 3: Pre-aggregate in the index If the data doesn't change often, pre-compute the monthly counts and store them as separate documents in your Azure AI Search index. The agent can then retrieve them directly. Simple but only works if near-realtime accuracy isn't needed.

I'd go with Option 2 if this is heading toward production, and Option 1 if you just need to demo it quickly.

Please upvote and accept the answer if it helps!

Nimesh 230 Reputation points

2026-06-29T06:26:35.56+00:00

Hi @Jubin Soni ,

Thanks for the response. When using option 2, how can we expose an endpoint and make sure that the Agent uses that endpoint against the matching prompt?

My excel files are basically stored in SharePoint. It syncs to Azure Blob using a logic app. Then only it is indexed by the Azure AI Search. So, are you asking to create an endpoint before data gets indexed or from Azure AI Search directly?

I have tried using a custom Open AI tool before and I encountered below issue where there is a limit for the maximum payload size.

https://learn.microsoft.com/en-us/answers/questions/5890288/issue-with-custom-open-api-tool-in-foundry