SSRS and writing a report with sub reports

Chat Forum

SSRS and writing a report with sub reports

14 posts & 4 voices | Started 6 months ago by mrmo | Latest reply from boxfish

Tags:

No tags yet.

mrmo - Member

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.

Posted 6 months ago #
mrmo - Member

bump

Posted 6 months ago #
damitamit - Member

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.

Posted 6 months ago #
mrmo - Member

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

Posted 6 months ago #
boxfish - Member

Does the subreport display OK when you preview it?

Posted 6 months ago #
mrmo - Member

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.

Posted 6 months ago #
boxfish - Member

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.

Posted 6 months ago #
boxfish - Member

Another question!

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

Posted 6 months ago #
mrmo - Member

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!!!

Posted 6 months ago #
The Pinkster - Member

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.

Posted 6 months ago #
mrmo - Member

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

Posted 6 months ago #
boxfish - Member

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

Posted 6 months ago #
mrmo - Member

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.

Posted 6 months ago #
boxfish - Member

Good luck!

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

Posted 6 months ago #

RSS feed for this topic

Reply

You must log in to post.

Singletrack Mountain Bike Magazine

Chat Forum

SSRS and writing a report with sub reports

Reply

Don't miss…

Singletrack

Website & Forums

Exits