Crystal Report use sub report to filter main report

I am working on supplier report.
I have main report that is grouped by suppliers then by Customers.
I am showing parallel: This year month – last year month sales, this year Quarter last year same quarter sale and This YTD sales and Last YTD sales. Report has date parameter for picking month for report to run. Report is  working ok.
User asked mi to put simple string parameter “Yes , “No” in report that will give him option to select only customers that had invoices that are   older than 2 Years-- (that he was customer not just recently but two years and more before).
I was thinking how to do that.
One way is to create sub report that will show only customers that have  Invoices older that two years and then use this sub report customers as filter for main report.
I need some help with details how to arrange this.
TarasAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

mlmccCommented:
Since the subreport runs after the main report, it can't be used to filter the main report data.

Try this

Add your Yes/No parameter
Name - HasOldOrders

Filter with this
(
Your Current Filter
)
OR
(
If {?HasOldOrders} = 'Yes' then
     {OrderDate} <= CurrentDate - 730
Else
    False
)

Open in new window


In the report add a formula
Name - HasOldOrder
If {OrderDate} <= CurrentDate - 730 then
    1
Else
    0

Use group selection (really suppression)
In the report
Click REPORT --> SELECTION FORMULAS --> GROUP
{?HasOldOrders} = 'No'  OR Sum({&HasOldOrder},{CustoemrName}) > 0

mlmcc
TarasAuthor Commented:
Hi mlmcc.
I will try this suggestion to day but just to clarify some issues. Will your suggestions provide me this:
As I have date parameter that user use for selecting month for report run e.g. he select August 2015.
And now with this new parameter  ?HasOldOrders selected to “Yes”  what will happened?

I need to show only customers that have orders before two years but I need to show all theirs orders up to August 2015 not just orders older than August 2013 = {OrderDate}<= CurrentDate-730
mlmccCommented:
It should get all the current data and get the data for orders before 2 years ago.

mlmcc
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

TarasAuthor Commented:
When I select parameter HasOldOrders  “Yes” it is doing proper selection not showing customer that do not have orders older that two years.
But.
As I am Summarizing all customers group in Supplier group using  sum function when I suppress particular customer group it is still showing the same sum amount in Supplier group???
How to get proper sum in supplier group.
TarasAuthor Commented:
And now I have problem with  column calculating  %of Total too.This column was base on Sum function which can not be used now.
mlmccCommented:
Do you only want data from the 2 year period included?

If so try this

If {?HasOldOrders} = 'No' or {DateFIeld} >= Minimum(LastYTD) then
    {AmountFIeld}
Else
    0

mlmcc
TarasAuthor Commented:
No I do not want only data from last two years.
I need customers that have orders older than two year or all customers(that where is parameter Yes/No). Your formula and parameter are showing right customers but now as I am suppressing particular group(customers when I select "yes") my summary and % of total column is still showing all amounts and perecntage for all customers even if I select only those customers that have older orders.
mlmccCommented:
Summaries include the data from suppressed records.  Use the formula I provided to filter out the suppressed data and summarize it.

mlmcc
TarasAuthor Commented:
mlmcc.
I apologize, for your previous question : "Do you only want data from 2 year period included?"
I said no but actually it is Yes.
Yes I am pulling orders that are only in range : From now To  back two years. Now is based on run date parameter, actually it is last date of selected month.

However with this additional parameter  HasOldOrders I am again pulling only orders in last two year range but with option  to ( restrict or not restrict to)  customers that have order older than 2 years = “yes”  or to all customers = “No” parameter value.

You said in report add a formula:
Name - HasOldOrder
If {OrderDate} <= CurrentDate - 730 then
    1
Else
    0
Where to put this formula?

 I added it to detail line ??

And in filter I did as you suggested:
(
Your Current Filter
)
OR
(
If {?HasOldOrders} = 'Yes' then
     {OrderDate} <= CurrentDate - 730
Else
    False
)
And then  I applied this too.
Use group selection (really suppression)
In the report
Click REPORT --> SELECTION FORMULAS --> GROUP
{?HasOldOrders} = 'No'  OR Sum({&HasOldOrder},{CustoemrName}) > 0

So after this customers are restricted properly but I do not have proper my Sum formula and % of total formula as it still summarized suppressed customers amounts.
And % Of Total column giving me same percentage as with old summary.
What I am doing wrong.
And again you said:
Do you only want data from the 2 year period included?

If so try this

If {?HasOldOrders} = 'No' or {DateFIeld} >= Minimum(LastYTD) then
    {AmountFIeld}
Else
    0
Where to put this formula???
mlmccCommented:
The last formula doesn't have to be on the report.  You just want to use It for the summaries.
It replaces your amount field you are summarizing now.  The IF just eliminates the data that isn't needed (over 2 years old or not an old customer (if YES is entered))
Put it in the detail section
Right click it
Click INSERT --> SUMMARY
Create the summary you need
Put it in the customer group footer

