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
Showing posts with label chart. Show all posts
Showing posts with label chart. Show all posts
Tuesday, March 20, 2012
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>
>
>
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>
>
>
Friday, March 9, 2012
Qaurterly Report
Hi everyone,
I'm currently using Visual Web Developer 2005 Express Edition, I have a chart that shows everything The Y axis displays the quantity and X axis shows the month.Well, the X axis I grouped it by month, but i want to set jan-march as q1 and so on.How can I format it, or do so it can display that format?
Thanks ahead of time,
Shuy
It would be easy if you could populate the data from the backend. You could group the data for three months and send the database back to the front end.
|||If you mean to change the output in some text field based on a column result, you can use IIF to make that change:
= IIF(Fields!Column.Value) = "Quarter1", "Jan-Mar",IIF(Fields!Column.Value) = "Quarter2", "Apr-Jun", IIF(Fields!Column.Value) = "Quarter3", "Jul-Sep", "Oct-Dec")
|||Thanks so much...just what i needed.
Subscribe to:
Posts (Atom)