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,
3. student number, and
4. 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?
Wow, this is interesting! First, it seems like you are dealing with an unusually large number of .rdls for a given business requirement. That could be a design flaw. Next, I'm wondering why you would suppress rows where the school does not match the school of the first record. Do you have sorting in your report such that the first school is definitely the school you are looking for?
Even though I'm not 100% clear on the business requirement, here is my guess at this point...I would add a window function to my source query giving me a new column with first school. I would then simplify my report by setting row visibility based on the new column. The window function would probably be FIRST_VALUE.
https://docs.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?view=sql-server-ver15
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply