Advanced Search in Report Studio

[Web2CRM]

Has anyone created a report in Report Studio with advanced search functionality as similar below?

Keyword Search _____________ (text input)

       Search Name 1 () tick box
       Search Name 2 () tick box
       Search Name 3 () tick box
       Search Name 4 () tick box

SUBMIT (button)

Once the submit button is hit, the report will display the result based on the value provided in the Keyword Search and Search Name tick boxes.

Thanks!!!

[CognosPaul]

If I understand, you want to have a text box prompt (Keyword Search) that the user fills out. He then checks the specific fields he wants to search through?

So if he's looking for the string "cromulent", he can select to search through the fields
1. "user comments"

• 
  2. "employee comments"
  3. "product description"
  4. "reviews"
• 
  
  The end filter would be something like: ([User Comments] like '%cromulent%') or ([Reviews] like '%cromulent%')
  
  

[Web2CRM]

Hi PaulM,

Yes, you exactly got what I mean...it is like a search engine inside the report.  So basically, there's only a single page report; no prompt page to be created.  The user will click the twistie "Advanced Search" in the report then it will display the above filters.  Thanks!!

[CognosPaul]

Due to infosec, I can't post a report XML

Do the following:

In your query, drag in all of the fields you'll want the user to be able to search.
In the page, create a text box prompt with the parameter SearchString.
Create a multiselect checkbox prompt. In the static choices, add the data item names of each field you want the user to search.

Add the following to the filter of your report query:

Code Select Expand

#
join('] like ' + sq(prompt('SearchString','token','whatever','%','','%')) + ' or [',
split(';'
,
promptmany(
   'Search Fields'
  ,'token'
  ,'1=1','[','','] like ' + sq(prompt('SearchString','token','whatever','%','','%'))

))
)
#

Let me know how it goes.

[Web2CRM]

Thanks PaulM..

I will try your suggestion, few questions though:

1.) 'token','whatever' = is this something that I need to create like parameters or name of a search field?  Or can I use that as is.
2.) Can I use the existing query in my report? Or do I need to create a separate query for filter use only?
3.) Can I add additional filter selection like a combination of tick boxes and radio button? Then use your filter script?

[CognosPaul]

The 'token' describes the data type of the parameter. Since it's not a string yet, leave it as is. We need to append the % before and after the fragment, inside the quotes. If you use the contains operator instead of the like, then you could switch that to string and get rid of the %.

'whatever' is the default value of the prompt. Since it's an optional, you can leave that as is. If the user doesn't select a field to search, then the prompt will always default to 1=1.

Add the filter to your existing query.

I rewrote it to accomodate multiple searchfield prompts.

Code Select Expand


#
prompt('SearchString','token','1=1',
join(' like ' +sq(prompt('SearchString','token','whatever','%','','%')) + ' or '
,

grep( '\[',
  array(
    split(';',promptmany('SearchFields','token','1'))
  , split(';',promptmany('SearchFields2','token','1'))
  )
)
) + ' like ''%','','%''')

#


For every searchField group you have, just add another ,split(promptmany) line

If the user doesn't select a searchstring, then the filter will default to 1=1 (notice the entire join statement is in the before text in the searchstring prompt). The checkbox and radio prompts need to have the full path to the field. So if you have Field1 and Field2 in your query, then the static choices should look like:

Code Select Expand

   USE                |    Display
----------------------+-----------
[Field1]              | Field 1
[Field2]              | Field 2
[NS].[Table].[Field3] | Field 3

[Web2CRM]

Thanks PaulM..

I'll let you  know how it goes,  ;)

[Web2CRM]

Hi PaulM,

I got this error when I tried to run the report.  Parsing error before or near position: 5 of: "like"

[CognosPaul]

Which rdbms are you using? You may need to switch like to contains and to get rid of the percent signs.

[Web2CRM]

I already switched to 'contains' but still got the same error "Parsing error before or near position: 9 of: "contains"

[CognosPaul]

Let's try something

Create a new page, and on that page create a singleton with the following expression:

#sq(
prompt('SearchString','token','1=1',
join(' like ' +sq(prompt('SearchString','token','whatever','%','','%')) + ' or '
,

grep( '\[',
  array(
    split(';',promptmany('SearchFields','token','1'))
  , split(';',promptmany('SearchFields2','token','1'))
  )
)
) + ' like ''%','','%''')
)
#

This will print out exactly what the filter will be. Run your report again and paste in the results.

[Web2CRM]

I got this error:

RQP-DEF-0217 Wrong expression type: ' like ''%SMS7010%'''.RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references.

I attached a sample screenshot of the report when I run it.

[CognosPaul]

This is after you created the new page with the singleton? The page should be the first one in the list.

Just to doublecheck, the singleton contains a query calculation that looks like:

#sq(
prompt('SearchString','token','1=1',
join(' like ' +sq(prompt('SearchString','token','whatever','%','','%')) + ' or '
,

grep( '\[',
  array(
    split(';',promptmany('SearchFields','token','1'))
  , split(';',promptmany('SearchFields2','token','1'))
  )
)
) + ' like ''%','','%''')
)
#

Note that there is the sq() function wrapped around the prompt.

Worst comes to worst, just run that query in tabular mode to see what it returns.