.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!

.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.

.Net trigger, reading all the data

I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables"

My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after.

Code Extract:

public static void splTrigger()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// string userName, realName;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlDataReader reader;
string data = "";

switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
command.CommandText = "SELECT * from " + "inserted";
reader = command.ExecuteReader();
//userName = (string)reader[0];
//realName = (string)reader[1];
// prepare data as name value pairs
for (int i = 0; i < reader.FieldCount; i++)
{
data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " ";
}
break;
...
}}
Did you try to read it from the original table ?

command.CommandText = "SELECT * from Youtable Y INNeR JOIN inserted i on Y.idColumn 0 I.Column";

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

As the error suggests, after triggers do not support those datatypes in after triggers (see BOL: Using text, ntext, and image Data in INSTEAD OF Triggers )

Are you able to change the columns with the offending datatypes to the new nvarchar(max), varchar(max), varbinary(max) types?

If not, the join to the original table and selecting the columns from the base table option seems to work nicely.