Fixed Jan -> Dec required but variable dates given 1

[beckji]

Hello,

I am currently have the following data

Part, Qty Sold, Date Sold which I am trying to display in a report where the sales are grouped by month.

The report I am trying to create is the one shown below:

Widget
Jan Feb Mar Apr May Jun Jul Aug .. Dec

123 Nil 456 789 Nil 212 Nil Nil 776

( nobody buys widgets during the summer holidays :-( )

After grouping my data by month the display I get is

Widget
Jan Mar Apr Jun Dec
123 456 789 212 776

Does anyone know how I can create the report to have a fixed 12 month, regardless of whether I have data for every month or not?.

I have had a look in the forum, but I am not sure how to phrase the search question.

Any help would be appreciated.

regards,

Jim

 

[blom0344]

The solution to your problem is to use a calenderfile that has an outer join to the fact table. That way you will always fetch the twelve months, regardless if the actual data contains just 7 distinct months.
Alternatively use a flatfile with the 12 monthcodes and link the dataproviders over the month. I think that should work as well I think...........

T. Blom
Information analyst
tbl@shimano-eu.com