Newbie Charting Question
Hi All, I’m a newbie at charting and needed some help on this one. When I run my data report with no chart it returns the data by group ITEM and is aggregated against the whole data-set. What I want to do is two things when I add a chart. 1. A chart that displays below each group. 2. In the chart is to break the data-set down by weeks for each grouped data-set. Example Data Return Main Report, (there are underlying dates in the data-set) ITEM QTY (sum) COST (avg) Apples 214 .91 Bananas 65 .45 Oranges 100 .15 So I'd like to add a simple bar chart below each ITEM for that group under it, except the aggregated values needs to be broken down by week. I wish I could attach a sample chart to this for the full visual...but this is all I could do. The sample below would be a chart for the ITEM Apples for example. ITEM QTY (sum) COST (avg) Apples 214 .91 Chart for Apples 120 3 100 X 2.5 80 X 2 60 X X 1.5 40 X X X 1 20 X X X 0.5 0 X X X X 0 Jan 1-2011 Jan10-2011 Jan 17-2011 Jan 24- 2011
February 14th, 2011 10:36am

It sounds like you have two questions: 1. How to repeat a chart for each group value 2. How to plot a time-series by week For item 1, after you have created and tested the chart, you can drag it into a list or table data region grouped by the ITEM field. I'd suggest that you add a list to the body of your report, set the DatasetName and then define the detail group using the appropriate field expression. For item 2, assuming this is a line, area or column chart; you can use any field in yur dataset to define the Category group, used to aggregate values along the X-axis. Although you can group on an expression derived from a date-type field, I would suggest that you add your week column or an alias to the query and then use that field for grouping. For example, if you have a SalesDate column in a table used in your query, add an alias column like this: SELECT Qty, Amount, SalesDate, YEAR(SalesDate) as Year, MONTH(SalesDate) as Month, DATEPART(wk, SalesDate) as Week, DAY(SalesDate) as Day FROM... In the chart, set the Category group to group and sort on the year and then week. You can use an expression to display anything you want for the axis label, such as: =Fields!Month.Value & First(Fields!Day.Value) & "-" & Fields!Year.ValuePaul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com *Please vote if helpful*
Free Windows Admin Tool Kit Click here and download it now
February 14th, 2011 4:19pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics