How to show created cases and closed cases on same chart for last 7 days. I have written SQL query to retrieve the result and trying to display it on the Chart. Can someone help me on this.
Hi lathareddy,
I am wondering about charts. Basically, the chart is structured on the x-axis and y-axis. In other words, the purpose of charts is to group similar things.
In the x-axis, what will be chosen? 'Created on' or 'Closed on'... it will not make sense. These are my thoughts. Run two different queries for created cases in the last 7 days and closed cases in the last 7 days. Show their records count side by side.
Hi Latha,
Here is an example of how you can do this:
SELECT DATEADD(day, DATEDIFF(day, 0, [CreatedDate]), 0) AS [Date], COUNT(CASE WHEN [Status] = 'Created' THEN 1 END) AS [CreatedCases], COUNT(CASE WHEN [Status] = 'Closed' THEN 1 END) AS [ClosedCases] FROM [Cases] WHERE [CreatedDate] >= DATEADD(day, -7, GETDATE()) GROUP BY DATEADD(day, DATEDIFF(day, 0, [CreatedDate]), 0)
Date
CreatedCases
ClosedCases
It is also worth noting that you can use the same query for the table and matrix for displaying the data in tabular format as well.
Please note that this is just an example, and you may need to modify the query and the chart settings to match the specific requirements of your report. And also the table structure, date column name and date format might be different based on your system.
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
MVP-Daniyal Khaleel 117
Tom_Gioielli 117 Super User 2025 Season 2
Rishabh Kanaskar 83
