Line chart from table containing static values
-
Hello everyone!
I have a SSRS 2016 report, which contains a dataset that produces a basic table as follows:Department 2014 2015 2016 2017
======== ==== ==== ==== ====
Department A 20 21 87 37
Department B 84 72 94 72
Department C 94 73 82 93The values are percentages for the years 2014-2017.
I'd like to add a regular line chart using the values as they appear in this table, with no aggregation. So, with 2014, 2015, 2016 and 2017 on the x-axis, percentage on the y-axis and 3 lines plotted showing the 4 values for each of the departments.
It sounds like a straightforward process, but I'm having trouble doing this, for reasons I can't work out. No matter how I try to structure the line chart, it never displays correctly. I'm guessing this is something to with SSRS trying to aggregate the values.
EDIT: I should add that the dataset uses a query consisting of 3 UNION'd queries, to return the 3 rows for Department A, B and C.
Any ideas? Is there an easier method I should be trying.
Many thanks,
Innerise -
Have you followed the steps as shown here: https://www.tutorialgateway.org/line-chart-in-ssrs/ ?
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
Hi
I am following a similar process. However, I don't want to do any aggregation, as per this part:Values: Any Numeric (Metric) value such as Total Sales, Sale amount, Number of Customers, Tax etc. All these values will be aggregated using aggregate function (Sum, Avg, Count etc). This is because we are grouping them with the category group items.
I want the values to be plotted as they already are in the table.
- Innerise - Friday, February 16, 2018 8:54 AM
The items that you are plotting have already been aggregated. Reapplying the same aggregate is not going to change the final results.
Another way to think about this, is that the result of aggregating a single value is exactly the same as that single value for certain aggregates (including SUM()). You only have a single value for each data point, because your data has already been aggregated.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
OK, that makes sense. I have this, so far:
The three departments are being shown as the Series, which is what I want.
However, I just can't seem to get the 4 years along the x-axis.
The chart data properties looks like this:
No amount of swapping around seems to display the 4 years along the bottom, with the values plotted as lines 🙁 -
To ensure that I'm not losing my mind, I created the above table in Excel and was able to create the line-chart I need in a few seconds, with minimal fuss:
If I have a dataset in SSRS that produces an identical table to the one above, why is it so difficult to produce a similar line chart in SSRS?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply