Hey guys,
I have thousands of records in the data source(i.e. Cube), which includes datetime information. I want to aggregate and present the data on a bar chart on monthly basis. That means the interval between the dates should be one month. Assume that the dates value will be labeled on the x-axis of the bar chart.
I tried to find out the solution for many days. I will really appreciate if anybody give me some idea.
Sincerely,
Amde
Try creating a category group with two expressions, using the following grouping expressions.=Fields!DateField.Value.Year
=Fields!DateField.Value.Month
For the label you could use something like, =Fields!DateField.Value.ToShortDateString()
You could also use two category groups, if you wanted an inner set of labels for the month and another outer set for the year.|||
Hi,
Thank youy for your feedback, however that doesn't solve my problem: here is the thing;
I created a bar chart report. The x-axis value of this chart is a timestamp(datetime) field of a dimension. Thousands of records are inserted in to this field everyday, as a result, I will have the timestamp information every second or minute. So here is the thing, If for instance, I want to present last 5 months data in the bar chart(x-axis), the chart can not accomodate all the data and it doesn't look good to present a data which occured every minute or hours. So I want to present the data on monthly basis based on the StartDate and EndDate parameters value provided by the user.
Assume the user wants to preview 5 months record from 2006-03-04 to 2006-07-04, the data should be aggregated and presented on monthly basis as shown below, instead of directly displaying all the data as they appear in the dataset.
2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04
Please let me know if you need more clarification.
Sincerely,
Amde
|||Adding the category fields with the groupings mentioned above should produce the grouping structure you are looking for. When you tried it what happened that was incorrect?The bounds provided by the StartDate and EndDate parameters can either be used in the the sql query. Or, if it can't be done there, then you can set a filter for the category group. Also, the reason there is a group expression for Year is that the data may span multiple years and I'm assuming that you don't want the data for the same month in multiple years to be aggregated together.
Here is a sample report that uses the northwind database to show the number of orders placed for each month. It contains a bar chart and two parameters, which are used in the sql query.
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=localhost; Initial Catalog=Northwind</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>40232364-d6a5-4917-bcad-13308e3a8f62</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>7/1/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>11/30/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title />
<Height>5.125in</Height>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.375in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>Northwind</DataSetName>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Bar</Type>
<Width>6.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Year</GroupExpression>
<GroupExpression>=Fields!OrderDate.Value.Month</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=MonthName(Fields!OrderDate.Value.Month)</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Count(Fields!OrderID.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>5.75in</Height>
</Body>
<rd:ReportID>6323408e-15e1-4a7f-8151-ac96e7ebf862</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>="SELECT OrderDate, OrderID FROM Orders WHERE Orders.OrderDate BETWEEN '" & Parameters!StartDate.Value & "' AND '" & Parameters!EndDate.Value & "'"</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>9.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||
Yes, that is correct. The assumption, group expression for year is also correct.
Thank you for your cooperation.
sql