Tuesday, December 16, 2008 1:16 PMHi,
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.
item1 from 01.01.2008 to 30.06.2008
item2 from 01.02.2008 to 31.03.2008
Is there any way to get such a result in a chart?
Tuesday, December 16, 2008 2:04 PMHi,
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.
Tuesday, December 16, 2008 5:31 PMAnswerer
What do you mean exactly by sum? Is it the "count" of items that occur either entirely or partially within the month?
Wednesday, December 17, 2008 10:47 AMHi,
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.
Monday, December 22, 2008 8:41 AMModeratorHi 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:
- Open the chart properties and go to Data tab.
- 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.
- Click the second Add button to create a category group. Set the expression as =Year(Fields!date.value). Click OK.
- 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.
***Xiao Min Tan***Microsoft Online Community***
- Marked As Answer by Xiao-Min Tan – MSFTModerator Monday, December 29, 2008 2:34 AM
Tuesday, December 23, 2008 7:09 AMModeratorWith 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.
January Item 1
February Item 1
February Item 2
March Item 1
march Item 2
April Item 1
Program Manager, SQL Server Reporting Services
- Marked As Answer by Sean BoonModerator Tuesday, December 23, 2008 7:09 AM