Nested Subreports and HasData

[LeetFade]

Hello everyone..

I've been struggling with this issue for a while now and have yet to find a
resolve. I have scowered the forums and have found what seemed to be
solutions, yet they didn't work for my scenario. I'm hoping someone can help.

Essentially, I have an Access project. I have a primary report
(rptEmpScoreCard). In that report is a set of 3 subreports -- Split Reports --
(rptEmpScoreCard_Split7, rptEmpScoreCard_Split10, rptEmpScoreCard_Split13).
In each one of those subreports lies a series of 5 more subreports -- Metric
Report -- which are reused and filtered based on the split (each of these
subreports contains data based on a metric with which we're judging the
agent's productivity on). Each subreport has it's own stored procedure built.

The Metric Reports know which data to pull by a hidden text box on each Split
Report that signifies which split it is, and that populates a parameter in
the stored procedure.

Now, the fun part. This all works well except for when one of the splits has
no data. I have the "Can Grow" and "Can Shrink" parameters set on all of the
reports. However, if one of the reports returns no data, it will hide the
detail section where the Metric Reports are, but it retains the area that
they would normally occupy. This keeps the following Split Report from moving
up and filling in that dead space.

I have tried the me.rptsub1.report.visible = Me.rptsub1.report.hasdata and
every possible variation of that which you could possibly think of, but I get
the same result. I CAN hide the data, but it still occupies the space. I have
tried forcing the detail height to 0 and it does nothing. I've put in a
message box to tell me what it reads as the height and I get something like
1520. This is after forcefully setting the height to 0. When I try from the
main report's detail on format to view the subreport and the hasdata (i.e. Me.
sbrptEmpScoreCard_Split7.report.visible = Me.sbrptEmpScoreCard_Split7.Report.
HasData) it hides everything. When I step through the code it says that the
subreport is unbound (gives a code of 1).

Any suggestions would be GREATLY appreciated. I also apologize for the length
of the post. <.<

 

[Marshall Barton]

I've been struggling with this issue for a while now and have yet to find a
resolve. I have scowered the forums and have found what seemed to be
solutions, yet they didn't work for my scenario. I'm hoping someone can help.

Essentially, I have an Access project. I have a primary report
(rptEmpScoreCard). In that report is a set of 3 subreports -- Split Reports --
(rptEmpScoreCard_Split7, rptEmpScoreCard_Split10, rptEmpScoreCard_Split13).
In each one of those subreports lies a series of 5 more subreports -- Metric
Report -- which are reused and filtered based on the split (each of these
subreports contains data based on a metric with which we're judging the
agent's productivity on). Each subreport has it's own stored procedure built.

The Metric Reports know which data to pull by a hidden text box on each Split
Report that signifies which split it is, and that populates a parameter in
the stored procedure.

Now, the fun part. This all works well except for when one of the splits has
no data. I have the "Can Grow" and "Can Shrink" parameters set on all of the
reports. However, if one of the reports returns no data, it will hide the
detail section where the Metric Reports are, but it retains the area that
they would normally occupy. This keeps the following Split Report from moving
up and filling in that dead space.

I have tried the me.rptsub1.report.visible = Me.rptsub1.report.hasdata and
every possible variation of that which you could possibly think of, but I get
the same result. I CAN hide the data, but it still occupies the space. I have
tried forcing the detail height to 0 and it does nothing. I've put in a
message box to tell me what it reads as the height and I get something like
1520. This is after forcefully setting the height to 0. When I try from the
main report's detail on format to view the subreport and the hasdata (i.e. Me.
sbrptEmpScoreCard_Split7.report.visible = Me.sbrptEmpScoreCard_Split7.Report.
HasData) it hides everything. When I step through the code it says that the
subreport is unbound (gives a code of 1).

The HasData/Visible stuff should not be needed.

Note that CanShrink won't when there are other controls in
the same horizontal "band"/

You didn't forget to set the CanShrink property for the
section containing the subreports, did you?

 

[LeetFade via AccessMonster.com]

The HasData/Visible stuff should not be needed.

