User Parameter

On the tab “User Parameters” you will see the empty list. Let’s add the parameter “Country”:

In the field SQL you should set the query which returns two fields: a key and a name. This query should return all possible parameter values. In most cases you will build a query which returns a key and a name of any level of the dimension or attribute. In our case to get the list of all countries we’ll need the following query:

select distinct EnglishCountryRegionName, EnglishCountryRegionName

from AdventureWorksDW2008.dbo.DimGeography

Pay attention to the fact that if you are using “Adventure Works 2005” database instead of 2008 version, then in your query you’ll have to change the database name from “AdventureWorksDW2008” to “AdventureWorksDW”.

Press the “Execute” button to see the results of the query execution.

In case you’ll get the message, saying that the SQL Server user, under which the application server establishes connection to the repository DB, has no access to the database from which the data is selected (“AdventureWorksDW”) – give the appropriate (“data reader”) access to the user on the “AdventureWorksDW” database. After that press the “Execute” button once again.

After executing the query successfully you will get the window with a result:

We see the list of possible values of the parameter. Press the button «Close», then «Save». The parameter appeared in the list:

Attention. If the database where you run the query and the repository DB are on different servers, you should create a linked server on the SQL Server where the repository DB is and use the query like this:

select … from [LinkedServer].AdventureWorksDW2008.dbo.DimGeography