Quarterly Values

i have a date heirarchy

dim date

Year

Quarter

Month

and measures SalesVolume

how can i create salesvolume calculated measure for prev quarter, prev 3 quarters and so on.

Dear Chilukurisri,

try to use the same logic of the other post and try to use the function QTD too.

Helped?

Regards!

|||thanx pedroCGD, but QTD will give Quarters to date value so if we select the current member then it will give the sum of quarters for all the values starting beginning of the year but what i am looking is for a formula to calculate last 3 quarters irrespective of wheather i select what member in the current year i.e end of the quarter or middle of the quarter. How can i achieve that?

Quarterly trending

I have a reporting query that I need to do a quarterly trending on. A
user enters a begining date and end date, and from there, I have to
determine the closest quarter to it.

Also, the actual Quarter starts on Sept 4th, then add 13 weeks to that,
will be the second quarter.

I was able to do the trending for week/month/year, but I'm stuck on
this quarterly trending.

Any help would be greatly appreciated.

Example of the monthly trending that I have written:

SELECT [VALUE],DATEPART(MONTH,DATESAMPLE) AS[MONTH]
FROM #TABLE
GROUP BY [VALUE], DATEPART(MONTH,DATESAMPLE)
ORDER BY EDIT_SP, DATEPART(MONTH,DATESAMPLE)This would be easy if your business used calendar quarters instead;
can't you make them change? <G> You could then group by
DATEPART(Quarter, DateSample)

Since that's not likely, a solution might be to use a calendar table
that takes a key Date and maps it to the appropriate quarter, eg:

CREATE TABLE Calendar (DateKey smalldatetime, PeriodQuarter int...
(other columns))

INSERT INTO Calendar (DateKey, PeriodQuarter)
SELECT '9/1/2005', 2
UNION ALL
SELECT '9/4/2005', 3

etc

You could then join and group on the PeriodQuarter value.

Just an idea.
Stu|||create a table of the quarters:

CREATE TABLE Quarters
(quarter_name CHAR(20) NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date ));

>> .. determine the closest quarter to it. <<

Whatr does thst mean? give me an algorithm.

Quarterly Schedule Fails

My data driven report has been scheduled to send last quarters data on the
fifth day of a new quarter.
I set the schedule to send:
Monthly for: Jan, April, July, Oct and on
Calendar Day: 5 at 2am.
For some reason I received the report today, which happens to be May 5.
What am I doing wrong?I also check the system date on the Reporting Services Server, so that's not
the problem. Is there another way to schedule Quarterly report delivery?
"John Broomfield" wrote:
> My data driven report has been scheduled to send last quarters data on the
> fifth day of a new quarter.
> I set the schedule to send:
> Monthly for: Jan, April, July, Oct and on
> Calendar Day: 5 at 2am.
> For some reason I received the report today, which happens to be May 5.
> What am I doing wrong?
>

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

Quarterly Chart

Hi, i have a table with column for quarter and year (just like the sample Report - Company Sales).
now, i'm trying to create a chart which shows the quarter as the series group /x-axis... how can i show the label of the quarter and the year. currently, it can only use either year or quarter.
so, what i want the x-axis label to be like this:
e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
2003 2004If you want the quarter to show up on the x-axis, then it has to be a
category group, not a series group. Just use two category groupings (one for
quarter, one for year) and you should get the desired labels.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel" <Daniel@.discussions.microsoft.com> wrote in message
news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> Hi, i have a table with column for quarter and year (just like the sample
Report - Company Sales).
> now, i'm trying to create a chart which shows the quarter as the series
group /x-axis... how can i show the label of the quarter and the year.
currently, it can only use either year or quarter.
> so, what i want the x-axis label to be like this:
> e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> 2003 2004|||ah yes, i meant the category. however, the label can only show either quarter or year. not both.
i need to differentiate between the first q1 and the second q1. I can only put on field for the 'label'. if i leave it blank, it will only show it by the quarter.
"Robert Bruckner [MSFT]" wrote:
> If you want the quarter to show up on the x-axis, then it has to be a
> category group, not a series group. Just use two category groupings (one for
> quarter, one for year) and you should get the desired labels.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Daniel" <Daniel@.discussions.microsoft.com> wrote in message
> news:9E9728F2-00BA-4ABB-9B38-9C5ECEBC3A10@.microsoft.com...
> > Hi, i have a table with column for quarter and year (just like the sample
> Report - Company Sales).
> > now, i'm trying to create a chart which shows the quarter as the series
> group /x-axis... how can i show the label of the quarter and the year.
> currently, it can only use either year or quarter.
> >
> > so, what i want the x-axis label to be like this:
> > e.g. Q1 Q2 Q3 Q4 Q1 Q2 Q3 Q4
> > 2003 2004
>
>|||Please try and investigate the attached example which gives you two levels
of groupings on the category axis (inner level: month, outer level: year).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Type>Circle</Type>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<DataLabel>
<Style />
<Visible>true</Visible>
</DataLabel>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Line</Type>
<Title>
<Caption>Sales / Cost</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="YearGroup">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Label />
</DynamicCategories>
</CategoryGrouping>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="MonthGroup">
<GroupExpressions>
<GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.625in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
FROM dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||got it... i didn't pay attention to the category groups, instead i put it in the group on inside the category group.
"Robert Bruckner [MSFT]" wrote:
> Please try and investigate the attached example which gives you two levels
> of groupings on the category axis (inner level: month, outer level: year).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Chart Name="TotalSalesByYear">
> <ThreeDProperties>
> <Rotation>30</Rotation>
> <Inclination>30</Inclination>
> <Shading>Simple</Shading>
> <WallThickness>50</WallThickness>
> </ThreeDProperties>
> <Style />
> <Legend>
> <Visible>true</Visible>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <Color>Brown</Color>
> </Style>
> <Position>RightCenter</Position>
> </Legend>
> <Palette>Default</Palette>
> <ChartData>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)</Value>
> </DataValue>
> </DataValues>
> <DataLabel />
> <Marker>
> <Type>Circle</Type>
> <Size>6pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> </ChartSeries>
> <ChartSeries>
> <DataPoints>
> <DataPoint>
> <DataValues>
> <DataValue>
> <Value>=(Sum(Fields!UnitPrice.Value *
> Fields!Quantity.Value)+8000)*1.15</Value>
> </DataValue>
> </DataValues>
> <DataLabel>
> <Style />
> <Visible>true</Visible>
> </DataLabel>
> <Marker>
> <Type>Diamond</Type>
> <Size>10pt</Size>
> </Marker>
> </DataPoint>
> </DataPoints>
> <PlotType>Line</PlotType>
> </ChartSeries>
> </ChartData>
> <CategoryAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <Margin>true</Margin>
> <Visible>true</Visible>
> </Axis>
> </CategoryAxis>
> <DataSetName>Northwind</DataSetName>
> <PointWidth>100</PointWidth>
> <Type>Line</Type>
> <Title>
> <Caption>Sales / Cost</Caption>
> <Style>
> <FontSize>14pt</FontSize>
> <FontWeight>700</FontWeight>
> </Style>
> </Title>
> <CategoryGroupings>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="YearGroup">
> <GroupExpressions>
> <GroupExpression>=Year(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Label />
> </DynamicCategories>
> </CategoryGrouping>
> <CategoryGrouping>
> <DynamicCategories>
> <Grouping Name="MonthGroup">
> <GroupExpressions>
> <GroupExpression>=Month(Fields!OrderDate.Value)</GroupExpression>
> </GroupExpressions>
> </Grouping>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!OrderDate.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Label>=MonthName(Month(Fields!OrderDate.Value))</Label>
> </DynamicCategories>
> </CategoryGrouping>
> </CategoryGroupings>
> <Height>6.125in</Height>
> <SeriesGroupings>
> <SeriesGrouping>
> <StaticSeries>
> <StaticMember>
> <Label>Cost</Label>
> </StaticMember>
> <StaticMember>
> <Label>Sales</Label>
> </StaticMember>
> </StaticSeries>
> </SeriesGrouping>
> </SeriesGroupings>
> <Subtype>Plain</Subtype>
> <PlotArea>
> <Style>
> <BackgroundGradientEndColor>White</BackgroundGradientEndColor>
> <BackgroundGradientType>TopBottom</BackgroundGradientType>
> <BackgroundColor>LightGrey</BackgroundColor>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </PlotArea>
> <ValueAxis>
> <Axis>
> <Title>
> <Style />
> </Title>
> <Style />
> <MajorGridLines>
> <ShowGridLines>true</ShowGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MajorGridLines>
> <MinorGridLines>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> </Style>
> </MinorGridLines>
> <MajorTickMarks>Outside</MajorTickMarks>
> <MinorTickMarks>Outside</MinorTickMarks>
> <Min>0</Min>
> <Visible>true</Visible>
> <Scalar>true</Scalar>
> </Axis>
> </ValueAxis>
> </Chart>
> </ReportItems>
> <Style />
> <Height>6.5in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=(local);initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>7.625in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Quantity">
> <DataField>Quantity</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> <Field Name="OrderDate">
> <DataField>OrderDate</DataField>
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT dbo.[Order Details].UnitPrice, dbo.[Order
> Details].Quantity, dbo.Orders.OrderDate, dbo.Orders.OrderID
> FROM dbo.Orders INNER JOIN
> dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order
> Details].OrderID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>