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.
Thing is, that the Table Column (query) defines the column from which the value should be returned when the user selects an entry in the lookup. But in addition, by selecting the column, you also define the table you are loading the values from. In your case, the lookup will present Person objects as your column is Person.CompanyMember.
The condition itself is a where clause where you can limit the list of entries for the lookup but cannot change the selection part by any means (e.g. Distinct, Top, ...).
The solution for your lookup needs to things.
MAX(UID_Person) AS UID_Person
FROM Person p
WHERE NOT CompanyMember IS NULL
GROUP BY CompanyMember
) AS x
WHERE x.UID_Person = Person.UID_Person
Thank you for the details and this solution partial resolved my requirement as the distinct Company is being displayed in my parameter. The only issue now is that the report is executing in the Web Portal and displays the company as expected but it is also displaying the related UID_Person within the report parameter when selected. I would only like to display the company and not the UID_Person that was used in the SQL query for the 'MAX'. I tried to reconstruct the query in a different way but was not successful. Is this the only solution that you recommend to bring back the company name in a parameter based on my requirement and not show the UID_Person?
What version are you using?
Can you share a screenshot from your issue in the Web Portal and the configuration of your parameter?
I was talking with Arlie (experienced issues adding screenshots), and I'm just attaching them. (V 8.0)
I will let Arlie add the rest if needed.
Have a great day!
Sounds like my little workaround doesn't work well in the web portal. You can see that in the Report Editor as well the selected object type will be marked as employees at the root of the combobox.
What you need to do then is to create a new read-only view with Schema Extension that contains the list of companies (using your distinct select statement if you like) and use that read-only view in your parameter.