How to Add 3 dummy values to a variable in report or object

building reports webintelligence
1

Hi to all
How to add 3 dummy values to a variable in webi report or 3 dummy values in a object in universe and bring into the report. When I drag this object into report it should display as row wise 3 values but not as one straight row. I tried to bring them as optional prompt but I am able to bring only one dummy value but not 3. Any help is appreciated.
Thanks Mohan[/list]

Mohanbo_us (BOB member since 2008-05-06)

2

I do not fully understand your requirement. Can you provide an example of what you are trying to achieve along with the results you currently get. Also explain what you have done to get your current results.

thanks

REB01 :us: (BOB member since 2004-11-29)

3

Are you looking for something like this?

OR

Prashant Purohit :india: (BOB member since 2009-02-18)

4

Sorry for the late reply. I wanted to put drill filter in the report so that user can select what ever block/ table he wanted to see. I have 3 different blocks and one summarized table I wanted to give user option to select which table/block he wanted to see or all blocks. I have achieved this by adding a prompt and user response but Now I want to do this in the report it self by not adding prompt. So if Is there any way to create a dummy variable which holds 3 values example block1, block2, All. So that I can use this variable in report filter and drill filter and achieve my results.
Currently when I run the report it prompts user to select which block he wants to see and based on the selection he will be able to see that block/ table and summarized table. Now I want to achieve the same results without using prompt. Any help is greatly appreciated.
THanks Mohan

Mohanbo_us (BOB member since 2008-05-06)

5

Hi Mohanbo_us
were you able to create a dummy variable with 3 values at report level in webi?

thanks

friend (BOB member since 2006-07-03)

6

If you don’t provide a prompt to the user… how do you expect to know what the user wants to see?? (I can only guess using input controls perhaps?)

As for creating a dummy variable set… that’s easy.
Just add another query to the report… with custom SQL.

Select 'Block 1' as "Selection Option' UNION Select 'Block 2'  as "Selection Option' UNION Select 'Block 3' as "Selection Option'

Then you can use the “Selection Option” just like you would any other object.
In an input control for example, to perhaps indicate a user choice.

JPetlev (BOB member since 2006-11-01)

7

Hi JPetlev,
Thanks for responding. But the question is creating a dummy variable at the report level without using Any data provider.

Like we do in sql using union statements as you listed above, can we do the same at webi report level. We can create a variable with a single constant value. but what if we need multiple values?

Thanks

friend (BOB member since 2006-07-03)

8

I’m not quite sure if we’re talking the same thing… when you say REPORT level, are you refering to the REPORT side only, and not the QUERY of the document? Usually when people say report level, they typically mean ‘document’ level (ie: not-universe-stuff), so I incorrectly assumed that was the case here as well.

The short answer is you can only use custom-sql in the Query itself (Query tab within the Document).

Within a “variable” on the report side of the document, think of it as a basic formula only, it’s just a way to convert, calculate, aggregate and perform logic operations on a SINGLE record value (though it is set based). Thus one value goes in (or a set) and one value comes out per record.

There is no way to use a variable to return a record set that I can think of. Only Queries can return record sets… but you can use custom sql union statements to do what you want. (But if your documents have the edit SQL permission locked down for webi, as I now suspect, then you’re out of luck).

JPetlev (BOB member since 2006-11-01)

9

Hi,

Try creating a variable with this formula

=If(RowIndex()=1) Then "A" Elseif(RowIndex()=2) Then "B" Elseif(RowIndex()=3) Then "C" Else ""

Let me know if that helps.

M H Mohammed :us: (BOB member since 2010-06-16)

10

Hi

Thank you for throwing some light on this.

The solution provided creates values but when I put in the report:
RowIndex Col 1 Dummy variable
1 book A
2 file B
3 paper C
4 pen

and 4 is emtpy as rowindex() =4 is not listed. But iam looking for a field that can bring in values as:

1 book A
2 file A
3 Paper A
4 Pen A
5 book B
6 file B
7 Paper B
8 Pen B
and so onn … like a cartesian product with that dummy varaiable?

Any ideas please?

Thanks

friend (BOB member since 2006-07-03)

11

You’ve already been given your answer…

You had a requirement of

The answer is you cannot.
WEBI does not allow you to create data out of thin air, it can only calculate on the data you’re given.

If you can convince someone to open up the permissions on your WEBI settings to allow you to create a custom data provider (aka query tab) with Custom SQL, then you can do what you want to accomplish.

Otherwise your only option is to export the data into Excel or Access, or some other reporting software and manipulate it there where you have FULL control.

JPetlev (BOB member since 2006-11-01)

12

Hi Mohan,

You cannot create a dummy variable with array of values. One way to achieve it is certainly using a new data provider with custom SQL in it as mentioned by JP.

Another way to achieve this is by creating a derived table in universe with the Union SQL to create dummy data.Create an object in the universe from the column of the derived table and later use it in a new data provider.

Now, the solution for friend’s scenario is again creating a derived table in the universe and creating an object from this table. When you pull this new object with dummy data within the same data provider of your report, it will create a cartesian product since there are no joins between derived table and other tables in the query. This will achieve the result you want to see in your report.

Hope this helps.

Regards,
S2

sayeed66 (BOB member since 2011-05-11)

13

jpetlev is correct, however, your response here seems to indicate that a cyclic calculation could be acceptable.
if you start with a variable containing:


=1+(((RowIndex()/3)-(Truncate((RowIndex()/3);0)))*3)

to get a cell that cycles through the set {1,2,3} you could then use it as an index to extract a letter from the string “ABC” to get something that cycles throuhg the set {A,B,C}.

By changing the index number from 3 to whatever you want and using a string of equivalent length, you can have whatever size set desired. However, for single character Alpha only strings you are limited to a max of 26 items.

Good Luck!

lawrence.sproul (BOB member since 2016-08-16)