Solved

How do I hide a total on a group in Reporting Services

Posted on 2008-06-24
7
1,826 Views
Last Modified: 2011-10-19
I would like to hide a total line on a group if the result is only one row.  
So for instance, I have:
Group 1                            
Item 1 $40
Item 2 $30
Item 3 $30
Total $100
Item 4 $20
Total $20
Group Total $120

I would like to eliminate that total if there is only one item so it would look like:
Group 1                            
Item 1 $40
Item 2 $30
Item 3 $30
Total $100
Item 4 $20
Group Total $120

So, can it be done and if so how?  I have this expression in the visibility hidden property but it doesn't quite work:  =Iif(CountRows("table2_part1") <> 1, False, True)
0
Comment
Question by:DawnAR
[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
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:jgv
ID: 21856732
Your expression should be working but you don't need an IIF statement in the expression for the Hidden property.
=CountRows("table2_part1") <= 1

Make sure that you are referencing the proper group as the scope in the CountRows() function. From your example, it looks like you may have 2 groups. The CountRows should have the inner group (the group where the footer should be suppressed if only 1 row) as the scope.
0
 

Author Comment

by:DawnAR
ID: 21857094
That didn't work.  I attached my results (using example names).
I have 3 groupings in Table2.  Part2 is my overall group, then Part1, then Expense.  So in my example, Engines and Glass belong to Part2 (1st group); Gas Tank, Air Filter, type of windshield belong to Part1 (2nd group - this is the total line I want elimated); and then last my 3rd group, Expense, which are fuel pump, filter, gas, air filter, etc.

example.xls
0
 
LVL 12

Expert Comment

by:jgv
ID: 21857488
Are you getting an error with the CountRows or it does not have any effect? Also, why are you using a group (instead of the details section) for the 3'rd group, expense? From the sample data the expense items appear to be the details of the report.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:DawnAR
ID: 21858013
No error, it just doesn't seem to have any effect.  The 3rd group has to be a group as well; and that's showing in the group footer.  There would be way too many rows of detail if I didn't make it a group.  I simplified the numbers in my attached example, but technically the "expense" amount is more like in the thousands as there are many fuel pumps, filters, etc. listed in the detail section.
0
 

Author Comment

by:DawnAR
ID: 21858070
Also, attached is a screenshot of my .rdl so you can see my groups, and maybe that will help too.
rdlexample.bmp
0
 
LVL 12

Accepted Solution

by:
jgv earned 250 total points
ID: 21858363
CountRows counts all of the rows (details that make up the group total) not just the number of group rows. You may only have 1 total row in the group (ie: Air Filter in your sample) but that is probably made up of numerous detail records which means that the CountRows is likely always greater than 1.

Try this instead. The first parameter should be a field that uniquely identifies the type of detail row (Fuel Pump, Fuel Filter, etc).
=CountDistinct(Fields!AUniqueField.Value, "table2_part1") <= 1
0
 

Author Closing Comment

by:DawnAR
ID: 31470179
Thank you! ! That worked!  I appreciate the help and the clarity of why my example wasn't working.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 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