SSRS - [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Argument 'Month' is not a valid value

Have you ever experienced this warning and error in expression while using IIF in the expression

Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ contains an error: Argument 'Month' is not a valid value.

Let us see why this warning and error occurs and how to overcome it.

For this example,I have created report with parameter of data type Integer and then textbox with following expression :

=IIF(Parameters!Month.Value=0,"Invalid Month Value",MonthName(Parameters!Month.Value))

So if you pass 5,it will return month name for value 5 as shown below :


If you  pass 0 it should return "Invalid Month Value" but you will notice error and warning as shown below :



 The reason for error & warning is

If you pass 0,it becomes like below
=IIF(0=0,"Invalid Month Value",MonthName(0))

In IIF eventhough it returns True or False part based on condition,it will evaluate both True and False part as shown in above example.

Change the textbox expression like shown below :

=IIF(Parameters!Month.Value=0,"Invalid Month Value",MonthName(IIF(Parameters!Month.Value=0,1,Parameters!Month.Value)))

Now pass 0 ,report will run without any error as shown below :





 

T-SQL - Create temp table with dynamic columns

In this post, let us see how to create temp table with dynamic columns.



DECLARE @ColumnsList TABLE ([DBId] INT,Versions VARCHAR(50))

INSERT @ColumnsList SELECT 1,'SQL Server 2008'
INSERT @ColumnsList SELECT 1,'SQL Server 2012'
INSERT @ColumnsList SELECT 2,'ORACLE 10g'
INSERT @ColumnsList SELECT 2,'ORACLE 11g'

IF OBJECT_ID('tempdb..##temp') IS NOT NULL
DROP TABLE ##temp
CREATE TABLE ##temp(dummy BIT);

DECLARE @Script         VARCHAR(8000),        
        @Script_prepare VARCHAR(8000);

SET @Script_prepare = 'ALTER TABLE ##temp ADD [?] VARCHAR(100);'
SET @Script = ''
SELECT @Script = @Script + REPLACE(@Script_prepare, '?',Versions) FROM @ColumnsList
WHERE [DBId] = 1 -- to be changed
EXEC (@Script)

ALTER TABLE ##temp DROP COLUMN dummy;

SELECT * FROM ##temp
--DROP TABLE ##temp


Reference - http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/701d7f4a-006a-47ea-9a38-d6417fde5d0b/



See Also:

SQL Server - XML Examples(Xpath,Xquery,XML DML,XML Index,XML Schema)

Example 1:

Mostly we need  XML in this format,i.e column names with their values enclosed within their column name tag

Consider below XML needs to be generated for the below mentioned sample table definition


<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
<field Name="ID">2</field>
<field Name="Name">Sunny</field>
<field Name="Age">24</field>
<field Name="Sex">Female</field>
</Employee>


DECLARE @Employee TABLE
(ID INT,
Name VARCHAR(100),
Age INT,
Sex VARCHAR(50))

INSERT @Employee SELECT 1,'Sathya',25,'Male'
INSERT @Employee SELECT 2,'Sunny',24,'Female'


DECLARE @xmldata XML
SET @xmldata = (SELECT ID,Name,Age,Sex FROM @Employee FOR XML PATH (''))

SET @xmldata = (
SELECT ColumnName AS "@Name",
       ColumnValue AS "text()"
FROM(
SELECT i.value('local-name(.)','varchar(100)') ColumnName,
       i.value('.','varchar(100)') ColumnValue
FROM @xmldata.nodes('//*[text()]') x(i)) tmp
FOR XML PATH ('field'),root('Employee'))

SELECT @xmldata



Example 2:

If XML is stored as NTEXT and if we are required to make changes in the node values of that XML

Sample XML,



<Employee>
<field Name="ID">1</field>
<field Name="Name">Sathya</field>
<field Name="Age">25</field>
<field Name="Sex">Male</field>
</Employee>

Passing comma separated values (SSRS - multivalued parameter) as input to the stored procedure

In this post, I have shared about how to handle when SSRS dataset gets delimited string as input from multi valued parameter.

Refer my post here

Note : The suggested work around in the above link will work only for single character multi-valued parameter

See Also: