Solved

Reporting Services 2005 - Navigation from Summary to Detail with Dynamic Parameters

Posted on 2006-06-19
2
367 Views
Last Modified: 2011-10-03
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:

[code]

     2006   2005   2004   2003   2002
Jan   $$     $$     $$     $$     $$

[/code]

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())
0
Comment
Question by:djehmli
2 Comments
 
LVL 4

Accepted Solution

by:
dnsvh earned 500 total points
ID: 16937311
You should be able to pass something like:

MONTH(Fields!PostedDateTime.Value) & "/" & "1" & "/" & YEAR(Fields!PostedDateTime.Value).
0
 

Author Comment

by:djehmli
ID: 16943067
Thank you for the response.  That helps clarify the syntax for the first day of the month, though I would use Year(Now()) for the second half.

But what would the approach be for the end date?  The months are dynamic.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SAP WEBi report SQL generating automatic Join 5 266
SSRS Legacy Report Running with no Data Source? 4 73
Subreport or multiple data sets? 9 62
Combine and display field 5 79
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
How to increase the row limit in Jasper Server.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question