#Multivalue error in hyperlink

[vinayakgole]

Hi,

We have a situation where in we need a scorecared as a parent report which will then pass on the relevant information to the child detail reoprt.

The scorecard will look like:

Red                      Yellow                 Green

------                     --------                 ---------

10                          20                       30

----                        -----                     ----

  |                            |                           |

hyperlink            hyperlink               hyperlink

The hyperlink should pass values to the child report which should show the details for the selected customers.

Hyperlink:

="<a href=\"../../opendoc/openDocument.jsp?sRefresh=Y&sDocName=Child Report&lsMCustomer="+URLEncode(""+[Customer])+"\" title=\"\"target=\"_blank\" nav=\"doc\">"+[Status Count] +"</a>

The child report has a prompt for customer.

However, the measure Status Count on the parent report always throws up a #Multivalue error. I know this issue has been discussed in some other posts. But I have started a different thread so we can arrive at a work around. If in the case above, I cant use a hyperlink to pass multiple values, what can be a better way.

The above is using

WebI 4.0 over Bex using a BICS connection.

Thanks,

Vinayak

[Former Member]

Vinayak,

Is [Status Count] defined as a measure? And is it counted at the Customer level? If not it should be, and that may solve the problem.

Thanks

[vinayakgole]

Hi Bill,

Thanks for your comment.

Yes it is a measure and is created with a pseudocode like

Red: count(customer) where sales<100

Green: count(customer) where sales>100

etc

Best regards,

Vinayak

[Former Member]

Hi Vinayak,

Should it not then be:

="<a href=\"../../opendoc/openDocument.jsp?sRefresh=Y&sDocName=Child Report&lsMCustomer="+URLEncode(""+[Customer])+"\" title=\"\"target=\"_blank\" nav=\"doc\">"+Count([Customer]) where ([sales]<100) +"</a>

and that formula would be the same in each cell of that row.

Is it a vertical table or a crosstab?

Thanks

[MariannevL]

The multivalue is not caused by the measure (the count), but by the customer dimension object.

Since you have more than one customer and you try to put it in one cell, you get multivalue.

Really the only option to pass more than one value to an opendocument is to use the concatenation.

Because you are on BW, it is also not an option to build the pre-query in webi and use the results of that query as a list of values for the detail report.

Because you are on BW they expect you to solve that kind of problem in BW.

I am sorry but I do not see any other options than either concatenation or the pre-query bex variable in the detail bex query.

Good luck,

Marianne

[vinayakgole]

Thanks Marianne,

Yes we have already considered the Bex pre query option however, there are some complexities with it and we are trying to work out a solution at the BO level.

The child is on a different Bex query and we do have a prompt there but the challenge is to pass the values to the prompt. We are also trying to put the list into a string with semi colons. Can you please think of anything that comes at the top of your mind about how we can achieve this? Concatenation is out of question and we are unable to find any other way to achieve this

Hi Bill,

thanks for your suggestion, we already tried it out but it doesnt seem to work in our case.

Best regards,

Vinayak

[Former Member]

Hello,

You could try using

+Sum([Status Count]) +"</a>

at the end of the hyperlink formula. That may work.

Thanks

[MariannevL]

I don't know how the detail report is built, if it is the same bex query or a different one,

but really the easiest option would be to have the category as the prompt/variable,

not a list of customers.

The only way to pass multiple values to a prompt is to have them in a row separated by semicolons (;).

ant it will take some doing to get a list of dimension objects values to go into one cell.

(that is if you get it to work at all... )

No seriously you would have to have a fixed maximum number of customers in the list
and pinpoint every single one of them. So this would work for 5 customers, 10 is hassle and if you do not even know how many there will be (1000?) it's simple not doable.

Hope you can make the category red/yellow/green somehow a bex variable (pre-query?) instead of customer...

Hope this helps,

Marianne