November 11, 2007 at 7:44 pm
Hi all.
I'm drawing a complete blank and have been for over a week now. I'm building a SQL 2005 report using Business Intelligence in Visual Studio 2005. I didn't build the DB table and thinking this could be my problem, but I'm asking for assistance if anyone could help.
Database info:
One table with these fields.... ID, surveyNumber, Q1, Q2, Q3, through Q12.
Each question field (Ex. Q3) has an answer of "Yes" or "No".
The graph I'm needing to create should have single vertical bars, one for each question. The questions (Q1, Q2, etc) should be the titles on the x-axis and the number of "Yes"s should be reflected along the y-axis. I don't need to reflect the "No" answers at all. Once built, I had planned on changing the Q1, Q2, etc to more meaningful titles. Also, a percentage for each question should be noted at the top of each bar calculating the percentage of "Yes"s for all the surveys taken (this was a bit hard to explain and hope you understand. I believe I might already have the answer to this part).
Anyways, I hope I've explained this well enough for someone to understand and also hope there's an answer for my issue. If it's an easy answer, please be easy as I'm a newbie to the whole concept of SQL 2005 Reporting. 😉
Any suggestions to this one-dimensional table/chart problem???
November 12, 2007 at 6:35 pm
First, create your query to convert your "yes" answers to a number (i.e. When "Yes" then 1 else 0). I'd suggest putting it into a view, then doing a standard sum across these fields.
Then, it's quite straightforward.
November 12, 2007 at 8:34 pm
Thanks for the recommendation Dave. Unfortunately, I'm still lost. I created some views and converted the Yes's and I still can't seem to get this chart to work. I can get it to display data (if I configure the chart manually), but the chart does not show titles on the x-axis.
Do you have other suggestions? Or does anyone else?
November 23, 2007 at 2:35 am
Hi
I'm new to this but I have had some success with the following:
SELECT COUNT( t1.Q1 ) AS Q1,
( SELECT COUNT( t2.Q2 ) FROM tablename t2 WHERE t2.Q2 = 'Yes' ) AS Q2,
( SELECT COUNT( t3.Q3 ) FROM tablename t3 WHERE t3.Q3 = 'Yes' ) AS Q3,
( SELECT COUNT( t4.Q4 ) FROM tablename t4 WHERE t4.Q4 = 'Yes' ) AS Q4.......
FROM tablename t1
WHERE t1.Q1 = 'Yes'
If the question field is boolean then obviously Yes would be 1.
Its not very elegant but it gives the data series you need to produce the chart. You could also configure it to produce percentage of "Yes" responses per question.
Cheers
November 25, 2007 at 10:42 am
Thank you David for the response. Also, sorry for the delayed reply as I actually read and executed your suggestion about an hour after you had posted it.
This helped me out...tons. Thanks
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply