Parameter using User!UserID for Subscription Report

Search: Find

Home

Book

Contents

Preview

Articles

Tools

Newsletters

Premium

Ask

About

Most Popular Articles

Using Secure Sockets Layer (SSL) for SQL Server 2000 Reporting Services
Creating an SSL Certificate to ensure your confidential reports remain confidential

SQL Reporting Services - Answers to your questions

Notify me of new comments

Author

Thread: Parameter using User!UserID for Subscription Report

HarpreetBhatti

Parameter using User!UserID for Subscription Report
Posted: Thursday, June 24, 2004 6:53 PM (GST)

Peter,

I am very impressed with the work you guys are doing. I am having an issue with a subscription report that requires the User's Windows LAN ID as an input parameter. This would be the user setting up the subscription.

The report has a parameter named 'LANID', for which I am using a default value of =User!UserID

... in order to automatically get the Users Windows LAN ID inputted into the Parameter.

When the report is run online, it works absolutely fine... But when I try to add a subscription, I get the javascript popup window triggered by 'Onclick'... which states...

---------------------------
Microsoft Internet Explorer
---------------------------
Subscriptions cannot be created because the credentials used to run the report are not stored, the report is using user-defined parameter values, or if a linked report, the link is no longer valid.
---------------------------
OK
---------------------------

We have tried so many different ways of trying to getting the User's LAN ID inputted at the time of subscription but we always run into the message above and to summarize it says that it has a dependency on the User profile and that is not doable.

Do you think there is any way to get this to work?... If not, do you know of any other way to set the User's (User who is subscribing) Windows LAN ID as the default value of the parameter at the time of setting up the subscription?

Would really appreciate your advice on this matter.

Thanks,

Harpreet

PS: I have already upgraded to SP1 and in the new features it says this...

4.3.1 Hiding Parameters in a Published Report

Specify a parameter value at run time (for example, through a subscription that is used to trigger report execution and delivery)... Not sure if we can use this feature by storing the LAN ID temporarily somewhere and then retrieve it later at the time of execution.

Comments:

Author Thread:

Peter

Parameter using User!UserID for Subscription Report
Posted: Friday, June 25, 2004 10:14 AM (GST)

If you email me the rdl I'll take a look and see if I can find the problem...

HarpreetBhatti

Parameter using User!UserID for Subscription Report
Posted: Friday, June 25, 2004 3:39 PM (GST)

Peter,

I just emailed the .rdl file and the stored procedure and a Word Document with more detail on the Issue to your hotmail email

Thanks for your help

-Harpreet

yiliu

Parameter using User!UserID for Subscription Report
Posted: Thursday, July 01, 2004 7:07 PM (GST)

Peter,

I have the same problem. When I try to subscribe a report which contains a subreport with a parameter, I get the same message that Harpreet got.

Thanks for any advice.

Yil

Peter

Parameter using User!UserID for Subscription Report
Posted: Thursday, July 01, 2004 8:50 PM (GST)

To be able to subscribe to a report - that report must not contain any expressions that have User!UserID.

reginamc

Parameter using User!UserID for Subscription Report
Posted: Friday, July 02, 2004 12:23 AM (GST)

I managed to work around this.

We are using windows nt authentication -- so people are logged into the db as their network id.

if you put this dataset into the report...

select suer_sname() as fullNetworkUserName,
right(suser_sname(),len(suser_sname())-charindex('\',SUSER_SNAME())) as username

you can refer to the fields within a scheduled report (Or an on demand report)

ASCNET\rmcclelland will be fullNetworkUserName (includes domain)

rmcclelland will be username (just the user, no domain)

I have no idea why they even have the global for user if you cannot use it when a report is scheduled!

Peter

Parameter using User!UserID for Subscription Report
Posted: Friday, July 02, 2004 1:58 AM (GST)

Yep that will get the username of the user account that is used to log into the Database.

But - the problem with Scheduled reports is that it is the Report Windows Service that is actually logging in to the database using the credentials in the DataSource - Integrated security with Scheduled reports by design doesn't work.

Or perhaps I'm missing something fundamental here?

Peter

daveroys

Parameter using User!UserID for Subscription Report
Posted: Sunday, July 04, 2004 2:11 AM (GST)

Wouldn't it be possible to acheive what you are trying to do by having a data-driven subscription (only available in the enterprise edition) and letting the users key in their subscription requirements on a form that puts their user id in to a table along with their other subscription requirements?

The data-driven subscription can then pass the user id to the report as a parameter.

angelo34jj

Parameter using User!UserID for Subscription Report
Posted: Thursday, July 29, 2004 12:29 AM (GST)

I got arround this by choosing the option "Credentials stored securely in the report server" in the data source

HarpreetBhatti

Parameter using User!UserID for Subscription Report
Posted: Monday, August 09, 2004 10:20 PM (GST)

We got around this by creating a Dummy Report Parameter in the Report and giving it a default value of something like 'DummyLanID'... and then we created a custom extension to the Subscription piece where in the code behind we got a hold of this Parameter by its name and then reset its value to the Windows Lan Id of the user using C# and Javascript.

CSR

Parameter using User!UserID for Subscription Report
Posted: Monday, October 31, 2005 2:07 AM (GST)

Any chance you'd be willing to make this extension available?

shekhar_shashi

Parameter using User!UserID for Subscription Report
Posted: Saturday, November 14, 2009 4:34 PM (GST)

I got the same error. I got past this error by setting up my user credentials in 'Execution Account' page of Reporting Services Configuration Manager. The execution account is used by reporting server to execute unattended operations.