Dynamic Filter in Web Intelligence

[Former Member]

Hi!!!

I need help about one issue. Let me explain my problem:

I have a webi report, this report contain the sales by day. The problem is that my variable of day contains values from current month, last month and month of las year. Example:

01.04.2010

01.3.2011

01.04.2011

02.04.2010

02.03.2011

02.04.2011

My variable of day has this behavior because i need to compare the sales of current month vs las month and current month of last year.

The measure that has the sales is database delegated.

In other view of my report, i need to show only the sales of current month, but when in my webi report filter the sales with the condition "is not null" (to hide the information about last year and last month) the intersaction between day and sales show me the following message:

01.04.2011

Sales: #NOT AVAILABLE

I really need to reach this behavior, but i dont know how to do to work my filter.

Someone can help me please?????

Ruddy Alvarado.

I am using BO XI 3.1 SP3

[Former Member]

Hi,

Are you saying you have a variable for day that aggregates the dimension of day up into current month, past month and same month last year? If so, rather than using a variable dimension to do this, you could use a variable measure. something like:

=sum([Sales] where(Date = This Month))

=sum([Sales] where(Date = Last Month))

=sum([Sales] where(Date = This Month Last Year))

Of course, with some logic in place of 'last month', etc.

This would then allow you to show (and compare) current month, previous month and same month previous year in a block that doesn't contain any date dimensions.

Am I understanding the problem correctly?

[Former Member]

Hi David!!

My variable is natural day.

For this month (april) natural day (my variable) has the following values:

1.) since 01.04.2010 to 30.04.2010

2.) since 01.03.2011 to 31.03.2011

3) since 01.04.2011 to the current day of april 2011

For the case that i have, i need that the value associated to natural day (in this case sales) has to be database delagated.

I would like to know how can i put something logic in the sentence "where", because my variable (natural day) changes ist value every day (because i am doing a month to day), in other words the variable dosent have a static value.

I will wait for your answer.

Ruddy Alvarado

[Former Member]

Hi Ruddy,

Sorry, my last answer kind of missed the point. Let me try again.

This is one of the downsides of database delegated measures. Essentially, it means that webi has to send a message to the database, telling it how to aggregate the sales measure. That message, in your case, would be something like "Hey database, please add up 'sales' for each 'natural day'". To which the database will reply "Sorry, I've never heard of 'natural day', can't help you." This is what #NOT AVAILABLE is.

Assuming that sales is just a simple sum, then you can change database delegated to sum in Universe designer, this will stop the #NOT AVAILABLE, is there a reason you say it has to be database delegated?

DG

[Former Member]

Yes, let me explain the reason because sales have to be database delegated.

In my case sales is not the sum of quantity of productos bought by a customer, sales is like a indicator that show me if customer bought or doesnt bought.

For example David is my customer and i sell 3 types of products: A, B and C.

Date 12.04.2011 David bought product A and B and 13.04.2011 David bought just product C.

In my report if i want to analyze sales by day and customer i have to have the following result:

*****Date 12.04.2011 and customer Dave

Sales = 1

*****Date 13.04.2011 and customer Dave

Sales = 1

In my report if i want to analyze sales by day and type of produc i have to have the following result:

*****Date 12.04.2011 and customer Dave

Sales Product A= 1

Sales Product B= 1

*****Date 13.04.2011 and customer Dave

Sales Product C= 1

Can you see my point??

In date 12.04.2011 i cant do a sum if i am analyzing sales by customer because if i do a sum dave show me a sales of 2.

The only way that i found for this behavior is to use database delegated.

I will wait for your answer.

[Former Member]

Why don't use just use a formula by checking the sum value?

If ( sales value >=1;1;0)

Irrespective of the sales value it always shows either 1 or 0 as you want.

Regards

Gowtham

[Former Member]

HI!!

I am not sure that i can use If ( sales value >=1;1;0) to solve my problem, let my explain why.

In my report i have to analyze the sales in differents levels, type of product, customer, office sales, etc, and the hierarchy

is the following:

1. Sales Office

2. Customer

3. Type of product

This means that a sales office has many customers and type of products anda customers has many type of products.

Example:

Sales office GS01 has 3 customers and if i am analyzing date 13.04.2011 i have to be for GS01 the value 3, but if i use "If ( sales value >=1;1;0)" i only get the value 1.

Some idea??

Thanks.

Ruddy Alvarado

[Former Member]

What you said.....

In my case sales is not the sum of quantity of productos bought by a customer, sales is like a indicator that show me if customer bought or doesnt bought.

Based on that, I have given that formula.

[Former Member]

Hi!!

Yes that is exactly that i what to do.

I saw that you put case and if iike key words, can you tell me how can i use this combination to reach the thing that i want?

I will wait for your answer and thanks for your help.

[Former Member]

Hi!!!

If i undestood that you say is to used the following formula:

If (sales>=1,1,0)?

I have the following questions:

1.) The variable Total Sales = If (sales>=1,1,0), where i have to created this variable? in my universe or in my webi report?

2.) sales that is the variable that have the valueos of bougt or no bougt (is the variable that i have to compare if is >1 or not) have to have the function of sum or database delegated??

Can you help me please??

[Former Member]

Hi Experts!!!

I have a webi report that use database delegated for 2 measures.

The problem that i have in this moment is that i have to use a filter to unshow some information.

For example my report has the information from 5 countries and i need to show only 2 countries in one tab of my report and in otrher tab i have to show the information of the other 3 countries.

When i use a filter inside the report, the information that use database delegated show me the following values: #NOT AVAILABLE.

How can i solve this problem??

I will wait for your answer.

Ruddy Alvarado

[Former Member]

Hi,

the error you are getting is by design for delegated measures. As a workaround create a new prompt instead of a filter.

Best Regards

[Former Member]

Hi Victor!!

I have a doubt about prompt.

My report have to has information of 5 countries. My report has 2 views (one with information about 2 countries, and the other tab with the information of the other 3 countries).

In each tab i am using a filter to unshow the information about countries that i dont need.

How can i use a prompt to reach the thing that i need?

[Former Member]

if the selection of the countries should not ready for input, you could duplicate the query in the webi report. In the first query you ad an "in list" filter with countries A and B, for the first report. That way the database should be able to deliver the desired result.

Note that in this solution, the filter is not applied on the report but rather on the query.

[Former Member]

Oh, if i understood you are telling me the following:

I have to have one query for each tab, in each query i have to filter the countries that i need?

Please tell me if i am right!!!

Do you know another solution??

Thanks.

[Former Member]

Yes Ruddy, you understood me correctly.

That is the only workaround I can help you with at this moment.

[Former Member]

Ok Victor, thanks for your help.

If you found another solution can you tell me??

My last question about this problem:

Duplicate query affect the performance of the query?? It means: with one query the report take 100 seconds and when i duplicate the query it takes 200 seconds?

[Former Member]

Yes obviously. But as the caching option is enabled in database side, it shouldn't take much time when it runs the same query second time.

[Former Member]

Hi Ruddy,

I'm quite sure we'll be able to find a formula that works in Webi to replace your database delegated measure. The gives you more freedom at the Webi end and means you don't need those multiple queries.

I think I understand your requirements, but might be wrong. Tell me, if you add a variable for

=count([Customer]; [Product]; [Date])

Is that like what you want?

You can have different variables for 'customer purchases' and 'product purchases'.

I did something very similar recently looking at whether a product had been sold in a store, and we solved this at the Webi level (for a more complex calculation that included any sale in the past six months).

[Former Member]

Hi David!!!

I tried to do the thing that you say using the following formula:

=Si(EsNulo([Mes/Act])=1) Then([Cobertura Cliente])=0 Else([Cobertura Cliente]=1)

But i have the following problem.

My measure "cobertura cliente" have to show only the customers with sales <> 0, for this reason i use the sentence "SI" (IF).

When i put the customer as a column of my webir report the measure "cobertura cliente" works good, but when i don put the customer as a detail of my report (or column of my report) the measures takes the custormers with sales = 0 and customers with sales <> 0.

My report does not to show the customers as a detail of the report.

How can i do this?

Pleas i need help.

Ruddy Alvarado

[Former Member]

Hi,

Yes that behaviour makes sense. You will need to specify a calculation context since you aren't showing customers. Look at "Understanding Calculation contexts" in the Webi manual for more info.

I'm not sure I understand your formula, but the end result will need to be something like "YOUR FORMULA" ForEach([Customer])

Hope that helps.