SSRS Query designer error..CLR Type does not exist

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

SSRS Query designer error..CLR Type does not exist

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-11-08 : 03:21:46

Hi Guys,


I have an SSRS report with 3 parameters.

composed of 2 dataset. dataset 1 is okey i did not encounter any problem but in dataset 2 which i have a problem.

@ExcRate data type Float : sample 61.41 

@FromDate

@ToDate

and this is my query:

;With Cte

as

(

select .

.

.

 ,Case When pur.CURRENCYCODE='PHP' Then Round((pur.QTYORDERED*pur.PURCHPRICE)/@ExcRate,5)
         When pur.CURRENCYCODE='USD' Then Round(pur.QTYORDERED*pur.PURCHPRICE,2)
     End As SPENDUSD     

From xxxx

where trandate between (@fromdate) and (@Todate)

)

Select * from CTE


My problem when i went  to SSRS query designer for dataset # 2 and run the script  i just encounter an error.

Parameter 3 ([Dynamics].[].[unknown]): CLR type does not exist or you do not have epermission to access

when i try to remove the @Excrate, i did not encounter an error and the processing went pass through.

my objective is to have a parameter of exchange rate to multiply the value of qtyordered * purchprice.

any idea guys about this issue or option. thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 05:49:00

Instead of using the query designer, first test the query in a SQL Server Management Studio window and if that works correctly, simply paste the query into the query window of the SSRS data set definition. Even better, make the query into a stored proc and call that from the SSRS.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-11-08 : 08:50:17

Hi sunita,

i tried already in SSMS and its working.
when i try to remove the first dataset the second dataset is working.
then when i tried to remove @ExcRate in the calculation both dataset is working. but when i try to activate @ExcRate in the calculation, in the Query designer then i run the script for dataset2,i got an error.

here is what ive done..

Parameters:

@fromdate

@todate

@Excrate datatype float

then my 2 dataset

Report 1

Dataset 1 ---raw data

Select

Itemid

,currentcode

,Case When pur.CURRENCYCODE='PHP' Then (pur.QTYORDERED*pur.PURCHPRICE)/@ExcRate

When pur.CURRENCYCODE='USD' Then (pur.QTYORDERED*pur.PURCHPRICE)

End As SPENDUSD

From sample

-------------------------------------
report 2

Dataset 2 --summary

;with cte

as

(Select

Itemid

,currentcode

,Case When pur.CURRENCYCODE='PHP' Then (pur.QTYORDERED*pur.PURCHPRICE)/@ExcRate

When pur.CURRENCYCODE='USD' Then (pur.QTYORDERED*pur.PURCHPRICE)

End As SPENDUSD

From sample

)

Select itemid

Currentcode

Sum(spendusd) as usdamount

From sample

Group by

Itemid

Currentcode

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-08 : 10:29:01

I don't know what the problem is, except to suggest that you make sure the report parameters are linked correctly to the query parameters in both data sets. Since you have correctly linked two other parameters, you may have done the third also correctly - nonetheless, here is a link with some pictures that describes it in detail: http://arcanecode.com/2010/07/13/adding-query-parameters-to-sql-server-2008-reporting-services-reports/

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-11-08 : 21:29:09

Done.. i got it.

i forgot the check box below the datatype...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources