Skip to main content

Notifications

Customer experience | Sales, Customer Insights,...
Suggested answer

SSRS Reports and SQL Query

(0) ShareShare
ReportReport
Posted on by 55

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.

  • Suggested answer
    Naveen Ganeshe Profile Picture
    Naveen Ganeshe 3,393 User Group Leader on at
    RE: SSRS Reports and SQL Query

    Hi Latha,

    Here is an example of how you can do this:

    1. Create a SQL query that retrieves the data for the chart. You will need to use a query that retrieves the number of created cases and the number of closed cases for the last 7 days, grouped by day. Here is an example of a query that does 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)
      
    1. In SSRS, create a new report and add a chart control to the report.
    2. Set the chart control's data source to the query you created in step 1.
    3. Set the chart type to line chart.
    4. Drag and drop Date field to the category group and CreatedCases and ClosedCases to the value field
    5. You can format the chart and add a chart title, legend and customizing color as per the need.

    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.

  • Suggested answer
    Abdul Wahab Profile Picture
    Abdul Wahab 12,070 Super User 2024 Season 1 on at
    RE: SSRS Reports and SQL Query

    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.

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.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans