?
Solved

How do I use the InScope function in an expression of an RDL report?

Posted on 2009-05-08
6
Medium Priority
?
1,912 Views
Last Modified: 2012-05-06
I am creating an RDL report, using SQL Server 2008.  

On my report, I need to display a constant list of interests. I need to identify within this constant list of interests which interests are "selected" or "chosen".  I currently have two seperate stored procedures set up, one to list all available interests, and another to list all the interests "selected" by a particular person.  I would like to re-use these 2 stored procedures, so I have set up 2 datasets in my report over these appropriate stored procedures, "AllInterests" (get all interests) and "EntityInterests" (get interests for a speicfic person).

I created a matrix table, with the dataset equal to "AllInterests". In a field in this table, I added an expression: =IIf(InScope("EntityInterests"), "Y", "N").  I was hoping that this would then put a "Y" in this field if the current "all interest" row in the table was included in the "EntityInterest" list; otherwise display a "N" in this field.

But this is not working; it is returning "N" for me all the time. Am I not using the InScope function properly? Or is there a better way to accomplish this without using the InScope function? I am very new to RDL reporting; this is acutally the first report i have ever created in RDL. Any suggestions would be appreciated.
0
Comment
Question by:farminsure
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 24339475
The report that you are creating, would it be specific for one person?  If so, "Person" should be a parameter of the report and you could then use that parameter to retrieve the selected person's interests.
How you set up the "Person" parameter would depend on your data and your business model.  If you're using the person's name to identify a person, that should be the label of the parameter.  And the value of the parameter should be the value that you need to look up a particular person's interests, the value that you need to provide to the EntityInterests stored proc.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 24339503
Here's some info on using parameters in Reporting Services: http://www.sql-server-performance.com/articles/reporting/report_parameters_p1.aspx
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24341300
Sorry, but in short, Yes you are using the InScope function improperly.  It is used to indicate whether the current instance of an item is in the specified scope.  See http://msdn.microsoft.com/en-us/library/ms156490.aspx for what it is for.
From that site: "The following code example indicates whether the current instance of the item is in the Product dataset, data region, or group scope.  =InScope("Product") "
So your example is always returning "N" because where you are invoking it is not in the other Dataset's Scope, not because that value is not because the value is not in the other dataset.
I have seen many people want to merge datastes or lookup values of one from the other and I have not seen anyone who can hack that yet.  In the mean time you will have to combine the info into one dataset to be able to indicate the interest.  It should not be too hard to use what you have in your current stored procedures to give you what you need, but it will mean either changing what EntityInterests returns or making a new one that takes a person like EntityInterests but returns all Interests but with a flag for the interested ones.  If you need SP help I can provide that if you share some details.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:farminsure
ID: 24353701
ValentinoV:  Yes, this report will be for one person at a time. But I am already passing in the desired person's ID as a parameter to the report. I am also successfully getting a list of interests for that person from my EntityInterests stored procedure. What I need to do though is combine the list specific to the one person with a list of all possible Interests. So there might be about 20 possible interests, but one person might have only choses "5" of those interests.
It's "combining" those pieces of information that I am having difficulties with; at least when using 2 stored procedures like I am doing.
It sounds as if I may have to create a new sproc to return the information I am wanting.  If you know of another method, please let me know; otherwise I will proceed with that method.
0
 

Author Comment

by:farminsure
ID: 24354050
CGLuttrell:
Thanks, that helps me out. I do have a question regarding how to set up the sproc...
My "All possible interests" (Interests) table consists of 2 columns, an ID and a Description. A second table stores the interests that a specific person has selected: CustomerInterests. This table consists of a unique ID column, a FK to the InterestsID table, and a FK to the PersonID.
Is this enough info for you to provide an example of how to set up a sproc to return all records in the Interests file, with a "flag" indicating the records that match in CustomerInterests...
 
0
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 2000 total points
ID: 24355135
Create the sproc to pass in PersonID and use the query below to get your data.
select AI.Id, AI.Description, EI.PersonID
from AllInterest AI
Left Outer Join EntityInterest EI on AI.ID = EI.InterestID and EI.PersonID = @PersonID

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

752 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