Splitting a user response from a prompt into multiple rows

[bassam]

Dear Experts ,

I have a prompt that takes multiple patterns from the user in one filter and would create a table in the webi report with three columns as shown in the image below:

Now I am trying to create a variable in which I can take the user response from the prompt and parse it on the basis of Pipe operator( | ). Such that the string values can convert into rows such as

And then use that variable to create a new table to get the sum of column C based on pattern.

Tools Using:

SAP Web Intelligence 4.2 SP03, Information Design Tool 4.2 SP03, Oracle 11g

Thanking you in advance

Regards,

Bassam

[jyothirmayee_s]

Hi,

Basically you want to display prompt values entered by the user, right?,

==>Try UserResponse() to display prompt values on the report and drag and drop dimension to a table column which prompt is based on.

Say, if you have prompt on State :

=Userresponse("Select States");

and drag and drop State dimension into the table.

==> Pipe sign to separate multiple entries: =Replace(UserResponse("Select State");";";"|");

If you want to show them in rows then =Replace(UserResponse("Select State");";";Char(13)+Char(10))

Thanks,

Jothi

[bassam]

Dear Jyothirmayee A,

Thank you for your response.

I altered the formula behind my the variable that would previously get UserResponse() without splitting the string based on Pipe operator. As per your comment I altered the Formula to this

=Replace(UserResponse([Query 1];"Fare Basis (* for all)");"|";Char(13)+Char(10))

Is this what you meant? With this I am able to split the user response based on pipe operator in a single cell with multiple rows.

But what I am looking for is to create multiple row cells so that I can calculate sum of other columns for that particular pattern/string. In the above case I want it to create four row cells for each of the column.

[jyothirmayee_s]

Hi,

Yes, this would show as multiple rows in single cell.I'm afraid you would be able to achieve this.

We cannot put each value in an individual row of a table.

Instead use a dimension that prompt is based on within the table as column. Like below, I have a prompt (promptval) on Group Acc Number .

Thanks,

Jothi

.

[bassam]

Hello Jyothirmayee A,

Since I am using a pattern match in my prompt I get multiple values for the same pattern so the above is not possible.

My current scenario is this:

I need to generate the required table(shown in the image above) based on the number of patterns entered by the user. Currently I am getting the values as in the current table(shown in the image above).

Thanks,

Bassam

[jyothirmayee_s]

Hi,

This would be an out of box situation. Not sure if you can actually assign each value of a prompt to a row and that too a match pattern.

Can you try this:

=If(Match([promptval];"*") ;[Fare Basis];[promptval])

another option:

=Count([Fare Basis]) in ([Promptval])

Thanks,

Jothi