Dynamics Community Forum Thread Details

Hello MEGA1,

You're right, it sounds simple enough, but unfortunately, reporting directly off the Audit History in Dynamics 365 (Dataverse) for general users is not straightforward and has some limitations. Here's a breakdown of the challenges and potential workarounds:

Challenges:

Audit Data Structure:
- Audit data is stored in the AuditBase table, which is designed for internal auditing purposes, not for direct user reporting.
- The data is stored in a normalized and somewhat complex format, making it difficult to create simple reports.

Access Restrictions:
- Direct access to the AuditBase table is often restricted for security and performance reasons.

Reporting Limitations:
- The standard Dynamics 365 reporting tools (Views, Charts) are not designed to work directly with the AuditBase table.
- FetchXML, while powerful, can be complex to write for audit data.

Workarounds and Potential Solutions:

Advanced Find and Export to Excel (Simplest, but Manual):
- Concept:
  - Use Advanced Find to query the audits entity.
  - Filter by the "Task" entity and the "Task Status" field.
  - Export the results to Excel.
- Steps:
  1. Open Advanced Find: Navigate to Advanced Find.
  2. Select "Audits": In the "Look for:" dropdown, select "Audits."
  3. Add Filters:
    - Add a filter for "Object Type Code" equals "Task" (or the appropriate entity type code).
    - Add a filter for "Attribute Mask" contains "statuscode" (or the logical name of your Task Status field).
    - Add a filter for the date range.
  4. View Results: View the results.
  5. Export to Excel: Export the results to Excel.
  6. Format in Excel: Format the data in Excel to create your desired report.
- Limitations:
  - Manual process.
  - Not suitable for recurring reports.
  - Requires Excel skills.

Power BI (Recommended for More Advanced Reporting):
- Concept:
  - Use Power BI to connect to the Dataverse and create a report based on the audits entity.
  - Power BI provides more flexibility in data transformation and visualization.
- Steps:
  1. Connect to Dataverse: Connect Power BI to your Dataverse environment.
  2. Select the "Audits" Entity: Select the audits entity.
  3. Transform Data: Use Power BI's Power Query Editor to transform the audit data into a more usable format.
    - You will have to parse the changedata field, to extract the statuscode values.
    - You will have to relate the objectid field to the task entity.
  4. Create Report: Create your desired report using Power BI's visualization tools.
  5. Publish and Share: Publish the report and share it with your users.
- Advantages:
  - More advanced reporting capabilities.
  - Interactive and dynamic reports.
  - Automated data refresh.
- Disadvantages:
  - Requires Power BI knowledge and licensing.
  - More complex to set up.

Custom Code (Plugin/Web API and Custom Report):
- Concept:
  - Develop a custom plugin or use the Web API to retrieve audit data.
  - Transform the data into a more usable format.
  - Create a custom report using a reporting tool (e.g., SQL Server Reporting Services, custom web page).
- Limitations:
  - Requires development expertise.
  - More complex to set up and maintain.

Key Considerations:

Performance: Audit data can be large, so consider performance implications when querying and reporting.
Security: Ensure that users only have access to the audit data they need.
Data Retention: Audit data might be subject to data retention policies.

Recommendation:

For simple, ad-hoc reports, Advanced Find and Excel is a quick and easy solution.
Power BI is the most recommended approach for more advanced and recurring reporting needs.
Custom code should only be used for very complex scenarios.

By using Power BI, you can create a robust and user-friendly report that meets your users' needs.

If my answer was helpful, please click Like, and if it solved your problem, please mark it as verified to help other community members find more. If you have further questions, please feel free to contact me.

My response was crafted with AI assistance and tailored to provide detailed and actionable guidance for your Microsoft Dynamics 365 query.

Regards,

Daivat Vartak