Report: Shrink does not work

I have a main report -"RptQryCom" that will have 11 subreports attached (SubRpt_01BS, SubRpt_02WWS, etc. through SubRpt_11OC).  I have with your expert help generated SubRpt_01BS and SubRpt_02WWS.

Each record in these  reports will have fields with no information and some entire SubRpts will not have any information in some records.

I need to Condense/Shrink the report to show only fields with information.  How is the best/an easy way to accomplish this shrinkage?

Presently there are two fields in each row of the SubRpt.  I would not expect any change in row that only had information in only one field of a row.

Timothy
PSAssm-2013-0905-11247.accdb
TimothyAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
1. I rarely use subreports unless it is the only way to get the output I need.
In many cases a "Grouped report" works much better, ...then issue like this will never come up in the first place.

2. Can you explain why a grouped report cannot be used?

3. Explain the structure of this report so it is clear why 11 subreports are needed.

4. When you post a sample database, please include explicit instructions on how to use it.
(I don't see any report there with 11 subreports...?)

5. It is not clear from your question title: "Report: Shrink does not work"
...which "Shrink" property you are referring to, ... The Can Shrink property of the report section, ..or the Can Shrink property of the SubReport...

...In either case the Can Shrink property will only work if the control or section has nothing to display, in the case of a subform, the subform "itself" will always display, even if no data present, ..so again, it is not clear why a grouped report cannot be used. (where this would not be an issue.

To be fair, you can certainly hide the subreport if no data is present, but it is much easier if a grouped report can be used instead

JeffCoachman
0
TimothyAAuthor Commented:
Jeff,

Thanks for your input.  It is helping me to realize how deficient my communication skills have become.  Your input is, also, is causing to reevaluate the whole report structure.

First.      I am not at all sure table structure is ideal. It can be changed, but not before this report must be furnished.
Second.      Now back to your reply:
1.      I can and am willing to change the report structure.
2.      There is no reason I know of preventing change to a grouping format.
3.      Structure:
Once the report (“RptQryCom”) is completed it will pull only text fields form the 11 tables with name form as “tbl_01_BS, tbl_02_WS, tbl_03_WPipe”, etc.  Only fields with field names ending in “_Com” will be in the selection pool.
Many of the fields are do not have visible information/empty (I loose tack of empty, no data, null, no string, empty string)
Report:
Present “RptQryCom” was to have header of fields form the “tblPS_GenData” and all final calculation for the PS_Master_ID = Site_PS_ID.  This part seems to be working fine.  Report body was to pull only text fields form the 11 tables with the names in the form of “tbl_01_BS, tbl_02_WS, tbl_03_WPipe”, etc.  I stared building subreports.  I soon realized that each subreport was mostly empty space.  It is here, I started looking into Shrink issues and eventually for help.
4.      My bust.  I will be more precise.
5.      I did not give any history.  Again my bust.  Before I sent out the help request, I had changed all Shrink options to yes.  These yes were turned on for everywhere control I could find the question in the subreport and in the detail.  I had noticed that neither the detail nor tab was shrinking when information seemed to be absent.

In closing, is seems to me I should start over.  Please advise what you see as the best way to proceed.

Timothy
0
Jeffrey CoachmanMIS LiasonCommented:
Timothy.

Hey, thanks for the feedback
;-)

My plan was to see if the report could be grouped.

In reading your feedback, it turns out you were correct..
If there are many Child reports based on separate tables then a grouped report is not practical
;-)

OK, so lets do this...
Here is a sample report with one main report and two sub reports, ...the only  Can Grow property that is set is the can Grow of the subreport object itself.

The subreports grow and shrink just fine, and when the subreport has no data it does not display.

Let me know


;-)

Jeff Coachman
Database128.mdb
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

TimothyAAuthor Commented:
Jeff,

Thanks so much.

I have to leave work now.  I will look it over this evening.

Thanks again.

Timothy
0
TimothyAAuthor Commented:
Jeff,

I have reviewed your "Database128.mdb" is see that the reports are shrinking by absent records in linked tables.  I also have been able to accomplish this in my latest version of the data base ("20130905@0552_PSAssm.accdb") attached.

If you will look at Report: RptQry01BS_All_Com_NoLabel", you see it seems to be shrinking properly.  I have formatted it to two columns.  The only problem is it will not shrink into the space with field text labels.  It shrinks past these empty spaces when I remove the labels as you can see in "RptQry01BS_All_Com".  I must have the labels for shown fields.  I will use the "RptQry01BS_All_Com" report form unless there is a straight forward fix to shrink over blank fields with labels.

Thanks your your continuing help with my issues.  It is a great comfort to be help by experts.

Timothy
20130905-0552-PSAssm.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
It is hard to see what you are talking about.

In Report: RptQry01BS_All_Com_NoLabel
...You have textboxes there, not subreports as you originally stated...

Please post a screenshot clearly pointing out where the problem area is.
And please draw a line between each control so I can clearly see where each begins and ends (and see what is, ...or is not shrinking...)

Jeff
0
TimothyAAuthor Commented:
Jeff,

Thanks for your reply.

I will have to send you the screen show from work tomorrow.

I realize I jumped around without giving a full explanation.  I came to the conclusion from earlier correspondence that the problem I was having was bigger than subreports.  It was first a report problem.  So I went back to looking at simple reports drawing from one table.  .

I have been working only with tbl_01_BS through queries Qry01BS_All_Com and Qry01BS_Nz. To obtain report results for 4 queries: RptQry01BS_All_Com, RptQry0BS_All_Com_NoLabel, RptQry01BS_Ns, and RptQry01BS_Nz_Show Labels.

I realize now I only referenced "Qry01BS_All_Com" in my earlier comment.

The question at least to me seem to be and should have been earlier, if I had been wise enough to understand the core issue, can empty fields with "text labels be shrunken"?

I will send the screen shows in the morning from work.

Timothy
0
TimothyAAuthor Commented:
Jeff,

I have spent sometime stating a report with subreports.  I hope this is more in line with the original question.

So attached find:  "ReportSoorceIssue.pdf" and "PSAssm_2013_0909_1700.accdb".

Main report "rptCatTotal_Com" control source is "qrpt_Master".  Subreport control sources are from individual queries, each starting with "qsrpt".  These queries draw directly from corresponding tables with name in the form of tbl_##_??.

I have studied the sample file you sent me earlier(Database128.mdb).  I have not been able to determine what I am over looking, neither not work at all. Neither subreport srpt05 and srpt06 which have completely empty groupings shrink.

Your help in figuring this out will be greatly appreciated.

Timothy
ReportSorceIssue.pdf
PSAssm-2013-0909-1700.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
Still a bit confused...

What page are you on in that screenshot?
what is the exact name of the subreport that is not shrinking?
What happened to drawing line between the subreports so I could see where they begin and end?
Just include enough subreports to demonstrate one that does not shrink...

In any event...
You were correct, the labels are what is stopping the report form shrinking.
You need to delete the labels and include that same text in the control source of the textbox, ...then add an expression to make nothing appear if the field is empty...
For example this works for the Capacity subreport's "Score" field:
    =IIf(IsNull([C_PumpCapacityScore_Com]),"","Score: " & [C_PumpCapacityScore_Com])

Note that on Page 2, with no data in the Capacity subreport, it has shrunk...
(Notice here how I have set the detail section to yellow, and enclosed it in black lines so you can clearly see the subreport?)
screenshot
JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimothyAAuthor Commented:
Jeff,

Thanks.  

I see what your are talking about.  I had been wondering how to make the label disappear and if it did would the empty string still allow it to shrink.  You have answered this question through example.  The beauty of your example is that it places the label and data field in the same code.  Thanks.


I have attached the earlier snap shoot from work showing the whole subreport not shrinking when empty.  

Again thanks for your help.
ReportSorceIssue2013-0910.pdf
0
Jeffrey CoachmanMIS LiasonCommented:
OK, so problem solved?
0
TimothyAAuthor Commented:
Jeff,

Thanks!

It seems to be fixed, I am working through it now.

I will get back with you later.

Your help is truly appreciated.

Timothy
0
TimothyAAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for TimothyA's comment #a39479896

for the following reason:

Jeff,

Continued to give me very good advice long after his patience with me had worn thin.  His help was greatly appreciated.
0
Jeffrey CoachmanMIS LiasonCommented:
I am a little confused here...

I posted a solid working solution and you thanked me for the help,...
...but you accepted your own post as the Solution?

JeffCoachman
0
TimothyAAuthor Commented:
Jeff,

You should be confused.  I messed up again.  Sorry.

How is the best way to change this to you?  Can I go back to your Posted on 2013-09-10 at 07:04:2 and accept as solution?

Timothy
0
Jeffrey CoachmanMIS LiasonCommented:
Is the "request attention"ink still visible for you?
If so, click it, and explain your wishes...
0
TimothyAAuthor Commented:
"Request attention" link is not still visible to me.

Timothy
0
TimothyAAuthor Commented:
Thanks again.

Timothy
0
Jeffrey CoachmanMIS LiasonCommented:
...LOL, you still did not select the post that actually solved the issue:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28231778.html#a39478484

Try again please..
;-)

Jeff
0
TimothyAAuthor Commented:
Jeff,

Only Accepted solution adjacent to the excellent 500 is still open for any action.  I am a little skittish of going their since it is at the same post.

Let me know if I should do anything more.

Timothy
0
Jeffrey CoachmanMIS LiasonCommented:
Not sure what you mean by that.  
But you selected a post that was not actually a "solution"...
Pleas click the "Request Attention" link and as for help in closing this question
0
TimothyAAuthor Commented:
Jeff,

This "Shrink question"  no longer has "Request attention" link or any other link on my view.

At any rate I think all is OK, the Accepted solution is only one record below the actual solution.

Thanks again.

Timothy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.