.rdlc table use of SUM and SWITCH together?

Hello everyone, thanks in advance for reading. I'm new to reports and have tried searching for my answer with no luck. Any direction would be great.

Here is the issue, I have 3 colums in the dataset which display String data ("High", "Medium", or "Low")

I've added a column to the table on the report where I would like to convert the value of "High", "Medium" and "Low" to 3,2,1 respectively then SUM these numbers.

Is this possible? I've tried several variations of the code below--maybe it's not possible to do what I want? Thanks again for your time and help.

Fields!PP.Value is set to:

=Switch(Fields!Avail_Rank.Value = "High", 3, Fields!Avail_Rank.Value = "Medium", 2, Fields!Avail_Rank.Value = "Low", 1)

=Switch(Fields!Volume.Value = "High", 3, Fields!Volume.Value = "Medium", 2, Fields!Volume.Value = "Low", 1)

=Switch(Fields!Integ_Rank.Value = "High", 3, Fields!Integ_Rank.Value = "Medium", 2, Fields!Integ_Rank.Value = "Low", 1)

=Sum(Fields!Avail_Rank.Value + Fields!Volume.Value + Fields!Integ_Rank)

One way of doing this is to define calculated fields on your dataset.

For example, define a calculated field called Avail_Rank_Numeric with the value expression: =Switch(Fields!Avail_Rank.Value = "High", 3, Fields!Avail_Rank.Value = "Medium", 2, Fields!Avail_Rank.Value = "Low", 1)

Then, in the Sum aggregate function in the report body use it as follows:
=Sum(Fields!Avail_Rank_Numeric.Value + ...)

-- Robert

|||Thank you very much! Your idea worked perfectly!

**sum**

Hi
I've follwoing data in table1 in SQL server2000,and I want to get the
result as below:
table1 result
Id Qty
-- --
1 500 1 500
2 502 2 1002
3 400 3 1402
4 140 4 1542
how is it possible in select statement?
Any help would be thankful.Here you go
CREATE TABLE #Table1(id int PRIMARY KEY, Quantity int NOT NULL)
INSERT INTO #Table1 VALUES(1,500)
INSERT INTO #Table1 VALUES(2,502)
INSERT INTO #Table1 VALUES(3,400)
INSERT INTO #Table1 VALUES(4,140)
SELECT id,
(SELECT SUM(Quantity) FROM #Table1 WHERE id <= O.id) As RunningTotal
FROM #Table1 O
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<R> wrote in message news:opsyn8fezjmw7tkz@.system109.parskhazar.net...
> Hi
> I've follwoing data in table1 in SQL server2000,and I want to get the
> result as below:
> table1 result
> Id Qty
> -- --
> 1 500 1 500
> 2 502 2 1002
> 3 400 3 1402
> 4 140 4 1542
>
> how is it possible in select statement?
> Any help would be thankful.|||I missed the ORDER BY
SELECT id,
(SELECT SUM(Quantity) FROM #Table1 WHERE id <= O.id) As RunningTotal
FROM #Table1 O
ORDER By ID
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ecqyxfV0FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Here you go
>
> CREATE TABLE #Table1(id int PRIMARY KEY, Quantity int NOT NULL)
> INSERT INTO #Table1 VALUES(1,500)
> INSERT INTO #Table1 VALUES(2,502)
> INSERT INTO #Table1 VALUES(3,400)
> INSERT INTO #Table1 VALUES(4,140)
> SELECT id,
> (SELECT SUM(Quantity) FROM #Table1 WHERE id <= O.id) As RunningTotal
> FROM #Table1 O
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> <R> wrote in message news:opsyn8fezjmw7tkz@.system109.parskhazar.net...
>