Crystal Report Rolling month- three months -YTD with previous year comparison

In Crystal Report 2008 .
I am working on one my old report that calculating rolling amounts from customer that have orders from different suppliers.
Columns that I need to compare are: Month, Three Months and Year (6 columns)
-      Full selected month this year to the same month previous year;
-       Three months back from selected month this year to the same three months in previous year
-      Current YTD up to full selected month to previous year to date.
Condition is that only Customers should be included that have orders from suppliers which are older than two years from selected date.
One customer can have several suppliers and one supplier can have several customers.
It means those customers have to have order older that two years from parameter date but they can have beside those  at the same time orders that are not older that two years too.
I need grouping on Suppliers and then their Customers and calculating those values for each customer with total for supplier.

Customer which do not have any orders (amounts) in previous year column should not be included in calculation and should not be shown in report.
I have problem how to restrict and not showing customers which do not have orders in previous year(even they could have in this year).

I created one Main report and two subreports (rpt_Main; rpt_SubMainA; rpt_SubMainB).
In rpt_SubMainA I presented every Supplier with their customer and do those calculation in columns.
In rpt_SumMainB I show only final summaries rows from sub reports rpt_SubMainA.

In rpt_Main I have shared variable “OldCusomers” where I collected all  customers that have orders older than two years from parameter(par_Run_Date) which I use as date for calculation.
Then I passed that shared variable value to be used as criteria in  rpt_SubMainA  plus added condition that orders in this sub report have to be in range previous YTD and Current YTD.

In subreport rep_SubMainA  I have grouping on Suppliers then Customers.


 Everything looks ok up to user asked me not to show those Customers that do not have any orders during previous year (even they could have orders during this year but if they do not have in previous year they should not be include and calculated.
Not sure how to solve this any idea?
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.

Vicki CorobSr BIDCommented:
mlmcc has a possible solution:
https://www.experts-exchange.com/questions/28983961/Crystal-11-Suppress-main-report-section-if-subreport-is-blank.html
With a couple of tweaks this could work.

Another possible solution is to build an SQL query using the With statement.  Then you wouldn't need the sub reports at all.  And can do an equal join to eliminate customers with no data in the 2 year record set.

I believe there is both a SQL Server with statement and an Oracle.  I'm familiar with the Oracle statement.  If it's designed well data can be returned in under a couple minutes.
0
James0628Commented:
This probably isn't your first choice, but the best solution would probably be creating a custom db query that gathered the information for the report.  The query could select the customers that had orders in the desired time period (2 years ago, or 2 years ago and the years since then), and calculate the month, 3 month, and YTD totals for this year and last year.  Then the report would only have to output that data.

 I'm not sure how to change your current report.  In a simpler report, you could use group selection, which actually suppresses/hides the groups that you don't want to see.  For example, if you simply had an order total field and an order date field, you could create a formula similar to the following that would only include the orders that were over 2 years before the parameter date:

if {Order Date field} < DateAdd ("yyyy", -2, {?par_Run_Date}) then
  {Order Total field}

 Then the group selection formula would be:

Sum ({@formula created above}, {Customer group field}) > 0

 That would select the groups (customers) that had orders that were more than 2 years before the parameter date.  Any customers that did not have orders prior to 2 years ago would not be shown.  FYI, the report would still be reading customers that did not have orders that old.  It just wouldn't be showing them.  So any report totals using CR summary functions would include those other customers.  If you needed totals, you'd have to use formulas to calculate those yourself.

 That's the basic idea, but I'm not sure exactly what you're doing in the main and subreports, so I don't know if you can use something like that.  I suspect not, but I'm really not sure.

 If you can't do that, there may be something of a "brute force" alternative.  You could create a new subreport and put it in the report header of the main report.  The new subreport would just look for customers that had orders over 2 years old (ignoring anything newer), and save those customer numbers in a shared variable array.  Then the main and subreports could, theoretically, check that array and suppress sections if a customer was not in there.  I _think_ that would work.  I called it "brute force" because it requires a new subreport (which means reading the orders yet again), and adding suppression formulas to all of the appropriate sections in the main and original subreports.

 One basic limitation to that approach is that arrays are limited to 1000 elements in CR, so if a single report could include more than 1000 customers with orders that are over 2 years old, that would be a problem.  If necessary, you could handle that by using multiple arrays.

 James
0
TarasAuthor Commented:
James.
Regarding arrays limitation of 1000 elements in CR I have 50 Customers and 40 suppliers, so that limitation will not be problem.
In main report I have shared variable that collects those customers from pull of 50 which have orders with suppliers than 2 years from selected parameter date.
When I check detail line of main report I am getting only Customers and that have orders older than 2 Years from parameter date.
 Then I am passing this shared variable value to rpt_SubMainA  and parameter date, in that sub report  I am grouping by customer then by supplier and  I have new condition that  I select only those customers that have orders from suppliers in range of past YTD to Current YTD.

However I do not need customers - suppliers which do not have orders  In Past YTD range to show not to calculate even if that particular customer - supplier  have order in Current YTD.
All issue is around haw to restrict this particular Customer - supplier combination not to show or calculate in past YTD.
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

James0628Commented:
Just to see if I understand the situation:

 Are you using the record selection in the main report to find the customers with orders over 2 years old, and then running the subreport(s) for each of those customers?  But then you only want to actually see one of those customers if they also had orders more recently?

 If so, then maybe you can use group selection, although I'm not sure what you mean by "in range of past YTD to Current YTD" (I wouldn't normally think of two YTD values as a range).  Do you mean this year, or last year, or both?

 IAC, you could potentially use formulas like the ones that I posted before, to see if a customer had any orders in the desired secondary date range (eg. last year), and then select those customers (groups), hiding/suppressing the rest.

 James
