SSRS Chart with Date Axis

reporting-servicesssrs-2008

I want to create a chart in SSRS. My data contains timestamps when a state changes from 1 to 0, or vice versa:

Date                   Value
3/31/2011 4:44:38 PM   1 
4/4/2011 2:35:43 PM    0
4/4/2011 3:05:09 PM    1
4/4/2011 5:57:10 PM    0
4/4/2011 7:08:29 PM    1
4/4/2011 7:22:03 PM    0
4/4/2011 9:00:32 PM    1
...

I'd like the X Axis for the chart to show the full date range (which it does) and then show the data as a stepped line showing when the state changes from one to another. The problem is, SSRS doesn't treat the x axis as a date range and plot the values relatively- it seems to just treat each data point as a simple date-unaware plot for the chart. At the moment I get this:
enter image description here

But what I want is something where the data points are plotted relative to their datestamp – this would give you an indication of how long the state was either 1 or 0 – kind of like this poor mockup (the black line is what I want instead of the blue line):
enter image description here

Is this possible?

Best Answer

Damn. In the end it was just a checkbox! I needed to go to the properties of the X Axis (the date range) and check "Scalar Axis". Done.

Related Solutions

Using SSRS line charts to graph time periods on the y-axis

Lets say you have table Times with Date and Hours. Declare Report parameter @LimitHourParam decimal - for target time. Set query of dataset:

SELECT Date, SUM(Hours) AS Hours, 'Actual Hours' AS LimitHour
FROM Times 
GROUP BY Date
UNION ALL
SELECT DISTINCT Date, @LimitHourParam AS Hours, 'Hours Limit' AS LimitHour
FROM Times

So you will get in result hours grouped by dates and some hour limit for unique each date.

Place Chart control in report page.

Go to Chart Properties->Data

Press "Add Values"
Erase value of Label and set Value to

=Sum(Fields!Hours.Value)

Go to "Point Labels" tab of "Edit Chart Value" dialog, check "Show point labels", set Data label to

= String.Format("{0}:{1}",  (Fields!Hours.Value / 1) - (Fields!Hours.Value Mod 1), (Fields!Hours.Value Mod 1)*60/1 - (Fields!Hours.Value Mod 1)*60 Mod 1)

Add Category Groups, set "Group On" expression to

=Fields!Date.Value

Add Series Group, set both "Group On" expression and Label values to

=Fields!LimitHour.Value

Go to Chart Properties->X Axis, set labels format dd/mm/yyyy

Result should look like this:

SSRS group on only one value on a chart

The process described in that link should be OK.

Say I have some sample data:

enter image description here

And a simple chart that has the same issue you're describing:

enter image description here

Under the Network_count Chart Series properties, change the Value field expression to:

=Sum(Fields!Network_count.Value, "Month_CategoryGroup")

enter image description here

Here, Month_CategoryGroup is the name of a Category Group:

enter image description here

Now the Network_count value is the total for all Locations:

enter image description here

You'll note I've removed Network_count from the Legend:

enter image description here

This was displaying incorrectly so I just removed it. If you still want a Legend, the easiest thing would be to create something with a tablix/textboxes outside of the chart.

Best Answer

Related Solutions

Using SSRS line charts to graph time periods on the y-axis

SSRS group on only one value on a chart

Related Topic