Using a column other than the 1st row in one of the textbox in ReportHeader

[Pinkesh]

In my ReportHeader there are a number of Fields that read from a SQL query. It works fine, as most of the fields are provided in the first row.

However there is a Field that require a condition in finding the right row. e.g.

SQL query:

CompanyName OfficeLocation  Name   Phone   EmployeeType

ABC                    Ontario            Tom      123         Contract

ABC                    Ontario            Ann       234        Full

ABC                    Ontario            May      456        Boss

 

ReportHeader:

Company Name: [=Fields.CompanyName]

Office : [=Fields.OfficeLocation]

Boss: [=Fields.Name] (Wont work, as it will display Tom and not May since Tom is the first row)

 

So how do I make it so it will display May in the Boss Field?

[Katia]

Hello Pinkesh,

You can use a conditional function IIf() to get the name of the person based on the value of another data field in the current data row.
In order the function to be evaluated for each row from the database, you need to use an aggregate function, for example:

= Sum(IIF(Fields.EmployeeType = "Boss", Fields.Name, ""))

Check the attached report demonstrating this approach. The report can be opened with Standalone Designer R3 2016.


I hope this will help.

Regards,
Katia
Telerik by Progress

[Pinkesh]

Hey Katia

 

Thanks, but what if there are multiple row with BOSS, would this give me back 'MayMayMayMay'?

Would First be able to fix it? =First(IIF(Fields.EmployeeType = "Boss", Fields.Name, "")) 

 

thanks

[Pinkesh]

oh nevermind, Max works for me