How to align two data regions?

Here is the scenario ...
I have a bar chart control with a Y-Axis that has values 1, 2, 3, 4 and 5.
To the right of it, I have a table control that will always have five rows of
data (ea. row associated to the values on the Y-Axis of the chart control).
I would like to arrange the two controls on my report so that the 5 rows in
the table control ALIGN with the 5 values on the Y-Axis of the chart control.
Any recommendations? Thanks - GPlease check my response on your first thread on how to customize the y-axis
labels.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:9E4DDFF4-BCF2-4249-9EDF-D7F95B61CAF5@.microsoft.com...
> Here is the scenario ...
> I have a bar chart control with a Y-Axis that has values 1, 2, 3, 4 and 5.
> To the right of it, I have a table control that will always have five rows
> of
> data (ea. row associated to the values on the Y-Axis of the chart
> control).
> I would like to arrange the two controls on my report so that the 5 rows
> in
> the table control ALIGN with the 5 values on the Y-Axis of the chart
> control.
> Any recommendations? Thanks - Gsql

how to accumulate values in different columns ?

Hi,

I have a table similar to this :

id | name | res_id
--+---+---
1 | foo | 1
2 | bar | 1
3 | foo | 1
4 | test | 2
5 | wheyho | 3
6 | ding | 2
7 | dong | 3
8 | foo | 2
9 | foo | 3
10 | bar | 1
11 | bar | 2
12 | bar | 2
13 | wheyho | 3

Now, I am looking for a SELECT statement that counts the result_id depending on its value ( 1,2 or 3 in this example ) and grouped by name. To make it a bit more clear I am looking for this result (with result_id 1 evaluates to 'pass', 2 to 'fail' and 3 to 'unknown' ):

name | pass | fail | unknown
---+--+--+----
foo | 2 | 1 | 1
bar | 2 | 2 | 0
test | 0 | 1 | 0
wheyho | 0 | 0 | 2
ding | 0 | 1 | 0
dong | 0 | 0 | 1

I can get single results when doing this staements :

SELECT name, count(result_id) AS pass FROM t1 GROUP BY name, res_id HAVING res_id = 1;

name | pass
--+--
foo | 2
bar | 2

SELECT name, count(result_id) AS fail FROM t1 GROUP BY name, res_id HAVING res_id = 2;

name | fail
--+--
foo | 1
bar | 2
ding | 1
test | 1

SELECT name, count(result_id) AS unknown FROM t1 GROUP BY name, res_id HAVING res_id = 3;

name | unknown
---+---
foo | 1
wheyho | 2
dong | 1

Is there any way to bring these single results together with one SELECT statements ?With SUMs and CASEs:

SELECT
name,
SUM(CASE WHEN res_id = 1 THEN 1 ELSE 0 END) as pass
SUM(CASE WHEN res_id = 2 THEN 1 ELSE 0 END) as fail
SUM(CASE WHEN res_id = 3 THEN 1 ELSE 0 END) as unknown
FROM t1
GROUP BY name

Untested, syntax may be off.|||Wow, this was a fast reply! And, what is more, a very good one. It works just the way I was looking for !

thanks a lot !