Skip to Content
author's profile photo Former Member
Former Member

Customer leakage report

Does anyone know if it is possible in WEBI to create a report which shows only customers who's sales this year are less than last year?

Everytime I try to create a report to do this it retruns all of the data from this year and last year and when I try to filter out the records based by the sales values it returns MuliValue.

Any help would be greatly appreciated.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 03, 2008 at 03:54 PM

    John,

    I was hoping that there would be a clever method for this using WEBI formulas but your approach would do the trick. It's just a pain having to alter the Universe to get this and other relatively simple reports to work.

    Many thanks for your help.

    Andy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 03, 2008 at 02:36 PM

    Andy,

    The data you are seeking is best solved using a nested query (the technical answer). The harder thing to do is to implement your request using a complete Designer approach (something at times I myself find as a difficult task) and will at those times create a view, and then use that view within Designer (add to the universe or create a new universe), then build a report (or reports) from that view.

    Nonetheless, you're solution is some kind of nested query solution, which, maybe now armed with this knowledge, can get you started to meeting your objective.

    Thanks,

    John

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 03, 2008 at 02:54 PM

    John,

    Thanks for your response.

    Can you recommend a site which has a detailed explanation of how to use a nested query?

    I have some reference matierial but I am unsure as to how I can use a nested query and return the data I require (Customer Info, This Years Value, Last Years Value for only customers who have leakage) without returning all of the sales info for both years.

    Many thanks,

    Andy

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Andy,

      Nested queries is an advanced subject in the study of SQL.

      Being that you've given a glimpse of your dataset names, here in rough details is how you might approach (I'm hoping that you have a seasoned DBA on your staff that can help further, so this example should be approached as psuedo code with a to-be polished version handled by someone in your area that is more knowledgable of your overall database schema). After thinking about your business problem, I'm nearly convinced that you need to build a view as follows:

      create view Last_Year_Customer_Info as
      select [Customer Info], 
               [Last Years Value],
               sum<sales> as [Last Years Sales]
      from <table>
      where [Last Years Value]=2007
      go
      
      create view This_Year_Customer_Info as
      select [Customer Info], 
                [This Years Value],
                sum<sales> as [This Years Sales]
      from <table>
      where [This Years Value]=2008
      go
      
      create view Compare_Last_Year_to_This_Year as
      select this_year.[Customer Info],
                [This Years Value],
                [This Years Sales],
                [Last Years Sales]
      from This_Year_Customer_Info this_year join 
             Last_Year_Customer_Info last_year 
                 on this_year.[Customer Info]=
                      last_year.[Customer Info]
      where [This Years Sales] < [Last Years Sales]
      go
      

      So you see that initially two views are created, one for each time period, and summed up by sales for each period. Then a third view also is developed to compare Customer Info to Customer Info for each year where this year sales is less than last year sales. This is not an elegant solution because view are getting hardcoded with values, so more tweaking can be done based on including date/time parameters, but this is workable and something to get you started, nonetheless.

      Thanks,

      John

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.