Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

Passing mdx parameters to RS through URL (SharePoint Integrated Mode)

Hi there,

I'm running MOSS 2007 in Integrated Mode, and I have a number of reports that are deployed to the Reports Library, and are working just fine when you go there and run them interactively.

Now however, I need to address the report via a URL, and pass it a parameter. Just to add a bit more pain, the report is in mdx, and expects an mdx parameter.

Here is the parameter "prm_cost_centre":

[DIM LEARNER AIM].[COST CENTRE DESC].&[Business]

Here is the URL of the report:

http://vmmoss:88/ReportsLibrary/Report1.rdl

(If you hit that URL as is, it runs fine, using the default parameter)

Now, I know that we have to escape the ampersand, so I'm thinking that this URL should do it.

http://vmmoss:88/ReportsLibrary/Report1.rdl&rs:Command=Render&prm_cost_centre=[DIM LEARNER AIM].[COST CENTRE DESC].%26[Business]

But it doesn't - It throws a 400 Bad Request error.

I know I must be very close, but I just can't work out what URL I need to pass in order to get the damn thing to run.

Any ideas?

I don't have very much experience with MDX params, but I might recommend escaping the periods or possibly the [brackets] too. Sorry I can't offer much more advice than that, but as in any debug scenario, try truncating the param string until you get something that works, then go from there!

|||

It's not possible - you can't pass parameters through the URL when running Reporting Services in Integrated Mode.

http://technet.microsoft.com/en-us/library/bb326290.aspx

How lame is that? All of a sudden, Integrated Mode becomes...worthless to most people, surely.

sql

Passing error messages from stored procedure to osql command

I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:

IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN

If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.

Questions:

How can that be accomplished?

How to restart the stored procedure ones the error has been corrected?

Thank you for your help.Use "/b" with your OSQL, and use RAISERROR in your error trapper. In the batch file check "ERRORLEVEL 1":

osql ..... /b
if errorlevel 1 goto blah-blah|||Do you have a sample that I can look at? Thanks|||Watch out for word wrapping.
This one runs DBCC INDEXDEFRAG on specified server + database.

@.echo off
set server=%1
set db=%2
set uid=-U%3
set pwd=-P%4
if "%1"=="" goto ServerError
if "%2"=="" set db=master
if "%3"=="" set uid=-E
if "%4"=="" set pwd=
echo Checking for existence of a view on server %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -l 1 -Q"if object_id('dbo.vw_DBCC_INDEX_DEFRAG') is not null drop view dbo.vw_DBCC_INDEX_DEFRAG" -b
if errorlevel 1 goto LoginFailure
echo Creating a view on server %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -i"Create_vw_DBCC_INDEX_DEFRAG.SQL" -b
if errorlevel 1 goto CreateViewError
if "%uid%"=="-E" set uid=-T
echo Generating the final script for server %server% database %db%...
bcp %db%.dbo.vw_DBCC_INDEX_DEFRAG out %server%_%db%_DBCC_INDEX_DEFRAG.SQL -S %server% %uid% %pwd% -c
if "%uid%"=="-T" set uid=-E
echo Processing INDEXDEFRAG script on %server% database %db%...
osql -S %server% %uid% %pwd% -d %db% -i %server%_%db%_DBCC_INDEX_DEFRAG.SQL -h-1 -n -w 256 -o %server%_%db%_DBCC_INDEX_DEFRAG.LOG -b
if errorlevel 1 goto ScriptProcessingError
echo Check %server%_%db%_DBCC_INDEX_DEFRAG.LOG for any errors!
goto end
:ServerError
echo No server and/or database specified!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:LoginFailure
echo Failed to login to %server%!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:CreateViewError
echo Failed to create vw_DBCC_INDEX_DEFRAG!
echo Execution returned Error Code %ERRORLEVEL%
goto end
:ScriptProcessingError
echo Failed to process the script: %server%_%db%_DBCC_INDEX_DEFRAG.SQL
echo Execution returned Error Code %ERRORLEVEL%
goto end
:end
@.echo on

Passing Command line arguments from Visual Studio

Hi all,

Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)

Any help would be greatly appreciated :-)

Steven

No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.

If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.

|||

I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.

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

