Solved

conditional suppression with shared variable

Posted on 2011-09-13
20
268 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
Comment Utility
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
Comment Utility
yes this is true.
my question is how do i suppress them ?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Which scenario do you have?

mlmcc
0
 

Author Comment

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

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you upload the report?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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. …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now