Prev: Adding Remarks in Reports
Next: SSRS 2005 questions
From: wazza_oz on 18 Dec 2009 02:19
I'm an experienced C# dev but have never used RS before and am struggling a
little with the paradigm... apologies if my question seems really retarded.

I am trying to sum rows depending on certain values. Suppose I have a
dataset with an "ID", a "Type" and a "Value" column, and the Value column
will contain only the value A or B, e.g.:

Type ID Value
Blue 10 A
Blue 11 A
Blue 12 B
Blue 13 A
Red 14 B
Red 15 A

and so on. I am grouping on Type. At the end of my group, I want to count
the number of As and Bs as two total lines. So, I create two hidden columns
by setting up an expression in textboxes "A_count" and "B_count" that contain
1 and 0 respectively if the value is A, and vice-versa if it is B. I should
be able to do an expression something like =Sum(A_count) and =Sum(B_count) in
my group footer to give me the result.

However... "A_count" and "B_count" are textboxes not fields in my dataset,
and so I don't seem to be able to reference them - I get an error that "Name
'A_count" is not declared". I have pored over MSDN for hours and Googled
myself silly but don't seem to be able to crack this one.

Any help gratefully received!

Thanks,
Waz.

From: Mark_Seven on 18 Dec 2009 12:41
On Dec 18, 1:19 am, "wazza_oz" <u56939(a)uwe> wrote:
> I'm an experienced C# dev but have never used RS before and am struggling a
> little with the paradigm... apologies if my question seems really retarded.
>
> I am trying to sum rows depending on certain values.  Suppose I have a
> dataset with an "ID", a "Type" and a "Value" column, and the Value column
> will contain only the value A or B, e.g.:
>
> Type     ID     Value
> Blue     10     A
> Blue     11     A
> Blue     12     B
> Blue     13     A
> Red      14     B
> Red      15     A
>
> and so on.  I am grouping on Type.  At the end of my group, I want to count
> the number of As and Bs as two total lines.  So, I create two hidden columns
> by setting up an expression in textboxes "A_count" and "B_count" that contain
> 1 and 0 respectively if the value is A, and vice-versa if it is B.  I should
> be able to do an expression something like =Sum(A_count) and =Sum(B_count) in
> my group footer to give me the result.
>
> However... "A_count" and "B_count" are textboxes not fields in my dataset,
> and so I don't seem to be able to reference them - I get an error that "Name
> 'A_count" is not declared".  I have pored over MSDN for hours and Googled
> myself silly but don't seem to be able to crack this one.
>
> Any help gratefully received!
>
> Thanks,
> Waz.

Waz,
try using the following expressions in your group footer:

="Count of A: " & SUM(IIF(Fields!Value.Value="A",1,0))

="Count of B: " & SUM(IIF(Fields!Value.Value="B",1,0))

Basically you're using the SUM function on an IIF expression that
returns 1 when true, and 0 when false.

HTH, Mark
From: wazza_oz via SQLMonster.com on 20 Dec 2009 16:46
Mark - you legend! Works perfectly, thanks so much.

Merry Christmas,
Waz.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200912/1

 | 
Pages: 1
Prev: Adding Remarks in Reports
Next: SSRS 2005 questions