Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Passing Query driven parameter to subreport not working

Avatar of ccaines
ccainesFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL Server 2005SSRS
13 Comments1 Solution3375 ViewsLast Modified:
Hi,

I have an SSRS 08 Report Server I am designing in VS08 and this report has a subreport which takes two dates as parameters (A start date and end date) and returns a table of results.

Firstly, the main report is passed a user selected parameter which defines a date range. For this example, we'll say "Last Month" = 5

The main report has a Dataset which takes this parameter and calculates and returns the first and last date between a particular timespan, and returns those dates. So again, we would feed the 5 in this example to this query and it would return Start_Date, "2010-07-01 00:00:00.000" and End_Date, "2010-07-30 00:00:00.000"

I know this works, as if I run the main report with a textbox returning the results of that query I get the dates expected, so know they're being generated.

The Subreport takes two parameters @SD and @ED, which are unsuprisingly the start and end date of it's query and returns a list of appointments between those dates.

The problem is that when I pass the two dates to the subreport, it returns nothing (or if I disallow blanks/nulls, give the "subreport is missing a parameter" error).

If I test the subreport directly by hardcoding in the date returned by the Main Report query then it works and, most importantly if I pass the parameters with hardcoded values in, again this works, so I know it's not a problem with the Subreport.

In the parameter setting for the Subreport I am using this expression (in the case of SD):

=Format(First(Fields!start_date.Value, "DateRange"), "yyyy-MM-dd HH:mm:ss.FFF")

however, just using =First(Fields!start_date.Value, "DateRange") yields the same results.

Looking for advice as I can't work out why these query results are getting to a textbox I place on the main report, but not into the parameter fields for the subreport.

Many thanks.


ASKER CERTIFIED SOLUTION
Avatar of Utkarsh Kulkarni
Utkarsh KulkarniFlag of India imageSr. Sw Eng

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 13 Comments.
See Answers