Count of Conditonal Formatting

Hi,

I have a report where conditional formatting is applied based on being compared to a fixed value in a second dataset. There are several columns where this is applied, and I would like to be able to count all where the condition is true (ie >= corresponding column in dataset2, background green) and all that are false (ie < dataset2, background red) and use that data in the same report and create a percentage.

Not sure whether the reporting layer is the place to do this, or whether to create a separate table and populate that with 1's and 0's beforehand and then use that data additionally in the report?

Many thanks

The usual way to do this is to use SUM() instead of COUNT(), like this:

Code Snippet

SUM(IIF(<condition is true>,1,0))

HTH,

>L<

|||

Hi,

For backgroud coloring, add to this to the column BackgroundColor property:

Code Snippet

=iif(<condition> = 0, "Green", "Red")

|||

Have a look at Brian Welcker's Blog for his post on Custom Aggregates:

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

In the Code block (available from the Report Properties dialog), you would add:

Dim orderIDs As System.Collections.Hashtable
Dim total As Double

Function MyFunc(ByVal orderID As Object, ByVal freight As Obect) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
MyFunc = total
End If
End Function

In your report, you add a hidden textbox with the value expression to compute the value:

=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))

In the footer of the table, you add a textbox with the value expression:

=Code.MyFunc(Nothing, Fields!Freight.Value)

to return the total value.

I believe it provides the functionality you need.

Larry

|||

Thanks, I think this will work within a column, therefore I get a total at the bottom of each column.

I also need a total at the end of each row (crosstab style), but obviously each column has a separate textbox and separate conditional formatting expression. It still boils down to whether that condition is true or false - do you know if this is possile or is it better suited to the database level?

eg for all condition true is >= value in dataset 2

dataset2 - baseline comparison table

col1, col2, col3

40,2,0

dataset1 - data

col1, col2, col3

20,2,4

45,0,0

what i'd like is to be able to include on the report that 2 true conditions were met and also include the actual data

col1, col2, col3, SumTrue

20,2,4,2

45,0,0,2

Appreciate everyone's help so far - i've got the formatting (ie red, green) working - just wondering if the sum of conditions across columns will work

|||

Actually I think it will still work, and without any exciting code. but possibly I'm not clear from your examples what your SumTrue column means, because it says "2" on each of the rows and I only see 1 match in each row against your baseline table?

>L<

|||

Hi..

The condition for true in this case is where the result is >= to the comparison table, in the example there are 2 on each row that are >= to the comparison table.

I have just got back to looking at this again and tried the following in the group footer of one of the columns

=Sum(IIF(Fields!col1.Value >= First(Fields!col1.Value, "BaseTable"),1,0))

and get an error saying aggregate functions cannot be nested in other aggregate functions.

|||

>>the result is >= to the comparison table

OIC. I thought it had to be = to be true. I apologize, I didn't see the > in your original post, although I see now that you said so! I will have to repro this to test, which is why I am noticing the > now . Hang on, doing that...

Well, it does work, as I said, to do the thing you asked about ( "adding across").

IOW, assuming DataSet2 is your real values and DataSet1 is the baseline, this works fine to get your fourth column across, as I expected:

Code Snippet

=IIF(Fields!col1.Value >= First(Fields!col1.Value, "DataSet1"),1,0) +

IIF(Fields!col2.Value >= First(Fields!col2.Value, "DataSet1"),1,0) +

IIF(Fields!col3.Value >= First(Fields!col3.Value, "DataSet1"),1,0)

For your group footer, though, you would have to do the custom aggregate bit.

That really seems like the long way around, though. Are you absolutely sure you shouldn't be doing this at the data level?

To illustrate: I did this to represent your the baseline data in my repro:

Code Snippet

SELECT 40 AS col1, 2 AS col2, 0 AS col3

and then this to represent the real data:

Code Snippet

SELECT 20 AS col1,2 AS col2,4 AS col3
UNION
SELECT 45 AS col1,0 AS col2, 0 AS col3

Ignoring the UNION, for a moment, because it was just there to give me two dummy rows in the real data, wouldn't we be better off if we did the following at the SQL level? What we're after is a Cartesian join with the one-row baseline data, which you can get with any dummy-true condition; I used 1=1 here:

Code Snippet

SELECT col1, col2, col3, basecol1, basecol2,basecol3,
CASE WHEN col1 >= basecol1 THEN 1 ELSE 0 END AS col1Comp,
CASE WHEN col2 >= basecol2 THEN 1 ELSE 0 END AS col2Comp,
CASE WHEN col3 >= basecol3 THEN 1 ELSE 0 END AS col3Comp
FROM (SELECT 20 AS col1,2 AS col2,4 AS col3
UNION
SELECT 45 AS col1,0 AS col2, 0 AS col3) real
JOIN (SELECT 40 AS basecol1, 2 AS basecol2, 0 AS basecol3) baseline
ON 1= 1

(Again just treat the UNIONed set as a placeholder for your real data and the literals on the right side of the join as a placeholder for however you get your single row of baseline data. )

Now you have everything you need in one dataset.

I added the col1Comp, col2Comp, and col3Comp columns just to make life easier on the reporting end, you really could do without them and do the >= comparisons against col1 versus basecol1 etc, because you would no longer be nesting aggregates. I included the three calculated columns because I don't see any reason to do any of this work on the reporting level, frankly...

For colors you could then write simple expressions like

=IIF(Fields!col1Comp.Value=1,"Green","Red")

Going across, you would only need to write this:

=Fields!col1Comp.Value + Fields!col2Comp.Value + Fields!col3Comp.Value

Going down is a simple =Sum(Fields1!col1Comp.Value) and of course the bottom right is a simple sum of the addition you see above.

>L<

|||

Absolutely spot on !! Works a treat.

Appreciate your help on this.

Thanks,

Mike

|||

Yea verily, data is a beautiful thing <g>.

Enjoy,

>L<