We have one calculated query item Actual Local Revenue. It is the summary of
Invoice Outlay Local Revenue + Actual Employee Local Revenue
Expressions in the Framework Manager
Invoice Outlay Local Revenue :
[Physical Layer].[JOB_OUTLAY_INVOICE_FACT].[INVOICE_OUTLAY_LOCAL_REV]
Actual Employee Local Revenue :
[Physical Layer].[JOB_ACTUAL_FACT].[LOCAL_REVENUE]
Actual Local Revenue
CASE
WHEN ([FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue] is missing and [FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue] is missing)
THEN (0)
WHEN [FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue] is missing
THEN total([FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue])
WHEN [FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue] is missing
THEN total([FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue] )
ELSE TOTAL([FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue] ) + TOTAL([FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue])
END
It is not working when I use directly job actual revenue to the report .I cannot rebuild this calculated item in the report .It is hidden calculation user only see actual local revenue .So it should be done in FM only
And it is giving me multiple rows with same data
Month to Date Year to Date
Clients Revenue Forecast Var Profit Profit % Revenue Forecast Var Profit Profit %
"BONGRAIN POLSKA SP. Z O.O. (PO£¡CZY£A SIE Z MLECZARNI¡ ""TUREK"")" (22310270) 528,554 385,600 73.0 54,142,965 13,497,477 24.9
361 DEGREES CONSULTING INC (21210000) 528,554 385,600 73.0 54,142,965 11 13,497,477 24.9
3M COMPANY (21210001) 528,554 385,600 73.0 54,142,965 198 13,497,477 24.9
3M POLAND SP.Z O.O. (22310008) 528,554 385,600 73.0 54,142,965 13,497,477 24.9
7-ELEVEN INC. (20010214) 528,554 385,600 73.0 54,142,965 28 13,497,477 24.9
7585373 CANADA INC. (SKOOK MANAGEMENT CONSULTANTS) (20010254) 528,554 385,600 73.0 54,142,965 10 13,497,477 24.9
7TH SENSE RESEARCH (21210002) 528,554 385,600 73.0 54,142,965 13,497,477 24.9
A-LEHDET OY (22010483) 528,554 385,600 73.0 54,142,965 30 13,497,477 24.9
A-TUOTTAJAT OY (22110171) 528,554 385,600 73.0 54,142,965 28 13,497,477 24.9
A.C.NIELSEN FINLAND OY (22010522)
But expected data is like this
Month to Date Year to Date
Clients Revenue Forecast Var Profit Profit % Revenue Forecast Var Profit Profit %
"BONGRAIN POLSKA SP. Z O.O. (PO£¡CZY£A SIE Z MLECZARNI¡ ""TUREK"")" (22310270) 3
361 DEGREES CONSULTING INC (21210000) 11 11 3 25.0
3M COMPANY (21210001) 73 73 -2 -2.8 198 198 16 8.1
3M POLAND SP.Z O.O. (22310008) 0 1
7-ELEVEN INC. (20010214) 28 28 4 12.9
7585373 CANADA INC. (SKOOK MANAGEMENT CONSULTANTS) (20010254) 10 10 4 38.3
A-LEHDET OY (22010483) 14 14 14 100.0 30 30 30 100.0
A-TUOTTAJAT OY (22110171) 7 7 6 86.1 28 28 26 90.1
A.C.NIELSEN FINLAND OY (22010522) 1 1 1 100.0
AAA Mid-Atlantic (7910296) 13 13 12 93.1 28 28 25 88.1
AADPA (7910048) 0 3 3 2 53.7
AAMA (7910122) 0 0
AARP (21210004) 18 18 -1 -5.6 164 164 9 5.2
AB DAGENS NYHETER ANNONSFÖRSÄLJNING (20310041) -6 -68
Please help me how to resolve this issue
Thanks,
Viswa
You could try:
ASE
WHEN ([FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue] is missing and [FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue] is missing)
THEN (0)
WHEN [FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue] is missing
THEN [FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue]
WHEN [FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue] is missing
THEN [FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue]
ELSE [FINANCE REPORTING].[Job Measures].[Invoice Outlay Local Revenue] + [FINANCE REPORTING].[Job Measures].[Actual Employee Local Revenue]
END
MF.