SSRS multiple lines based on same data type - Line Chart

4
votes

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.

3
sqlsql-servertsqlreporting-servicesssrs-2008
If you want to get any help, you need to post at least an anonymised version of your SQL. - iamdave
@iamdave If you can help me, do you mind giving your email ID? I'll forward you the query. Thank you! - RMu
I'm afraid I do mind. Please take the time to post your SQL in a manner you feel suitable and we will take the time to look at it and try to help. Without knowing your name, company or database structure there is very little we can do to tie this query back to you or do anything malicious with it. If you feel you need to, rename the tables and remove any extra columns that aren't essential to joins or filtering before posting. - iamdave

3 Answers

3
votes

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

    =MonthName(Month(Fields!SomeDate.Value))

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

    =Month(Fields!SomeDate.Value)

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

2
votes

I think it would work the way you want if you return both months worth of data in your query.

Add a parameter for each date then add a filter to the data based on the parameters with either the query or the dataset.

Then add the month as a Series in your chart and Group On an expression like 

=FORMAT(Fields!Your_Date.Value, "YYYY-MM")
0
votes

What you need to do is:

1) Add one more data set which could define the range of the valid values for the month parameter.

2) Change the main data set, include the parameter in the WHERE condition (single value or multi-value).

3) Edit the parameter properties.

4) Edit the report field in the design pane.