SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 SSRS Query designer error..CLR Type does not exist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 11/08/2012 :  03:21:46  Show Profile  Reply with Quote
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!



Edited by - Villanuev on 11/08/2012 03:58:33

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/08/2012 :  05:49:00  Show Profile  Reply with Quote
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.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 11/08/2012 :  08:50:17  Show Profile  Reply with Quote
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


Edited by - Villanuev on 11/08/2012 08:54:31
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/08/2012 :  10:29:01  Show Profile  Reply with Quote
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/
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

407 Posts

Posted - 11/08/2012 :  21:29:09  Show Profile  Reply with Quote
Done.. i got it.

i forgot the check box below the datatype...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000