How do I use the InScope function in an expression of an RDL report?
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("EntityIntere
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.
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("EntityIntere
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.
Here's some info on using parameters in Reporting Services: http://www.sql-server-performance.com/articles/reporting/report_parameters_p1.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.