Post reply

Error while executing the ssrs report in sql 2005

  • April 23, 2010 at 8:34 pm

    #234285

    First I created a main report and a child report.

    Child Report: Select [Provider Type] As ProviderType,Status, [Sub Timely Category] AS SubTimelyCategory, [Completion Month] from dbo.Test

    Main report contains bar charts and I clicked on bar in the main report went into properties --Data---Edit---Action---jump to report -Added a child report then clicked on the parameters next to the jump to report and added the parameters.

    ParameterName Parametervalue

    ProviderType = Fields!ProviderType.value

    SubTimelyCategory =Fields!SubTimelyCategory.Value

    TotPercent3 =Fields!TotPercent3.Value

    Then I went back to child report and I modified child report as

    Select [Provider Type] As ProviderType,Status, [Sub Timely Category] AS SubTimelyCategory, [Completion Month]

    Where ([Provider Type]=@[Provider Type])

    AND ([Sub Timely Category]=@[Sub Timely Category])

    from dbo.Test

    Then I went into Report Parameters and I added

    In Name and Prompt boxes with following.

    ProviderType

    SubTimelyCategory

    TotPercent3

    Then I went to main report and then click on the bar and I was able to go to the subreport and even I was able to see the parameters in parameters boxes but I clicked on the view report I was getting the following error

    An error occurred during local report processing

    An error has occured during report processing

    Query execution failed for data set 'Datasetname'

    Must declare the scalar variable"@"

  • April 23, 2010 at 10:48 pm

    #1157225

    Hi Krishna,

    The problem is in the section.

    Where ([Provider Type]=@[Provider Type])

    AND ([Sub Timely Category]=@[Sub Timely Category])

    Please give the parameter name same as you have given in the "Report Parameter" part in the layout, Im sure there you would have given it like ProviderType (the reason being the Name wont accept space in it).

    Also dont try to write like:

    Where ([Provider Type]= [@Provider Type])

    AND ([Sub Timely Category]= [@Sub Timely Category])

    becasue in your table there is no column named as [@Provider Type] or [@Sub Timely Category]

    So change the above section as

    Where ([Provider Type]=@ProviderType)

    AND ([Sub Timely Category]=@SubTimelyCategory)

    Provided at the Report Parameter section in the layout you have given the Name of the parameters as ProviderType and SubTimelyCategory.

    Note: In the report Parameter section of layout , you can give space or [] for the Prompt but not for the Name section.

    Thanks & Regards,
    MC

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply