Solved

conditional suppression with shared variable

Posted on 2011-09-13
20
271 Views
Last Modified: 2012-05-12
Ok so I need to know how to create shared variable between a main and sub report..
if the customer count is 1 in the subreport then I need to suppress the whole record  in the subreport AND the matching record in the main report

guessing a shared variable is the way to go.. looking at examples but havent gotten it yet..
Anyone have a answer for this?

0
Comment
Question by:shockacon
  • 9
  • 6
  • 4
20 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36531756
It depends on where the subreport is in the main report.

It has to be before the data you want suppressed in the main report.

For example
RH
Details A - Main report data
Details B - subreport
RF

That can't be done

RH
Details A - Subreport
Details B - Main report data
RF

In that scenario you can suppress the main report

mlmcc
0
 

Author Comment

by:shockacon
ID: 36531791
yes this is true.
my question is how do i suppress them ?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36532327
Which scenario do you have?

mlmcc
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:shockacon
ID: 36532768
RH
Details A - Subreport
Details B - Main report data
RF
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36532985
IN the subreport there should be an option to NO PRINTING IF NO RECORDS
In the subreport add a formula
WhilePrintingRecords;
Shared NumberVar RecCount;
RecCount := Count({SomeField});
""

You can suppress details B with
WhilePrintingRecords;
Shared NumberVar RecCount;
RecCount = 0

In details A add a formula
WhilePrintingRecords;
Shared NumberVar RecCount;
RecCount := 0;
""

mlmcc

0
 

Author Comment

by:shockacon
ID: 36533131

N the subreport there should be an option to NO PRINTING IF NO RECORDS
In the subreport add a formula
WhilePrintingRecords;
Shared NumberVar RecCount;
RecCount := Count({SomeField});
""

You can suppress details B with
WhilePrintingRecords;
Shared NumberVar RecCount;
RecCount = 0

In details A add a formula
WhilePrintingRecords;
Shared NumberVar RecCount;
RecCount := 0;
""
0
 

Author Comment

by:shockacon
ID: 36533141
Ooops sorry

"N the subreport there should be an option to NO PRINTING IF NO RECORDS"  what does this refer too? is it necessary to make the others work in the formulas?

where do the three formulas go? details header section? please be more specific thx
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36533368
When editting the subreport click FILE -->REPORT OPTIONS
The should be an option for SUPPRESS PRINTING IF NO RECORDS or something like that.

The  subreport formula can go anywhere in the subreport that isn't suppressed.

The other 2 are for the main report.  1 in details A the other in the suppression formula for details B

mlmcc
0
 

Author Comment

by:shockacon
ID: 36538235
this isnt working.
When editting the subreport click FILE -->REPORT OPTIONS SUPPRESS PRINTING IF NO RECORDS
it will suppress the whole report  EXCEPT the first  record in the main report.


that and the suppression formulas arent working either. I cant supress the subreport, its showing blank sections in the main report.  

truly frustrating


Ive tried every combination I could think of  every way imaginable.


I need to start over.

this is all I want to do..
Suppress both sub and main report records where there is a record BOTH reports OK
keep the records where there is a record in the main report and not in the subreport, OK
suppressing the blank spaces in the subreport section where there isnt a record.NOT OK

Currently I have details B Showing only the records that dont have a match in the sub.. whiich good. its just that darn subreport in A showing the "blank sections"

this is all that is in my sub
a formula in the header called subformula
WhilePrintingRecords;
Shared numberVar myTotal := {#distinct customers}
details suppression area in section expert  {#distinct customers} = 1 with suppress checked

main report
details b supression section  is {@mainformula} = 1 suprerss not checked.
details a nothing

this config works best, but its a workaround with the spaces.   jany ideas too get these spaces to go away/supress.. so close!! :)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36538417
Can you upload the report?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 36538731
If the subreport is not producing any output when there are no records (you just get a blank space on the report), then go to the subreport format and check the "Suppress blank subreport" option on the Subreport tab, and check the "Suppress blank section" option for detail section A in the main report.

 James
0
 

Author Comment

by:shockacon
ID: 36818075
ok so I think I was going about this the wrong way..

I need to do a conditional suppression( and forget the subreport) I need to supress all records per customer if they have visited in the last year.

example

cust 1
visit date 1
1/1/2009
visit date 2
8/1/2011
SUPPRESS

cust 2
visit date 1
03/2009
visit date 2
01/2010
KEEP (SHOW)

cust 3
visit date 1
08/2010
visit date 2
04/2011
SUPPRESS


basically if the've visited in the last 365 days I want to suppress ALL VISITS for that customer
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 334 total points
ID: 36818355
Do you want to do this in the report (you need to suppressed records for something) or can you do it in the selection criteria

Command
SELECT YourFieldList
FROM YourDatabase
Where NOT RecordId IN (SELECT RecordId FROM YourTable Where VisitDate > CurrentDate - 365)

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 36890642
If you can use a manual query, like a CR Command, or a view or stored procedure in the db, then doing it in that query, as mlmcc suggested, would probably be the most efficient.

 If you want to do it in the report, group selection seems like the way to go.  That will suppress any groups that don't meet your criteria.  You'd have the report grouped on the customer and then go to Report > "Selection Formulas" > Group and enter something like this:

Maximum ({visit date field}, {customer field}) > CurrentDate - 365

 That says to select (don't suppress) any customer where the latest visit date (Maximum) is within the last 365 days.  You might need to tweak that a bit, like use >= instead of >.  It's 9/30 and CurrentDate - 365 gives me 09/30/2010.  If the last visit was on 09/30/2010, should that customer be included or not?  Use > to exclude them, >= to include them.

 If your visit date field is a datetime, I'd use the Date function to remove the time, to make sure that it doesn't affect the comparison (unless you want it to):

Date (Maximum ({visit date field}, {customer field})) > CurrentDate - 365


 Keep in mind that group selection just suppresses the other groups.  The data is still read by the report, and if you use any CR summaries, like to get grand totals, they will include the suppressed groups.  If you need totals that only include the selected groups, you'll need to use formulas and variables for that.

 James
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 334 total points
ID: 36892845
Since you want to suppress the records if the most recent visit was over 1 year ago use

Maximum ({visit date field}, {customer field}) < CurrentDate - 365

mlmcc

0
 
LVL 34

Expert Comment

by:James0628
ID: 36896020
No, mlmcc.  He wants to suppress if the last visit was _within_ the past year.  From the end of his last post:

 >  if the've visited in the last 365 days I want to suppress ALL VISITS for that customer


 I guess he's trying to produce something like a list of "inactive" customers.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36896548
Meant SELECT not suppress.

Since the GROUP selection formula selects records the < is correct
He wants to select records where the max visit date is BEFORE (<) Today - 1 year

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 36900668
Argh.  You're right.  I was thinking of it like # of days, as in "more than 365 days ago".

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37068376
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

Title # Comments Views Activity
Formatting a Crystal Reports Report 9 94
Crystal Reports Groups need Hand Cursor on Hover 4 79
Exclude some records from totals 10 58
Crystal Reports No Record Returned 2 60
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

791 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