SQLTeam.com | Weblogs | Forums

Ssrs 2012 display unjique school numjber

In an SSRS 2012 report, I am trying to use the following statement and the syntax works but I do not need the distinct rows I want:
iif (first(Fields!schoolNumber.Value) = Fields!schoolNumber.Value,false,true)

Basically what I am trying to do is to use the school number of the first record when the particular report is running, I want to keep the value result set of (school number) returned by the query and save the result somewhere like in a hidden parameter value.
I then want to use the row visibility property, and display the result of the information from each of the rows returned if the results = school numbers are the same.

Basically the information to be displayed are:

  1. school number,
    2, school year,
  2. student number, and
  3. grade.

I cannot create another paramter value since there are 72 existing rdls that already pass school year, student number, and grade among the rdls and I do not want to pass another parameter value around among the existing 72 rdls.
I think using addtional dataset by work, but I do not know how to setup the lookup furnction to accomplish my goal.

Here is the sql I am using right now:

SELECT distinct

		studentNumber			AS StudentNumber	
	,	admingrade					AS AdminGrade			
                        ,	[schoolNumber]
	                [grade]
	     

FROM 

	[dbo].[StudentAssessment]             
          
WHERE 
studentNumber in (@StudentNumber) 
and	admingrade IN (@Grade)
AND schoolYear = @SchoolYear 

Basically overall I want to display all information for one school number.

Thus would you tell me what I can do to solve the problem?

Why can you not create a parameter for that RDL? I do that all of the time. It isn't predicated on any other RDL's using that parameter. Just create a data set for the values to be selected from and then assign that data set as the Available Values. Then you can set the Filter property to be SchoolNumber = @SchoolNumber.

1 Like