none
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
    Tuesday, December 16, 2008 1:16 PM

Answers

  • 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***
    Monday, December 22, 2008 8:41 AM
  •  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
    • Marked as answer by Sean Boon Tuesday, December 23, 2008 7:09 AM
    Tuesday, December 23, 2008 7:09 AM

All replies

  • 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)
    • Marked as answer by Sean Boon Tuesday, December 23, 2008 7:09 AM
    • Unmarked as answer by Sean Boon Tuesday, December 23, 2008 7:09 AM
    Tuesday, December 16, 2008 2:04 PM
  • What do you mean exactly by sum? Is it the "count" of items that occur either entirely or partially within the month?

    Tuesday, December 16, 2008 5:31 PM
  • 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.


    Wednesday, December 17, 2008 10:47 AM
  • 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***
    Monday, December 22, 2008 8:41 AM
  •  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
    • Marked as answer by Sean Boon Tuesday, December 23, 2008 7:09 AM
    Tuesday, December 23, 2008 7:09 AM