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.
Thanks
Arlie