My subreport only shows 1 record, even when there are more. Why?
-
I've got a main report, which is a detail report. And a smaller report, that's used by the main report as a sub-report. The sub-report displays all records related to the selected EntryID that's displayed in the main report. (There's a main table and a child table, that are in a 1-to-many relationship.) When I preview data for the sub-report, selecting a value for EntryID that I know has more than 1 record in the child table for that EntryID, it shows me all of the records for that EntryID. Exactly as it should. But when I show it in the sub-report of the main report, it only shows the first record.
Why is that? And how do I fix it?
Kindest Regards, Rod Connect with me on LinkedIn.
-
Not sure how you would end up with only one record in your subreport. Are you using a parameterized stored procedure as the source for your subreport's dataset? Does the subreport contain a tablix?
-
I'm at home now, so can't look up whether or not I use a stored procedure or not, but I think I do. I do know that it uses a Tablix.
Kindest Regards, Rod Connect with me on LinkedIn.
-
pietlinden, I'm at work now. I've just checked the sub-report. It does use a stored procedure. Like I said, it works fine, so long as I run the sub-report by itself. (Do a preview, while in Visual Studio 2017.) But from within the main report, when I preview it there within VS 2017, it only shows 1 record, even if I know there's more.
Kindest Regards, Rod Connect with me on LinkedIn.
-
What kind of visual are you using for the subreport? Is it a tablix? I would test it by creating a simple tablix and populating it and see if you get more than one record in the tablix.
Beyond that I'm not sure what to suggest because without data it's impossible to test.
- pietlinden - Monday, December 3, 2018 9:43 AM
I may be misunderstanding you, so please forgive me if I am. I am not using any visual in the subreport, as in a chart or map. It does use a Tablix and the data is generated from a stored procedure. Are you suggesting that I create another report, as a main report, which has a Tablix in it, then within that Tablix I have a subreport that is the subreport I'm trying to see, with multiple records (if there are multiple records)?
Concerning the data, yes it is frustrating that I can't display it to you. Unfortunately, it is patient data. If I were to display the main report with the subreport, I'd be violating HIPAA regulations. However, I can preview just the subreport with multiple records, as doesn't have any PII in it.
Kindest Regards, Rod Connect with me on LinkedIn.
-
I used to work in healthcare, so I totally understand the confidentiality stuff. (Taking the Good Clinical Practices exam after looking at what they were doing nearly gave me a heart attack!)
I can only think of a few reasons why you'd only get one record in the subreport:
- you're inadvertently aggregating in the subreport ... that's why I was wondering what would happen if you dropped another tablix in there and didn't summarize.
- you're filtering the subreport so you only get one value per parent record.If neither of those is true, I'm not sure... I've made that mistake when dropping subreport fields where they don't belong and get an automatic aggregate wrapped around my field... other than that, I'm out of ideas.
-
I could very well be aggregating on the subreport, in the main report. I'm far from a SSRS expert. In fact, I've not done that many sub-reports on a report. So, how can I tell if I've inadvertently aggregating that sub-report on my main report?
Kindest Regards, Rod Connect with me on LinkedIn.
-
You'd be able to tell because when you drop the field on the wrong report, you'd get something like
= FIRST(DataSetName!FieldName.Value)instead of just the fieldname.
=FieldName.ValueI would check the filters on the subform too. Maybe that's filtering out the other records.
-
In the sub-report I have the following:
=Fields!Started.ValueIn the main report, it's just the sub-report that's there. By that I mean the sub-report control is on the main report. The sub-report control points to the sub-report.
In the sub-report, I used a Tablix. Should I have used something different?
Kindest Regards, Rod Connect with me on LinkedIn.
-
That looks normal. You don't have any filters on the subreport, do you?
What happens if you make the parameters for the subreport visible (temporarily) and open it by itself? It works as expected - multiple rows in your tablix?Sorry, I'm just trying to work out why the subreport would do this,and unless I'm way off (someone feel free to correct me!), the ways to filter the subreport are:
1. in the stored procedure it's based on
2. with the join between the parent report and the child
3. with the Filters in SSRS.So where is this filtering sneaking in?
-
Something else I want to mention, in case this has some bearing on my problem, I've only tested this by using the Preview functionality within Visual Studio 2017. I've not deployed it to a SSRS Report Manager on any server. I can't imagine this would make a difference, but could it?
Kindest Regards, Rod Connect with me on LinkedIn.
-
It shouldn't. The only way I can see that making a difference is if you're filtering the report based on who's running/reading it (using a filter on the username).
You might have to ping Sue H and see what she thinks could be going on. - Rod at work - Tuesday, December 4, 2018 2:28 PM
Rod - can you provide a screen shot of the stored procedure parameters, the report parameters for the parent report, the sub-report configuration with parameters and the sub-report parameters?
You can also check the sub-report for any filters on the dataset(s) - it is possible that a filter is getting set and used when the sub-report is called from the parent report that is not getting set when run directly.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Jeffrey,
Here are the stored procedure parameters:
CREATE PROCEDURE [dbo].[spStopDetailsByEntryID]
@EntryID int
AS
And here's a pic of the sub-report's parameters
And here's what it looks like from the main report
And here's the expression from for the sub-report on the main report:
I don't know about filters in the main report being applied to the dataset that could effect the sub-report. There's only 1 dataset in the main report. It returns 1 record, by design, because the main report shows everything from the parent table for 1 EntryID. However, that @EntryID on the main report, is itself a parameter for the main report. Basically, the main report, which has a parameter of @EntryID, passes that @EntryID to the sub-report, which queries a child table. The child table is in a one-to-many relationship with the parent table.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply