Search code examples
reporting-serviceschartstimeserieschart

Chart with multiple dates (on X-axis) and one line per status

Using SSMS 2008 R2, I have a table with a list of sales orders. Each row has:

  • ID
  • date
  • status

I am just interested in the ID and status:

ODate   OStatus
------  -------
4-01    1
4-01    1
4-01    4
4-01    4
4-01    1
4-02    1
4-02    2
4-02    2
4-02    1
4-03    1
4-03    1
4-03    1
4-03    1
4-03    1
4-03    3
4-03    4
4-03    1
4-03    3
4-03    1
4-03    1
4-03    1

I want a line chart with:

  • One line for each status;
  • X-axis showing dates;
  • Y-axis measuring the total each day in their respective category: how many 1, 2, 3, and 4 for each day.

I've tried many combinations of GROUP BY and COUNT options in SQL that don't work. I know I need multiple series, but I get an error or simply the same sum results in a horizontal line.

Solution

  • You can do all the grouping at the report level based on the raw data above:

    enter image description here

    Results based on your DataSet:

    enter image description here

    No formatting or anything has been applied, but the results look correct.