Passing Command line arguments from Visual Studio

Hi all,

Is it possible to pass command line arguments to a package when running it from within VS? I want to set the value of a variable via the commandline, and found that you can to this in DtExec with the "/set \Package.Variables[...].Value;..." syntax. According to the docs, you should be able to pass the same argument via the 'CmdLineArguments' property in the 'Properties' dialog of an SSIS project in VS (CmdLineArguments. Run the package with the specified command-line arguments. For information about command-line arguments, see dtexec Utility), but unfortunately, this doesn't seem to work (even though the exact same argument does work when entered in DtExec)

Any help would be greatly appreciated :-)

Steven

No. Since you are in a IDE/Debug environment with Visual Studio, I think it is a minor limitation that you cannot do this, you can just change and set anything your require in the IDE.

If you have external configuration information you wish to set all the time then /SET is probably not the best solution. Using the built in Configurations support in SSIS would be a better choice and this does work in VS. See the SSIS menu.

|||

I have the exact same issue. I just need a single parameter that needs to change every time I call my package, so it doesn't really warrant external configuration. The following article implies that CmdLineArguments are only taken into account whenever you use dtexec externally to execute the package and then attach to it to debug. See the last section ("Testing and Debugging your code") for details.

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

sql

Passing a date report parameter on the URL

I'm trying to call a report I created (and deployed to our dev server -
running reporting services 2005), while passing the parameters on the URL.
I think I'm very close, but it doesn't seem to like the format that the date
is in. However, I've tried everything I can think of (surrounding the date
with quotes, double quotes, pound symbols, and every combination of those I
could think of), and yet I'm still getting the error "The value provided for
the report parameter 'StartDate' is not valid for its type.
(rsReportParameterTypeMismatch) "
The URL I'm using is:
http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
The two "date" parameters are obviously set up as date types (especially as
when I browse to the report without passing parameters, it allows me to pick
the dates for each parameter, rather than just allowing text entry).
Any ideas/suggestions?Scott,
Try to not use the ' " or # and just the value like
Start_Date=06/17/2007&End_Date=06/20/2007
Reeves
"Scott Lyon" wrote:
> I'm trying to call a report I created (and deployed to our dev server -
> running reporting services 2005), while passing the parameters on the URL.
> I think I'm very close, but it doesn't seem to like the format that the date
> is in. However, I've tried everything I can think of (surrounding the date
> with quotes, double quotes, pound symbols, and every combination of those I
> could think of), and yet I'm still getting the error "The value provided for
> the report parameter 'StartDate' is not valid for its type.
> (rsReportParameterTypeMismatch) "
>
> The URL I'm using is:
> http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
>
> The two "date" parameters are obviously set up as date types (especially as
> when I browse to the report without passing parameters, it allows me to pick
> the dates for each parameter, rather than just allowing text entry).
>
> Any ideas/suggestions?

Pass user credentials RS web service?

I'm using the RS web service to pull back customized reports via a C#
app, which is running under a service account.
I need to pass in the user's credentials to the Render method to make
sure the user has been given access to the report on the Security tab
of the front end.
If I pass the default credentials, the service account's credentials
are used.
Can anyone help? Many thanks.
BurtYou need to create an instance of System.Net.NetworkCredential.
Try:
rs.Credentials = new System.Net.NetworkCredential(UserName, Password);
instead of:
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
"Burt" wrote:
> I'm using the RS web service to pull back customized reports via a C#
> app, which is running under a service account.
> I need to pass in the user's credentials to the Render method to make
> sure the user has been given access to the report on the Security tab
> of the front end.
> If I pass the default credentials, the service account's credentials
> are used.
> Can anyone help? Many thanks.
> Burt
>|||David,
Thanks, but how do I get the current user's password? I'm using windows
authentication on this intranet app.
Burt|||FYI, the solution was:
WindowsImpersonationContext windowsImpersonationContext = null;
WindowsIdentity currentIdentity =(WindowsIdentity)Thread.CurrentPrincipal.Identity;
windowsImpersonationContext = currentIdentity.Impersonate();
MyService.Credentials =System.Net.CredentialCache.DefaultCredentials;
windowsImpersonationContext.Undo();
windowsImpersonationContext = null;