Good morning,
Here is the query I use for the report:
Code:
SELECT tblSource.Time,
tblSource.Entity,
tblMasterData.SortID,
tblSource.Acct,
tblMasterData.Description,
tblSource.ActDKK,
tblSource.FcDKK,
[ActDKK]-[FcDKK] AS dAbsDKK,
IIf([FcDKK]<>0,[dAbsDKK]/[FcDKK],9.99) AS dRelDKK, /*to avoid Div!0 error*/
tblSource.ActLocCur, /*not being used in this, but a related report*/
tblSource.FcLocCur, /*not being used in this, but a related report*/
tblSource.Comment
FROM tblMasterData RIGHT JOIN tblSource ON tblMasterData.Name = tblSource.Acct
WHERE (((tblSource.Time) Between [Choose start period YYYYMM:] And [Choose end period YYYYMM]))
ORDER BY tblMasterData.SortID;
And here is a small sample dataset for it.
Maybe I should elaborate briefly on the background on why I want to isolate the 'Flat' member from the others. The sum of all REG_* entities can but don't necessarily have to be the same number as the 'Flat' (=Total) member. In my first post it is the case that all REG_ members equal the number I have on the related 'Flat' transaction. However it can easily look like this...
Attachment 48263
...which would then indicate, that the missing variances need to be investigated. For the first column we're missing ~338k, which is the 866k from the 'Flat' member minus all listed 'REG_*' entities.
Solutions I've been thinking about could look like, that I either conditional format and remove the 'Flat' member from the total calculation (below left) or remove it from the detail query, however show it below the total comparison (below right)
Hope this makes sense.