mlmcc
TarasAuthor Commented:
I am running six summaries three Monthly, Quarterly and last 12 months for this and three summaries for past year.
 All formulas are driven by date parameter {?Month of Report}.

I assume I will need to adjust them relating to your input. Can you help me with this three for this year I will figure out three for last year.
Here are three formulas for this Year:

Formula for monthly amount:
NumberVar Amount
Amount:=0;
If Year ({@OrderDate}) = Year({?Month of Report}) And Month({@OrderDate}) = Month({@Month Of Report}) then
Amount:= {Order.TotalAmount} – {Order.TotalTax}
Else
Amount:=0;
Amount;


NumberVar ThisYearQtrAmount;
DateVar SMonth;
DateVar EMonth;
DatetimeVar RunDate;
DatetimeVar StartDate;
RunDate:={?Month of Report};
EMonth:=Date(Year(RunDate),Month(RunDate),Day(RunDate));
StartDate:=DateAdd(“m”,-2,RunDate);
SMonth:=Date(Year(StartDate),Month(StartDate),Day(StartDate));
If
(
{@OrderDate} in (dateserial(Year(EMonth),Month(EMonth) +1,1-1) to
Dateserial(Year(SMonth),Month(SMonth),1)
)
) then
ThisYearQtrAmount:= {Order.TotalAmount}- {Order.Totaltaxes}
Else
ThisYearQtrAmount:=0;


**********************************************

NumberVar ThisYearAmount;
If
({@OrderDate} in (dateserial(Year({?Month of Report})-1,Month({?Month of Report}) + 1,1) to
Dateserial(year({?Month of Report}),Month({?Month of Report}) +1,1-1)
) then
ThisYearAmount:= {Order.TotalAmount} – {Order.TotalTaxes}
Else
ThisYearAmount:=0;
mlmccCommented:
Yes, you will also need to check if they have an order from 2 years ago.

Try this
Add this formula
Name - IsOldCustomer
If ({@OrderDate} <= CurrentDate - 730) then
    1
Else
    0

Add this test to the summary formulas
({?HasOldOder} = 'No' OR Sum({@IsOldCustomer},{CustomerName}) > 0)

mlmcc

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
TarasAuthor Commented:
Thanks a lot I will tray it.
TarasAuthor Commented:
This part can not be added to summary formula:
Add this test to the summary formulas
 ({?HasOldOder} = 'No' OR Sum({@IsOldCustomer},{CustomerName}) > 0)
mlmccCommented:
What do you mean it can't be added?

mlmcc
TarasAuthor Commented:
Hi mlmcc

This is one of formulas that I am using as start for summary formulas: This is  to get This Year Month amount beside others for (for  Quarter and Year- 12month )amount.
@ThisYearMonth
NumberVar Amount
Amount:=0;
If Year ({@OrderDate}) = Year({?Month of Report}) And Month({@OrderDate}) = Month({@Month Of Report}) then
Amount:= {Order.TotalAmount} – {Order.TotalTax}
Else
Amount:=0;
Amount;
I am using this in crystal Sum function to summarize by group Customer then By Supplier.
You suggested:
Add this test to the summary formulas
({?HasOldOder} = 'No' OR Sum({@IsOldCustomer},{CustomerName}) > 0)
When I add this part to that formula I got this:
NumberVar Amount
Amount:=0;
If Year ({@OrderDate}) = Year({?Month of Report}) And Month({@OrderDate}) = Month({@Month Of Report})
And
(
({?HasOldOder} = 'No' OR Sum({@IsOldCustomer},{CustomerName}) > 0)

)
then
Amount:= {Order.TotalAmount} – {Order.TotalTax}
Else
Amount:=0;
Amount;


Problem start now when I am using this for groups summary.
 I can not use this formula for crystal  Sum function(I am using Crystal Sum function not manually made) for calculating summary for Customer and summary for Supplier so the same for  % Of Total for customer. This part (Sum({@IsOldCustomer},{CustomerName}) > 0)) is c problem.
When I press summary function this formula  (@ThisYearMonth) is not visible any more when I remove this part
 Sum({@IsOldCustomer},{CustomerName}) > 0) I can see formula and I can use it to do group summary but that is not ok with out this part.
So that is problem.
mlmccCommented:
Try taking out the part I recommended adding.  I think it will be handled in the suppression

And
 (
 ({?HasOldOder} = 'No' OR Sum({@IsOldCustomer},{CustomerName}) > 0)

 )

mlmcc
TarasAuthor Commented:
mlmcc , we are going in circle :


Summaries include the data from suppressed records.  Use the formula I provided to filter out the suppressed data and summarize it.

 mlmcc
TarasAuthor Commented:
I am completely confused.
mlmccCommented:
Sorry.  I just read your message.  You need that test to eliminate them from the summary but the test removes them from the available objects for summary since the test includes a summary.

I think you will have to do this with manual summaries.

