Wildcards in WEBI table

[gunesbt]

Hi all,

I am building a WEBI report based on a BEx query in BI 4.0 SP13.

In the report, I want to filter sales organizations using wildcards.E.g I want to display sales organizations starting  with 500(like 500*). But I need to filter this in

the report, not in the selection screen.In addition, I can not handle this in BEx because there are many webi reports on the BEx query.

So I tried to add a filter to the table in the report using “equal to “ operator.But In the filter of the table, I can not write 500*, because it forces me to select sales orgs which already exist  in the report, not allowed to write 500* manuelly. In addition, using between or in list operator does not satisfy the requierement, because it forces me to select existing sales orgs in the report and there might be missing sales orgs which are not available in the report at that time.

Therefore, I tried to create a dimension variable called var_salesOrg. and var_salesOrg= salesorg where (salesorg between("5000000";"6000000")). It works fine without the measure.When I add the measure to the table, it shows #unavailable.

Any ideas on how to handle this requierement?

Regards

[nscheaffer · Accepted solution]

I have a couple of suggestions...

1. Try creating a report filter where salesorg is between 5000000 and 6000000.
2. Create a variable whose formula is "=substr([salesorg]; 1; 3)" and call it something like salesorg_prefix. Put that variable on your report as a new column and hide it.  Then create a report filter where your new salesorg_prefix variable is equal to 500.

I hope one of these works for you, but it would certainly be much nicer to be able to use a wildcard or to have a "Like" operator.

[gunesbt · Accepted solution]

Hi Noel,

Your 2nd suggestion works fine without measure.It filters the rows where salesorg=500* but when I add the measure "amount" to the table, it shows "#unavailable" again.There might be some OSS notes for this.

[former_member184594 · Accepted solution]

This version of BO does not support Bex Query measures if you are using WebI variables. The reason for that is measures coming from Bex Queries are "database delegated", that mean WebI does not aggregate itself instead it is asking BW to aggregate this measure and when you create a variable or formula in WebI and put a measure next to it will be asking BW however there cannot find any value in variable and formula. That's the reason.

However, there is a workaround for this if you are not using hierarchies in your report, create an OLAP BAPI universe in Universe Designer Tool (the old way) and set the measures as "SUM" because by default they are "database delegated"

This issue will be solved in BO 4.0 Feature Pack 3. With Feature Pack 3, WebI will support key figures with summation and you'll not be receiving this error.

http://scn.sap.com/community/bi-platform/businessobjects-bi-for-sap/blog/2012/03/18/bi-4-feature-pac...

Also check this:

http://scn.sap.com/thread/3146599

[Former Member]

HI,

Select the Table

Click on Analysis

Click Filter - add filter

select block1 ( if you have only one block) - depends

add filter

seelct the dimension (sales organization)

select between

start value = 5000000

end value = 600000

click on ok

If this would not solve your prob. please try to attach screen shot. It may help people for prompt response.

Hope this helps.

Thanks,

Rajendra

[former_member226862]

I don't think you could handle 'like' type of filtering in BEx query even if you created report specific BEx query.

Suggestion #2 from Noel looks promising except that wildcard specific data filtering would occur within WEBI ad potentially could cause massive data volums sent from SAP BW to BO server before 'like' type filter is applied.