Crystal Reports - Running total evaluate and forumla conditions

I have a report where one of the fields "cube" in the details sections is the same for each line showing the cube used for that total route listed by stop number. Next, I have the correct value listed in the group section using standard running total but need to now break down further to analyis cube by locationid.  However, i am unable to do this using standard running total as i need it to evaluate based on the location ID but but because the same value is loaded on the details section for each line i cannot get to total correctly in the group footer for DATE.  

Attached is a small sample of what I am deaing with showing two route and current GROUP 1 in yellow and Requested data in RED for GROUP 2.

I am sure there is an easy way to do this but need help from experts.
Who is Participating?

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

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.

You said the route is "GROUP 1" and the date is "GROUP 2"?  Is that true, or is it really the other way around, so the report is grouped by date, and then route?  Looking at your sample data, I'm assuming that it's date, and then group.

 I think a cross-tab might work.

 Insert > Cross-tab

 Use Location for the columns

 Use the date group field for the rows.  You may want to check the Group Options in the Cross-tab Expert and make sure they're the same as the ones you used for the report group.

 Use your "Cube Utilized" field for the summarized field, and use the "Change Summary" option to make it a maximum.

 Go to the Customize Style tab and check the "Suppress Row Grand Totals" and "Suppress Column Grand Totals" boxes.

 Put the cross-tab in the date group footer.

kelsanitAuthor Commented:
You are correct it is Date then route.  I cannot do the cross tab as  I have a lot of other data on this report. Is there not another way using variable or printing record fucnction?
Other data on the report won't interfere with the cross tab.

You can do it with formulas and variables.

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.

kelsanitAuthor Commented:
attached is a copy of the report with saved data. Can you share with me how to do this in formulas or variable format?
Does this show what you want?
kelsanitAuthor Commented:
No sir. In the Group Footer 1 have summary for all data and in that footer break out the summary by location.

for example:
knoxville Cube  499   Chattanooga cube 501
The cross-tabs that mlmcc added are basically what I had in mind, except that he used the location (route) for the rows and the date for the columns.  Based on your example, I think you want to use location for the columns and date for the rows.  If so, you can right-click on a cross-tab, select Cross-tab Expert, and switch the row and column fields.

 And if you're going to use the cross-tab in the group footer, you may also want to suppress the column and row totals in that one.

 I hadn't seen your last post when I wrote this one, and I hadn't really looked at what was mlmcc was summarizing in the cross-tabs.  If it's not the right field, go into the Cross-tab Expert and change the summarized field to RS_ROUTE.STARTING_QUANTITY_SIZE1 (or whatever field you want to see), and make sure the summary is still a maximum.

kelsanitAuthor Commented:
Yes, i went in and changed the cross tab but still not want I am looking for.

I have attached a new sample file that cleans up the data to try and better expalain what I am trying to get (see items in red)
Since there can be multiple routes per "location" i need those summed up which is why i cannot use "MAX" functiona and why I was tyring to do a running total that evaluated by route & formula that = specific location which was limitation in running total function.
Yeah, if you're trying to sub-total a repeating value, that may not work.

 FWIW, where do the 501 and 1600 come from in your red line?  You seem to be getting those 2 figures from 2 different columns -- 501 from "cube utilized" and 800x2 from "Cube  Capacity".

kelsanitAuthor Commented:
Chattanooga should be 501   (cube utilized for chattanooga locations)
Knoxville should be 998   (Sum of both knoxville location route cub capacity)

goal is to sum  the items in yellow
(Sum of both knoxville location route cub capacity)
Once again, that should be "cube utilized", not "capacity", right?  Just trying to make sure that we're on the same page here.

 And "cube utilized" in your spreadsheet is RS_ROUTE.STARTING_QUANTITY_SIZE1 in the report, correct?

 Assuming that the answer is "yes" to both of those questions, here's my understanding of what you're trying to do:

 For a given date, you have records for different "locations" (Your @Location formula?), and for different routes within each "location".  You have a value (STARTING_QUANTITY_SIZE1 ?) for each route, which is repeated in each record.  You want a total of that value for each "location", for each day, but the value needs to be added only once for each route (instead of being added for each record).

 If all of that sounds right, how about this?

 Add a group on your "location" (@Location ?), and put it in between the date and route groups, so that the report is grouped by date, location and then route.  You can suppress the headers and footers for the "location" group.

 Create a running total on the field you want to total (STARTING_QUANTITY_SIZE1 ?).  Set it to evaluate when the route group changes, and reset when the location group changes.  That should give you a total for each location, with the field only included once per route.

 Change the cross-tab to use your "location" (@Location ?) for the columns (instead of route, or whatever it's using now), and use the new running total for the summary.  Since the summary uses a running total, they're won't be any summary options to worry about.

 That should give you a column for each location, with the total from that running total.

 I'm attaching a version of the report with those changes.

Oh.  If you're really using the #Equip_* running totals, #Equip_Charlotte may be "broken" in my version of the report.  When I was looking around at different things in the report, I looked at that running total and got a warning on the "Evaluate" formula, and CR changed it.  I suspect that you're using something that my older version of CR doesn't support.  IAC, that running total may need to be fixed.

 I don't recall running into anything else like that, but, if you're going to use the things in the report that I posted, the safest thing would be for you to recreate everything in your report (add the group and the running total, and change the cross-tab(s)), rather than use the version of the report that I posted.

kelsanitAuthor Commented:
James, you are correct and the cross tab fuction is working.  

However, I wanted to see if you know of a way to get the same results without a cross tab so i can use the value in a formula?  I am trying to pull all this data on one line so it has a clean export to Excel where it will be used for further analysis.

example of formual i want to use it in will be %Cube Utilization

it will take the cube used by location divided by the cube capacity
The problem with trying to produce those figures "manually" is that you're trying to show totals from locations that were read earlier in the report.  At the end of the report, any normal summaries are going to be for the last location, etc.

 If you have a pre-defined set of locations, you could create separate running totals for each possible location (it looks like you were trying to do something along those lines).  But if a new location is added to your data, you'd have to edit the report and add running totals, etc. for the new location.

 A more flexible approach would be to use array variables to save the totals for each location.  Basically, you'd have separate arrays for the location names and whatever totals you want to save, and at the end of each location in the report, you'd put the name and totals for that location in the next slot in the arrays.  At the end of the report, you'd use the values in the arrays to produce your output.  It's kind of complicated and, honestly, I don't really want to get into it, but I think it would work.

 A cross-tab may still be an option.  They added some capabilities to cross-tabs in the more recent versions of CR.  I think I've seen the option to add a calculation mentioned.  If your version of CR includes some of those new features, you may be able to create a running total for the capacity (just like the other running total I created), add the new running total to the cross-tab, and use the two running totals to do your calculation in the cross-tab.  I'm using an older version of CR that doesn't have those features, so I can't really tell you any more than that.

 Or, if you're going to be exporting this to Excel, another option might be to just add the second running total for the "capacity" to the cross-tab, so you get the total "used" and "capacity" for each location, and then let Excel do the % calculation.

 If you don't have too much data, another option might be a subreport.  Basically, make a copy of your current report, with a group on location, put the location name and the "used" and "capacity" (?) running totals in the group footer, and add a formula that calculates your % from those running totals.  Suppress most everything else in the subreport.  Then insert that in the report footer of your original report.  If you're using any parameters, link the parameters in the main report to the parameters in the subreport.  The down side is that you'd be reading the data twice, once in the main report and again in the subreport, so if you have a lot of data, that may not be practical.

kelsanitAuthor Commented:
i think i will go with the sub report and pull in the two values as the I was able to get the cross tab works to dispay it does not export correctly.

thanks for all your help.
Yeah, if reading the data twice isn't a problem, a subreport might be the simplest solution.

 You're welcome.


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