cancel
Showing results for 
Search instead for 
Did you mean: 

Summarizing variables field with calculations

Former Member
0 Kudos

I have three fields which I need to summarize, as a group summary, then a grand total. The first field, Total_STD_Labor_Cost, is in a sub-report, which is a calculation and stored in a "Receipts" variable field. I then multiply Receipts*STDCost=Total_STD_Labor_Cost. This results, I can pass to the main report, but can not summarize it, regardless where it is placed in the main report, report footer or any group footer. The second and third fields are in the main report and are calculated using the "Receipts" variable field and cost fields.  I can not summarize these two field either, I suspect because they include the "Receipts" variable field is why I am not getting any results. Running totals do not seem to work or apply here. So I am looking to either capture the sums of the fields by group and again in the grand total. Using the insert summary feature does not give the sum option.

Any thoughts would be truly appreciated.

eadeane7

Accepted Solutions (0)

Answers (2)

Answers (2)

abhilash_kumar
Active Contributor
0 Kudos

Hi Edmund,

You cannot use the 'insert summary' option or create a running total against a formula field that uses print time functions or shared variables.

You mentioned that you need to show a subtotal (group total) and Grand Total, correct?

Where exactly is the Subreport located on the Main report? Could you post some screenshots of the 'Design' and 'Preview' pane of the report to see where the formulae are placed?

-Abhilash

Former Member
0 Kudos

Sure. The issue is that I am able to pass the variable receipts from the sub-report to the main. The problem is using that receipts variable in calculations in the same group, in order to show a per item listing across all fields in that group row. then getting a total per calculation grouped by product line and then a grand total fro all.  See screenshots. 

If that receipts variable is moved above the group the calculation are in, the results are zero.

The subreport is in the header, if moved above that point, receipts variable value changes to zero.  Any light you can shed on this will be greatly appreciated.

Edmund

Former Member
0 Kudos

With multiple items it gets worst. So to summarize I need to get sub totals for STD Ll cost, LL labor cost and TL labor cost, then multiply each total by receipts by product line in bold,"TWNAI" as an example in screenshot 4.

Former Member
0 Kudos

This is the format I am aiming for:

The format looks simple, but the LL number in the current system are in a sub reports. So the goal is to be able to multiply the receipts amount per line item times the three unit cost to get the total. Then sum those three grouped by product line. CBS, Chemicals are the product groups.

JWiseman
Active Contributor
0 Kudos

hi Edmund,

where exactly do you want this value to be set?

a) as a single value in each individual group 2?

you need to move the subreport to its own group header 2 section, above the existing group header 2 section. i.e. insert a new group header section, move the subreport to that new section, and then move that new section up so it is group header 2 A.

then you can move your shared variable formula to group header 2 B where the other existing fields are. you can also underlay group header 2A with group header 2B.

b) as a single value across an individual group1?

otherwise, if you're trying to get one consistent value from the subreport across a group 1, then the subreport must be on the group 1 level. otherwise that shared variable is not going to be consistent. moving that subreport up to the group 1 level has its own implications though as the record selection / linking may not match the group 1 level.

-jamie

Former Member
0 Kudos

Morning,

I am trying to do this report from scratch, but getting this error:

Query Engine Error 'If tables are already linked then the join type cannot change'.


There are multiple tables that are repeated three times, in order to get the different levels of labor cost. I need to add a table in order to get that "receipts" number.  Which is just a (qty) field times a (cost) field, but (qty) must come from this new tables.  Thoughts on the error message?


Edmund

Former Member
0 Kudos

Quick question, why is it that you cannot pass variable from a sub report, without the sub-report being viewed? It the sub report is in a header and flagged to "show", then you can see the variable in the footers below. Once you flagged the subreport in the header to be hidden, the variables disappear.

Edmund

Former Member
0 Kudos

Hello,

See the attached screenshot, the values being passed from the sub-report keep repeating until the data in the sub-reports changes. I have tried the 3 part variables method and that did not work. Is it the placement in the headers and footers that I am missing? I saw you eluded to that before, which I tried before pulling in the second variable (Qty) and the (cost), so I could do the calculation on the footer of the Item, which is working, but the repeating is the issue now.

Thanks again for any assistance.

Edmund

JWiseman
Active Contributor
0 Kudos

hi Edmund,

you need to decide whether you want the variable the same throughout a group 1 or if you want the variable to change every group 2. the placement on a group 1 or group 2 header or on a group 1 or group footer will affect what values are available to the sections below.

also, depending on where you do end up placing the subreport, you need to ensure that the subreport links match the group level where you have placed the subreport.

the subreport cannot be suppressed or be in a suppressed or hidden section. if you need to hide the subreport:

1) you can edit the subreport and suppress all of the sections inside the subreport,

2) then go back to the main report & right click on the subreport and choose Format Subreport

3) go to the Subreport tab and select Suppress Blank Subreport

4) go to the Section Expert for the section which contains the subreport and select Suppress Blank Section

Former Member
0 Kudos

OK, I am getting closer. I not have my correct variables and they are resetting correctly. I just need to summarized three fields. Now these three fields are calculated with variables from the first sub report. I need to total by group footer #1 for Total STD lab cost, LL Lab cost and TL Lab cost.

