March 26, 2008 at 2:28 am
Hi
I have some report parameters defined whereof two are uniqueidentifiers:
ElementName = 'ALL'
TemplateScorecardID = '85DC707D-3C00-46CB-A7FE-3CF6E7EE71CC'
CompanyScorecardID = 'D65BA811-5EAD-4A29-9C9A-D4F9A1B4F54D'
Period = 1
Now, I have a dataset which is calling a stored procedure that accepts two uniqueidentifiers:
===================================================================
Set fmtonly Off
EXEC procTemplateScorecardMeasureElementOutput
@ElementName,
CONVERT(uniqueidentifier,@TemplateScorecardID),
CONVERT(uniqueidentifier,@CompanyScorecardID),
@Period
===================================================================
When I try to run the report I receive the following error message:
An error occurred during local report processing
An error has occurred during report processing
Query execution failed for data set 'my dataset'
Incorrect syntax near the keyword 'CONVERT',
===================================================================
The question: How can I use report parameters to hold a default uniqueidentifier and then use that in the dataset query?
I have tried using it without the convert function but then the following error is raised.
An error occurred during local report processing
An error has occurred during report processing
Query execution failed for data set 'my dataset'
Error converting data type nvarchar to uniqueidentifier.
March 26, 2008 at 3:01 am
I found a solution!
The problem is that I cannot convert the parameter to a GUID which is what the stored procedure expects so .....
1. Added the following code to the report properties
Public Function ConvertToGuid(value As String) As Guid
Dim result As Guid
result = New Guid(value)
return result
End Function
2. When setting the Dataset parameters - use the Code. syntax to call the custom function
I created to convert the string parameter to a Guid!!!
@TemplateScorecardID =Code.ConvertToGuid(Parameters!TemplateScorecardID.Value)
@CompanyScorecardID =Code.ConvertToGuid(Parameters!CompanyScorecardID.Value)
3. Tested and the report runs ! ! ! now this should work just fine when passing the parameter via querystring!
🙂
May 27, 2008 at 2:28 pm
Thanks a lot, this really helped me out!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply