Results 1 to 5 of 5

Thread: Report filter options

  1. #1
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    205

    Default Report filter options

    Within a DynamicAI report, how would one build a multi selection filter finding selected values inside a string?

    I have one column that may contain any combination of following Values: [PM][CO][ST][SW][EX]

    Lets say I have 3 Rows with a mixture of these values

    1. [PM][CO][ST]
    2. [PM][ST]
    3. [EX][CO][ST]


    If the user selects [PM], I want to show the user only Row1 and 2, that contain [PM]
    If the user selects [PM] and [CO], I want to show the user Row 1, 2 and 3, which contain at least one of the values.

    Within a Dynamic SQL View, I have previously worked with Parameters, but only for Dates using the @DATE parameter. Is there something similar with lists, generated from another table?

    Ben

  2. #2
    Join Date
    Feb 2009
    Posts
    1,483

    Default Re: Report filter options

    Hi Ben,

    I find your question ambiguous, so I’ll answer all the possibilities I can think of.

    You define filters on table.columns, not rows. When you choose a multi-select filter and the user highlights multiple values in it, then Dynamic AI generates in its query: Where Column_Name IN ('Val1','Val2','Val3'). Then any row in the result set having any of those 3 values in the filtered column will be selected.

    If you want to use a dyn-view parameter, then you would manually write a where clause in the view -- and instruct you users how they need to delimit their entries properly. (There is a convoluted, complicated way to attach a selection list to a parameter but its too involved to explain here.)

    If you want to make an enforced filter based on user-profile then use Alias Bundles. That's basically defining a set of values with a common key (think Codemast), and then setting the user-profile alias to the key value. You define the bundle of values in the system, system, security tab page. There you can specify the data source for the values and the columns for the key and the value. At runtime Dynamic AI assembles the list of values for the given key and generates a where clause using the IN (value list) syntax.

    If you want to search for a substring within all the string columns within a data source then you use 'FREE SEARCH' that Dynamic AI by default makes available in every report.

    This video demonstrates these and other advanced techniques: Adaptive, Embedded, Web-based Reporting (38 min.)

    Bob

  3. #3
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    205

    Default Re: Report filter options

    I started with adding a "Free Search" filter on the desired column.

    1 - Typing in Filter Values:


    1. Typing in '[CO]', the report returns rows with only [CO] within the column value
      1. Code:
        SQL: (LTRIM(RTRIM(DAI_.Division)) = '[CO]')

    2. Typing in '*CO*', the report returns all rows with values containing 'CO' within the column value [This is great]
      1. Code:
        SQL: (LTRIM(RTRIM(DAI_.Division)) LIKE '%CO%')

    3. Typing in '*CO**PM*', the report returns only rows containing both values [This should return containing either CO or PM or both - What am I missing?]
      1. Code:
        SQL: (LTRIM(RTRIM(DAI_.Division)) LIKE '%CO%PM%')


    I tried the same reports with % instead of * as the wildcard placeholder. The results were the same.

    2 - Selection List from Values in Column:
    When the report generates the (multi-) selection list from the values in the column, it generates values like [CO][CO][PM][SW] or [CO][CO][CO].
    While this is expected, it is not desirable for the user to select the right combination.

    3 - Selection list with list from Divisions table [This is what I desire to produce]:
    Instead of manually typing the filter values, I created a list from the Divisions Table to be used as multi selection list.
    I have many reports running using this type of selection. We are doing exactly this on all Sales reports using a (multi-) selection list for all active SalesReps.


    1. Selecting CO, the report returns no records
      1. Code:
        SQL: (LTRIM(RTRIM(DAI_.Division)) IN ('CO'))

    2. Selecting CO and PM, the report returns no records
      1. Code:
        SQL: (LTRIM(RTRIM(DAI_.Division)) IN ('CO','PM'))


    I did notice that the SQL between the Typed (uses LIKE) and Selected (uses IN) filter is different.

    What am I doing wrong?
    Last edited by Benjamin; 6-Apr-2021 at 12:39 PM.

  4. #4
    Join Date
    Sep 2011
    Location
    Minneapolis, MN
    Posts
    205

    Default Re: Report filter options

    After not receiving any results with above filter, I adjusted the division selection list to include the brackets. Now I am getting some results, but not all.

    1. Selecting [CO], the report returns records that only contain [CO] in the column value.
      1. Code:
        SQL: (DAI_.Division IN ('[CO]'))

    2. Selecting [CO] and [PM], the report returns records that only contain one of the selected values, either [CO] or [PM], but not combination of more than 1, or a combination of both.
      1. Code:
        SQL: (DAI_.Division IN ('[CO]','[PM]'))


    Playing around inside an SQL View I found the right combination as an SQL Expression:
    Code:
    (DAI_.Division LIKE '%CO%' OR DAI_.Division LIKE '%PM%')
    How would I accomplice that using multi-selection filters in a DynamicAI Report?
    Last edited by Benjamin; 6-Apr-2021 at 02:56 PM.

  5. #5
    Join Date
    Feb 2009
    Posts
    1,483

    Default Re: Report filter options

    If you're asking can Dynamic AI use random multiple values from a list of values in a multi-select drop-down list attached to a column filter, and then generate a where clause with: LIKE value 1 OR LIKE value 2 OR LIKE value 3, the answer is, no, it does not work that way. Any single filter applies to the single column data associated with that filter and of course it cannot know that in some circumstance, instead of using the entire column value, you want to use a substring from that column value. Multiple filters defined in a report will be combined using 'AND' in the "Where" clause. The use of 'OR' and 'LIKE' require programming, either in the 'Free Conditions' block in report design, or in the SQL for a dyn-View. Like I said, attempting to attach a selection list to a view parameter is complicated and well beyond the scope of a forum post. If you haven't watched that video, have a look. It demonstrates the use of dyn-partitions. If what you want to do is possible with Dynamic AI, then the solution probably involves either a partition list or redirected filters. (A redirected filter is a filter defined in a report, but fed back into the dyn-view sort of like a view parameter.) You'll find more information about both in the help doc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •