SSRS : How to give parameter values in URL Http requests

mdxssrs

I created a dataset in an SSRS report and this dataset uses a parameter. Here is the request:

select { [Measures].[ID Distinct Count]} on COLUMNS,
non empty filter({ [DIM ANESTH].[PBM ANE NOM].[PBM ANE NOM] }, 
not InStr( [DIM ANESTH].[PBM ANE NOM].CURRENTMEMBER.MEMBER_VALUE, 'Unknown') = 1) on ROWS
from BDD_PAT_ANESTH where ( StrToSet(@param_req, Constrained))

param_req is a parameter name I gave, and SSRS created a corresponding report parameter and joined both. Here are some screenshots:

All it works, but I want to call the report server via http requests (in C#), and provide with some parameter values. Usually, SSRS http URLs finish by the parameters : …&param1=value1&param2=value2 and so on. But I don't see how I can provide with the value "[DIM ANESTH].[ID].&[3]" for example.

An other way could be to define a custom parameter whose values are integers, and to define a second parameter whose origin is the first one and whose values are some members like "[DIM ANESTH].[ID].&[3]".

Do you know how I can achieve either method (I think the first one could be easier).

thank you.

PS: I found this about parameters but it seems to be only partially relevant.

EDIT : @McNets
I tried to follow your advice, but with no success: considering the third screenshot, the one with default values, I tried to put in my browser this url :

http://localhost/reportService?%2fSSRS%2fReport6_tryParamFrom0&rs:Command=PDF&param_req=5

and I got this error:

Reporting Services Error Default value or value provided for the
report parameter 'param_req' is not a valid value.
(rsInvalidReportParameter)

Please see : here from microsoft.
What am I missing?

Best Answer

Done. Defining a parameter 'param2' whose values are integers and captions are strings, the point is the MDX query:

select {[Measures].[ID Distinct Count]} on COLUMNS,
  non empty filter({[DIM ANESTH].[ID].MEMBERS  },
  [DIM ANESTH].[ID].CURRENTMEMBER.MEMBERVALUE = @param2 ) on ROWS from BDD_PAT_ANESTH

Related Solutions

Sql-server – How to pass multi-valued characters in SSRS Report

I found a solution. I did not properly split the values in the stored procedure with a UDF.

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:

=join(Parameters!<your param name>.Value,",")

This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.

Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.

Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:

WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))

Therefore, SSRS will pass the following value:

@Flag = 'A,B,C'

Then my UDF will parse that string out correctly to:

A
B
C

And populate my @Flag parameter correctly with SELECT * FROM UDF()...

SSRS – Graph Line Value for Parameter

You need to refer to the parameters in your main sql query.

Something like:

Where databaseName = @databases

Or if you want to be able to select multiple databases.

Where databaseName in (@databases)

Here is an example using date parameters and multi-select parameters in a report https://technet.microsoft.com/en-us/library/aa337396(v=sql.105).aspx

If you Google " ssrs parameter sql query" there are lots of working examples and even some youtube videos giving step by step instructions.

Best Answer

Related Solutions

Sql-server – How to pass multi-valued characters in SSRS Report

SSRS – Graph Line Value for Parameter

Related Question