Hiding Main Report row if subreport is empty

I have a main report which has a list of people, and a subreport with
some data about each person. How do I hide that person's name (ie the
main report row) if the subreport of that person does not contain any
data?
Thanks,
HanHey Hannibal,
There are probably a few ways to deal with this, but what I usually do is
just add a reference in your main reports dataset to the people_detail table.
Then add an expression that evaluates that reference. For example:
if your dataset is
SELECT pkyPeople, firstname, lastname FROM People
modify it to be
SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
FROM People as p LEFT OUTER JOIN People_Details as pd on
p.pkyPeople=pd.fkyPeople
Then, when your running your report you just need an expression in the
'visibility' sections 'Hidden' field that reads
=IIf(Fields!fkyPeople.value = "", True, False)
Michael
"Hannibal111111" wrote:
> I have a main report which has a list of people, and a subreport with
> some data about each person. How do I hide that person's name (ie the
> main report row) if the subreport of that person does not contain any
> data?
> Thanks,
> Han
>|||On Jul 18, 7:16 pm, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Hey Hannibal,
> There are probably a few ways to deal with this, but what I usually do is
> just add a reference in your main reports dataset to the people_detail table.
> Then add an expression that evaluates that reference. For example:
> if your dataset is
> SELECT pkyPeople, firstname, lastname FROM People
> modify it to be
> SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
> FROM People as p LEFT OUTER JOIN People_Details as pd on
> p.pkyPeople=pd.fkyPeople
> Then, when your running your report you just need an expression in the
> 'visibility' sections 'Hidden' field that reads
> =IIf(Fields!fkyPeople.value = "", True, False)
> Michael
>
> "Hannibal111111" wrote:
> > I have a main report which has a list of people, and a subreport with
> > some data about each person. How do I hide that person's name (ie the
> > main report row) if the subreport of that person does not contain any
> > data?
> > Thanks,
> > Han- Hide quoted text -
> - Show quoted text -
I would love to do that, unfortunately the detail data is contained on
a different server, which is the reason why I had to use the
subreport. Any other ideas?|||As a matter of fact I do!
i would change my nesting from
Main Report (people data)
--> SubReport (people details)
To be
1. Main Report (Parameter Data)
2. --> SubReport (People Data filtered by paramters. 1 person at a
time)
3. --> subSubReport (Details)
Now, level 1 simply passes the current person to level 2. At level 2 you
need two datasources. The first one looks at the People Server the second
one at the Details Server. The first dataset for the level 2 report looks
just like it does currently, except you need to add a WHERE PeopleId =@.People (to filter for the current person). Then you need to add a second
dataset, whose DataSource is the Detail Server. In this dataset you filter
for the details of that same person. Then just evaluate
First(Fields!PersonId.Value,"PersonDetails") to see if there is any data. At
this point you will then know whether or not to hide the details subreport.
Please feel free to ask for more description if this doesn't make sense! I
hope this helps though.
Michael
> On Jul 18, 7:16 pm, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Hey Hannibal,
> > There are probably a few ways to deal with this, but what I usually do is
> > just add a reference in your main reports dataset to the people_detail table.
> > Then add an expression that evaluates that reference. For example:
> >
> > if your dataset is
> >
> > SELECT pkyPeople, firstname, lastname FROM People
> >
> > modify it to be
> >
> > SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
> > FROM People as p LEFT OUTER JOIN People_Details as pd on
> > p.pkyPeople=pd.fkyPeople
> >
> > Then, when your running your report you just need an expression in the
> > 'visibility' sections 'Hidden' field that reads
> >
> > =IIf(Fields!fkyPeople.value = "", True, False)
> >
> > Michael
> >
> >
> >
> > "Hannibal111111" wrote:
> > > I have a main report which has a list of people, and a subreport with
> > > some data about each person. How do I hide that person's name (ie the
> > > main report row) if the subreport of that person does not contain any
> > > data?
> >
> > > Thanks,
> >
> > > Han- Hide quoted text -
> >
> > - Show quoted text -
> I would love to do that, unfortunately the detail data is contained on
> a different server, which is the reason why I had to use the
> subreport. Any other ideas?
>|||Ah...I just figured out why this may not work because I've never deployed
anything like that...however can you assign 2 datasources in ReportManager to
a single report? Interesting problem....
"Hannibal111111" wrote:
> On Jul 18, 7:16 pm, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Hey Hannibal,
> > There are probably a few ways to deal with this, but what I usually do is
> > just add a reference in your main reports dataset to the people_detail table.
> > Then add an expression that evaluates that reference. For example:
> >
> > if your dataset is
> >
> > SELECT pkyPeople, firstname, lastname FROM People
> >
> > modify it to be
> >
> > SELECT p.pkyPeople,firstname,lastname, pd.fkyPeople
> > FROM People as p LEFT OUTER JOIN People_Details as pd on
> > p.pkyPeople=pd.fkyPeople
> >
> > Then, when your running your report you just need an expression in the
> > 'visibility' sections 'Hidden' field that reads
> >
> > =IIf(Fields!fkyPeople.value = "", True, False)
> >
> > Michael
> >
> >
> >
> > "Hannibal111111" wrote:
> > > I have a main report which has a list of people, and a subreport with
> > > some data about each person. How do I hide that person's name (ie the
> > > main report row) if the subreport of that person does not contain any
> > > data?
> >
> > > Thanks,
> >
> > > Han- Hide quoted text -
> >
> > - Show quoted text -
> I would love to do that, unfortunately the detail data is contained on
> a different server, which is the reason why I had to use the
> subreport. Any other ideas?
>