20 Replies Latest reply: May 3, 2011 4:56 AM by David Gilbertson RSS

Dynamic Filter in Web Intelligence

Ruddy Alvarado
Currently Being Moderated

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

  • Re: Dynamic Filter in Web Intelligence
    David Gilbertson
    Currently Being Moderated

    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?

    • Re: Dynamic Filter in Web Intelligence
      Ruddy Alvarado
      Currently Being Moderated

      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

      • Re: Dynamic Filter in Web Intelligence
        David Gilbertson
        Currently Being Moderated

        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

        • Re: Dynamic Filter in Web Intelligence
          Ruddy Alvarado
          Currently Being Moderated

          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.

Actions