need date object in report

[skkz]

Hello,

I have to create a report in WEBI, The report contains 3 columnns mainly.

Type, Date, Amount

Type is coming from Dimension Table, Date & Amount coming from same Fact table.
There is no direct joins between these 2 tables, There is another dimension table which joins these 2 tables.

The report should show the amount sum for each type. but the sum should be for last 1 year.
for example.
If the user enter the date between last month (05/01/2009 - 05/31/2009)
for each type i should get all 31days and with the sum amount for last one year.
example

Type date amount
aaa 05/01/2009 300.00
aaa 05/02/2009 310.00
aaa 05/02/2009 340.00
-----
-------
----
bbb 05/01/2009 600.00
bbb 05/02/2009 710.00
bbb 05/02/2009 740.00


Kindly Help
Thanks

 

[Madawc]

You can select using a formula like

Month({your.date}) = 5 and
Year({your.date}) = 2009

To show days without data, you'd need to have a table that has all of the dates. Or else to a 'mock-crosstab', accumulating totals for each date.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Please explain more about what you mean by the "sum amount for last one year". Do you mean that for each date/type you want to see the sum for the last year based on that date and type, e.g., the sum for 5/2/08 - 5/1/09 for type bbb 1st record, followed by the sum for 5/3/08 - 5/2/09 for type bbb 2nd record?

-LB