locked
User profile dependencies problem in report scheduling RRS feed

  • Question

  • Hello

    I have a report i want to cache each night.

    To make this possible, we do not use parameters but use filters based upon de user that is executing the report.  So we can cache the report the first time any user executes it and the same report is already cached for all users becasuse the security is based on filters, not in query parameters.

     

    When i try to schedule it, i get the message:

    "user profile dependencies and cannot be run unattended rsHasUserProfileDependencies"

     

    Although user variable paramter is not used in the query and it is only used in the filter when any user views the report RS dont allow me to schedule it.

     

    How can I schedule this report to be executed all night unattended ?? The user is not a query parameter so it should allow it.

     

    Thanks in advance

    Thursday, August 2, 2007 5:18 PM

Answers

  •  

    Hello Pablo,

     

    if you use the User!UserID to filter the data at runtime, the system tries to evaluate the User during Cache Load.

    Therefor you will need a "method" to get rid of the UserID during the Caching Operation.

     

    The filter will be defined as Expression with User!UserID, you will have to implement a piece of code instead which will evaluate the UserID at runtime and also brings up an errorhandling:

     

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

     

    Doing the filter this way will also need a Workaround during filterhandling, because you will need a valid filter instead of "System" like shown in the code above.

     

    Credits to Jan Pieter Posthuma and Brian Cooper for this solution can be found here:

    http://www.fits-consulting.de/blog/PermaLink,guid,12db87cf-9d2f-4a2f-9f1b-a6cc1f692ea7.aspx

     

    Cheers,

    Markus

    Friday, September 7, 2007 3:31 PM

All replies

  • You must use stored credentials if you want to use a scheduled report:

    Stored credentials are required if you want to support subscriptions, or schedule report history generation or report snapshot refreshes.

    http://msdn2.microsoft.com/en-us/library/ms160330.aspx

    You will not be able to use the method you outlined above and also use scheduled reports.  There may be a way to use the logged in user to filter the parameters rather than the report and put a front end in place that queries for the parameters and enters them automatically.

     

    Larry

    Tuesday, August 7, 2007 4:57 AM
  • At subscription time report processing does not have access to the user profile information. Any expression like User!... will cause “has a user profile dependency” error.

     

    I believe you can work around this error by taking out this line in the report:

      <Language>=User!Language</Language>

     

    We have that line in there so that the report's dates will be in the language that is set in IE, rather than how the date is stored on the AT.  If you dont mind giving that up, you can try the above option.

     

    HTH

    mauli

    Tuesday, August 7, 2007 5:28 PM
  •  First of all, thanks a lot for your answers.

    I already have the credentials stored in the datasource.

    I use a superuser the can retrieve all the data.

    Once the data is cached with all the data (becasuse i used a superuser and didn't use parameters) I use the User info the Filter. I understand that in scheduling time i can't use that data, but my complain I that I do not use it in query time, i use it later when the concrete user tries to see the report.

     

    Your other suggestion, use the front-end to place the user data could be great, but i am not sure if from the front-end we can initialize the parameter. We will try.

     

    Again, thanks a lot.

    Friday, September 7, 2007 7:33 AM
  •  

    First of all, thanks a lot for your answers.

    Thanks for the suggestion, but, can you explain me a bit more what is the objetive of that line and where i should delete it ?

    I am new in RS and i am not completely sure if i am doing it correctly and the impact of deleting that line.

    Thanks a lot again

    Friday, September 7, 2007 7:43 AM
  •  

    Hello Pablo,

     

    if you use the User!UserID to filter the data at runtime, the system tries to evaluate the User during Cache Load.

    Therefor you will need a "method" to get rid of the UserID during the Caching Operation.

     

    The filter will be defined as Expression with User!UserID, you will have to implement a piece of code instead which will evaluate the UserID at runtime and also brings up an errorhandling:

     

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

     

    Doing the filter this way will also need a Workaround during filterhandling, because you will need a valid filter instead of "System" like shown in the code above.

     

    Credits to Jan Pieter Posthuma and Brian Cooper for this solution can be found here:

    http://www.fits-consulting.de/blog/PermaLink,guid,12db87cf-9d2f-4a2f-9f1b-a6cc1f692ea7.aspx

     

    Cheers,

    Markus

    Friday, September 7, 2007 3:31 PM
  •  

    Hello Markus

     

    THANKS !!! That seems to be the perfect solution to my problem. Just only one thing. I have more than 50 reports. Do i have to write the function code in each report (code panel y properties window) or can i just write it once some where and just reference it in all (this for sure is mandatory) the reports ?

     

    Thanks A LOT in advance.

    Wednesday, September 12, 2007 2:15 PM
  • Hi Pablo,

     

    there are certain ways to "use" code in SSRS,

     

    First way is the Custom Code implemented in every report like you did with the code snippet.

     

    Second way - and much less mantainance-intense - is to use a custom assembly, but there are several things to do includeing code access security to be able to use custom assemblies...

     

    Bryant Likes http://blogs.sqlxml.org/bryantlikes/articles/824.aspx has published an article about coding in SSRS including all neccessary links to the MS documentation.

     

    Hope that helps...

     

    There still will be the references in the reports to build - but you could also do that using a self-written RDL-handler - depending on the amount of reports you would have to touch manually...

     

    cheers,
    Markus

    Wednesday, September 12, 2007 2:31 PM