Ramya Ramya - 1 year ago 115
SQL Question

SSRS multiple lines based on same data type - Line Chart

enter image description hereI have a TSQL Query which gives a month data for some performance baselines. I'm showing the data in a line chart. Now i would like to add few more parameters in the report to give options to choose data from two different months and show it in the same line chart for comparison. I'm not sure how to begin with. Any ideas on how i have to get the data set and display in the line chart would be helpful. Any links which simulate the expected results would be helpful as well.

Edit: I have brought data for two months in the same dataset and when i do series grouping expression like @Hannover Fist said i get below graph. But when i do expression with category grouping i Do not get overlapping graph because something goes wayhire with the dates! Please guide what i'm doing wrong! Below is my expected graph.

enter image description here
Note: The query it too big and I can not post it here for security reasons. Please let me know if you need any information about the query.

Answer Source

You want to have a simple set of dates on the horizontal axis (category group) and then use the month as a series group.

If possible, return the day of month and the month as separate columns in the dataset. Let the database do what the database is good at. Using AdventureWorksDW as an example,

select ..., 
datepart(day, fulldatealternatekey) as Day, 
left(datename(month, fulldatealternatekey), 3) as Month, 
sum(SomeValue) as SomeValue
from ...

Then in your line chart, set Values as Sum(SomeValue), set Category Groups as Day and Series Group as Month. That should pretty well do it. It might try to set the axis labels for every 5 days (5, 10, 15, etc.), but you can override this by going to the Axis Options in the Horizontal Axis Properties and changing Interval to 1 (just type in the value to replace Auto).

If you cannot change the dataset query, then use expressions to accomplish the same thing:

  • For Category groups, use your date. But then right-click on it in the Chart pane and open Category Group Properties. Click the expression button next to label and set this expression (replacing SomeDate with your actual Date column) to

    =DatePart("d", Fields!SomeDate.Value)

  • Repeat for the Group On expression for category group.

  • Next, add your date as a Series group, but right-click on it and open Series Group properties. Change the Label and Group On expressions to


  • Set the Sorting for Series Group if necessary for the month. Just use instead:


Here's what I got - and am assuming this was the structure you are looking for. It's a bit unclear in your question, but this is my take on what you're asking for. ssrs example

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download