Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

why is my result getting multiplied by 17??

Status
Not open for further replies.
Insulations504

Insulations504

IS-IT--Management
Feb 16, 2023
5
US
thread767-1267707

I have a report i am writing, pulling info from a SQL table.

The Sum of each cost type is multiplying by 17. For example the total should result in $1,260,415 and I'm getting $21,427,055. 17 is the number of subcontracts for this particular job.

Below is the formula for all of the cost types.

Sum ({@CT1}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT2}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT3}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT4}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT5}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT6}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT7}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT8}, {brptJCCostRev;1.Contract})=0 and
Sum ({@CT9}, {brptJCCostRev;1.Contract})=0 and
Sum ({brptJCCostRev;1.ActualCost}, {brptJCCostRev;1.Contract})=0 and
Sum ({brptJCCostRev;1.BilledAmt}, {brptJCCostRev;1.Contract})=0 and
{?ShowZeros}='N'

In another thread, the user figured out that there was no filter in place to limit the sub contracts it was pulling amounts from, so it was pulling the amounts for everything - 10 times (b/c there were 10 sub contracts). How do I set this filter to only take the one total of the cost type and not multiply it by the number of subcontracts?
 
Sort by date Sort by votes
It's hard to be specific with a solution based on the information provided, but the inclusion of the subcontracts has resulted in the detail section to be repeated for each of them which means the sum is 17 times higher.

You could use either a Running Total or a Variable so as to accumulate the total only once for each contract.

If you require more specific instructions you will need to provide more details on the report structure (grouping, location of the formula provided, details of 9 @CT× formulas etc.)

Hope this helps.

Cheers, Pete
 
Upvote 0 Downvote
See below for the Section Expert. The Formulas I posted above are coming from "Group Footer #3: brptJCCostRev;1.Contract - A" Where would I got to put in a Running Total of a Variable?

Report Header
Page Header
Page Header a
Page Header b
Group Header #1
Group Header #2
Group Header #3
Details
Group Footer #3
Group Footer #2
Group Footer #1
Page Footer
​
Page Footer a
​
Page Footer b
Report Footer
 
Upvote 0 Downvote
Sorry, my earlier post should have said Running Total OR a Variable.

I am still a bit confused by the "[highlight #FCE94F]= 0[/highlight]" against each of the formulas above and the reference to "{?ShowZeros}='N'".

Is what you have shown above, the contents of another formula, a Group Selection formula or simply a typo?

Assuming the 11 formulas above are all sitting in GF3, Grp 3 is {brptJCCostRev;1.Contract}), there are 17 rows of data (details section), all showing the same values for {@CT1}, {@CT2} ... etc, you could simply drag each of those {@CTx} formulas to GF3.

Does that help?

If you then need to add each of those amounts for totals in Grp2, Grp1 etc, that will require some additional work which I am happy to help with but I need to be sure I understand the situation before investing additional time to assist.

If you are happy to post a copy of the report with saved data, it might be easier than trying to explain it, but I understand you may not be prepared to do that.

Cheers,
Pete.

 
Upvote 0 Downvote
Crystal_t172dk.jpg


So in the Detail Section their are the cost types: @CT1, @CT2, @CT3, etc.
Each of these have the following formula:

if {brvJCContStat.CostType} = 1 then {brvJCContStat.ActualCost} else 0

So how do I edit this formula so that each Cost Type is not multiplying the correct amount by 17 (the number of subcontracts for this job)
 
Upvote 0 Downvote
Actually, what I asked for was to post the report with saved data, not a screenshot.

A screenshot showing the section names might help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tlbrown88
  • Locked
  • Question
How does one get the results of a Calculation (SharedVar) on the same line as the Sub Report?
Replies
4
Views
623
tlbrown88
tlbrown88
chish_nc
  • Locked
  • Question
Need help getting oldest anniversary date to show
Replies
3
Views
413
pmax9999
pmax9999
Hellak
  • Locked
  • Question
Simple report - filtering by comparing two fields
Replies
1
Views
318
pmax9999
pmax9999
AleXSR700
  • Locked
  • Question
How can I make variable that is re-defined per section and readable in same-section formula? 2
2
Replies
20
Views
1K
AleXSR700
AleXSR700
PJ222
  • Locked
  • Question
is date within any date range in a dynamic array of date ranges
Replies
1
Views
334
PJ222
PJ222

Part and Inventory Search

Sponsor

Back
Top