none
Problem with Report Caching RRS feed

  • Question

  • 1. I have a stored proc that does 3 things:

    a. Clears a table

    b. inserts data into the table using dynamic SQL, so that I can change parameter choices

    c. selects from the table for display

    Then I wrote a report to call the proc.

    2. If I use a parameter that says show all data where widget = 'N' it will display fine

    3. But if I change the parameter setting to widget = 'Y', the displayed data is still from #2.

    4. Then, if I simply click "View Report" one more time, I get the correct data.

    Is there anything I've said here that is a red flag? It behaves as if it cached the data from the first run,

    but it didn't get flushed until I ran the report two times!


    Corey Fleig

    Thursday, August 22, 2019 12:08 AM

All replies

  • Hi Coreysan

    According to your description , I test It on my machine , seems it works fine in the report builder .

    Where did you get the issue ? report builder?ssdt? or the web portal ?

    If you are using the ssdt , seems you’d better remove the rdl.data in the project path to make sure the dataset refresh .

    Or it would related to the sp , mine is simple , like below , you could try to check a simple sp and see if it works fine or not .

    create table emp_salary (
    name varchar(50),
    age int,
    salary int
    )
    
     test_1 
     @name varchar(50)
    ,@age int
    ,@salary int 
    
    as
    begin
    declare @sql varchar(max)
      
    TRUNCATE  table   emp_salary
    set @sql = 'insert into emp_salary (name, age ,salary) VALUES ('+char(39)+@name+char(39)+','+convert(varchar(50),@age)+','+convert(varchar(50),@salary)+')'
    exec(@sql)
    select * from emp_salary
    end 
    

    If you still get issue after you checked above , you could offer more detailed information about your issue to us for more further research .

    Hoping for your reply.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Thursday, August 22, 2019 2:39 AM
  • Eric,

    Thanks so much for being willing to help out. I really do appreciate that.

    I use VS 2017 to create my reports, and deploy to a report server that's inhouse.

    In my report, I call a stored proc and pass the parameters, one of which is a switch. If user wants

    contract info, the parameter is 'Y', otherwise 'N'. The parameter is used in dynamic SQL:

    select @wstr = 'select * from mytable where [contract] = ' + '''' + @wcontract + ''''

    This is the only report I have out of 137, that won't refresh properly. If I simply click the "view report" button

    a second time, it gets corrected (About 4,000 lines).

    I have checked "use single transaction" on the datasource. I wouldn't be surprised if I just recreated the report from scratch it would work just fine! I might do that!

    Anyway, does anything ring a bell?


    Corey Fleig

    Thursday, August 22, 2019 3:45 PM
  • BTW - I rewrote a part of the report and it all worked fine, so the problem might have something to do with all the other objects in the report. I have a graph, a couple more datasets and tables, etc. So I'm going to add one object at a time from the original, and see where it breaks!

    Corey Fleig

    Thursday, August 22, 2019 3:55 PM
  • Hi Coreysan

    Hoping for your reply and hope you could offer your solution here.

    Thanks for your support and understanding.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

    Monday, August 26, 2019 2:55 AM