sql server reporting services: RS 2000 - Problem with percentage graphs -- sql server reporting services

RS 2000 - Problem with percentage graphs

Simon Dingley
3/13/2006 9:04:38 PM

sql server reporting services:

I am using VS2003 .Net & RS2000

Just wondered if someone could help. I am relatively inexperienced with
anything remotely complex in Reporting Services and need to generate a
graph that displays my data as percentages.

My simple query returns the following data collected from a questionnaire:

-------------------------------
| Q1a | Percentage
|------------------------------
| Excellent | 70%
| Good | 10%
| Fair | 5%
| Poor | 5%
| N/A | 10%
|------------------------------

My query is as follows:

SELECT Q1a, CAST(LEFT(CAST(COUNT(Q1a) AS NUMERIC) /
(SELECT COUNT(*)
FROM
dbo.tblQuestionnaireNonPurchased) * 100, 2) AS VARCHAR) + '%' AS Q1aTotal
FROM dbo.tblQuestionnaireNonPurchased
GROUP BY Q1a

I have added a graph in the report designer and set the data point as
[Percentage] and the series field as [Q1a] which lists the possible
answers given.

The problem I have is that the graph no matter what type, pie or line,
displays all results as equal. If i have a pie chart it is divided into
5 equally sized slices and the same applies to bar charts. Hopefully I
am missing something really obvious but your help would be much appreciated.

RE: RS 2000 - Problem with percentage graphs

Amarnath
3/13/2006 10:04:37 PM

Hi,

The problem is that your [percentage] is varchar and not integer. So SSRS
can't distribute. After you convert it into integer/float, go to "drop data
items in your chart" and right click for "Edit chart value". on the value,
select SUM(... instead of Count....

This should solve your problem.

Any doubts let me know

Amarnath

[quoted text, click to view]

Re: RS 2000 - Problem with percentage graphs

Simon Dingley
3/14/2006 12:00:00 AM

Hi Amarnath,

Thanks that worked however after converting to an int I can no longer
display the percentage values beside each figure on a pie. If I add the
"P" format code to the point label I end up with figures like 7,000%
which is a bit strange, is there something else I am missing here ?

Thanks again.

Re: RS 2000 - Problem with percentage graphs

toolman
3/15/2006 8:37:13 AM

Simon,
Take out the * 100 from your query. SSRS knows to do the calculation
and add the % sign if you use the P format code.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.