Solved

Show report sections based on expression

Posted on 2016-09-12
7
35 Views
Last Modified: 2016-09-13
I have a report that I would only like to show data based on criteria. I use an expression to highlight fields and would like to limit the report sections to that same data from the expression only. Hopefully that makes sense...

Expression used to highlight:

= iif ((Fields!ACFD_Level_of_Service.Value = "Cancelled – PTA or At Scene - No Pt Contact" or Fields!ACFD_Level_of_Service.Value = "Technical Assistance Provided – No Pt Care" or Fields!ACFD_Level_of_Service.Value = "Initial Assessment Only" ) and ((Fields!PPlus_Arrival_Min.Value > 3.00)), "Yellow", "No Color")

This is what a portion of report looks like, I would like to suppress report sections with any data that does not meet the criteria in the expression. The  next section of data in report below the highlighted one (in red box) is what I would like to suppress from report view...
Sample of Fields
0
Comment
Question by:srodgers45
  • 4
  • 3
7 Comments
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
How are your report sections defined in the RDL? Is each one obtained from one dataset row and displayed in a table data region across three detail rows?
0
 

Author Comment

by:srodgers45
Comment Utility
Yes, single dataset with the 3 rows displaying report information. hopefully that it what you're asking. I only want to display report information based on the criteria in the expression, I guess it would be similar to a built in parameter/filter. Which would be the 3 rows of data above the red box (excluding header obviously)

thanks for any help you can provide...

Sample RDL
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
You have at least three choices for hiding sections of the report. The most efficient is to write the main dataset query so that rows that should not be shown are not included in the dataset. In some situations, however, this can be more trouble to implement than it is worth.

Another option is to filter the data rows in the RDL. You can do this at the dataset level or at a lower level (see Add Dataset Filters, Data Region Filters, and Group Filters (Report Builder and SSRS)). The filter structure is rigid, and permits only AND conditions between expressions. I think you could do with a dataset level filter here, but you need to be able to construct the filter condition (True = include data) as a series of ANDed expressions. This approach is less efficient because you are filtering after the data has been queried out of the source database.

A third option (the least efficient, but possibly the easiest to implement) is to control row visibility. Each row in a tablix has a boolean Row Visibility property, for which True means "hidden" and False means "shown" (it's confusing). When a row in the dataset is represented by several tablix rows, as is the case here, you will need to specify the same visibility expression for each row in order to hide them all. The property can be accessed by right-clicking a row selector (on the left side, when the tablix is selected and in the state where row and column selectors are visible). Your visibility expression will be one that is True when no cells are highlighted, or whatever other condition you would like.

Do any of these sound like what you are looking for?
RowVisibility.png
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:srodgers45
Comment Utility
Can you give me an expression example for the third option? I could not find one in reference link you provided.  Thank You for the help either way!

I tried this and it did not work... I am not that familiar with SSRS expressions.

= iif ((Fields!ACFD_Level_of_Service.Value = "Cancelled – PTA or At Scene - No Pt Contact" or Fields!ACFD_Level_of_Service.Value = "Technical Assistance Provided – No Pt Care" or Fields!ACFD_Level_of_Service.Value = "Initial Assessment Only" ) and ((Fields!PPlus_Arrival_Min.Value > 3.00)), "True", "False")
0
 
LVL 13

Expert Comment

by:Megan Brooks
Comment Utility
Yes - i wanted to make sure I was on the right track. I'm finishing up something else and then I will have another look. One thing I can tell you now is that you shouldn't need the IIf(xxx, True, False) wrapper - it is redundant - and you wouldn't want to put True and False in quotes because they are boolean constants recognized by the compiler. The expression (i.e. "xxx") itself is sufficient.
0
 
LVL 13

Accepted Solution

by:
Megan Brooks earned 500 total points
Comment Utility
Syntactically, you would need something like

=Not ((Fields!ACFD_Level_of_Service.Value = "Cancelled – PTA Or At Scene - No Pt Contact" OrElse Fields!ACFD_Level_of_Service.Value = "Technical Assistance Provided – No Pt Care" OrElse Fields!ACFD_Level_of_Service.Value = "Initial Assessment Only" ) AndAlso Fields!PPlus_Arrival_Min.Value > 3.00)
I have kept your 'highlight' logic, but turned it around so that it returns True when not highlighted. I also changed the And and Or operators to their short-circuit forms AndAlso and OrElse, so that they behave like they would in any other programming language. It doesn't really matter -- there can be a little wasted time when And and Or are used -- but it's a VB.Net programmer habit to make the substitution. The parentheses around "Fields!PPlus_Arrival_Min.Value > 3.00" don't do anything and I removed them. I added parentheses around the entire expression so that the initial Not would apply correctly.

If you have code values that are associated with the conditions you are testing you may be able to make this logic a lot cleaner by returning the codes as well as the text strings in the dataset query, and then performing the highlight tests on those code values instead of on the strings. That way, if the text changes slightly (and the code value does not change), the report still works.

One other thing you could do, but might not want to bother with, is perform the highlight test as a calculated field in the dataset. A calculated field is a calculation that is performed after the SQL query completes but before the data is returned to the report, and it adds a new field to the dataset that represents the value of the calculation. If you call the calculated field "IsHighlighted" then the highlighting expression can simply test "=IIf(Fields!IsHighlighted.Value, <color options>)". The row visibility expression becomes "=Not Fields!IsHighlighted.Value". You could also specify a dataset filter for IsHighlighted = True and not have to specify row visibility at all.
0
 

Author Closing Comment

by:srodgers45
Comment Utility
That worked, i will look into the other suggestion as well. Thank you very much for your time!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

728 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

9 Experts available now in Live!

Get 1:1 Help Now