0

i am try to access data from stored procedure to ssrs report. but i don't get the answer.

it shows the error "[rsRuntimeErrorInExpression] The Caption expression for the chart ‘Chart 4’ contains an error: The query returned no rows for the dataset. The expression therefore evaluates to null."

I got data, when i execute the stored procedure in server management studio. but i don't get data in ssrs report.

when i searched for the solution for this problem, some sites ask to add the following code in the beginning 

   declare @FMTONLY bit

   if (1=0) begin
       set @FMTONLY=1;
        set FMTONLY off;
  end

and end of the stored procedure

  if @FMTONLY=1
    begin
        set FMTONLY on;
    end

i did this but it is not working for me.

i like to know what is the reason for getting this error? and how can i solve this error?

thanks in advance

Anupa Sankar
  • 491
  • 2
  • 7
  • 17
  • I would take out any additional code from your stored proc, it will only confuse matters. Does you SP take parameters? Have you tried executing the dataset query from the dataset designer in Visual Studio/ Report Builder? Have you ensured the report is pointing to the correct database/server with the correct credentials? – Alan Schofield May 08 '17 at 10:44
  • my stored procedure took parameters.my report is pointing to correct database. and it's credentials are correct. – Anupa Sankar May 08 '17 at 12:00
  • So have you tried executing it from the dataset designer in VS/Report Builder? You should be prompted for parameters. This will show you exactly what is being sent to your report. – Alan Schofield May 08 '17 at 12:06

2 Answers2

2

This SQL returns only null.

if (1=0) can never be true, so nothing ever happens to your bit - it's never set to either true or false, only null.

BishNaboB
  • 1,047
  • 1
  • 12
  • 25
0

i don't know why this error happened. i try my best to solve this problem. but i don't get any solution. At last i created a new shared data source with another name, and same credentials. and it works.

this is not a valid solution for this problem. but this is how i solved my problem.

Anupa Sankar
  • 491
  • 2
  • 7
  • 17