Note that CanShrink won't when there are other controls in
the same horizontal "band"/

You didn't forget to set the CanShrink property for the
section containing the subreports, did you?

I took the label- which I want to remain visible - and put it in the report
header. I put only the subreports in the detail section. I set the detail
section and every control in the subreports (including the subreport sections)
to can shrink and can grow.

If I don't use the visible code, it shows the fields with all 0's. I'm
assuming this has something to do with the stored procedure. If the agent has
data for ANY of the splits, every one of the split reports return a HasData
of TRUE (or unbound depending on where I call the code).

I did make sure that everything in the horizontal row did have can shrink set
to true and I made sure there were no label fields.

 

[Marshall Barton]

I took the label- which I want to remain visible - and put it in the report
header. I put only the subreports in the detail section. I set the detail
section and every control in the subreports (including the subreport sections)
to can shrink and can grow.

If I don't use the visible code, it shows the fields with all 0's. I'm
assuming this has something to do with the stored procedure. If the agent has
data for ANY of the splits, every one of the split reports return a HasData
of TRUE (or unbound depending on where I call the code).

I did make sure that everything in the horizontal row did have can shrink set
to true and I made sure there were no label fields.

Ok, that clears the air of the usual reasons for not
shrinking.

Note:
* A text box can shrink if its value is Null, a ZLS ("")
or if it is invisible.
* A subreport can shrink if it has no data or is invisible.
* An attached label will be invisible if you make its parent
control invisible.
These facts mean that you should not have any reason to mess
with the controls and sections in the subreport.

From what you say about 0s in the subreport, it sounds like
the subreport really does have some data. Is there any way
you can set things up so you can run the subreport's record
source query independently of the reports to verify that
there are no records returned?

 

[LeetFade via AccessMonster.com]

Ok, that clears the air of the usual reasons for not
shrinking.

Note:
* A text box can shrink if its value is Null, a ZLS ("")
or if it is invisible.
* A subreport can shrink if it has no data or is invisible.
* An attached label will be invisible if you make its parent
control invisible.
These facts mean that you should not have any reason to mess
with the controls and sections in the subreport.

From what you say about 0s in the subreport, it sounds like
the subreport really does have some data. Is there any way
you can set things up so you can run the subreport's record
source query independently of the reports to verify that
there are no records returned?

The issue is that if one of the splits has data, the rest, which have no data
for their respective splits, return 0's. I'm assuming this is because each
split is using the same set of subreports (I could be wrong here). Just to
quickly recap, I have 3 split (a.k.a calling campaign) reports which are each
a subreport on the main scorecard report. Each of the split reports uses the
exact same set of 5 subreports which are metrics. So when an agent has data
for one of the splits (in the metric subreports), the rest of the splits
return 0's in their metric subreports. Because of these 0's, it's retaining
the space because it technically isn't null.

I just tested the SP independent of the report for a date that I knew had
data on one split, and not others and I selected a split where there should
be no data.. I got 0's.... So I'm guessing it's an SP issue and not a report
issue...

 

[LeetFade via AccessMonster.com]

Marshall,

Thank you VERY much for your help. I found the error finally.

This is a project that has been turned over to me so I've assumed someone
else's code. What he had done was write a CASE WHEN statement that returned a
0 if there was no data instead of a NULL. I have corrected this and removed
all of my VISIBLE = FALSE statements and it's working!!

Thank you again for your help.

 

[Marshall Barton]

Marshall,

Thank you VERY much for your help. I found the error finally.

This is a project that has been turned over to me so I've assumed someone
else's code. What he had done was write a CASE WHEN statement that returned a
0 if there was no data instead of a NULL. I have corrected this and removed
all of my VISIBLE = FALSE statements and it's working!!

Thank you again for your help.

Nice job of tracking it down, verifying the problem,
correcting the cause, cleaning up the test code and getting
the desired result.

Only one follow up thought here. Keep this issue in mind in
case you find a calculation that does something different
with Null instead of 0 (e.g. an average).

 

[LeetFade via AccessMonster.com]

