Solved

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

Posted on 2006-06-19
2
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

623 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