This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

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.

Thanks

Arlie

Parents
  • 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.

    1. Set the Display Template on the tab page Value definition to %CompanyMember%
    2. Set the Condition (query) to
      EXISTS
      (
          SELECT 1
          FROM
          (
              SELECT CompanyMember, 
                     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
      )
      

    HtH

  • Hi Markus,

    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?

    Thank you,

    Arlie Ramirez

  • What version are you using?

    Can you share a screenshot from your issue in the Web Portal and the configuration of your parameter?

Reply Children
  • Hello Markus,

    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!

    Roman Demchenko 

  • 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.

  • Hi Markus,

    Thank you so much for all your help and valuable information. I was able to bring in the required fields but went against the department table. The clause wizard was then used to derive the following code and this worked excellent.

    (NOT EXISTS

    ( SELECT 1 FROM

    (SELECT UID_Department FROM Department WHERE 1 = 1) as X

    WHERE X.UID_Department = Department.UID_ParentDepartment ) )

    AND (isnull(ShortName, N'') <> N'0')

    AND (IsInActive = 0)