Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSRS - Sub total counting

Posted on 2010-09-07
12
Medium Priority
?
2,497 Views
Last Modified: 2012-05-10
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_Id00.Value) > "")

Thanks for the help.
0
Comment
Question by:EYoung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 33625815
the count should be done a group header/footer or table footer.
i think Count(Fields!Offer_Id00.Value) should work for you.
add this expression in table footer or groupheader/footer
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 33625912
It depends whether you have blank values or NULL values in the empty rows.
If you have NULL values then, Count(Fields!Offer_Id00.Value) will work
if you have blank values then you can use CountDistinct(Fields!Offer_Id00.Value)  -1 , as blank values will also be considered here, you need remove that from count.
0
 
LVL 5

Expert Comment

by:Varghese Jacob
ID: 33626063
Think you can get the desired result using COLUMNS and COUNTIF
=COLUMNS(A1:G1)-COUNTIF(A1:G1,"")
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 7

Author Comment

by:EYoung
ID: 33627961
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.
0
 
LVL 7

Author Comment

by:EYoung
ID: 33628583
Raised points.  Need help with this.  Thank you.
0
 
LVL 7

Author Comment

by:EYoung
ID: 33628789
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_Id00.Value) > "")
=Count(IIf(Trim(Fields!Offer_Id00.Value) > "", 1, 0))
=Count(Len(Trim(Fields!Offer_Id00.Value)) > 0)
=Count(Not IsNothing(Fields!Offer_Id00.Value))
=IIf(Trim(Fields!Offer_Id00.Value) > "", Count(Fields!Offer_Id00.Value), -1)
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 33629001
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
0
 
LVL 7

Author Comment

by:EYoung
ID: 33629149
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.
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 33629563
try this
= sum(IIF(Len(Trim(Fields!Offer_ld00.Value))> 0,1,0)
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 1000 total points
ID: 33629589
try this
= sum(IIF(Len(Trim(Fields!Offer_ld00.Value)) > 0,1,0))
0
 
LVL 7

Author Comment

by:EYoung
ID: 33629951
Here is what worked:
=sum(IIf(Fields!Offer_Id00.Value = "", 0, 1))

Your suggestions helped me to find the answer.

Thank you
0
 
LVL 7

Author Closing Comment

by:EYoung
ID: 33629959
Thank you
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question