ReportServer Forum

jar

Member

Registered: 2013-08-08

use a uservariable in a parameter

Hello,

Is it possible to use a uservariable in a parameter.
I have the situation that in our database there are about 150 unique stores. But the enduser can only see 1 or a few of them. For each user we have u uservariable of the type list with 1 or more stores.

I have created a dynamic list to select some values from the database and I added a parameter of the type  user-variable. For those end-users with multiple stores I get all the data of both stores at the same time. I would like to have an option to choose the one store (default value) or one of the storen I have in my user-variable.

So I tried a datasource parameter and added a query to load all stores. But then I see all stores and not only the stores in my user-variable. I also tried adding a user-variable parameter to the report and then a datasource parameter in which I filter where the store field = $P{store} but that also is not working.

Any clues on how to filter a datasource parameter query using a uservariable?

Regards,
Jeroen

Last edited by jar (2017-02-17 11:06:25)

karolina

Member

Registered: 2014-08-09

Re: use a uservariable in a parameter

Hi,

Have you found the solution?

Karolina

jar

Member

Registered: 2013-08-08

Re: use a uservariable in a parameter

No, unfortunatly not ... seems like something more people would use.

karolina

Member

Registered: 2014-08-09

Re: use a uservariable in a parameter

Hi,

Looks like it is possible - provided that I understood your case correctly.

I created a simple test table (in a PostgreSQL forum_test datasource; if you use another database, you may need to adjust queries):

1. Test table and sample values:

CREATE TABLE stores
(id SERIAL PRIMARY KEY,
name VARCHAR(255),
symbol VARCHAR(10));

INSERT INTO stores (name, symbol) VALUES
('Store 1', 'A'),
('Store 2', 'A'),
('Store 3', 'B'),
('Store 4', 'B'),
('Store 5', 'C'),
('Store 6', 'C'),
('Store 7', 'D'),
('Store 8', 'D'); 

2. Then I created a user variable named 'storeUserVariable' (of a list type) and assigned values:
for admin: A | B | C
for test_user_a: A | C
for test_user_b: B

3. Then I created a dynamic list report with 2 parameters:
storeUserVariable - a user variable parameter
storeSymbols - a datasource parameter, with multiple selection, dependent on the storeUserVariable parameter

4. The main query for the report is:

SELECT * FROM stores WHERE $X{IN, symbol, storeSymbols}

5. The specific details for the datasource parameter are:

datasource: the forum_test datasource
query:

SELECT DISTINCT symbol FROM stores s 
WHERE $X{IN, symbol, storeUserVariable}
ORDER BY symbol ASC

6. In the parameter view of the report each user may select from symbols of stores that are in his/her user variable list, i.e: admin can select from A, B, C, test_user_a from A, C and test_user_b sees only B.

7. Single selection would work too, you just have to modify the report query:

SELECT * FROM stores WHERE symbol = ${storeSymbols}

Please tell me if this works for you.

Karolina

jar

Member

Registered: 2013-08-08

Re: use a uservariable in a parameter

Hello Karolina,

Thanks you very much for your very detailed how-to. You understood perfectly.

I had the same idea by first creating as parameter with userVariable and the a datasource parameter using the userVariable parameter. But somewhere trying to get it working I lost the $ in the main report Query. So I had X{IN, symbol, storeSymbos} and totally overlooked it.

And I also did not add the userVariable parameter in the "Depends on" option. I thought that pointing to the userVariable parameter was enough to have it linked. But you have to explicitly add the userVariable parameter as a "Depends on" value in the datasource parameter. That did the trick for me. It is working correctly for me now.

It feels a little redundant to have two parameters for what I think should be just one (a userVariable parameter but with the options like a regular datasource parameter). But I made the userVariable parameter mandatory and hidden so is looks nice also.

Thank you.

Kind regards,
Jeroen