Variance Calculation In Report Builder.

[mgtaylor3]

AFternoon,

I Have a report where i Have YTD Totals in Actual and Budget  Columns.

These Columns expand to show the various  Month values.

These columns are Grouped on a 'Scenario'  Column and is filtered on what is int hat Column, so for Actuals 

Scenraio = 'Actual'

Same for Budget.

 this all works well...

Until Variance.

I am uses an ODBC Link to a Snowflake DB... and using an SQL Query within a Expression to get the data'

Previously I as using a pBIX to get the data and I had no problems in creating the expression needed in the expanded Variance column  to show actual - budget

ie:  PBIX I had to do this...

=Sum(IIF(FIELDS!SCENARIO_ID.VALUE = "Actual", Fields!Amount_NZD.Value , 0)) - Sum(IIF(FIELDS!SCENARIO_ID.VALUE = "Budget" And FIELDS!BUDGET_ID.VALUE = Parameters!zBudget_Model.Value, Fields!Amount_NZD.Value , 0))   Within a  Grouped column of Months... worked a treat.

Now using the ODBC Link...this errors out. same field names etc,.... but will not calulate.

data example

                                APRIL-21   MAY -21            APRIL-21      MAY-21     APR-21      MAY-21

Category                Actual          Actual              Budget         Budget       Variance  Variance

'Name'                     2000              3000             2500             2500             -500          500

Cannot now seem to get this when using the ODBC Link....   

Expression example... 

="

="SELECT ACCOUNT_CATEGORY,
,DIM_D365ACCOUNT_KEY,ACCOUNTTYPE,BUDGETMODEL,BUDGETTYPE,EBITMDR_FLAG,
EBIT_FLAG,FINANCIAL_MONTH_NUMBER,EBITDA_FLAG,DIM_D365ENTITY_KEY,
FINANCIAL_YEAR,DIM_D365MARKETTYPE_KEY,SCENARIO,
Sum(PLNZD_AMOUNT_ACT) as AMOUNT_NZD,
Sum(NZD_AMOUNT_BUD) as BUDGET_NZD,
MONTH_SHORTNAME,ACCOUNT_DESCRIPTION,

CASE WHEN ACCOUNT_CATEGORY IN ('Revenue','Cost of Goods Sold') Then 'Group1'
WHEN ACCOUNT_CATEGORY IN ('Fixed Overheads','Variable Overheads') Then'Group2'
Else 'Group3' End as PLGroup,

CASE WHEN ACCOUNT_CATEGORY IN ('Revenue','Cost of Goods Sold','Fixed Overheads','Variable Overheads') Then 'OP'
Else '' End as OP,
CASE WHEN ACCOUNT_CATEGORY IN ('Fixed Overheads','Variable Overheads') and DIM_D365ACCOUNT_KEY<>656005 and DIM_D365ACCOUNT_KEY<>656010 Then'OPM'
ELSE '' End as OPM,
CONCAT(MONTH_SHORTNAME,'-',Right(CALENDAR_YEAR,2)) AS F_MonCY,
CONCAT(DIM_D365ACCOUNT_KEY,' ',ACCOUNT_DESCRIPTION) AS Account_Code_Desc

FROM LMI_PRODUCTION.FINANCE.FACT_GL

Where
DIM_D365ENTITY_KEY In ('" + Join(Parameters!Entity.Value,"','")+"')
and FINANCIAL_YEAR In ('" + Join(Parameters!FINYEAR.Value,"','")+"')
and FINANCIAL_MONTH_NUMBER <=('" + Join(Parameters!AGGGENERALLEDGERMonFY.Value,"','")+"')
and (BUDGETMODEL in ('" + Join(Parameters!zBudget_Model.Value,"','")+"') OR BUDGETMODEL IS NULL)

Group By
ACCOUNT_CATEGORY
,DIM_D365ACCOUNT_KEY
,ACCOUNTTYPE
,BUDGETMODEL
,BUDGETTYPE
,EBITMDR_FLAG
,DESCRIPTION
,EBIT_FLAG
,ACCOUNT_CAPTION
,FINANCIAL_MONTH_NUMBER
,EBITDA_FLAG
,DIM_D365ENTITY_KEY
,FINANCIAL_YEAR
,DIM_D365MARKETTYPE_KEY
,SCENARIO
,MONTH_SHORTNAME
,ACCOUNT_DESCRIPTION
,CALENDAR_YEAR"

 ANy thoughts?   

[amitchandak]

@mgtaylor3 , it is giving error ?

like this one can be

<= Parameters!AGGGENERALLEDGERMonFY.Value(0)

[mgtaylor3]

Hi Amitchandak,  the parameters all work, the query , at this stage doesn't seem to be the issue..  when using the calc

=Sum(IIF(FIELDS!SCENARIO_ID.VALUE = "Actual", Fields!Amount_NZD.Value , 0)) - Sum(IIF(FIELDS!SCENARIO_ID.VALUE = "Budget" And FIELDS!BUDGET_ID.VALUE = Parameters!zBudget_Model.Value, Fields!Amount_NZD.Value , 0))

within the report now  doesn't seem to work.... doesn't now seem to distinguish the difference in SCENARIO_ID

while in a Column the report works well with a filter.... just in a CELL that Expression now, doesn't seems to work.

Looking at the shape of the data comign through I think i might have a quick solution by creating a Case statement to have the Actuall and budget values I require in the same Value column..

Scenario    Value

Actual         1000

Actual         1000

Budget        2000

 instead of

Scenario Amount   Budget

 Actual     1000

Actual      1000

Budget                     2000   as it is now.....   it maybe this that is throwing it.