The group #1 is by product line and needs to be reset based on the numbers being passed Group footer #2 (by item). I also need a grand total for the three in the report footer.  As you can see form the screen shot, I tried the three part variables, called PlinetotalSTDLabcost_Calc, _Display and _Reset, but it is not working as I hoped.

Here are the formulas for the three variables fields:

"PLineTotalSTDLabCost_calc:

Whileprintingrecords;

Shared Numbervar PlineLaborStd;

PlineLaborStd:= PlineLaborStd + ({@Total_std_Lab_cost})

The display field:

Whileprintingrecords;

Shared Numbervar PlineLaborStd;

PlineLaborStd

Te reset field:

Whileprintingrecords;

Shared Numbervar PlineLaborStd;

PlineLaborStd:=0

Here is the report in design view:

Any thoughts or help would be appreciated.

Edmund

JWiseman
Active Contributor
0 Kudos

you need to be specific as to what the expected values should be with very specific examples from your preview. and where are these three formulas actually located on your report?

you need to ensure that any subreports that pass shared variables are in their own sections. these subreport sections must be above the section that contains any formulas referencing the shared variables.

also, please note that any flow for the variables is from top to bottom...i.e. if you set a variable's value at a group footer level, it will not be available on the same group instance's header. so any variables you need throughout an instance of a group should be set at a group header section, preferably in a separate section above any formulas referencing that variable.

Former Member
0 Kudos

OK, I created the part variables, reset, calc and display.(3 sets for three  different totals. Using an example from Spiceworks.com. 

Formula structure

-the first formula for calculation should be written as such;

whileprintingrecords;

shared numbervar InsSum;

InsSum:= InsSum + [formula to be totaled]

this formula should be placed in the same section as the field to be summarized.

-the second formula for reset should be written;

whileprintingrecords;
shared numbervar InsSum;
InsSum:=0

this formula should be placed in the section proceding the section containing the field to be summarized.

-the third display formula should be written;

whileprintingrecords;
shared numbervar InsSum;
InsSum

and this formula should be placed where you were wanting the summary to appear.

So in my crystal report there are three groups.  Group footer #1 has a subreport in to pull the product line name in.

Within that group I need to total std labor cost, lowlevel laborcost and highlevel labor cost.

Group #2 is by bomitem, which is a part number

Group #3 is for linenumber which refers to a subreport that has no bearing on the totals

I am trying to achieve. 

Using the first set of fields, PlineTotalSTDLabCost_Calc, PlineTotalSTDLabCost_Display and PlineTotalSTDLabCost_Reset,, where should

These fields be place to return the results I am looking for base on what I have explained.

Formula for PlineTotalSTDLabCost_Calc:

Whileprintingrecords;

Shared Numbervar PlineLaborStd;

PlineLaborStd:= PlineLaborStd + ({@Total_std_Lab_cost}

Formula for PlineTotalSTDLabCost_Display:

Whileprintingrecords;

Shared Numbervar PlineLaborStd;

PlineLaborStd

Formula for PlineTotalSTDLabcost_Reset:

Whileprintingrecords;

Shared Numbervar PlineLaborStd;

PlineLaborStd:=0

  Base on what I have explained and the screenshots, where should these field be placed in order to get totals by product line (Group #1) and grand totals in the report footer?

JWiseman
Active Contributor
0 Kudos

hi Edmund,

can you provide a couple of screenshots. first can you colour code the individual formulas so that we can easily tell the difference in preview mode.

the first screenshot should show the design mode of your main report. the second should show the preview of the main report.

then please let us know what an example group 1 total should be.

thanks,

-jamie

JWiseman
Active Contributor
0 Kudos

as a guess though, and if this is not correct, please do attach the screenshots as per the previous request...

1) since your subreport is in group header 2 a, then the formula that increments the running total using the shared variable should be in group header 2 b.

2) since you want to reset on every group 1, then the reset formula should be in group header 1

3) the display should then be in group footer 1

if the above steps don't work, you can also feel free to send me the report by going to my profile and using my email address on the profile. you would need to ensure that the report has Save Data With Report checked in the File menu and has been previewed to the last page.

-jamie

JWiseman
Active Contributor
0 Kudos

hi Edmund,

you'll need to use manual running totals to sum these values. the reason why you can't use a Summary on these formulas as you have a shared variable from a subreport. the evaluation times won't let you use a summary on these.  if you haven't used them before, then the formulas below will help you get started using manual running totals.

1) create a new formula named mrt with this syntax:

     whileprintingrecords;

     numbervar g:= g + 1;

     numbervar r:= r + 1;

place mrt in your details section. it will create two running totals. the first one we'll reset later on the group level. the second one will never be reset so it will end up being your report level total.

2) create a new formula, mrtr, that will be a reset to place in your group header

     whileprintingrecords;

     numbervar g:= 0

mrtr will reset your group level running total.

3)  create a new formula mrtgd with this syntax to place in your group footer

     whileprintingrecords;

     numbervar g;

mrtgd will display your group level running total.

4) create a new formula mrrd to place in your report footer

     whileprintingrecords;

     numbervar r;

5) now that you have the basics of the syntax, you can do things like add your shared variable from your subreport to your formulas. e.g. change mrt to this syntax...

     whileprintingrecords;

     shared numbervar s; //change this later to be your subreport shared variable

     numbervar g:= g + s;

     numbervar r:= r + s;

now you'll see your manual running totals increment using your subreport shared variable.

i hope this helps,

jamie