Solved

SSRS - Non-existent row/column group appearing

Posted on 2014-10-22
7
1,459 Views
Last Modified: 2014-10-23
I have a sample dataset attached in Excel, and as you can see, the last field is called UtilityOrder and is returning 1, 2, or 3 only. I have a tablix containing a few expressions and a gauge, and as you can see here, I've set up RowGroup and ColumnGroup on the tablix:

Design View
Group Expressions are:
RowGroup = Floor((Fields!UtilityOrder.Value-1) / 2)
ColumnGroup =(Fields!UtilityOrder.Value-1) Mod 2

In this example, the intention is that tablix panels go across the row, and once 2 have been placed, a new row is started. The problem is that I'm getting a phantom 4th panel. I've added UtilityOrder to the third expression to illustrate:

Gauge screen output
Why is the 4th panel appearing when UtilityOrder=4 doesn't exist?

Thanks,
Jeff
0
Comment
Question by:jdallen75
  • 4
  • 3
7 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 40398779
The cell in the matrix is there because there is a row an a column for it. There just no value in the cell. With normal text fields these would come up empty most of the time, so you won't notice. But now there is a gauge in the cell so you get a gauge without a value. The solution is to use the Hidden propery of the gauge to hide it when there is no value. You can do this by setting the hidden propery to:
=Fields!UtilityOrder.Value is nothing

Open in new window

You can do this in the properties list directy or in the Gauge Panel Properties window in the Visibility section in the 'Show or hide based on an expression' field.
0
 

Author Comment

by:jdallen75
ID: 40399435
Nicobo,

Thanks for the reply. That *almost* worked. I had to apply that visibility expression to each object (gauge and 2 text objects) and it worked for them. But for some reason, in the header, it thinks UtilityOrder is 2?? (It's almost like a leftover from the object above it). I've also applied the visibility on the tablix object 2, but the header is causing it to remain:

Almost hidden!
Any thoughts?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40399775
Strange indeed. It almost seems like a bug. There is no record meeting the columns and row grouping so the fields should be empty. I was able to solve it by using this formula for the hidden property:
=sum(Fields!UtilityOrder.Value) is nothing

Open in new window


And now I think about it looks a lot more like bug. Because how can sum(Fields!UtilityOrder.Value) be nothing when Fields!UtilityOrder.Value has a value.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:jdallen75
ID: 40399964
I'm pretty sure it's a bug, but a colleague of mine managed to find a work-around:

1. Remove the grouping on the existing tablix (and name it InnerTablix)
2. Apply the visibility expression to this InnerTablix
3. Drop another matrix on the page, and call it OuterTablix
4. Bind the same dataset to this OuterTablix
5. Configure the row and column groups as before
6. Delete extra rows and columns

So far, it has worked for 2 examples.
Thanks again, Nicobo
Jeff
0
 

Author Closing Comment

by:jdallen75
ID: 40399967
Your suggestion got me on the right track.. even if there's an underlying bug.
0
 

Author Comment

by:jdallen75
ID: 40399970
Sorry, I missed the "Sum" part in your previous comment. I'll try that again on my next example to fix. Thanks.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40400176
Another way to fix it might be using one cell only and place a rectangle in that cell. Inside the rectangle you can place multiple controls (gauges and textboxes). Then you can set the visibility of the rectangle.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now