Using empty User field as Any user

Hi!

I need to create a Live Report which would allow me to filter the results by User.

In case when User input is blank, I need it to be considered as "Any value" and the report should show results for all users.

But if I leave the User input field blank, the system returns me "No results found"

For testing purposes I removed the link to this user input field in SQL script itself and replaced it with the some fixed value, eg: instead of "UserID = %1" I put "UserID" = 22881, but still if I leave input field blank, it returns "no results found", though the result set in no way depends on the input.

Is this a behavior 'as designed" of Content Server 10, and are there any standard ways to tell CS that blank field should be considered as "Any value"?

Thank you in advance for assistance.

Find more posts tagged with

Comments

Brad_Ceballo

This is for MSSQL and will add a leading and trailing wildcard to your query. Leaving the %1 UserInput parameter blank will pull back everything, but you may also have undesired results when targeting exact usernames (i.e. a parameter value of admin will find “Admin” and “otadmin@otds.admin”)

SELECT * FROM Kuaf WHERE Name LIKE '%%' + %1 + '%%'

Brad

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, November 21, 2012 5:41 AM
To: eLink Recipient
Subject: Using empty User field as Any user

Using empty User field as Any user

Posted bylushnikova@terralink.ru (Lushnikov, Alexey) On 11-21-2012 05:39

Hi!

I need to create a Live Report which would allow me to filter the results by User.

In case when User input is blank, I need it to be considered as "Any value" and the report should show results for all users.

But if I leave the User input field blank, the system returns me "No results found"

Is this a behavior 'as designed" of Content Server 10, and are there any standard ways to tell CS that blank field should be considered as "Any value"?

Thank you in advance for assistance.

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Alexey_Lushnikov

This is not MS SQL problem.

I tried to use wildcards. And, as I described in the second part of my post, I replaced %1 in SQL with some fixed value, so the request in no way depended on user input, but still live report returned no results.

Looks like CS performs a check and if this field is blank, it just stops, doen't perform any requests, and returns empty result.

Brad_Ceballo

Sorry Alexey,

The provided SQL works for name, but you’re using a “user selection” box which equates to an Integer value in the database.

The behavior you’re seeing was logged as bug #LPAD-12975, but was closed with resolution “as designed”.

Strings and Integer Bind Variables are handled differently by Oracle’s SQL Client and MSSQL’s ODBC Connection, which is why the “name” query is submitted and the “userID” query is not.

In summary, you’ll need to manually enter the username as a string (to use the provided SQL) or be forced to select a user.

As a workaround, you can have 2 reports – One being user specific and the other not. This is the approach taken with in the shipped LiveReports that run into the same issue.

Best regards,

Brad

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, November 23, 2012 12:20 AM
To: eLink Recipient
Subject: RE Using empty User field as Any user

RE Using empty User field as Any user

Posted bylushnikova@terralink.ru (Lushnikov, Alexey) On 11-23-2012 00:17

This is not MS SQL problem.

Looks like CS performs a check and if this field is blank, it just stops, doen't perform any requests, and returns empty result.

[To post a comment, use the normal reply function]
Topic:	Using empty User field as Any user
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Alexey_Lushnikov

Thanks for clarification.

I made two different reports.