nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Is it possible with LiveReports to query SQL using more than one Input parameter with AND or OR and

Chad Latka

Is it possible with LiveReports to query SQL using more than one Input parameter with AND or OR and one of the inputs left blank ?

I have two inputs to a LiveReport which are attributes from a Category of Attribute

SSN -- %1

NAME -- %2

Example: AND (LLAttrData.ValStr = %1) AND (LLAttrData_1.ValStr LIKE '%%'+%2+'%%') sort of works

but AND (LLAttrData.ValStr = %1) OR (LLAttrData_1.ValStr LIKE '%%'+%2+'%%') does not return expected results.

I want to return results if one or the other can be left blank (using one or the other input parameter) basically ignoring what is one column depending which input is used.

Find more posts tagged with

Comments

christopher.choo@delawareconsulting.com

I had this question before but couldnt find the solution from KC. My alternative i go for creating a storeprocedure with case condition to achieve this objective.

Some feed also said that i need to use webreport to achieve this objective.

Hope it gives u an idea to move on.

John Simon

Do you have LiveReportExtenstions installed (I believe it comes with WebReports)? If so have a look at the SQL Templates section in their documentation.

Cheers...

christopher.choo@delawareconsulting.com

Hi John,

How do i get to sql template you mentioned? i have webreport installed, but not sure if it has livereport extension u mentioned.

Still new to this but i have knowledge on SQL. Can you give more guide like screenshots on how to get more sample/template for livereport?

Appreciate it very much if can get help from you. Thank you.

Pramod Mohandas

Hi Christopher,

Open the LiveReport via the Edit option from its Function Menu.

You should then be able to access the suggested Help section as below (Attached Image).

Note the difference in the LR’s GUI in the background? This is due to the presence of an add-on module – LiveReport Extensions

image001.png

sıɹɥɔ

Hi Chad/Christopher,

The conditions in your SQL expressions can be against the user input in addition to being against data. So, for example, you can do something like:

... where (ValStr=%1 or %1='' or %1 is null)

to allow an empty user input to be ignored. (The two comparisons against %1 allow for support of both SQL Server and Oracle.)

Hope this helps,

-chris

Ian_Whitfield

Hi Christopher,

If you are on CS10.5 then all LiveReport Extensions features, including templates, have been merged with core LiveReports. The documentation for the feature is here: https://knowledge.opentext.com/knowledge/piroot/llescor/v100500-1503/llescor-h-ugd/en/html/llesrep-h-ugd-lr-sql-templates-bg.htm.

As mentioned, with CS10, LiveReport Extensions is an optional module bundled with WebReports. The above documentation still applies to CS10 as I don't think much has changed with this feature.

Regards
Ian

Chad Latka

@ Chris Webster.

Thank you for the reply and for SQL guidance/lesson, I will give that a try !

Chad

Chad Latka

@ Ian Whitfield,

Thank you for your reply. We do not have the Web Reports License in our CS10.5 environment and I did not know that the Extensions or SQL Template was actually included. Thank you for providing that note and link to the Help section on the subject ! I will definitely take a look at that.

Chad

christopher.choo@delawareconsulting.com

Quoted Ian Whitfield on 02/02/2016 07:03 AM:

Hi Christopher,

The documentation for the feature is here: https://knowledge.opentext.com/knowledge/piroot/llescor/v100500-1503/llescor-h-ugd/en/html/llesrep-h-ugd-lr-sql-templates-bg.htm.

Thanks Ian for the link. it's very helpful.

One more question on this, have you tried to have dropdown in livereport? presentation wise it doesnot have to be a real dropdown, it can be something that allow user to select value from a range of custom values.

My findings tell me it's not possible with only livereport, has to use webreport, do you have same understanding?

Thank you.

Hey Chris,

I'm a sql user too and still sync my sql knowledge with oracle db(forced to because of the project).

Thanks for the guidance, will try out and post my findings here if i have anything solid.

Regards,

Christopher

Ian_Whitfield

Hi,

My understanding is that this isn't possible without WebReports or some kind of weblingo/oscript customisation.

Regards
Ian

Jean-Philippe_Martin

I don’t know if it fit exactly what you are looking for but I wanted to have an optional parameter for one of my report, so this is the way I did it in the SQL :

and (

SUBTYPE IN (0, 141, 142, 136, 202, 751)

OR (%4 = 1)

)

If the user input 1 for the %4 parameter, it will negate the condition.

De : eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Envoyé : 2 février 2016 07:57
À : eLink Recipient
Objet : Is it possible with LiveReports to query SQL using more than one Input parameter with AND or OR and one of the inputs left blank ?2 2

Courriel provenant de l’externe
ATTENTION, avant d’accéder à une pièce jointe ou à un lien de ce courriel, assurez-vous que celui-ci provient d’un tiers deconfiance.

Is it possible with LiveReports to query SQL using more than one Input parameter with AND or OR and one of the inputs left blank ? 2 2

Posted by Whitfield, Ian On 02/02/2016 07:48 AM

Hi,

My understanding is that this isn't possible without WebReports or some kind of weblingo/oscript customisation.

Regards
Ian

[To post a comment, use the normal reply function]
Topic:	Is it possible with LiveReports to query SQL using more than one Input parameter with AND or OR and one of the inputs left blank ?
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Appu_Nair1

If a hardcoded pull down is what you need see this method where I use a customview object to call a LR.

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=62056557&objAction=download&viewType=1

Quoted Ian Whitfield on 02/02/2016 11:35 AM:

Hi,

My understanding is that this isn't possible without WebReports or some kind of weblingo/oscript customisation.

Regards
Ian