Quarterly Chart grouping

Hi,
I have seen a couple posts about this but none seem to answer my question.
I have a bar chart currently grouping by month; category groups expression
=month(Fields!CLSDDATE.Value)
I would like to group this chart by quarter but I am at a loss, any
suggestions?
Thanks,
MarcusHow about:
=month(Fields!CLSDDATE.Value)/4
Your label could be:
="Q" & month(Fields!CLSDDATE.Value)/4
HTH,
Magendo_man
"Marcus K" wrote:
> Hi,
> I have seen a couple posts about this but none seem to answer my question.
> I have a bar chart currently grouping by month; category groups expression
> =month(Fields!CLSDDATE.Value)
> I would like to group this chart by quarter but I am at a loss, any
> suggestions?
> Thanks,
> Marcus|||Unfortunately that doesn't change the resulting chart.
"magendo_man" wrote:
> How about:
> =month(Fields!CLSDDATE.Value)/4
> Your label could be:
> ="Q" & month(Fields!CLSDDATE.Value)/4
>
> HTH,
> Magendo_man
>
> "Marcus K" wrote:
> > Hi,
> >
> > I have seen a couple posts about this but none seem to answer my question.
> >
> > I have a bar chart currently grouping by month; category groups expression
> > =month(Fields!CLSDDATE.Value)
> >
> > I would like to group this chart by quarter but I am at a loss, any
> > suggestions?
> >
> > Thanks,
> > Marcus|||You would be better adding to or amending your dataset definition by either:
a) Grouping your data by quarters
b) Adding a column in your query/stored procedure to calculate the quarter
number on each of the current rows using someting like:
SELECT .., .., .., MONTH(CLSDDATE)/4 AS QuarterNo
FROM MyTable
WHERE ...
Then you could use the QuarterNo column as the category group for your chart.
HTH,
Magendo_man
"Marcus K" wrote:
> Unfortunately that doesn't change the resulting chart.
> "magendo_man" wrote:
> > How about:
> >
> > =month(Fields!CLSDDATE.Value)/4
> >
> > Your label could be:
> >
> > ="Q" & month(Fields!CLSDDATE.Value)/4
> >
> >
> > HTH,
> > Magendo_man
> >
> >
> > "Marcus K" wrote:
> >
> > > Hi,
> > >
> > > I have seen a couple posts about this but none seem to answer my question.
> > >
> > > I have a bar chart currently grouping by month; category groups expression
> > > =month(Fields!CLSDDATE.Value)
> > >
> > > I would like to group this chart by quarter but I am at a loss, any
> > > suggestions?
> > >
> > > Thanks,
> > > Marcus|||I was trying to stay away from writing another SP :(
Looks like that is probably my only option though.
Thanks!
Marcus
"magendo_man" wrote:
> You would be better adding to or amending your dataset definition by either:
> a) Grouping your data by quarters
> b) Adding a column in your query/stored procedure to calculate the quarter
> number on each of the current rows using someting like:
> SELECT .., .., .., MONTH(CLSDDATE)/4 AS QuarterNo
> FROM MyTable
> WHERE ...
> Then you could use the QuarterNo column as the category group for your chart.
> HTH,
> Magendo_man
> "Marcus K" wrote:
> > Unfortunately that doesn't change the resulting chart.
> >
> > "magendo_man" wrote:
> >
> > > How about:
> > >
> > > =month(Fields!CLSDDATE.Value)/4
> > >
> > > Your label could be:
> > >
> > > ="Q" & month(Fields!CLSDDATE.Value)/4
> > >
> > >
> > > HTH,
> > > Magendo_man
> > >
> > >
> > > "Marcus K" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have seen a couple posts about this but none seem to answer my question.
> > > >
> > > > I have a bar chart currently grouping by month; category groups expression
> > > > =month(Fields!CLSDDATE.Value)
> > > >
> > > > I would like to group this chart by quarter but I am at a loss, any
> > > > suggestions?
> > > >
> > > > Thanks,
> > > > Marcus|||You shouldn't have to write another SP. Could you not just add the extra
column QuarterNo to your SELECT clause:
FLOOR(MONTH(CLSDDATE)/4) AS QuarterNo
You can then use the QuarterNo as your category group and sum the
appropriate value(s) against this.
HTH,
Magendo_man
"Marcus K" wrote:
> I was trying to stay away from writing another SP :(
> Looks like that is probably my only option though.
> Thanks!
> Marcus
> "magendo_man" wrote:
> > You would be better adding to or amending your dataset definition by either:
> > a) Grouping your data by quarters
> > b) Adding a column in your query/stored procedure to calculate the quarter
> > number on each of the current rows using someting like:
> > SELECT .., .., .., MONTH(CLSDDATE)/4 AS QuarterNo
> > FROM MyTable
> > WHERE ...
> >
> > Then you could use the QuarterNo column as the category group for your chart.
> >
> > HTH,
> > Magendo_man
> >
> > "Marcus K" wrote:
> >
> > > Unfortunately that doesn't change the resulting chart.
> > >
> > > "magendo_man" wrote:
> > >
> > > > How about:
> > > >
> > > > =month(Fields!CLSDDATE.Value)/4
> > > >
> > > > Your label could be:
> > > >
> > > > ="Q" & month(Fields!CLSDDATE.Value)/4
> > > >
> > > >
> > > > HTH,
> > > > Magendo_man
> > > >
> > > >
> > > > "Marcus K" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have seen a couple posts about this but none seem to answer my question.
> > > > >
> > > > > I have a bar chart currently grouping by month; category groups expression
> > > > > =month(Fields!CLSDDATE.Value)
> > > > >
> > > > > I would like to group this chart by quarter but I am at a loss, any
> > > > > suggestions?
> > > > >
> > > > > Thanks,
> > > > > Marcus|||I changed my query as you suggested, but the quarters are divided up strangely?
0 includes jan feb mar
1 includes apr may jun july
2 includes aug sept oct nov
3 includes dec
Any ideas?
Thanks for all your help already!!
Marcus
"magendo_man" wrote:
> You shouldn't have to write another SP. Could you not just add the extra
> column QuarterNo to your SELECT clause:
> FLOOR(MONTH(CLSDDATE)/4) AS QuarterNo
> You can then use the QuarterNo as your category group and sum the
> appropriate value(s) against this.
> HTH,
> Magendo_man
> "Marcus K" wrote:
> > I was trying to stay away from writing another SP :(
> >
> > Looks like that is probably my only option though.
> >
> > Thanks!
> >
> > Marcus
> >
> > "magendo_man" wrote:
> >
> > > You would be better adding to or amending your dataset definition by either:
> > > a) Grouping your data by quarters
> > > b) Adding a column in your query/stored procedure to calculate the quarter
> > > number on each of the current rows using someting like:
> > > SELECT .., .., .., MONTH(CLSDDATE)/4 AS QuarterNo
> > > FROM MyTable
> > > WHERE ...
> > >
> > > Then you could use the QuarterNo column as the category group for your chart.
> > >
> > > HTH,
> > > Magendo_man
> > >
> > > "Marcus K" wrote:
> > >
> > > > Unfortunately that doesn't change the resulting chart.
> > > >
> > > > "magendo_man" wrote:
> > > >
> > > > > How about:
> > > > >
> > > > > =month(Fields!CLSDDATE.Value)/4
> > > > >
> > > > > Your label could be:
> > > > >
> > > > > ="Q" & month(Fields!CLSDDATE.Value)/4
> > > > >
> > > > >
> > > > > HTH,
> > > > > Magendo_man
> > > > >
> > > > >
> > > > > "Marcus K" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have seen a couple posts about this but none seem to answer my question.
> > > > > >
> > > > > > I have a bar chart currently grouping by month; category groups expression
> > > > > > =month(Fields!CLSDDATE.Value)
> > > > > >
> > > > > > I would like to group this chart by quarter but I am at a loss, any
> > > > > > suggestions?
> > > > > >
> > > > > > Thanks,
> > > > > > Marcus|||I ended up using a case...
SELECT ......, CASE WHEN MONTH(CLSDDATE) BETWEEN 1 AND 3 THEN 1 WHEN
MONTH(CLSDDATE) BETWEEN 4 AND 6 THEN 2 WHEN MONTH(CLSDDATE)
BETWEEN 7 AND 9 THEN 3 ELSE 4 END AS quarterno,
.........
Thanks again!
MArcus
"magendo_man" wrote:
> You shouldn't have to write another SP. Could you not just add the extra
> column QuarterNo to your SELECT clause:
> FLOOR(MONTH(CLSDDATE)/4) AS QuarterNo
> You can then use the QuarterNo as your category group and sum the
> appropriate value(s) against this.
> HTH,
> Magendo_man
> "Marcus K" wrote:
> > I was trying to stay away from writing another SP :(
> >
> > Looks like that is probably my only option though.
> >
> > Thanks!
> >
> > Marcus
> >
> > "magendo_man" wrote:
> >
> > > You would be better adding to or amending your dataset definition by either:
> > > a) Grouping your data by quarters
> > > b) Adding a column in your query/stored procedure to calculate the quarter
> > > number on each of the current rows using someting like:
> > > SELECT .., .., .., MONTH(CLSDDATE)/4 AS QuarterNo
> > > FROM MyTable
> > > WHERE ...
> > >
> > > Then you could use the QuarterNo column as the category group for your chart.
> > >
> > > HTH,
> > > Magendo_man
> > >
> > > "Marcus K" wrote:
> > >
> > > > Unfortunately that doesn't change the resulting chart.
> > > >
> > > > "magendo_man" wrote:
> > > >
> > > > > How about:
> > > > >
> > > > > =month(Fields!CLSDDATE.Value)/4
> > > > >
> > > > > Your label could be:
> > > > >
> > > > > ="Q" & month(Fields!CLSDDATE.Value)/4
> > > > >
> > > > >
> > > > > HTH,
> > > > > Magendo_man
> > > > >
> > > > >
> > > > > "Marcus K" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have seen a couple posts about this but none seem to answer my question.
> > > > > >
> > > > > > I have a bar chart currently grouping by month; category groups expression
> > > > > > =month(Fields!CLSDDATE.Value)
> > > > > >
> > > > > > I would like to group this chart by quarter but I am at a loss, any
> > > > > > suggestions?
> > > > > >
> > > > > > Thanks,
> > > > > > Marcus|||Hi Marcus,
SQL Server Reporting tool already provided us the function for Quarter. You,
in fact, no need to use the CASE. Share some stuff with you that will make
your query and chart easy and cleaner.
Why not you try this...
1. change your query to "SELECT datepart(year,CLSDDATE) AS Year,
datepart(quarter,CLSDDATE) AS Quarter, datepart(month,CLSDDATE) AS
Month,xxx,xx,....GROUP BY datepart(quarter,CLSDDATE),
datepart(month,CLSDDATE)"
2. so now you already have a quarter column in numeric, same as month
column. At your chart properties, click on 'Data' tab where you will see the
category group.
3. Add two category groups. First, insert
=Fields!Quarter.Value,=Fields!Year.Value at the Expressions. Second category
groups, =Fields!Month.Value only.
4. you may modify the expression for 'Label', eg: ="Q"+Fields!Quarter.Value
for the first category group. Use switch for your month label.
=switch(=Fields!Month.Value=1,"Jan",=Fields!Month.Value=2,"Feb"...)
HAPPY REPORTING!
Seang Mei
"Marcus K" wrote:
> I ended up using a case...
> SELECT ......, CASE WHEN MONTH(CLSDDATE) BETWEEN 1 AND 3 THEN 1 WHEN
> MONTH(CLSDDATE) BETWEEN 4 AND 6 THEN 2 WHEN MONTH(CLSDDATE)
> BETWEEN 7 AND 9 THEN 3 ELSE 4 END AS quarterno,
> .........
> Thanks again!
> MArcus
> "magendo_man" wrote:
> > You shouldn't have to write another SP. Could you not just add the extra
> > column QuarterNo to your SELECT clause:
> >
> > FLOOR(MONTH(CLSDDATE)/4) AS QuarterNo
> >
> > You can then use the QuarterNo as your category group and sum the
> > appropriate value(s) against this.
> >
> > HTH,
> > Magendo_man
> >
> > "Marcus K" wrote:
> >
> > > I was trying to stay away from writing another SP :(
> > >
> > > Looks like that is probably my only option though.
> > >
> > > Thanks!
> > >
> > > Marcus
> > >
> > > "magendo_man" wrote:
> > >
> > > > You would be better adding to or amending your dataset definition by either:
> > > > a) Grouping your data by quarters
> > > > b) Adding a column in your query/stored procedure to calculate the quarter
> > > > number on each of the current rows using someting like:
> > > > SELECT .., .., .., MONTH(CLSDDATE)/4 AS QuarterNo
> > > > FROM MyTable
> > > > WHERE ...
> > > >
> > > > Then you could use the QuarterNo column as the category group for your chart.
> > > >
> > > > HTH,
> > > > Magendo_man
> > > >
> > > > "Marcus K" wrote:
> > > >
> > > > > Unfortunately that doesn't change the resulting chart.
> > > > >
> > > > > "magendo_man" wrote:
> > > > >
> > > > > > How about:
> > > > > >
> > > > > > =month(Fields!CLSDDATE.Value)/4
> > > > > >
> > > > > > Your label could be:
> > > > > >
> > > > > > ="Q" & month(Fields!CLSDDATE.Value)/4
> > > > > >
> > > > > >
> > > > > > HTH,
> > > > > > Magendo_man
> > > > > >
> > > > > >
> > > > > > "Marcus K" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have seen a couple posts about this but none seem to answer my question.
> > > > > > >
> > > > > > > I have a bar chart currently grouping by month; category groups expression
> > > > > > > =month(Fields!CLSDDATE.Value)
> > > > > > >
> > > > > > > I would like to group this chart by quarter but I am at a loss, any
> > > > > > > suggestions?
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Marcus