?
Solved

Crystal max-date from sub-report

Posted on 2010-01-11
25
Medium Priority
?
730 Views
Last Modified: 2013-11-15
I would like to do the following:

I have a main report which will display certain totals.  Also I need to find certain dates from other tables using sub-reports.  Sub-report: reads table-B.  For every record-type "B" on Table-B, I need to find the latest date from these records.  Then I need to bring this max-date back to the maijn report and print on the group-footer line.
I know how to use shared variable, but I don't know how to store a "maximum date."

Can you help?
Thanks
0
Comment
Question by:mbroad02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 10
  • 2
25 Comments
 
LVL 16

Expert Comment

by:wykabryan
ID: 26283973
ok.. so you know the hard stuff.. add date to the detail line, then right click insert summary and choose max at the report foot level. Now, since you know how to do a shared variable. set the shared variable to the report footer max(fieldname).
0
 

Author Comment

by:mbroad02
ID: 26284276
Ok, I have done many shared variables, but I was not aware that I could pass the summary value from the footer to another report.   I am trying to do this, but get an error (see attached Word doc) . I place the date in the detail line (hidden) create a summary in the footer for max(date).  Then I create a formula with a shared variable which stores (and passes to main report) the max-date. Am I doing this correctly?
Variable-setup-error.doc
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26284493
see attached.
Save the date.xls in c:\
save the simple.txt as simple.rpt


date.xls
simple.txt
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 16

Expert Comment

by:wykabryan
ID: 26284603
Thanks Mlmcc. the attachment notes didnt say anything..

mbroad02 see this attachment for the report. It was designed in CR10.
simple.rpt
0
 

Author Comment

by:mbroad02
ID: 26285436
I have my report formatted as you have suggested, but have a problem (nothing is displaying on main report).  By the way, when I display the data on the subreport, both the MAX field and the formula passing the max to the shared variable display the date.

See attached.

Thank you
Date-Subreport.doc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 26285522
Where is the subreport?

Is that section suppressed?
If so then the subreport doesn't run

mlmcc
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26285710
also, you need to have the WHILEPRINTINGRECORD; unsuppressed. This must show up in every one of your shared variable formulas.


0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 100 total points
ID: 26285814
Actually that isn't required.  Any formula with a Shared variable is automatically set to WhilePrintingRecords pass.

I always use it to simply document it.

mlmcc
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26285826
hmm.. never knew that.. I always do it as practice.. it make sure that it is executed.. Never know when the next patch is going to break something like that..
0
 

Author Comment

by:mbroad02
ID: 26286505
Folks--No, the subreport is not supressed, I just had it hidden.  The sub-report was working and displaying data.  I saw a comment above saying that my attachment didn't say anything.  I am confused.  Wasn't this helpful in allowing you to understand my problem?   What other documentation would help in debugging this situation?  A report definition?

In your sample, you have nothing but the subreport report-header supressed.  This would cause every line in the sub-report to print, which I don't want.

HELP!
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26286549
right, i just wanted to show you how to get the subreport max date to the main report. The order of which it is displayed on the main report is important. You must show the subreport then the variable call. If the subreport is in groupfoot1a then the formula must exist in groupfooter1b.
0
 

Author Comment

by:mbroad02
ID: 26286605
The subreport is located in the main report detail section.  The formula which returns the variable is located in the group footer.  I do this hundreds of times but I can never seem to remember the placement, what can/cannot be supressed/hidden, etc...
Thanks
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26286643
when using shared variables you can not hid or suppress the subreport.

so how do you get it to appear without making it appear?? unhide it, then go into your subreport and change the color to white. Then get rid of the subreport borders by right clicking on the subreport and format subreport, border tab.  In my example I left the top border for you so you can see where the subreport is..


date.xls
simple.rpt
0
 

Author Comment

by:mbroad02
ID: 26286794
I understand what you are saying, but there are two issues here:
(1)  No matter how "white" or small I make the subreport, since it reads many date records, if the sub-report is not hidden, it will put a large blank section each time it runs.
(2)  I am NOT hiding or supressing the subreport and it is still not passing the varibale value.

I have attached this report (CR 10).  It is only a start of a report but the fields and subreport of which we have spoken are there.

See what you think.
Thanks
0
 

Author Comment

by:mbroad02
ID: 26286829
Whoops--forgot to attach the report---here it is-----
Case-Progress-Extract-Dates-0111.rpt
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26286831
right in the subreport the detail section is being displayed. You can suppress the detail section in the sub as long as it does not contain the shared variable. In my example it takes up very little space on the main report but there is more than 5-6 lines per group.
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26286863
0
 

Author Comment

by:mbroad02
ID: 26286931
I truly appreciate your help.  The field is now displaying.  however, as you can see on the attached, since the subreport must read hundreds of detail records to get the max date, the detail section on the main report is a large blank section, which is not acceptable.  how can I have this not show??

Thanks
large-detail-space.doc
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26286948
did you see my last attachment.. This should take care of it for you.
0
 

Author Comment

by:mbroad02
ID: 26286986
Yes, in fact the DOC print-screen I just sent was that of your version (had a lot of blank space)...
0
 
LVL 16

Accepted Solution

by:
wykabryan earned 1900 total points
ID: 26287030
heheh.. right..

insert new section in the group footer. then drag the groupfooter b above groupfooter a (this will reduce the time of dragging and dropping each column). Then move the subreport into groupfootera. suppress detail line.
0
 

Author Comment

by:mbroad02
ID: 26287068
VIOLA!!!!!   Simple but very effective!  thanks so VERY much for all of your asistance!
0
 
LVL 16

Expert Comment

by:wykabryan
ID: 26287079
sure no problem.. easy breeze..
0
 

Author Closing Comment

by:mbroad02
ID: 31675499
Excellent help.  Went above and beyond!!  A pleasure to have assistance like this!! Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question