Let’s say you have a dataset and it read in 15 rows.
There are two columns called, ‘Task’ and ‘Priority’
SELECT Task, Priority FROM *some_table*
Task Priority
------------------------------
a 1
b 3
c 4
d 1
e 5
f 7
g 10
h 2
i 1
j 2
k 1
l 8
m 9
n 4
You have this tablix on your report that has 10 rows and 2 columns.
The first column represents the priority (1 – 10) and the second column represents a count.
Total Tasks Count
-------------------
Priority 1 5
Priority 2 2
Priority 3 1
Priority 4 2
Priority 5 1
Priority 6 0
Priority 7 1
Priority 8 1
Priority 9 1
Priority 10 1
Total 15
Based on the dataset results, you simply want to tally the priorities
and show a grand total as shown above.
In each of the 10 textboxes that represent count, provide the following expression:
=sum(iif(Fields!Priority.Value = "1", 1, 0))
This says: If the value of the Priority of the row being processed = “1”, then sum 1, else sum 0.
For each row where the Priority = “1” it will continue to sum “1”.
Do this for the remaining textboxes:
=sum(iif(Fields!Priority.Value = "2", 1, 0)) =sum(iif(Fields!Priority.Value = "3", 1, 0)) =sum(iif(Fields!Priority.Value = "4", 1, 0)) etc....
A quick way to tally all the counts for a Total textbox (= 15), add the following expression to the total textbox:
=ReportItems!tbPriority1.Value + ReportItems!tbPriority2.Value + ReportItems!tbPriority3.Value + ReportItems!tbPriority4.Value + ReportItems!tbPriority5.Value + ReportItems!tbPriority6.Value + ReportItems!tbPriority7.Value + ReportItems!tbPriority8.Value + ReportItems!tbPriority9.Value + ReportItems!tbPriority10.Value
ReportItems! represents the name you gave the textbox.
For example, in the properties window for the Priority 1 textbox, type ‘tbPriority1’ for the Name property.
Another alternative to this? Have 10 different select count(*) SQL statements for each priority and simply assign the value of each count to each of the ten textboxes. As you can see, the above method is more efficient.