Can you use a command as the data source for the report so the SQL takes care of choosing just the records you want?

mlmcc
TarasAuthor Commented:
Ok I can use manual summaries.
But how I will show % of total for customer group in Supplier Group.
 Before when I used Crystal function summary it was easy to show % of Total for Customer Group relating to Total for Supplier. If I use manual summaries how to calculate % of Customer total in Total of  Supplier. Problem is in that at time when I calculate particular Customer Total I do not have Supplier total.
mlmccCommented:
With manual summaries you won't be able to get the total until the end.  You could use a subreport to calculate the total.

The only other way I can see to do this is to use a command or a query/stored procedure in the database.

mlmcc
TarasAuthor Commented:
How do you think with command?
mlmccCommented:
It would be the same SQL you have with the added filter of
Copy the SQL from
DATABAE --> SHOW SQL

Create a COMMAND
Paste the copied SQL
Add a parameter to the command

AND
( {?HasOldOrder} = 'No' OR {CustomerID} IN (SELECT CustomerId From YourTable WHERE OrderDate <= CurrentDate - 730))

mlmcc
TarasAuthor Commented:
I can not use command.
Is it possible to create array in subreport and put all customers from subreport Customer group that have orders < 2y. Then use this list of those customers in main report.
How to do that
mlmccCommented:
You could but since the subreport will run after you have selected the records for the main report, you will have the same issues with the summaries.

Is there a subreport in the report now?

mlmcc
TarasAuthor Commented:
You are right I tried, got list of customers in share variable and then tried to use it as filter for records in main report I got message that that part can not be evaluated at this time of filtering.

No there is not sub report now in report. I am trying now to do my summaries in main report  manually and that is ok. I have now issues with  % of total I am trying to calc totals in subreport and use it from sub report in main report.It is lot of work as subreport has to be almost identical as main report to get those suppliers totals - which I can use I main report as totals in customers group to calculate % customers total in supplier total.
Does this sub report for calculating totals has to be in same group section where is Supplier group in main report or it could be in main report header ?
mlmccCommented:
If you just want to calculate the overall total it could be in the report header.

I was thinking you could use the main report to build the list of customers then put the current report as a subreport in the report footer.  You could then filter the data from the variable.

mlmcc
TarasAuthor Commented:
No overall total, I need totals for each supplier. Then I use it in main report in customer group for calculating percentage of that customer in particular supplier.
So I assume it has to go in supplier group in main report and make link by supplier ID.

I will try this idea:
"I was thinking you could use the main report to build the list of customers then put the current report as a subreport in the report footer.  You could then filter the data from the variable."


So my current report will become subreport in footer. Actually  it will be only part that will be seen.
Just now sure what I will need to suppress in main report and will it work if I suppress part of it?
mlmccCommented:
You can use 2 formulas

Main report report header
WhilePrintingRecords;
Global StringVar NamesList;
NamesList := '';

Open in new window


In the detail section
WhilePrintingRecords;
Global StringVar NamesList;
NamesList := NamesList & "|" & {CustomerName};
''

Open in new window


Add another formula to link on
WhilePrintingRecords;
Global StringVar NamesList;
NamesList 

Open in new window


Put the subreport in the report footer
Link it on the third formula

Change the selection formula in the subreport to be
{CustomerName} IN {?pm_NameList}

mlmcc
TarasAuthor Commented:
I am not getting how it will work.
Can you explain me.
And when you said:
In the detail section
WhilePrintingRecords;
Global StringVar NamesList;
NamesList := NamesList & "|" & {CustomerName};
''
In what report this go main or sub report??
And when you said:
Add another formula to link on
WhilePrintingRecords;
Global StringVar NamesList;
NamesList
In what report this formula go main or sub report??
mlmccCommented:
That will go in the main report.  The idea is to build the list of customers in the main report then use the list to filter the subreport.

The linking formula will be in the main report and used to link the main report to the subreport.

mlmcc
TarasAuthor Commented:
To clarify, that main report is new one that has to be created and our current one will be sub report tajt goes in footer of that new one.

And for this formula :
I assume this can not go in detail section but in group section of main report.
we are grouping by customer then by supplier
and we can not put this I detail section it will create duplicate customers entries.

In the detail section
 WhilePrintingRecords;
 Global StringVar NamesList;
 NamesList := NamesList & "|" & {CustomerName};
mlmccCommented:
Yes, it would go in the Customer group section.  You only want to add the customer once.

mlmcc
TarasAuthor Commented:
You sadi linking formula will be in main report!!
 Where in main report? Should I put it in one of report section?
TarasAuthor Commented:
NamesList has to be numbervar????
mlmccCommented:
What do you mean?

mlmcc
TarasAuthor Commented:
It is working now ok
Thanks a lot for your time and patience with me.
mlmccCommented:
Glad we finally got it.

mlmcc
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
Crystal Reports

From novice to tech pro — start learning today.