Ssrs 2008 not working when changed to use a stored procedure

SSAS and SSRS
1

In a new SSRS 2008 report, I am going to change the inline sql to a stored
procedure since that is a requiement for the project I am working on. The sql works fine

within the ssrs 2008 report, but has a problem in the stored procedure.

The error message that is displayed is the following:
'Query execution failed for dataset Msg 8114, Level 16, State 1 Procedure spRec,
line 0 Error converting data type varchar to int.'

The ssrs 2008 report has 18 embedded tablixes within the main tablix. The
embedded tablixes wiithin the ssrs main tablix will display selected reports based upon a

report parameter that a user. The embedded reports will be displayed based upon the report

number. In addition, I only want the data for the selected reports to be returned from
the sql. I want this to occur since the data from the runs will be more than is
needed for each uniuqe run. Note: The data returned will be all in the same format. There

will be a column in each data row showing what report number the

data should be selected for.

The report parameter is setup as allow multiple values and data type = text.
The dataset to pass data to the report parameter looks like:
SELECT 'Locator' AS rptName, 1 AS rptValue
UNION
SELECT 'letter',2
UNION
SELECT 'Wallet ', 3
UNION
SELECT 'Cum Stu', 4
UNION
SELECT 'Attend', 5
UNION
SELECT 'Test',6

The sql before each section looks like:

IF 1 in (@report)

IF 2 in (@report)

I have tried changing the properties of the report parameter to integer and the
if statements listed above but it has not worked.

Thus can you tell me what I can do to solve my problem?

2

This type of error typically happens when SQL Server tries to convert a string to an int. That may not have been your intention. For example, run the code below and you will see that error.

select 1 as Col1 
union all
select 'abcd'

There is not enough information in what you posted to suggest what part of your code is causing this error.

1 Like