0

I have been working with SSRS for the last year and a half or so. Everything I know from it has been learned by trial and error and google. So hopefully I get my terminology correct, but forgive me if I say something incorrectly.

I am using Visual Studios 2015, connecting to SQL 2012 server. I'm creating an asp.net/VB web application to display SSRS reports on a local site. All of my reports up to this point have been a single report with no sub-report. If "sup-report data" was desired, I created a drill-through, using an Action, to navigate to a completely separate page. This has been working really well for all my reports, but now I would like to get a little fancier.

I have been trying to create a SSRS report that contains a sub-report. The main report contains summary information about batches of a particular product that has been run (blue and white portion of the table in the picture). If a user wants to view more specific details about the batch, they can click on the expand/collapse button of the "Process Order" column, and a sub-report below the summary line will appear with more specific information about the batch (sub report is the light grey/dark grey table).

enter image description here

I configured the properties on the main report to pass the ProcessOrder value as a parameter on the sub-report and create a parameter on the sub report to accept this parameter.

enter image description here

You can tell that this parameter is being passed successfully because I have the respective parameter being passed and displayed just above the table (and it matches the number on the main table). As you can see from the picture, I also have the Process Order number displayed on the sub-report table itself, and this number is different than what is being passed to the report. It is showing a "1", which is the default value that I gave to the SQLDataSource control on my aspx page to build the dataset. I've tried not setting a "Default value" but then I get the following error: “Data retrieval failed for the subreport, 'test_Sub_Report2', located at: [location on hard drive] Please check the log files for more information.” Has anyone encountered this problem before, or can anyone give me an idea of a direction to go from here? I feel like I’m very close since I’m getting the parameter successfully passed to the sub-report, but the dataset is just not using that parameter to go get the data from SQL, and I’m not sure how to tell it to do so.

I can add more details and post more of the actual code, just let me know what you need and I will do my best to get it to you. I would have included more screenshots, but I don't have enough rep points to do so yet.

Improve this question
3
  • You don't mention at what point you map the parameter on the sub-report to the dataset that populates the sub-report. What have you done to expect that your subreport data should be related to the passed parameter and not the default value of 1? Commented Dec 22, 2015 at 20:00
  • Maybe that's where I'm failing. I have included the sub-report object on my main report, in a Details Row of a tablix. On that sub-report, I went into the properties, on the Parameters tab, and selected the [PROCESS_ORDER] field to be passed to the parameter @processOrder. Then on the sub-report, I created a parameter, processOrder, and assumed that it would use that parameter to build the dataset. Everything that I've found online indicates that it should be that easy. If not, I'm not sure where or how to link the passed parameter from the main report to the dataset creation of the sub-report Commented Dec 22, 2015 at 23:16
  • This is the tutorial that I followed when I was trying to figure it out. It just seems to work in his case: youtube.com/watch?v=6S8Zzqthsbw Commented Dec 22, 2015 at 23:17

2 Answers 2

Reset to default
0

You need to go into the DataSet that populates the sub-report, and go to the Parameters tab, and map the ProcessOrder parameter of the dataset to the Variable that you pass the ProcessOrder parameter to from the main report.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I'm not sure what you mean. On my main report, I've gone into the properties of the sub-report to the Parameter tab and set the Parameters to pass from there. That's how the sub report is getting the numbers to display, but it's just not using the numbers to build the dataset, it's using the default value from the asp SQLDataSet control.
Do you mean that this report is being added to an ASP page, and that the sub-report is using a DataSet from the ASP page instead of the DataSet within the SSRS?
I don't know of another way to do it. I have a Dataset within each SSRS; main Report: DataSet1, sub-report: DataSet2. On my asp page I have a Report Viewer Control that specifies that the report will have two Report data sources, which I link to the reports using SqlDataSource Controls. These are also how I load my parameters into the main report from two datepicker fields and textbox.
Ok, do what I said in my answer, but do it in SSRS. Not in ASP. And in your sub-report, make sure your tablix is using SSRS DataSet2 as it's datasource, and not an ASP datasource.
As far as I know the SqlDataSource control on the asp page works with the DataSet in the SSRS to populate the page. This is how it works on all my other reports, both pieces are required. But in the case of sub reports, I'm unable to get the sub-report dataset to populate using the passed parameters, it will only use the default value from the asp SQLDataSource. I have linked the Process Order to the parameter from the main report to the sub-report parameter on the RDLC, but this is how it's been from the beginning.
|
0

I ended up figuring out the answer. I used the answer from @Lazy Coder on this question: SubReport is not working after adding parameter I was adding the parameters to the sub-report incorrectly. I did have them passed to the sub-report through the table on my main report, which was getting the parameters to the sub-report, but the sub-report was not using these values to create the data set. On my test_page.aspx.vb file I had to change my SubReportProcessing Event Handler. I needed to explicitly set the parameters values there. Since the time that I originally posted this question, I added another parameter to my stored procedure as well, subTotalTime, to correctly filter my results. 

Public Sub SetSubDataSource(sender As Object, e As SubreportProcessingEventArgs)
    Dim report = DirectCast(sender, LocalReport).DataSources(0)
    Dim subProcessOrder = e.Parameters("subProcessOrder").Values(0)
    Dim subTotalTime = e.Parameters("subTotalTime").Values(0)
    SqlDataSource_PPMS_test_Sub_Page.SelectParameters(1).DefaultValue = subProcessOrder
    SqlDataSource_PPMS_test_Sub_Page.SelectParameters(2).DefaultValue = subTotalTime
    e.DataSources.Add(New ReportDataSource("DataSet2", SqlDataSource_PPMS_test_Sub_Page))
End Sub

SqlDataSource_PPMS_test_Sub_Page is the id of my SqlDataSource object on my aspx page.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.