Re: Matrix Report Headache

Just an idea, haven't tried it:

Potentially, you could outer join the sales figures to the month table (i'm assuming there is a month lookup table), and then add something similar to the following in the 'SELECT' clause of the SQL:

DECODE(total_sales, NULL, 0, total_sales)

i.e. this would effectively populate this column with 0's for any month that a salesperson didn't have any sales, otherwise would select the total sales $'s.

This solution would all be done in the SQL layer. I expect doing so, however, and getting back the result set in this manner would facilitate the matrix reporting using Oracle reports. It's just an idea, so you'll have to try it out! ... Interesting question.

Noah Wollowick
IT Consultant

On Tue, 3 Nov 1998 Thenardier_at_POBoxes.com wrote:

> hi folks,
>
> i'm working on a report which shows the total sales figures of each
> sales person for the last 12 months. i want the report to look like this:
>
> (current mth)
> Name 12/1997 01/1998 02/1998 ... ... 11/1998 Total
> ------ ------- ------- ------- ... ... ------- -------
> SalesA 10 10 20 ... ... 1 999
> SalesB 10 10 20 ... ... 1 999
> SalesC 10 10 20 ... ... 1 999
> ...
>
> u can see the month range is dynamic.
>
> i know that matrix report can help me to build a report like this.
> my headache is, say, if there is a month in which no sales is made.
> (everything can happen, who know? :) ) the corresponding column
> will disappear! then i'll propably see 02/1998 column followed by
> 04/1998. but i need to show the sales figures are 0s in 03/1998!
>
> what should i do?
>
> thanx in advance.
>
> thenard
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>
Received on Tue Nov 03 1998 - 00:00:00 CET