Chart grouped by date

Hi,
I´m trying to define a column chart grouped by date and I can´t find the
proper way.
What I need is to show every month from one date to another even if there´s
no data in a specific month.
I also need to sum data from different recordsets grouping by month + year.
For example:
MY DATASETS
ds1:
date1 > Total1
1/1/2007 > 20
9/1/2007 > 15
1/3/2007 > 10
1/4/2007 > 40
1/5/2007 > 25
ds2:
date2 > Total2
1/2/2007 > 2
9/2/2007 > 1
1/4/2007 > 5
1/4/2007 > 4
1/6/2007 > 3
PARAMETERS:
StartDate: 1/1/2007
EndDate: 31/12/2007
MY CHART SHOULD SHOW:
January 2007 > 35
February 2007 > 3
March 2007 > 10
April 2007 > 49
May 2007 > 25
June 2007 > 3
July 2007 > 0
August 2007 > 0
September 2007 > 0
October 2007 > 0
November 2007 > 0
December 2007 > 0
How can I do this?
What must I put in the chart group?, is there any "UNION" option in groups?
(i.e. month(Fields!Date.Value, "ds1") & " " & year(Fields!Date.Value, "ds1")
UNION month(Fields!Date.Value, "ds2") & " " & year(Fields!Date.Value, "ds2")
How do I set the values?
Any help will be apreciated.
Thanks,
MónicaOn Nov 5, 12:48 pm, "M=F3nica" <monica.d...@.augure.com> wrote:
> Hi,
> I=B4m trying to define a column chart grouped by date and I can=B4t find =the
> proper way.
> What I need is to show every month from one date to another even if there==B4s
> no data in a specific month.
> I also need to sum data from different recordsets grouping by month + yea=r=2E
> For example:
> MY DATASETS
> ds1:
> date1 > Total1
> 1/1/2007 > 20
> 9/1/2007 > 15
> 1/3/2007 > 10
> 1/4/2007 > 40
> 1/5/2007 > 25
> ds2:
> date2 > Total2
> 1/2/2007 > 2
> 9/2/2007 > 1
> 1/4/2007 > 5
> 1/4/2007 > 4
> 1/6/2007 > 3
> PARAMETERS:
> StartDate: 1/1/2007
> EndDate: 31/12/2007
> MY CHART SHOULD SHOW:
> January 2007 > 35
> February 2007 > 3
> March 2007 > 10
> April 2007 > 49
> May 2007 > 25
> June 2007 > 3
> July 2007 > 0
> August 2007 > 0
> September 2007 > 0
> October 2007 > 0
> November 2007 > 0
> December 2007 > 0
> How can I do this?
> What must I put in the chart group?, is there any "UNION" option in group=s?
> (i.e. month(Fields!Date.Value, "ds1") & " " & year(Fields!Date.Value, "ds=1")
> UNION month(Fields!Date.Value, "ds2") & " " & year(Fields!Date.Value, "ds=2")
> How do I set the values?
> Any help will be apreciated.
> Thanks,
> M=F3nica
Unfortunately, you cannot Union two datasets together (wish you
could). The data has to be aggregated before it is delivered to the
report. I've had suggestions to me of (1) Custom DataSets, (2)
Analysis Server joined Cubes, (3) SELECT from a SQL Server containing
Linked Servers to the two datasets, then UNION on the server and
return a single dataset to SSRS.
Your second issue, Group By Month, create your Chart based off of
DataSet 1, add the DATE1 as a Category, then go to Chart Properties,
Data Tab, select the Category group, Edit, and change the Group
Expression to something like:
=3DDateAdd( d, 1-Day(Fields!date1.Value), Fields!date1.Value)
then change the Label to
=3DFormat( Fields!date1.Value, "MMMM yyyy" )
and change the Sorting to
=3DDateAdd( d, 1-Day(Fields!date1.Value), Fields!date1.Value)
this way the data is sorted by calendar date, not by text
-- Scott