Report Parameter - Problem working with Uniqueidentifiers

• I.W Coetzer

  Ten Centuries

  Points: 1060

  More actions

  March 26, 2008 at 2:28 am

  #184553

  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.

• I.W Coetzer

  Ten Centuries

  Points: 1060

  More actions

  March 26, 2008 at 3:01 am

  #793897

  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!

  🙂

• agarwick

  SSC Rookie

  Points: 25

  More actions

  May 27, 2008 at 2:28 pm

  #820828

  Thanks a lot, this really helped me out!