0
mlmccCommented:
I have been thinking about this but haven't had time to suggest a solution.

The easiest way to do this is as James suggested above, create a Crystal Command that produces the desired data using one or more subqueries.

Another way to do this would be to pull use the main report as a shell.

The data for the main report would be the customers who had orders older than 2 years.

You could add a subreport that uses that list and checks who had orders last year.

Is this based on the current date or a user specified date?

So if this is for 30 Sep 2018 you want to see
  Orders for Sep 2018, Orders for Jul-Sep 2018 and Orders for Jan-Sep 2018 from this year
  Orders for Sep 2017, Orders for Jul-Sep 2017 and Orders for Jan-Sep 2017 from last year

  with the added conditions that they have orders earlier than Sep 2016 and order in the Oct 2016 - Sep 2017 timeframes.  Is that correct?

mlmcc


The second subreport simply pulls the data based on the customers remaining in the list.
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
TarasAuthor Commented:
James.

Hi James I have 6 columns, am doing comparison between:
Three columns values for this year and columns values for past Year.

Column 1. This year Month – parameter date full month.
Column 3.This Year rolling three months back from current date- current month.
Column 5. And this year YTD - included parameter month.
Compering those amounts with:
Column 2. Last year month (the same as current month)
Column 4.  Last year rolling three months
Column 6. Last year YTD.
I assume it is your two YTD values range.
0
TarasAuthor Commented:
Current Month      Rolling Current 3 Months       Rolling Current YTD       Past Year Month       Past Year Rolling 3 months       Past Year YTD
Customer A:                                          
      Supplier1      $10.00                    $65.00                         $380.00             $25.00             $120.00                   $490.00
      Supplier2      $10.00             $45                                 $235.00             $0.00             $0.00                   $0.00
      Supplier3      $10.00             $65.00                         $120.00             $45.00             $270.00                   $850.00
Customer A:      Total:$30.00             $175.00                          $735.00             $70.00             $390.00                   $1,340.00
                                          
Customer B:                                          
      Supplier 1      $120.00               $350.00                   $805.00             $90.00             $330.00                   $1,250.00
      Supplier 2      $40.00             $350.00                   $1,100.00       $45.00             $450.00                   $1,350.00
      Supplier 5      $15.00             $45.00                   $205.00             $0.00             $0.00                         $0.00
