Mixing
How do I graph 3 independent result values on a single chart in SSRS?
I have a survey with 3 questions which are answered with a rating from 1 to 5. My dataset includes a field for each question, Q1, Q2, and Q3. In my SSRS report, I want to show the results of the survey on a column chart where the x-axis has the numbers 1 through 5, and each number along that axis shows 3 columns, 1 per question/field, counting the number of responses of that rating.
I am no SSRS expert, but I can usually get it to do what I want. This seems like it should be easy, but I'm having a lot of trouble with it. I've done a lot of searching and can't seem to find a solution.
I can graph a single survey question with the following configuration:
Values: count(Q1) ;
Category Group: Q1
I can't figure out where to add the other questions so that they appear as a new column compared to the same x-axis values. Everything that I have tried results in evaluating the results for each answer combination in relation to each other. I want to show the results of each question independent of the others.
What I expect to end up with is 3 Values: count(Q1), count(Q2), and count(Q3), and a single category group containing the numbers 1-5.
I thought maybe I need to create a table array as a variable with the numbers 1-5 to use as my category group. And if that's the case, I can't figure out how to do that either.
Or maybe I'm going about this all wrong. Does what I'm doing automatically relate the values? Essentially what I want is for 3 independent graphs to be displayed on the same chart axis. Is this possible?
Crude example of the chart I'm trying to create
Sample data
sql
sql-server reporting-services asked Jan 2 at 16:49
ScottoScotto
34
add a comment |
I have a survey with 3 questions which are answered with a rating from 1 to 5. My dataset includes a field for each question, Q1, Q2, and Q3. In my SSRS report, I want to show the results of the survey on a column chart where the x-axis has the numbers 1 through 5, and each number along that axis shows 3 columns, 1 per question/field, counting the number of responses of that rating.
I am no SSRS expert, but I can usually get it to do what I want. This seems like it should be easy, but I'm having a lot of trouble with it. I've done a lot of searching and can't seem to find a solution.
I can graph a single survey question with the following configuration:
Values: count(Q1) ;
Category Group: Q1
I can't figure out where to add the other questions so that they appear as a new column compared to the same x-axis values. Everything that I have tried results in evaluating the results for each answer combination in relation to each other. I want to show the results of each question independent of the others.
What I expect to end up with is 3 Values: count(Q1), count(Q2), and count(Q3), and a single category group containing the numbers 1-5.
I thought maybe I need to create a table array as a variable with the numbers 1-5 to use as my category group. And if that's the case, I can't figure out how to do that either.
Or maybe I'm going about this all wrong. Does what I'm doing automatically relate the values? Essentially what I want is for 3 independent graphs to be displayed on the same chart axis. Is this possible?
Crude example of the chart I'm trying to create
Sample data
sql
sql-server reporting-services asked Jan 2 at 16:49
ScottoScotto
34
So to clarify you want a barchar similar to the one shown on this link (ignore the fact that its horizontal) docs.microsoft.com/en-us/sql/reporting-services/report-design/…
– Dale Burrell
Jan 2 at 21:27
Also please post a small representative set of your data for us to work with.
– Dale Burrell
Jan 2 at 21:27
add a comment |
I have a survey with 3 questions which are answered with a rating from 1 to 5. My dataset includes a field for each question, Q1, Q2, and Q3. In my SSRS report, I want to show the results of the survey on a column chart where the x-axis has the numbers 1 through 5, and each number along that axis shows 3 columns, 1 per question/field, counting the number of responses of that rating.
I am no SSRS expert, but I can usually get it to do what I want. This seems like it should be easy, but I'm having a lot of trouble with it. I've done a lot of searching and can't seem to find a solution.
I can graph a single survey question with the following configuration:
Values: count(Q1) ;
Category Group: Q1
I can't figure out where to add the other questions so that they appear as a new column compared to the same x-axis values. Everything that I have tried results in evaluating the results for each answer combination in relation to each other. I want to show the results of each question independent of the others.
What I expect to end up with is 3 Values: count(Q1), count(Q2), and count(Q3), and a single category group containing the numbers 1-5.
I thought maybe I need to create a table array as a variable with the numbers 1-5 to use as my category group. And if that's the case, I can't figure out how to do that either.
Or maybe I'm going about this all wrong. Does what I'm doing automatically relate the values? Essentially what I want is for 3 independent graphs to be displayed on the same chart axis. Is this possible?
Crude example of the chart I'm trying to create
Sample data
sql
sql-server reporting-services asked Jan 2 at 16:49
ScottoScotto
34
I have a survey with 3 questions which are answered with a rating from 1 to 5. My dataset includes a field for each question, Q1, Q2, and Q3. In my SSRS report, I want to show the results of the survey on a column chart where the x-axis has the numbers 1 through 5, and each number along that axis shows 3 columns, 1 per question/field, counting the number of responses of that rating.
I am no SSRS expert, but I can usually get it to do what I want. This seems like it should be easy, but I'm having a lot of trouble with it. I've done a lot of searching and can't seem to find a solution.
I can graph a single survey question with the following configuration:
Values: count(Q1) ;
Category Group: Q1
I can't figure out where to add the other questions so that they appear as a new column compared to the same x-axis values. Everything that I have tried results in evaluating the results for each answer combination in relation to each other. I want to show the results of each question independent of the others.
What I expect to end up with is 3 Values: count(Q1), count(Q2), and count(Q3), and a single category group containing the numbers 1-5.
I thought maybe I need to create a table array as a variable with the numbers 1-5 to use as my category group. And if that's the case, I can't figure out how to do that either.
Or maybe I'm going about this all wrong. Does what I'm doing automatically relate the values? Essentially what I want is for 3 independent graphs to be displayed on the same chart axis. Is this possible?
Crude example of the chart I'm trying to create
Sample data
sql
sql-server reporting-services sql
sql-server reporting-services asked Jan 2 at 16:49
ScottoScotto
34
asked Jan 2 at 16:49
ScottoScotto
34
edited Jan 3 at 19:34
Scotto
asked Jan 2 at 16:49
ScottoScotto
34
asked Jan 2 at 16:49
ScottoScotto
34
asked Jan 2 at 16:49
ScottoScotto
34
34
So to clarify you want a barchar similar to the one shown on this link (ignore the fact that its horizontal) docs.microsoft.com/en-us/sql/reporting-services/report-design/…
– Dale Burrell
Jan 2 at 21:27
Also please post a small representative set of your data for us to work with.
– Dale Burrell
Jan 2 at 21:27
add a comment |
So to clarify you want a barchar similar to the one shown on this link (ignore the fact that its horizontal) docs.microsoft.com/en-us/sql/reporting-services/report-design/…
– Dale Burrell
Jan 2 at 21:27
Also please post a small representative set of your data for us to work with.
– Dale Burrell
Jan 2 at 21:27
So to clarify you want a barchar similar to the one shown on this link (ignore the fact that its horizontal) docs.microsoft.com/en-us/sql/reporting-services/report-design/…
– Dale Burrell
Jan 2 at 21:27
So to clarify you want a barchar similar to the one shown on this link (ignore the fact that its horizontal) docs.microsoft.com/en-us/sql/reporting-services/report-design/…
– Dale Burrell
Jan 2 at 21:27
Also please post a small representative set of your data for us to work with.
– Dale Burrell
Jan 2 at 21:27
Also please post a small representative set of your data for us to work with.
– Dale Burrell
Jan 2 at 21:27
add a comment |
2 Answers
2
active
oldest
votes
I would do the work in SQL and pass simple results to the chart.
I recreated your sample data (or or less) then used UNPIVOT to get the resluts into a better structure for the chart to consume.
Here's the T-SQL I used to recreate and convert the sample data...
DECLARE @t TABLE(ResponseID int, Q1 int, Q2 int, Q3 int)
INSERT INTO @t VALUES
(1,2,2,2), (2,5,5,5), (3,1,5,3), (4,4,2,5), (5,4,3,4),
(6,5,4,4), (7,2,2,5), (8,3,2,1), (9,1,5,2), (10,3,1,4),
(11,3,1,1), (12,4,4,2), (13,2,4,4), (14,1,1,5), (15,2,4,2),
(16,4,4,4), (17,5,1,3), (18,2,2,1), (19,5,2,1), (20,4,2,5),
(21,2,3,2), (22,1,5,3), (23,5,1,1), (24,4,1,1), (25,2,3,2),
(26,3,5,1)
SELECT Question, Score, COUNT(*) AS ScoreCount
FROM
(
SELECT ResponseID, Question, Score
FROM
(SELECT ResponseID, Q1, Q2, Q3 FROM @t) p
UNPIVOT
(Score FOR Question IN (Q1, Q2, Q3)
) AS unpvt
) q
GROUP BY Question, Score
ORDER BY Question, Score
This gives us the following results..
Question Score ScoreCount
Q1 1 4
Q1 2 7
Q1 3 4
Q1 4 6
Q1 5 5
Q2 1 6
Q2 2 7
Q2 3 3
Q2 4 5
Q2 5 5
Q3 1 7
Q3 2 6
Q3 3 3
Q3 4 5
Q3 5 5
Then I just added a simple column chart, dragged ScoreCount to the values, Score to the Category Groups and Question to the Series Groups.
The design looks like this..
And the final output looks like this...
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
Thank you! This is exactly what I was needing. I didn't realize the format of the table needed to change. The problem I have now is that my data source is a SharePoint list, and apparently I can't manipulate the SharePoint query in this way. I guess I should have mentioned that, but I didn't think it mattered.
– Scotto
Jan 4 at 15:05
AH sorry, I didn't realise. I don't work with SharePoint but I don't think you can do much with the data directly in SSRS. I'm guessing but the best approach might be to try to get the data in the correct format directly from SharePoint or automate a scheduled process that takes the data and dumps it out somewhere in a relational table that you can manipulate as above. I did a quick search forUNPIVOT SharePoint listand there are plenty of questions, just no answers! Good luck
– Alan Schofield
Jan 4 at 15:34
I thought plotting 3 values from the same row would be easy, and I guess if I were able to use the Unpivot function in my query it wouldn't be so bad. But with SharePoint data I did end up creating a new list for my report data and scheduling a workflow to dump the survey results to the new list. Once the data was serialized like that formatting the chart was easy. Thanks again for the help.
– Scotto
Jan 7 at 13:56
No problem, glad you got it sorted.
– Alan Schofield
Jan 7 at 14:23
add a comment |
If you want 3 independent graphs for three separate graphs, you can do like this. This may be little long but I have provided 2 cases for your question. I created this table for SSRS report, and then used it in report. I assumed every fields are populated (ratings 1-5 everytime) (Scenario 1) If not I have provided another solution below (Scenario 2).
Scenario 1:
Table creation:
Create table ssrsQuestion (Question varchar(250), Ratings int, Numberofpeople int)
insert into ssrsQuestion values ('Q1', 1, 5) ,
('Q1', 2, 30) ,
('Q1', 3, 40) ,
('Q1', 4, 56) ,
('Q1', 5, 100) ,
('Q2', 1, 15) ,
('Q2', 2, 30) ,
('Q2', 3, 50) ,
('Q2', 4, 80) ,
('Q2', 5, 120) ,
('Q3', 1, 10) ,
('Q3', 2, 20) ,
('Q3', 3, 30) ,
('Q3', 4, 40) ,
('Q3', 5, 50)
Then you can use this query in SSRS report. I have used sum, probably you might have to use count.
select Question, ratings, sum(Numberofpeople) totalnumbers from ssrsQuestion
group by Question, ratings
order by Question, Ratings;
After that you can add values to the graph in this way. Since you need 3 graphs, right click on Chart properties and add filter for each question.
your output will be in this way filtered on each question.
Scenario 2:
If your ratings fields are not populated all the time which may be expected. You can use this to show 1-5 every time.
Create tables to hold ratings and Question and then left join the original table. First lets delete the records from original table.
delete from ssrsQuestion
where Question in ('Q3', 'Q2') and Ratings in (1,3)
Create a static value table for ratings and Question.
create table ratings (ratings int) insert into ratings select 1 union select 2 union
select 3 union Select 4 union select 5
create table Questions (Question varchar(250)) insert into Questions select 'Q1'
union Select 'Q2' union select 'Q3'
Then Use this Query in SSRS, it will give records where you have no records for people response as well.
select q.Question, r.ratings, sum(Numberofpeople) totalnumbers from Questions q
cross join ratings r
left join ssrsQuestion sr on sr.question = q.question and r.ratings = sr.ratings
group by q.Question, r.ratings
order by q.Question, r.ratings
Then refresh the fields and your SSRS report graph will look like this (even if there is no data it will show the field, we can see for Question 2 and 3)
answered Jan 3 at 2:30
AviAvi
924314
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010146%2fhow-do-i-graph-3-independent-result-values-on-a-single-chart-in-ssrs%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I would do the work in SQL and pass simple results to the chart.
I recreated your sample data (or or less) then used UNPIVOT to get the resluts into a better structure for the chart to consume.
Here's the T-SQL I used to recreate and convert the sample data...
DECLARE @t TABLE(ResponseID int, Q1 int, Q2 int, Q3 int)
INSERT INTO @t VALUES
(1,2,2,2), (2,5,5,5), (3,1,5,3), (4,4,2,5), (5,4,3,4),
(6,5,4,4), (7,2,2,5), (8,3,2,1), (9,1,5,2), (10,3,1,4),
(11,3,1,1), (12,4,4,2), (13,2,4,4), (14,1,1,5), (15,2,4,2),
(16,4,4,4), (17,5,1,3), (18,2,2,1), (19,5,2,1), (20,4,2,5),
(21,2,3,2), (22,1,5,3), (23,5,1,1), (24,4,1,1), (25,2,3,2),
(26,3,5,1)
SELECT Question, Score, COUNT(*) AS ScoreCount
FROM
(
SELECT ResponseID, Question, Score
FROM
(SELECT ResponseID, Q1, Q2, Q3 FROM @t) p
UNPIVOT
(Score FOR Question IN (Q1, Q2, Q3)
) AS unpvt
) q
GROUP BY Question, Score
ORDER BY Question, Score
This gives us the following results..
Question Score ScoreCount
Q1 1 4
Q1 2 7
Q1 3 4
Q1 4 6
Q1 5 5
Q2 1 6
Q2 2 7
Q2 3 3
Q2 4 5
Q2 5 5
Q3 1 7
Q3 2 6
Q3 3 3
Q3 4 5
Q3 5 5
Then I just added a simple column chart, dragged ScoreCount to the values, Score to the Category Groups and Question to the Series Groups.
The design looks like this..
And the final output looks like this...
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
Thank you! This is exactly what I was needing. I didn't realize the format of the table needed to change. The problem I have now is that my data source is a SharePoint list, and apparently I can't manipulate the SharePoint query in this way. I guess I should have mentioned that, but I didn't think it mattered.
– Scotto
Jan 4 at 15:05
AH sorry, I didn't realise. I don't work with SharePoint but I don't think you can do much with the data directly in SSRS. I'm guessing but the best approach might be to try to get the data in the correct format directly from SharePoint or automate a scheduled process that takes the data and dumps it out somewhere in a relational table that you can manipulate as above. I did a quick search forUNPIVOT SharePoint listand there are plenty of questions, just no answers! Good luck
– Alan Schofield
Jan 4 at 15:34
I thought plotting 3 values from the same row would be easy, and I guess if I were able to use the Unpivot function in my query it wouldn't be so bad. But with SharePoint data I did end up creating a new list for my report data and scheduling a workflow to dump the survey results to the new list. Once the data was serialized like that formatting the chart was easy. Thanks again for the help.
– Scotto
Jan 7 at 13:56
No problem, glad you got it sorted.
– Alan Schofield
Jan 7 at 14:23
add a comment |
I would do the work in SQL and pass simple results to the chart.
I recreated your sample data (or or less) then used UNPIVOT to get the resluts into a better structure for the chart to consume.
Here's the T-SQL I used to recreate and convert the sample data...
DECLARE @t TABLE(ResponseID int, Q1 int, Q2 int, Q3 int)
INSERT INTO @t VALUES
(1,2,2,2), (2,5,5,5), (3,1,5,3), (4,4,2,5), (5,4,3,4),
(6,5,4,4), (7,2,2,5), (8,3,2,1), (9,1,5,2), (10,3,1,4),
(11,3,1,1), (12,4,4,2), (13,2,4,4), (14,1,1,5), (15,2,4,2),
(16,4,4,4), (17,5,1,3), (18,2,2,1), (19,5,2,1), (20,4,2,5),
(21,2,3,2), (22,1,5,3), (23,5,1,1), (24,4,1,1), (25,2,3,2),
(26,3,5,1)
SELECT Question, Score, COUNT(*) AS ScoreCount
FROM
(
SELECT ResponseID, Question, Score
FROM
(SELECT ResponseID, Q1, Q2, Q3 FROM @t) p
UNPIVOT
(Score FOR Question IN (Q1, Q2, Q3)
) AS unpvt
) q
GROUP BY Question, Score
ORDER BY Question, Score
This gives us the following results..
Question Score ScoreCount
Q1 1 4
Q1 2 7
Q1 3 4
Q1 4 6
Q1 5 5
Q2 1 6
Q2 2 7
Q2 3 3
Q2 4 5
Q2 5 5
Q3 1 7
Q3 2 6
Q3 3 3
Q3 4 5
Q3 5 5
Then I just added a simple column chart, dragged ScoreCount to the values, Score to the Category Groups and Question to the Series Groups.
The design looks like this..
And the final output looks like this...
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
Thank you! This is exactly what I was needing. I didn't realize the format of the table needed to change. The problem I have now is that my data source is a SharePoint list, and apparently I can't manipulate the SharePoint query in this way. I guess I should have mentioned that, but I didn't think it mattered.
– Scotto
Jan 4 at 15:05
AH sorry, I didn't realise. I don't work with SharePoint but I don't think you can do much with the data directly in SSRS. I'm guessing but the best approach might be to try to get the data in the correct format directly from SharePoint or automate a scheduled process that takes the data and dumps it out somewhere in a relational table that you can manipulate as above. I did a quick search forUNPIVOT SharePoint listand there are plenty of questions, just no answers! Good luck
– Alan Schofield
Jan 4 at 15:34
I thought plotting 3 values from the same row would be easy, and I guess if I were able to use the Unpivot function in my query it wouldn't be so bad. But with SharePoint data I did end up creating a new list for my report data and scheduling a workflow to dump the survey results to the new list. Once the data was serialized like that formatting the chart was easy. Thanks again for the help.
– Scotto
Jan 7 at 13:56
No problem, glad you got it sorted.
– Alan Schofield
Jan 7 at 14:23
add a comment |
I would do the work in SQL and pass simple results to the chart.
I recreated your sample data (or or less) then used UNPIVOT to get the resluts into a better structure for the chart to consume.
Here's the T-SQL I used to recreate and convert the sample data...
DECLARE @t TABLE(ResponseID int, Q1 int, Q2 int, Q3 int)
INSERT INTO @t VALUES
(1,2,2,2), (2,5,5,5), (3,1,5,3), (4,4,2,5), (5,4,3,4),
(6,5,4,4), (7,2,2,5), (8,3,2,1), (9,1,5,2), (10,3,1,4),
(11,3,1,1), (12,4,4,2), (13,2,4,4), (14,1,1,5), (15,2,4,2),
(16,4,4,4), (17,5,1,3), (18,2,2,1), (19,5,2,1), (20,4,2,5),
(21,2,3,2), (22,1,5,3), (23,5,1,1), (24,4,1,1), (25,2,3,2),
(26,3,5,1)
SELECT Question, Score, COUNT(*) AS ScoreCount
FROM
(
SELECT ResponseID, Question, Score
FROM
(SELECT ResponseID, Q1, Q2, Q3 FROM @t) p
UNPIVOT
(Score FOR Question IN (Q1, Q2, Q3)
) AS unpvt
) q
GROUP BY Question, Score
ORDER BY Question, Score
This gives us the following results..
Question Score ScoreCount
Q1 1 4
Q1 2 7
Q1 3 4
Q1 4 6
Q1 5 5
Q2 1 6
Q2 2 7
Q2 3 3
Q2 4 5
Q2 5 5
Q3 1 7
Q3 2 6
Q3 3 3
Q3 4 5
Q3 5 5
Then I just added a simple column chart, dragged ScoreCount to the values, Score to the Category Groups and Question to the Series Groups.
The design looks like this..
And the final output looks like this...
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
I would do the work in SQL and pass simple results to the chart.
I recreated your sample data (or or less) then used UNPIVOT to get the resluts into a better structure for the chart to consume.
Here's the T-SQL I used to recreate and convert the sample data...
DECLARE @t TABLE(ResponseID int, Q1 int, Q2 int, Q3 int)
INSERT INTO @t VALUES
(1,2,2,2), (2,5,5,5), (3,1,5,3), (4,4,2,5), (5,4,3,4),
(6,5,4,4), (7,2,2,5), (8,3,2,1), (9,1,5,2), (10,3,1,4),
(11,3,1,1), (12,4,4,2), (13,2,4,4), (14,1,1,5), (15,2,4,2),
(16,4,4,4), (17,5,1,3), (18,2,2,1), (19,5,2,1), (20,4,2,5),
(21,2,3,2), (22,1,5,3), (23,5,1,1), (24,4,1,1), (25,2,3,2),
(26,3,5,1)
SELECT Question, Score, COUNT(*) AS ScoreCount
FROM
(
SELECT ResponseID, Question, Score
FROM
(SELECT ResponseID, Q1, Q2, Q3 FROM @t) p
UNPIVOT
(Score FOR Question IN (Q1, Q2, Q3)
) AS unpvt
) q
GROUP BY Question, Score
ORDER BY Question, Score
This gives us the following results..
Question Score ScoreCount
Q1 1 4
Q1 2 7
Q1 3 4
Q1 4 6
Q1 5 5
Q2 1 6
Q2 2 7
Q2 3 3
Q2 4 5
Q2 5 5
Q3 1 7
Q3 2 6
Q3 3 3
Q3 4 5
Q3 5 5
Then I just added a simple column chart, dragged ScoreCount to the values, Score to the Category Groups and Question to the Series Groups.
The design looks like this..
And the final output looks like this...
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
answered Jan 3 at 23:01
Alan SchofieldAlan Schofield
6,49511021
6,49511021
Thank you! This is exactly what I was needing. I didn't realize the format of the table needed to change. The problem I have now is that my data source is a SharePoint list, and apparently I can't manipulate the SharePoint query in this way. I guess I should have mentioned that, but I didn't think it mattered.
– Scotto
Jan 4 at 15:05
AH sorry, I didn't realise. I don't work with SharePoint but I don't think you can do much with the data directly in SSRS. I'm guessing but the best approach might be to try to get the data in the correct format directly from SharePoint or automate a scheduled process that takes the data and dumps it out somewhere in a relational table that you can manipulate as above. I did a quick search forUNPIVOT SharePoint listand there are plenty of questions, just no answers! Good luck
– Alan Schofield
Jan 4 at 15:34
I thought plotting 3 values from the same row would be easy, and I guess if I were able to use the Unpivot function in my query it wouldn't be so bad. But with SharePoint data I did end up creating a new list for my report data and scheduling a workflow to dump the survey results to the new list. Once the data was serialized like that formatting the chart was easy. Thanks again for the help.
– Scotto
Jan 7 at 13:56
No problem, glad you got it sorted.
– Alan Schofield
Jan 7 at 14:23
add a comment |
Thank you! This is exactly what I was needing. I didn't realize the format of the table needed to change. The problem I have now is that my data source is a SharePoint list, and apparently I can't manipulate the SharePoint query in this way. I guess I should have mentioned that, but I didn't think it mattered.
– Scotto
Jan 4 at 15:05
AH sorry, I didn't realise. I don't work with SharePoint but I don't think you can do much with the data directly in SSRS. I'm guessing but the best approach might be to try to get the data in the correct format directly from SharePoint or automate a scheduled process that takes the data and dumps it out somewhere in a relational table that you can manipulate as above. I did a quick search forUNPIVOT SharePoint listand there are plenty of questions, just no answers! Good luck
– Alan Schofield
Jan 4 at 15:34
I thought plotting 3 values from the same row would be easy, and I guess if I were able to use the Unpivot function in my query it wouldn't be so bad. But with SharePoint data I did end up creating a new list for my report data and scheduling a workflow to dump the survey results to the new list. Once the data was serialized like that formatting the chart was easy. Thanks again for the help.
– Scotto
Jan 7 at 13:56
No problem, glad you got it sorted.
– Alan Schofield
Jan 7 at 14:23
Thank you! This is exactly what I was needing. I didn't realize the format of the table needed to change. The problem I have now is that my data source is a SharePoint list, and apparently I can't manipulate the SharePoint query in this way. I guess I should have mentioned that, but I didn't think it mattered.
– Scotto
Jan 4 at 15:05
Thank you! This is exactly what I was needing. I didn't realize the format of the table needed to change. The problem I have now is that my data source is a SharePoint list, and apparently I can't manipulate the SharePoint query in this way. I guess I should have mentioned that, but I didn't think it mattered.
– Scotto
Jan 4 at 15:05
AH sorry, I didn't realise. I don't work with SharePoint but I don't think you can do much with the data directly in SSRS. I'm guessing but the best approach might be to try to get the data in the correct format directly from SharePoint or automate a scheduled process that takes the data and dumps it out somewhere in a relational table that you can manipulate as above. I did a quick search for
UNPIVOT SharePoint list and there are plenty of questions, just no answers! Good luck– Alan Schofield
Jan 4 at 15:34
AH sorry, I didn't realise. I don't work with SharePoint but I don't think you can do much with the data directly in SSRS. I'm guessing but the best approach might be to try to get the data in the correct format directly from SharePoint or automate a scheduled process that takes the data and dumps it out somewhere in a relational table that you can manipulate as above. I did a quick search for
UNPIVOT SharePoint list and there are plenty of questions, just no answers! Good luck– Alan Schofield
Jan 4 at 15:34
I thought plotting 3 values from the same row would be easy, and I guess if I were able to use the Unpivot function in my query it wouldn't be so bad. But with SharePoint data I did end up creating a new list for my report data and scheduling a workflow to dump the survey results to the new list. Once the data was serialized like that formatting the chart was easy. Thanks again for the help.
– Scotto
Jan 7 at 13:56
I thought plotting 3 values from the same row would be easy, and I guess if I were able to use the Unpivot function in my query it wouldn't be so bad. But with SharePoint data I did end up creating a new list for my report data and scheduling a workflow to dump the survey results to the new list. Once the data was serialized like that formatting the chart was easy. Thanks again for the help.
– Scotto
Jan 7 at 13:56
No problem, glad you got it sorted.
– Alan Schofield
Jan 7 at 14:23
No problem, glad you got it sorted.
– Alan Schofield
Jan 7 at 14:23
add a comment |
If you want 3 independent graphs for three separate graphs, you can do like this. This may be little long but I have provided 2 cases for your question. I created this table for SSRS report, and then used it in report. I assumed every fields are populated (ratings 1-5 everytime) (Scenario 1) If not I have provided another solution below (Scenario 2).
Scenario 1:
Table creation:
Create table ssrsQuestion (Question varchar(250), Ratings int, Numberofpeople int)
insert into ssrsQuestion values ('Q1', 1, 5) ,
('Q1', 2, 30) ,
('Q1', 3, 40) ,
('Q1', 4, 56) ,
('Q1', 5, 100) ,
('Q2', 1, 15) ,
('Q2', 2, 30) ,
('Q2', 3, 50) ,
('Q2', 4, 80) ,
('Q2', 5, 120) ,
('Q3', 1, 10) ,
('Q3', 2, 20) ,
('Q3', 3, 30) ,
('Q3', 4, 40) ,
('Q3', 5, 50)
Then you can use this query in SSRS report. I have used sum, probably you might have to use count.
select Question, ratings, sum(Numberofpeople) totalnumbers from ssrsQuestion
group by Question, ratings
order by Question, Ratings;
After that you can add values to the graph in this way. Since you need 3 graphs, right click on Chart properties and add filter for each question.
your output will be in this way filtered on each question.
Scenario 2:
If your ratings fields are not populated all the time which may be expected. You can use this to show 1-5 every time.
Create tables to hold ratings and Question and then left join the original table. First lets delete the records from original table.
delete from ssrsQuestion
where Question in ('Q3', 'Q2') and Ratings in (1,3)
Create a static value table for ratings and Question.
create table ratings (ratings int) insert into ratings select 1 union select 2 union
select 3 union Select 4 union select 5
create table Questions (Question varchar(250)) insert into Questions select 'Q1'
union Select 'Q2' union select 'Q3'
Then Use this Query in SSRS, it will give records where you have no records for people response as well.
select q.Question, r.ratings, sum(Numberofpeople) totalnumbers from Questions q
cross join ratings r
left join ssrsQuestion sr on sr.question = q.question and r.ratings = sr.ratings
group by q.Question, r.ratings
order by q.Question, r.ratings
Then refresh the fields and your SSRS report graph will look like this (even if there is no data it will show the field, we can see for Question 2 and 3)
answered Jan 3 at 2:30
AviAvi
924314
add a comment |
If you want 3 independent graphs for three separate graphs, you can do like this. This may be little long but I have provided 2 cases for your question. I created this table for SSRS report, and then used it in report. I assumed every fields are populated (ratings 1-5 everytime) (Scenario 1) If not I have provided another solution below (Scenario 2).
Scenario 1:
Table creation:
Create table ssrsQuestion (Question varchar(250), Ratings int, Numberofpeople int)
insert into ssrsQuestion values ('Q1', 1, 5) ,
('Q1', 2, 30) ,
('Q1', 3, 40) ,
('Q1', 4, 56) ,
('Q1', 5, 100) ,
('Q2', 1, 15) ,
('Q2', 2, 30) ,
('Q2', 3, 50) ,
('Q2', 4, 80) ,
('Q2', 5, 120) ,
('Q3', 1, 10) ,
('Q3', 2, 20) ,
('Q3', 3, 30) ,
('Q3', 4, 40) ,
('Q3', 5, 50)
Then you can use this query in SSRS report. I have used sum, probably you might have to use count.
select Question, ratings, sum(Numberofpeople) totalnumbers from ssrsQuestion
group by Question, ratings
order by Question, Ratings;
After that you can add values to the graph in this way. Since you need 3 graphs, right click on Chart properties and add filter for each question.
your output will be in this way filtered on each question.
Scenario 2:
If your ratings fields are not populated all the time which may be expected. You can use this to show 1-5 every time.
Create tables to hold ratings and Question and then left join the original table. First lets delete the records from original table.
delete from ssrsQuestion
where Question in ('Q3', 'Q2') and Ratings in (1,3)
Create a static value table for ratings and Question.
create table ratings (ratings int) insert into ratings select 1 union select 2 union
select 3 union Select 4 union select 5
create table Questions (Question varchar(250)) insert into Questions select 'Q1'
union Select 'Q2' union select 'Q3'
Then Use this Query in SSRS, it will give records where you have no records for people response as well.
select q.Question, r.ratings, sum(Numberofpeople) totalnumbers from Questions q
cross join ratings r
left join ssrsQuestion sr on sr.question = q.question and r.ratings = sr.ratings
group by q.Question, r.ratings
order by q.Question, r.ratings
Then refresh the fields and your SSRS report graph will look like this (even if there is no data it will show the field, we can see for Question 2 and 3)
answered Jan 3 at 2:30
AviAvi
924314
add a comment |
If you want 3 independent graphs for three separate graphs, you can do like this. This may be little long but I have provided 2 cases for your question. I created this table for SSRS report, and then used it in report. I assumed every fields are populated (ratings 1-5 everytime) (Scenario 1) If not I have provided another solution below (Scenario 2).
Scenario 1:
Table creation:
Create table ssrsQuestion (Question varchar(250), Ratings int, Numberofpeople int)
insert into ssrsQuestion values ('Q1', 1, 5) ,
('Q1', 2, 30) ,
('Q1', 3, 40) ,
('Q1', 4, 56) ,
('Q1', 5, 100) ,
('Q2', 1, 15) ,
('Q2', 2, 30) ,
('Q2', 3, 50) ,
('Q2', 4, 80) ,
('Q2', 5, 120) ,
('Q3', 1, 10) ,
('Q3', 2, 20) ,
('Q3', 3, 30) ,
('Q3', 4, 40) ,
('Q3', 5, 50)
Then you can use this query in SSRS report. I have used sum, probably you might have to use count.
select Question, ratings, sum(Numberofpeople) totalnumbers from ssrsQuestion
group by Question, ratings
order by Question, Ratings;
After that you can add values to the graph in this way. Since you need 3 graphs, right click on Chart properties and add filter for each question.
your output will be in this way filtered on each question.
Scenario 2:
If your ratings fields are not populated all the time which may be expected. You can use this to show 1-5 every time.
Create tables to hold ratings and Question and then left join the original table. First lets delete the records from original table.
delete from ssrsQuestion
where Question in ('Q3', 'Q2') and Ratings in (1,3)
Create a static value table for ratings and Question.
create table ratings (ratings int) insert into ratings select 1 union select 2 union
select 3 union Select 4 union select 5
create table Questions (Question varchar(250)) insert into Questions select 'Q1'
union Select 'Q2' union select 'Q3'
Then Use this Query in SSRS, it will give records where you have no records for people response as well.
select q.Question, r.ratings, sum(Numberofpeople) totalnumbers from Questions q
cross join ratings r
left join ssrsQuestion sr on sr.question = q.question and r.ratings = sr.ratings
group by q.Question, r.ratings
order by q.Question, r.ratings
Then refresh the fields and your SSRS report graph will look like this (even if there is no data it will show the field, we can see for Question 2 and 3)
answered Jan 3 at 2:30
AviAvi
924314
If you want 3 independent graphs for three separate graphs, you can do like this. This may be little long but I have provided 2 cases for your question. I created this table for SSRS report, and then used it in report. I assumed every fields are populated (ratings 1-5 everytime) (Scenario 1) If not I have provided another solution below (Scenario 2).
Scenario 1:
Table creation:
Create table ssrsQuestion (Question varchar(250), Ratings int, Numberofpeople int)
insert into ssrsQuestion values ('Q1', 1, 5) ,
('Q1', 2, 30) ,
('Q1', 3, 40) ,
('Q1', 4, 56) ,
('Q1', 5, 100) ,
('Q2', 1, 15) ,
('Q2', 2, 30) ,
('Q2', 3, 50) ,
('Q2', 4, 80) ,
('Q2', 5, 120) ,
('Q3', 1, 10) ,
('Q3', 2, 20) ,
('Q3', 3, 30) ,
('Q3', 4, 40) ,
('Q3', 5, 50)
Then you can use this query in SSRS report. I have used sum, probably you might have to use count.
select Question, ratings, sum(Numberofpeople) totalnumbers from ssrsQuestion
group by Question, ratings
order by Question, Ratings;
After that you can add values to the graph in this way. Since you need 3 graphs, right click on Chart properties and add filter for each question.
your output will be in this way filtered on each question.
Scenario 2:
If your ratings fields are not populated all the time which may be expected. You can use this to show 1-5 every time.
Create tables to hold ratings and Question and then left join the original table. First lets delete the records from original table.
delete from ssrsQuestion
where Question in ('Q3', 'Q2') and Ratings in (1,3)
Create a static value table for ratings and Question.
create table ratings (ratings int) insert into ratings select 1 union select 2 union
select 3 union Select 4 union select 5
create table Questions (Question varchar(250)) insert into Questions select 'Q1'
union Select 'Q2' union select 'Q3'
Then Use this Query in SSRS, it will give records where you have no records for people response as well.
select q.Question, r.ratings, sum(Numberofpeople) totalnumbers from Questions q
cross join ratings r
left join ssrsQuestion sr on sr.question = q.question and r.ratings = sr.ratings
group by q.Question, r.ratings
order by q.Question, r.ratings
Then refresh the fields and your SSRS report graph will look like this (even if there is no data it will show the field, we can see for Question 2 and 3)
answered Jan 3 at 2:30
AviAvi
924314
answered Jan 3 at 2:30
AviAvi
924314
answered Jan 3 at 2:30
AviAvi
924314
answered Jan 3 at 2:30
AviAvi
924314
924314
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54010146%2fhow-do-i-graph-3-independent-result-values-on-a-single-chart-in-ssrs%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So to clarify you want a barchar similar to the one shown on this link (ignore the fact that its horizontal) docs.microsoft.com/en-us/sql/reporting-services/report-design/…
– Dale Burrell
Jan 2 at 21:27
Also please post a small representative set of your data for us to work with.
– Dale Burrell
Jan 2 at 21:27