-1

I have a report in Cognos 10.1.1 that is pulling Profit, Pounds, and Profit/Lbs. Each query has a field 'Measure Unit' that is labeled as 'Spread $', 'Spread Lbs' or 'Spread $/Lb'. It joins these three queries via a union and then loads them into a Crosstab list. The individual queries work correctly, and when dumped into a List Report are fine, but for some reason when in the crosstab report, the calculated profit/lbs column is always wrong. How can I get the calculated column to total correctly in the crosstab?

Query Structure

Three queries pull seperate Measure Units and measures for Profit, Lbs and Profit/Lbs.

Profit Measure Calculation

The calculation for Profit.

Spread $/Lb Measure Calculation

The Calcuation for Profit/Lbs

Spread Measure Calculation Properties

Properties for the measure fields.

Final Report Sample

What the report looks like. Middle column should equal Spread $ / Spread Lbs, but does not when in crosstab.

Damienknight
  • 1,768
  • 2
  • 16
  • 30
  • Define "wrong". What value do you get? What value do you expect? If you "View Tabular Data" on the query and calculate by hand the way you think you should, do you get the answer you want or the answer Cognos gives you? – Bacon Bits Nov 17 '14 at 18:33
  • Please see screenshots. – Damienknight Nov 17 '14 at 18:49
  • No, I'm asking what the difference is between "right" and "wrong". Pennies? Dollars? Thousands of dollars? Negative when they should be positive? Blank when there should be a value? Is it consistent between the fields? Is your union eliminating duplicates? Should it be? What happens when `[qrySalesNonFerrous].[Measure]` has a Aggregate or Rollup Aggregate that's not Automatic? Is the Crosstab actually doing a sum? An average? A min or max? A product? What do you mean "It's wrong"? – Bacon Bits Nov 17 '14 at 19:02
  • Please review the screenshots. There is an example of what it is doing. Screenshots also contain the properties on the column that determine aggregation. Please completely read and review questions before posting comments. – Damienknight Nov 17 '14 at 20:01
  • I agree with Bacon Bits. You need to define the problem more clearly. We can't know the scale of the error if we don't know what value you expect to get. – Johnsonium Nov 17 '14 at 20:20
  • 'profit/lbs' = profit, divided by pounds. Also could describe it as Profit Per Pounds. The incorrect result is displayed in screenshot. If you are too lazy to read the question, please don't both posting any comments or answers. – Damienknight Nov 17 '14 at 20:48
  • Sorry, there is insufficient information to answer your question. It's not a matter of laziness -- after all, you're also unable to answer it with the same screenshots -- it's a lack of necessary information. There are no syntax errors in your screenshots. All I can tell you is that if you insist this is sufficient information then you're looking in the wrong place. All I can say is define your aggregate function explicitly on the data item and in the cross tab, verify that your union is or isn't removing duplicates as necessary, and look at the query results and calculate by hand. – Bacon Bits Nov 17 '14 at 21:14

1 Answers1

1

Try to customize Solve Order for your calculations. Default behavior is Total(Value1/Value2). You need to archive Total(Value1)/Total(Value2) (Don't change your formulas, just set Solve Order for calculations)

Alexey Baturin
  • 1,163
  • 1
  • 7
  • 11
  • It seems like you are onto something here. I tried changing the 'Measure' fields solve order to 3. The measure = 'Profit / GT'. Both the Profit and GT fields are set to solve order 1. Still have the same results. – Damienknight Nov 18 '14 at 20:07
  • I changed solve order without success, but then changed my query so it was calculating the Spread/GT in the final unioned query instead of in the sub query, and that allowed the totalling to work right. You get vote/solution because you led me to the answer. Also, thanks so much for reading the question before commenting, you are a hero! – Damienknight Nov 18 '14 at 20:14