Customer leakage report

[Former Member]

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.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Andy,

You can use the available formula's within Webi.

Suppose you have the obejcts: \[customer\], \[year\] and \[sales\] as result objects and your query filter

limits the data to this year (2008) and last year (2007).

Create a variable for

1. \[sales 2008\]: =Sum(If(\[year\]=2008;\[sales\];0))

2. \[sales 2007\]: =Sum(If(\[year\]=2007;\[sales\];0))

You need the Sum around the If to prevent the MuliValue error.

Now put the created objects in your report. Add an extra column which calculate the difference between

those objects (=\[sales 2008 - \[sales 2007\]) and save that as a variable where you can put your filter

on (anything less than zero). That will show you all the customers who this year less than last year.

The next step would be to create some additional variables which will calculate the current year and last year for you based on the last execution date.

Cheers,

Harry

[Former Member · Accepted solution]

I had a very similar idea on Sunday morning.

Top marks to you though for a clear explanation.

[Former Member]

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

[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

[Former Member]

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