SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Issues with totaling in a matrix
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meef
Yak Posting Veteran

99 Posts

Posted - 08/20/2012 :  12:07:06  Show Profile  Reply with Quote
I have a report here that is supposed to show spending from one year to another. So, for example, you put in a start date of 1/1/11 and an end date of 8/20/12 and it will compare last year to this year month by month. My problem is it's totaling all of 2011 instead of just the first 8 months, as seen below:




There are no weird grouping or anything in the report, I'm just grouping the years and another field not shown in the image. As you can see, the numbers for 2011 definitely do not add up to $627,250, that's the the total for the entire year of 2011. How can I make it only total up the months it has in common with the current year (in this example, only months 1-8)?

Edited by - meef on 08/20/2012 12:08:33

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/20/2012 :  17:16:22  Show Profile  Reply with Quote
Are you bringing entire years data to matrix or only first 8 months?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

meef
Yak Posting Veteran

99 Posts

Posted - 08/21/2012 :  08:19:07  Show Profile  Reply with Quote
It's bringing everything from the date range, this is probably a case where adding something to the stored procedure will be the best solution but I'm not sure what to add. Right now it's not really limiting on anything other than the date, so if I put in 1/1/11-8/22/12 it's pulling all of it.

Edited by - meef on 08/21/2012 08:54:40
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/21/2012 :  10:50:00  Show Profile  Reply with Quote
either add filter to bring only 8 months data from procedure or add a filter on matrix to filter out data after 8 months

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

meef
Yak Posting Veteran

99 Posts

Posted - 08/21/2012 :  14:22:22  Show Profile  Reply with Quote
It's not always going to be 8 months though, it's however long the period between the two dates would be. That's what I'm not sure how to do in the SP, take the dates and count how many days between the two and only display that data.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/21/2012 :  15:00:57  Show Profile  Reply with Quote
quote:
Originally posted by meef

It's not always going to be 8 months though, it's however long the period between the two dates would be. That's what I'm not sure how to do in the SP, take the dates and count how many days between the two and only display that data.


is value parameter driven?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

meef
Yak Posting Veteran

99 Posts

Posted - 08/21/2012 :  15:45:15  Show Profile  Reply with Quote
It is. The user can put it whatever start and end date they want when the report is run.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/21/2012 :  15:57:35  Show Profile  Reply with Quote
ok. then what you can do is to have a hidden parameter to get month details from user input enddate value and use it to filter matrix data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000