Report Query

[Former Member]

HI All

We have 3 prompts

1 prompt user enter the values, 2 prompt with lov, 3 prompt with lov.

in the query we want to pass

select

datavalue,timestamp from numericlog where logname = concat(concat(prompt1,prompt2),prompt3)

so that the user can get the result set.

We are getting the above values from symentic layer. 

Can we do this in webi or we should do this on Symenticlayer itself or how should we do this.

[Former Member · Accepted solution]

Hi Kalika,

You have two options, either create a single prompt and give the user concated value to enter.

To do this you have to concat the three columns in a dimension and set a prompt in WebI for it.

Second option would be to ask the users to enter three values first and then use that concated value as where clause using a condition .

I hope this helps

Thanks,

Avinash

[Former Member · Accepted solution]

Hi avinash,

Can you give me more detail please.  Sorry for troubling  you, new to webi and universe just wanted to know, thank you for helping me.

[Former Member · Accepted solution]

Hi Kalika,

Please follow the steps :

All the three prompts which the user enters, create them at the universe level using a Filter condition with @Prompt .

So your three prompts would be like :

Condition 1 = (Column = @Prompt (,,,,,,,)

Condition 2 = (Column = @Prompt (,,,,,,,)

Condition 3 = (Column = @Prompt (,,,,,,,)

When you will add these three conditions in the report, your three prompts will appear as it does now.

Now to Concatenate these three user values in one Prompt at the run time :

Add another condition for Logname in your case:

Condition 4 = (Column =  '@Where (Condition 1)' || '@Where (Condition 2)' || '@Where (Condition 3)'

Or else you can directly write the @Prompt Syntax  instead of @Where and contact it using pipes or Concatenate function.

Add this condition 4 in the report, This will not prompt you anything extra, the values passed for other prompts will be used in this prompt at the run time to Concatenate the string .

The above example is with Oracle, tweak the functions and syntaxes as per the database.

Please let me know if this helps.

Thanks,

Avinash

[Former Member · Accepted solution]

I am getting an error saying

"Multiple query Filters contain  a prompt with the same text, but different operand type or operator count of values.

This is how i am trying to pass

select datavalue,timestamp from numericlog where logjam = 'LT30111:IO.IOVALUE,2s_2W'

'LT30111:IO.IOVALUE,2s_2W' I am trying to break this in 3 prompt where first prompt user enters value manually LT30111

2 prompt :IO.IOVALUE or IOVALUE

3 prompt ,2s_2w or ,5s_5w

all these 3 has concatenate and go as one value.  How can i achieve.  Can someone help me out please.

[Former Member · Accepted solution]

Hi Kalika,

The above method explained should help you, the error is because you have not used same operator for all your prompts.

Prompt with same texts has to have same operators like '=', 'Mono/Multi'

Help me with the definition of all your prompts in case you are unable to find the cause of the error.

Thanks,
Avinash

[Former Member · Accepted solution]

Filter 1 = NUMERICLOG.LOGNAME = @Prompt(Obj1)

Filter 2 = NUMERICLOG.LOGNAME = @Prompt(Obj2)

Filter 3 = NUMERICLOG.LOGNAME = @Prompt(Obj3)

Filter 4 = NUMERICLOG.LOGNAME = @Where(Prompts\Filter1)'||'@Where(Prompts\Filter2)'||'@Where(Prompts\Filter3)

Multiple Query Filters contain a prompt with the same text, but different operand type or operator count of values. (IES 00015).

This is the error i am getting.

Am i doing something wrong.

[Former Member · Accepted solution]

Please help me with the Definition of @Prompt for all the filters .

[Former Member · Accepted solution]

I have 3 prompts

Obj1, Obj2 Obj3

Obj1 is empty prompt

Obj2 is with LOV containing :IO.IOVALUE; IOVALUE

Obj3 is with LOV having ,2s_2w

I have created 4 filters

Filter1 = columnfield = @prompt(obj1)

Filter2 = columnfield = @prompt(obj2)

Filter3 = columnfield = @prompt(obj3)

Filter4 = NUMERICLOG.LOGNAME = @Where(Prompts\Filter1)||@Where(Prompts\Filter2)||@Where(Prompts\Filter3)

Thank you so much Avinash for helping me out.

[Former Member · Accepted solution]

Hi Kalika,

Can you help mw with the definition of the prompts the exact synatax. Maybe a dummy one ..

Thanks,
Avinash

[Former Member · Accepted solution]

Hi Avinash i am attaching screen shots.

then i created filters

am i doing anything wrong here.

[former_member211127 · Accepted solution]

Hi Avinash,

Just create a dimension with concatenating all three objects. Use that dimension in the filter and create three prompts at same filter and concatenate them at prompt level.

XYZ(Dimentsion)= concat(concat(obj1),(obj2)),(obj3))

XYZ =concat(concat(prompt(obj1),prompt(obj2),prompt(obj3))

Hope this helps you!

Thanks,

Venkat.

[Former Member · Accepted solution]

Hi Venkat,

I have already tried that, it didn't work.

[former_member211127 · Accepted solution]

Kalika,

I did not understand, Were it throwing any error or were it not meeting your requirement?

[Former Member · Accepted solution]

This is the error i am getting.

Multiple Query Filters contain a prompt with the same text, but different operand type or operator count of values. (IES 00015)

[Former Member · Accepted solution]

Hi Kalika,

As per your screenshots, you have to change the prompt text of all the prompts from "Enter parameter" to say, "Enter obj1", "Enter Obj2" and "Enter Obj3" etc.

Try using this.

-Ankush

[Former Member · Accepted solution]

HI Ankush,

Where should i enter this under filters are under the prompts.

[Former Member · Accepted solution]

Kalika,

Change the prompt text in the parameter option. See your screenshots.

Change them to Obj1, Obj2 and Obj3.

-Ankush

[Former Member]

Finally i made it work.  What i did is i created a custom LOV values and dragged to the prompts

and then i did the concatenation and it worked fine.

Thank you all for your help.

[Former Member]

See if you are using an '=' operator for a prompt that is set as multi or vice-versa. If the same prompt is used multiple places, it should have the same type and same operand used. Please check.

[Former Member]

Hi Kalika,

On universe create an object like

Obj_concat = concat(concat(@select(obj1),@select(obj2)),@select(obj3))

where obj1 = object assigned to prompt1

obj2 = object assigned to prompt2

like wise for obj3..

Now you can apply where clause on this newly created object obj_concat.

Thanks,

Swapnil

[Former Member]

Swapping can you give me an example please how to create an object on a universe. sorry for troubling new to IDT.

[Former Member]

Can you give me an example of creating one object in the universe please using this example

datavalue,timestamp from numericlog where logname = concat(concat(prompt1,prompt2),prompt3)

[amitrathi239]

Hi,

Create one object in the universe.use concat function in universe and then use in prompt.

like

{fn concat(@Select(Store\State),@Select(Store\City))}

Amit

[Former Member]

can you explain me more Amit, little bit confusing what you said.  Lets say you have created an object how do you use that object as a prompt in webi.