No data in result column in combined report

Bouman, Arie · Sep 2, 2019 8:51AM

Hi all,

I'm trying to combine two same environments and have some calculations done, it works when no measurements are added but as soon as I add a measurement I get an error.

Example which works:

1. Source, QuantityAvailable, AvgDayprice, Sum(0)

2. Source, sum(0), sum(0), Contractprice

with result columns:

Source, QuantityAvailable, AvgDayPrice, Contractprice, QuantityAvailable * AvgDapPrice, QuantityAvailable * Contractprice, Diff Qavailable * Dayprice - Contractprice. Last 3 columns added via "Add Result Column"

First I faced a lot of problems since it looked like BI can't calculate for some reasons the QuantityAvailable * Contractprice. I've solved this one finally with help from some archived threads with formula saw_3 * sum(saw_1 by saw_0)

Now my issue, I wanted to extend this working table with one or multiple columns, for example I added a column "ContractNr"

Practise learned that is shoudl be placed directly after "Source"

I've done this with both lines

and within result columns I've put it on same location and consequently changed the formula(s) within the calculated columns.

But result 0 and an error message

"

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 23004] Invalid request: Subtotal GROUP BY clause cannot contain an aggregate expression D1.c2. (HY000)

SQL Issued: SELECT saw_0, saw_1, saw_2, saw_3, saw_4, sum(saw_2 by saw_0) * sum( saw_3 by saw_0) saw_5, saw_4 * sum(saw_2 by saw_0) saw_6, sum(saw_2 by saw_0) * sum( saw_3 by saw_0)-saw_4 * sum(saw_2 by saw_0) saw_7, AGGREGATE(saw_4 * sum(saw_2 by saw_0) BY saw_1), AGGREGATE(sum(saw_2 by saw_0) * sum( saw_3 by saw_0)-saw_4 * sum(saw_2 by saw_0) BY saw_1) FROM ((SELECT 'contract' saw_0, "Contract Attributes"."ContractNr" saw_1, "Contract Facts"."QuantityAvailable" saw_2, "Contract Facts"."AvgDayPrice"*1000 saw_3, sum(0) saw_4 FROM "ERP Procurement" WHERE "Contract Attributes"."ContractNr" = 15011795) UNION (SELECT 'RawMater' saw_0, "Raw Material Attributes"."ContractNr" saw_1, sum(0) saw_2, sum(0) saw_3, "Raw Material Facts"."ContractPrice"/1 saw_4 FROM "ERP Procurement" WHERE "Raw Material Attributes"."ContractNr" = 15011795)) t1 ORDER BY saw_0, saw_1

"

Can somebody give me a clue where to look for please. I'm more or less convinced that it is something with ContractPrice but all trials with formulas gave same results.

Thanks in advance,

Arie

Joel · Sep 2, 2019 10:34AM

Two options I'd suggest (if I understand your issue correctly):

Build the union report from scratch with all columns that you require
Add new columns to the end of the existing columns

Bouman, Arie · Sep 3, 2019 5:08AM

Hi Joel,

Thanks for quick response. Your suggestion seems to work although I still get some unexpected results. For example, the columns headers don't respond to the contents of the columns anymore.

To be honest I'm struggling with this problem (the extra columns and calculating with results) for several days and find it extremely exhausting.

Regards,

Arie

Joel · Sep 3, 2019 7:21AM

The Union reports need to be handled with care!

Your suggestion seems to work although I still get some unexpected results. For example, the columns headers don't respond to the contents of the columns anymore.

If you can be a bit more specific about the issues that you're now encountering, we might be able to help you out - a redacted screenshoot may be useful.