Totals for a calculated field

[Amber12]

I am building a report based on another report created in an older version. I am stuck – because I have a field that is not calculating right. I have a standard cost calculation as a sum of accounts:

if (([Material Costs - Accts 4001 to 4175]+[Labour Costs - Accts 4210 to 4275]+[Variable OH Costs - Accts 4310 to 4375]+[Fixed OH Costs - Accts 5110 to 5175]+[In Transit Costs - Acct 1361]+[Transfer Pricing - Acct 4440]) is not missing) then ([Material Costs - Accts 4001 to 4175]+[Labour Costs - Accts 4210 to 4275]+[Variable OH Costs - Accts 4310 to 4375]+[Fixed OH Costs - Accts 5110 to 5175]+[Transfer Pricing - Acct 4440]+[In Transit Costs - Acct 1361]) else (0)

Based on this, in the report I have the standard cost only for the period I run the report for:

total (IF ([Accounting Date Period and Description] = ?Fiscal Period?) THEN ([Std cost calculated 2]) ELSE (0))

When I run the report I get an amount that repeats over and over again. (see first image).

However, If I add the Std cost calculated 2 field, I get the right numbers but are not totaled right. (see second image)

How can I solve this situation? I tried choosing none for aggregation and it generated some ugly errors. Any help?

Thank you

Paul Edit: you can attach a new image by clicking on modify and opening the attachments and other options section.

[hespora]

a) you'll want to edit that screenshot. you're showing clear names of either your customers or your suppliers, which is likely to be in violation of compliance rules at your employer, plus it's against board rules.

b) the first kind of calculation not working is expected. you're using a total function without specifying an aggregate level with a for clause, thus implying report level aggregation. i.e., it's totalling all the values it is receiving in the query.
(also, i don't quite see the need for the if then else inside the total - you're specifying fiscal period via parameter, so I am assuming you're detail filtering on period anyways, as I see no period in the output)

c) the second *should* work, depending on aggregate functions, which I'd set to total/total. if that for some reason does not work, unlock the report via the padlock item, and replace the total in the footers with a separate data item

Code Select Expand


total(
  [std cost calculated 2]
  for [whatever your ID field is called]
)


[Amber12]

Thank you for your reply. How will I take off the uploaded image?
I'll try your suggestions shortly.

[New_Guy]

Hi,

Check for the attached icon and you can modify your msg. Let us know if that helped.


Good luck
New guy

[Amber12]

Thank you New Guy. That helped.

[Amber12]

Hespora, how do I specify an aggregation level? Can you please elaborate on b)? I understand what is the problem but I don't know how to fix it.

[New_Guy]

Hi,

For example
For Total([Revenue]) you will get Total revenue for Products.
For Total([Revenue] for [Product Line]) you will get Total revenue for each Product Line.
For Total([Revenue] for [Product Type]) you will get Total revenue for each Product Type.

You have to mention on what you want to total on.

Good luck
New guy

[Amber12]

I would say I need standard cost for each customer. Right now it's aggregating total and it's repeating the same result. I changed aggregation to calculated and to none and nothing changes. I set under Group Span "Customer name" and nothing changed.

[Amber12]

Anybody else that could help? I think it's a matter of aggregation...

[Corrigon]

Hi Amber,

Just to clarify - in your attached image, the highlighted values of 5219.70 and 10439.39 are correct, yes?

If so, then your issue is that the Summary value (blue line) of 413752.39 is not correct. You want it to show as 15659.09 yes? (5219.70 + 10439.39 = 15659.09).
And then obviously you want all your Summary rows to behave in the same way for each Customer. And then your master total (All Customers) should aggregate all the summaries together into one master total.

Am I right so far?

If so, then Option C from Hespora's post should work. Unlock the formatting using the padlock icon, and remove the default Summary item.
Drag in a new data item into the node and use the expression Hespora provided.

Code Select Expand

total([std cost calculated 2]  for [whatever your id field is called])

The data item following the 'for' clause is the key. By using the data item for Customer, you are telling Cognos to sum the [std cost calculated 2] values for each instance of Customer, which I think is what you want.

Unless you've already tried that? If so, can you advise what the report did?

[Amber12]

The problem is that Standard cost is listed based on standard cost calculated using this expression:
total (IF ([Accounting Date Period and Description] = ?Fiscal Period?) THEN ([Std cost calculated 2]) ELSE (0))

If my report has only Standard cost, all I get is an amount that repeats over and over (the totals). However if I add to the report the field standard cost calculated, then the Std cost values changes to correct values - individuals but the total is not right (which I can solve). The problem is how do I get the Standard cost to show up correct WITHOUT having standard cost calculated there. My initial post explains this.