Create Report Parameter using Table

Hi ,

I am new to the reporting tool and attempting to create a Report Parameter that pulls all the distinct company members from the database so the user can select the desired company name. I have created the report but when using the Data Source = Table and select Person - CompanyMember for the Table Column (query) it does not bring back the distinct company members from the tables. When I execute the report in the web portal it shows the parameter but gives me the values for all the person (Users) and not the company. In reality, I would like by Parameter to = 'select distinct companymember from person' so that the user can select the available companies from Identity Manager. Am I able to use this custom SQL to create a parameter or any assistance would be greatly appreciated.

These are the settings I currently have set up for my "Company" Parameter

1. General settings

Parameter name = Company

Parameter Type = User Prompt

Display name = Company

Mandatory Parameter = NO

2. Value Definition

Data Type = String

Range = No

Multivalue = Yes

Multiline = Yes

Data Source = Table

Table Column  (query) = Person - CompanyMember

Condition (query)= NULL

Empty value override = yes (box is checked)

Sample Value = NULL

Default value = NULL

3. This is the SQL that I am using in my Data Source for the WHERE clause for my Parameter:

AND p.companymember IN (Select ParameterValue from dbo.QBM_FCVStringToList(@company,Char(7),0,1))

NOTE: I would like for the user to have a prompt to select the company name from the database.



Parents Reply Children
No Data