Marshall,

[quoted text clipped - 6 lines]

Thank you again for your help.

Nice job of tracking it down, verifying the problem,
correcting the cause, cleaning up the test code and getting
the desired result.

Only one follow up thought here. Keep this issue in mind in
case you find a calculation that does something different
with Null instead of 0 (e.g. an average).

I've definitely got my eyes peeled for that one. I found one issue already in
that I do these statistics for 2 weeks prior, 1 week prior, Current Week, MTD,
and QTD all horizontally. Now if I have data in say the MTD and QTD but
nothing else, the rest of the fields shrink and leave white areas. This is
because of the Can Shrink being used per text box. If I don't use can shrink
on the individual controls, it leaves formatted blank rows and doesn't shrink
the entire report even when all are null so I need to adjust the CASE WHEN to
reference the most encompassing value (QTD Goal) vs. the local value (i.e.
GoalValueWeek1).

 

[Marshall Barton]

[quoted text clipped - 6 lines]

Thank you again for your help.

Nice job of tracking it down, verifying the problem,
correcting the cause, cleaning up the test code and getting
the desired result.

Only one follow up thought here. Keep this issue in mind in
case you find a calculation that does something different
with Null instead of 0 (e.g. an average).

I've definitely got my eyes peeled for that one. I found one issue already in
that I do these statistics for 2 weeks prior, 1 week prior, Current Week, MTD,
and QTD all horizontally. Now if I have data in say the MTD and QTD but
nothing else, the rest of the fields shrink and leave white areas. This is
because of the Can Shrink being used per text box. If I don't use can shrink
on the individual controls, it leaves formatted blank rows and doesn't shrink
the entire report even when all are null so I need to adjust the CASE WHEN to
reference the most encompassing value (QTD Goal) vs. the local value (i.e.
GoalValueWeek1).

If you don't need to edit the values (not likely for a
calculated statistic), don't forget that you can use a
control source expression with the Nz() function. E.g.

=Nz(v1,0) + Nz(v2,0) + . . .

 

[LeetFade via AccessMonster.com]

[quoted text clipped - 6 lines]

[quoted text clipped - 17 lines]

reference the most encompassing value (QTD Goal) vs. the local value (i.e.
GoalValueWeek1).

If you don't need to edit the values (not likely for a
calculated statistic), don't forget that you can use a
control source expression with the Nz() function. E.g.

=Nz(v1,0) + Nz(v2,0) + . . .

Sadly, I've never heard of the Nz function. I'll have to look that one up.

 

[Marshall Barton]

[quoted text clipped - 6 lines]

[quoted text clipped - 17 lines]

reference the most encompassing value (QTD Goal) vs. the local value (i.e.
GoalValueWeek1).

If you don't need to edit the values (not likely for a
calculated statistic), don't forget that you can use a
control source expression with the Nz() function. E.g.

=Nz(v1,0) + Nz(v2,0) + . . .

Sadly, I've never heard of the Nz function. I'll have to look that one up.

Definitely look it up. It's probably way better than
messing with the data table or even creating a special
purpose query, because it localizes the Null/Zero issue to
the calculation where it matters.

 

[LeetFade via AccessMonster.com]

[quoted text clipped - 6 lines]

[quoted text clipped - 9 lines]

Sadly, I've never heard of the Nz function. I'll have to look that one up.

Definitely look it up. It's probably way better than
messing with the data table or even creating a special
purpose query, because it localizes the Null/Zero issue to
the calculation where it matters.

I actually cheated a little bit. LoL. I got the VPs to sign off on causing
the data label to show when any column had data and then just coloring the
background of each subreport so they showed the same color as the report when
there were sections with no data. So, essentially, the text boxes still
shrink and hide, but the background color stays consistent with the rest of
the report. If all columns are null, it just hides the whole section.

It was a sign off that saved me a LOT of headache. Each SP is close to 500
lines because of accounting for multiple management levels in the reporting.

I'll still take a look at that report as I would prefer the 0's if there's
data anywhere, but there is a whole lot of nested code to work with.