Customer B:      Total:$175.00       $745.00                   $2,110.00       $135.00               $780.00                      $2,600.00

Final Total:....
Problem here is Customer A - Supplier 2 and Customer B Supplier 5 they should not be include(not seen)  and not summarized in Current Year data.
0
TarasAuthor Commented:
I mixed order of columns a bit  but I hope that this will give an  idea what I want to achieve.
0
TarasAuthor Commented:
mlmcc, yes it is correct.
0
TarasAuthor Commented:
mlmcc, you said:
"You could add a subreport that uses that list and checks who had orders last year"


If I put selection criteria in report records :  that customers are included in list "Customer with order older than 2 years" and add additional criteria that I want to select only order that are in past ytd range how I will get orders from the same customers that are in this current ytd range to compare them in this sub report?
0
James0628Commented:
Then I am passing this shared variable value to rpt_SubMainA  and parameter date, in that sub report  I am grouping by customer then by supplier and  I have new condition that  I select only those customers that have orders from suppliers in range of past YTD to Current YTD.
What do you mean by "and  I have new condition that  I select only those customers that have orders from suppliers in range of past YTD to Current YTD"?

 I got the impression that the new condition was a second date criterion.  Something along the lines of: You want to see the customers with orders before 2 years ago, but (this is the new condition) only if they also had orders last year.  But I'm not sure what to make of "in range of past YTD to Current YTD".

 You've mentioned comparing some columns (eg. this month this year and last year).  I assumed that you were just showing those figures on the report, so that people could look at the columns and compare them.  But after one of your latest posts, I'm wondering if maybe you're actually trying to use those columns to somehow control which customers are on the report (eg. only show a customer if this year > last year (ie. the YTD orders increased)).

 James
0
TarasAuthor Commented:
James;
This condition
What do you mean by "and  I have new condition that  I select only those customers that have orders from suppliers in range of past YTD to Current YTD"?

Is present as you need to do comparison between this and last year records.
How you will do comparison if you do not restrict - provide pull of orders that are seating in this time range?

Next condition is new one that client asked me: He said I do not want to see in Customers group those suppliers that do not have order in previous year that have amounts of $0.00.
I can suppress easily them but how to do with amounts that are still calculated for current year even those suppliers group are suppressed.
0
mlmccCommented:
So not only does the customer have to have orders but the customer/supplier pair has to have orders.

LEt me try to build a sample report

mlmcc
0
TarasAuthor Commented:
James I do not to put this conditions on report level I can put them in formulas for calculating particular amounts .
0
TarasAuthor Commented:
yes mlmcc,  that actually a point in all this new requirements .
0
James0628Commented:
Is present as you need to do comparison between this and last year records.
How you will do comparison if you do not restrict - provide pull of orders that are seating in this time range?
I think maybe the use of "range" (singular) was confusing me.  You seemed to be saying that you wanted a single range from "past YTD" to "Current YTD", and I didn't understand what that meant.  But now I think that you're talking about 2 ranges, one for this year and one for last year (eg. 01/01/2018 to 09/26/2018, and 01/01/2017 to 09/26/2017).

 It sounds like mlmcc has a better handle on what you want than I do, so I'll wait and see what he can come up with.

 James
0
TarasAuthor Commented:
Thank you mlmcc and James, I created Main report as mlmcc suggested (just container) and two sub reports based on  command, I did all filtering in command based on passed parameters from main report. I am not done as  I have issue with passing multi valued number parameter to command in CR 2008 I will  post it as next question.
Thank you a lot.
0
mlmccCommented:
As far as I know you can't pass a multivalue parameter to a command

mlmcc
0
James0628Commented:
mlmcc,

 FWIW (and it may not be much :-) ...

 You may very well be right.  I thought I'd seen someone post here on ExEx that it worked (at least in their specific case), with CR replacing the parameter in the Command with a comma-separated list of values.  I also have the feeling that that was something that was added in later versions of CR.  But I could be wrong about all of that.

 James
0
mlmccCommented:
I'll check CR2008 in the morning.  All I have installed here is CR XI R2
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
Crystal Reports

From novice to tech pro — start learning today.