I have a report whose parameters are limited by the user who is logged in. Ex. if a user had full access they would see all 100 hospitals in our hospital parameter, if another user had limited access they might only see 5 - this will vary largely on who is logged in. The data source that drives these parameters are set as follows: Credentials stored securely in the report server, username and password are entered and Impersonate the authenticated user after a connection has been made to the data source is checked.

This all works great. I now want to cache this report for X minutes. So I goto Properties/Execution and select 'Cache a temporary copy of the report. Expire copy of report after a number of minutes'. Applying this gives me the following error: Credentials used to run this report are not stored.

In the example above, I would like to cache 100 versions of the report (because there are 100 hospitals) so I can guarentee that no matter who is logged in, they get a cached version.

How do I go about achieving this?

Hi David,

I had a similar case.  If SSRS can capture individual user's information. Such as UserName, you can try following method.

1. Create a table contains  Username and Associate hospital Name or ID.

2. Create a dataset for #1. 

3. Create or modify your current "Hospital Name Parameter" using #2.   Use Expression. =User!UserID

*This may re-do some backend stuffs.

4. Create a service account and apply to "Credentials stored securely in the report server" section.  This is common User/pass for report generation.

But report will capture user information and apply to Hospital Name parameter.

 

If anyone has better idea, please share.

 

Hi SoyLover,

Thanks for the response. I've setup a new report to test the above. When I try to cache a temporary copy of the report for X minutes it now gives me the following error: The report has user profile dependencies.

I'm guessing its referring to =User!UserID

Thoughts?

it now gives me the following error: The report has user profile dependencies.

I'm guessing its referring to =User!UserI

Hi David,

Yes, that is the cause. To work around it, you can write custom code to call the User!UserID in your report. The custom code is like this:

Public Function UserName()
Try
Return Report.User!UserID
Catch
Return "System"
End Try
End Function

then, on your report, you can call =code.UserName() to return the current user id to filter your data.

Check http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/cb4e7816-d421-4921-a4e5-b258888748f9/ with the similar issue.

thanks,
Jerry

• Marked as answer by Jerry NeeModerator Wednesday, February 09, 2011 2:25 AM

it now gives me the following error: The report has user profile dependencies.

I'm guessing its referring to =User!UserI

Hi David,

Yes, that is the cause. To work around it, you can write custom code to call the User!UserID in your report. The custom code is like this:

Public Function UserName()
Try
Return Report.User!UserID
Catch
Return "System"
End Try
End Function

then, on your report, you can call =code.UserName() to return the current user id to filter your data.

Check http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/cb4e7816-d421-4921-a4e5-b258888748f9/ with the similar issue.

thanks,
Jerry

Thanks Jerry,

That did the trick.
I keep refreshing the report, appears to take the same time to load as when it wasn't cached - any ways of testing this?

cheers

the above trick will only allow us to save the caching option in report manager for a perticular report , but a new copy of the report will be generated every time , cross checked in Executionlog3 ,

I'm facing a similiar problem and as the previous reply suggests, the trick above let us configure the caching options without throwing errors, but the cache is indeed being refreshed eveytime the report is run.

I also checked it against ExecutionLogStorage table, and I see that the report is hitting the database every single time.

Is there a way to make it really cache the report when using User!UserID?

I'm not using it as dataset's parameter, but as dataset's filter instead. That should cause the data to be filtered without causing the cache invalidation.

Any help will be appreciated.

Thank you.