Solved

Show report sections based on expression

Posted on 2016-09-12
7
48 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
[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 14

Expert Comment

by:Megan Brooks
ID: 41795328
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
ID: 41795366
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 14

Expert Comment

by:Megan Brooks
ID: 41795400
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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

Author Comment

by:srodgers45
ID: 41795425
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 14

Expert Comment

by:Megan Brooks
ID: 41795429
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 14

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41795467
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
ID: 41796365
That worked, i will look into the other suggestion as well. Thank you very much for your time!
0

Featured Post

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.

Question has a verified solution.

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

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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