It seemed really simple reading the notes,

I need to create a report containing a sub report and i need both parts to be filtered for a date range.

I have entered two parameters in the report, remarkably called Date1 and Date2, i have then created the sub report and i have also created two parameters, shock horror, called Date1 and Date2.

In the report i have inserted a sub report box to which i have right clicked and linked the subreport, and then set the parameters to be Date1 =createdon and Date2 also equal to createdon.

If i don’t try and filter the sub report then, apart from bringing back all the data the report as a whole works, now as soon as i try and insert the filter into the subreport the report as a whole no longer works, the main report still functions but the sub report section comes back with no data as a message.

bump

I’m assuming you need to link the parameters in the properties of the subreport box.

i.e. Right click subreport box
Go to parameters tab
Select Date1 for Param Name. Then write an expression in Param Value to point to the main report’s Date1 param.

Then the same again for Date2.

thanks, did that and it didn’t work? i have suspicions there may be some issues with the server.

Does the subreport display OK when you preview it?

If i preview the subreport on its own it is ok, but as soon as i try and use the subreport in the main report it won’t work.

But there have been a few issues over the last week with the CRM server and the reporting server, which i think may be causing the report to time out.

Can you eliminate this potential issue by mocking up some data on a local server? I’ve always found SSRS report parameters a bit flaky and sometimes rebuilding the report does the trick.

Another question!

Are your datasets defined by you writing T-SQL statements locally (in BIDS), or are you calling stored procedures?

I am new to all this, and i only have some access rights,

Basically what i have is MS visual Studio to write the reports in, i am using the visual tools to l develop a query to link various defined views, then a little tweaking of the SQL to get it to do what i want, ie extract the data i require for the report.

I then use the report writing tools to create the graphical user friendly report. This can then be imported into Dynamics CRM.

I had noticed the flakiness!!!

Are the date formats the same in both the report & subreport?

I’ve not used SSRS but in Crystal reports when linking date parameters they both need to be set to either Date or DateTime otherwise the parameters won’t pass across.

dates are the same, i am running the reports off the same views,

Here’s how I’d do it. Sorry if this is a bit obvious.

1. Create a new report, with a new dataset defined by, for example,

SELECT fieldA FROM table1 WHERE dateField1 = @startDate

2. This should automatically create a new report parameter called @startDate with the same type as the dateField1 column.

3. Build the report (minus the subreport)

4. Create a second report (to be your subreport) with a new dataset defined by, for example,

SELECT fieldB FROM table2 WHERE dateField2 = @startDate

5. This similarly creates a report parameter @startDate with the same type as the dateField2 column

6. Check that the @startDate parameters in both reports have the same type e.g. datetime

7. Then add a subreport to the first report, and in the subreport properties, define the subreport parameter @startDate = Parameters!@startDate

thanks for that boxfish, at least i am happy that i am doing it the right way.

I suspect i need to talk to IT and get them to explain why the servers aren’t working correctly.

Good luck!

Formatting dates & times in SQL Server can be a right pain, even without parameters being involved.