EYoung
asked on
SSRS - Sub total counting
I have a MS SSRS report that has several columns that I want to add sub-totals to. The cells in the columns are not numbers, they are values like C11, C52, C95, etc. Each column will have the same value in some of its cells like C11. I want to count the number of cells in a column that have a value present. For example, if the first column has five of its ten cells (rows) filled with C11, I want to print the sub total of 5. If the next column has only three of the ten cells (rows) filled with C52, then I want the sub total to be 3.
What formula can I use to create a count of cells that have a value and are not blank?
I tried using this Expression but it counts all cells (rows) and not just the ones that have a value present:
=Count(Trim(Fields!Offer_I d00.Value) > "")
Thanks for the help.
What formula can I use to create a count of cells that have a value and are not blank?
I tried using this Expression but it counts all cells (rows) and not just the ones that have a value present:
=Count(Trim(Fields!Offer_I
Thanks for the help.
It depends whether you have blank values or NULL values in the empty rows.
If you have NULL values then, Count(Fields!Offer_Id00.Va lue) will work
if you have blank values then you can use CountDistinct(Fields!Offer _Id00.Valu e) -1 , as blank values will also be considered here, you need remove that from count.
If you have NULL values then, Count(Fields!Offer_Id00.Va
if you have blank values then you can use CountDistinct(Fields!Offer
Think you can get the desired result using COLUMNS and COUNTIF
=COLUMNS(A1:G1)-COUNTIF(A1 :G1,"")
=COLUMNS(A1:G1)-COUNTIF(A1
ASKER
sureshbabukrish: The count is done in the group footer and is not working. There are only two possible values in the field: the repeating value such as "C11" or blank. No Nulls. I don't think the CountDistinct will work because I am not looking for distinct values. All the values in a given column would always be the same. For example, some groups have 200 rows. The first column could have 150 "C11" and 50 blank cells. The next column could have 50 "C12" and 150 blank cells.
vajacob: I am using SSRS not Excel.
vajacob: I am using SSRS not Excel.
ASKER
Raised points. Need help with this. Thank you.
ASKER
I have tried the following and all they do is count every row regardless of whether or not there is a value in the field. I just want to count those fields in a column that have a value other than blank.
=Count(Trim(Fields!Offer_I d00.Value) > "")
=Count(IIf(Trim(Fields!Off er_Id00.Va lue) > "", 1, 0))
=Count(Len(Trim(Fields!Off er_Id00.Va lue)) > 0)
=Count(Not IsNothing(Fields!Offer_Id0 0.Value))
=IIf(Trim(Fields!Offer_Id0 0.Value) > "", Count(Fields!Offer_Id00.Va lue), -1)
=Count(Trim(Fields!Offer_I
=Count(IIf(Trim(Fields!Off
=Count(Len(Trim(Fields!Off
=Count(Not IsNothing(Fields!Offer_Id0
=IIf(Trim(Fields!Offer_Id0
then add one more column in your query, it should have a case statement as shown below
select Offer_ld00, Case When len(Offer_ld00) = 0 then 0 else 1 end colval from....
use Sum(colval) in group footers, you should do the same for all columns similar to Offer_ld00
select Offer_ld00, Case When len(Offer_ld00) = 0 then 0 else 1 end colval from....
use Sum(colval) in group footers, you should do the same for all columns similar to Offer_ld00
ASKER
sureshbabukrish: I have 25 columns. I really don't want to add 25 more columns to the report. Additionally, the users want to export the report to Excel and the formatting of Excel output is difficult because SSRS adds extra columns if the alignment is not exact. I will if there are no other options. It just seems odd that I can't count non-blank fields.
try this
= sum(IIF(Len(Trim(Fields!Of fer_ld00.V alue))> 0,1,0)
= sum(IIF(Len(Trim(Fields!Of
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is what worked:
=sum(IIf(Fields!Offer_Id00 .Value = "", 0, 1))
Your suggestions helped me to find the answer.
Thank you
=sum(IIf(Fields!Offer_Id00
Your suggestions helped me to find the answer.
Thank you
ASKER
Thank you
i think Count(Fields!Offer_Id00.Va
add this expression in table footer or groupheader/footer