Reporting Services 2005 - Navigation from Summary to Detail with Dynamic Parameters
Posted on 2006-06-19
I have a summary report with one group. The group is by month. There are five dynamic columns based on year. There are two parameters that come in from the stored procedure for the Report begin date and the reports end date. The layout is as follows:
2006 2005 2004 2003 2002
Jan $$ $$ $$ $$ $$
I get the years dynamically using variations of =Year(now())
I get the months using =MonthName(Month(fields!PostedDateTime.value))
The detail come from calculated fields similar to the following: =iif(Year(fields!PostedDateTime.value) = Year(Now())-1,cint(Fields!Amount.Value),0)
Which I then sum at the group level.
The report looks great, now comes the good part. What my boss wants is if someone clicks each dollar amount that it passes the right begin date and end date parameters to bring back all the detail. So if my reports original paramenters were from 1/1/2003 - 4/30/2006 and they click on the amount under column 2006, row April I need to pass as a parameter to my detail report a begin date of 4/1/2006 and an end date 4/30/2006.
If the columns were hardcoded no biggie, but since they are dynamic, my coding expertise is lacking. I am not sure how to rebuild my parameters.
Something like begin date being= ((Month(fields!PostedDateTime.value))+ 1 + Year(Now())