SSRS 2008 Chart question (summarized by month)

Question

Hi,

I am trying to get a chart for the following:

          I do have a list of items with a start date and end date for each item.
          Now I want a chart that shows me the sum of items for each month.
         
          e.g.:
          item1 from 01.01.2008 to 30.06.2008
          item2 from 01.02.2008 to 31.03.2008

          result:
          january: 1
          february: 2
          march: 2
          april: 1
          .
          .
          .

Is there any way to get such a result in a chart?

Thanks,
Jan

Answer 1

Hi,
as per your result Im writing the following query. This query gives sum of the items in one month.
 select sum(items) from Table1 grop by datepart(mm, datefield)

If there is another date range instead of the months, please provide example correctly.

---

Vennela (www.ggktech.com)

Answer 2

What do you mean exactly by sum? Is it the "count" of items that occur either entirely or partially within the month?

Answer 3

Hi,

I need the count per month of items which have been in stock to see the stock history of the last months/years. But I think I now found a solution by using a calendar table - if anyone knows a way to do this without a calendar table - please let me know.

Answer 4

Hi Jan,  

As I understand the issue, you want to use a chat to display the item count by month. X Axis displays months and years, and Y Axis display the item counts.  If this is the case, try the following instructions to build a chart: 

1. Open the chart properties and go to Data tab.
2. Click the first Add button to new a chart value. In the Edit Chart Value box, set the value as =count(Fields!item.value). Click OK.
3. Click the second Add button to create a category group. Set the expression as =Year(Fields!date.value). Click OK.
4. Click the category groups Add button again to create the second group. Set the expression as =Month(Fields!date.value). Click OK.

If you have any further questions, please let me know.

Thanks.

---

***Xiao Min Tan***Microsoft Online Community***

Answer 5

 With the way that the data is currently structured you will not be able to build a chart directly against it.    You will need to have a result set where for each month you basically have a list of the products that were available in that month.

For example:

Month       Product
January    Item 1
February   Item 1
February  Item 2
March       Item 1
march       Item 2
April         Item 1
.........


-Sean

---

Program Manager, SQL Server Reporting Services