November 9, 2015 at 2:18 am
MATRIX DESIGN based on MDX Query
Column
CATEGORY (Column Group splits into 2 fields - ACTUAL (A) & BUDGET (B))
SUM(PERIODIC)
REPORT
- A --- B <---------- Category
10 --- 28 <---------- Sum(Periodic)
33 --- 55 <---------- Sum(Periodic)
Desired Conditional formatting logic
When column "A" Sum(Periodic) < "B" Sum(Periodic) then column "A" Sum(Periodic) font is red else black.
E.g. both values listed under "A",10 & 33, would be red since they are both less than the "B" column, 28 & 55.
How can I form this expression?
November 11, 2015 at 11:51 am
=IIF(Fields!Category.Value = "Actual" AND Fields!Periodic.Value <5,"red","black") <---------This is working
However, I need to replace the <5 with the Budget comparison as below which is not working yet.
=IIF(Fields!CAT_ACTUAL_BUDGET.Value = "Actual" AND Fields!Periodic.Value < Fields!CAT_ACTUAL_BUDGET.Value = "BUDGET" AND Fields!Periodic.Value,"red","black") <------This returns all the values however no color changes occur.
Anybody have insight into how to correctly build out his formula.
November 11, 2015 at 2:16 pm
The problem as I see it, is that you looking at two different rows of data.
You could try with the Lookup function or the Previous function (if you have the correct sort in place).
I would add another column to the result set indicating whether the actual value is above or below budget.
November 11, 2015 at 3:27 pm
rick.servant (11/11/2015)
=IIF(Fields!Category.Value = "Actual" AND Fields!Periodic.Value <5,"red","black") <---------This is workingHowever, I need to replace the <5 with the Budget comparison as below which is not working yet.
=IIF(Fields!CAT_ACTUAL_BUDGET.Value = "Actual" AND [highlight="#ffff11"]Fields!Periodic.Value < Fields!CAT_ACTUAL_BUDGET.Value = "BUDGET" AND Fields!Periodic.Value[/highlight],"red","black") <------This returns all the values however no color changes occur.
Anybody have insight into how to correctly build out his formula.
The expression highlighted looks very wrong...has the IFCode on the forum doctored it or have you made a mistake?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 11, 2015 at 4:34 pm
This ticket is still open, any ideas?
=IIF(Fields!Category.Value = "Actual" AND Fields!Periodic.Value <5,"red","black") <---------This is works
So I compared to "budget" periodic value instead of the number 5 but to no avail though. See IIF below.
=IIF((Fields!CAT_ACTUAL_BUDGET.Value = "Actual" AND Fields!Periodic.Value) < (Fields!CAT_ACTUAL_BUDGET.Value = "BUDGET" AND Fields!Periodic.Value),"red","black")
It returns the values but not the conditional formatting.
Any ideas on how to make this work? See first comment box for question.
December 3, 2015 at 6:01 pm
=IIF(Fields!Category.Value = "Actual" AND Fields!Periodic.Value <5,"red","black")
Maybe with
=IIF(Fields!Category.Value < Fields!Periodic.Value ,"red","black")
?
If actual is less than budgeted I would think that is a good thing. I would highlight it red if it were say >10% over budget or something similar. Just my two cents.
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply