CODEWITHSUNDEEP
reporting-servicesreportgroupingreportbuilder3.0
Radu N.
Radu N.

Reputation: 23

Grouping Data in Report Builder and adding a calculated field

I have the following query output from SQL: Query and Report Formats

I have managed to restrain my data for the first columns but I don't know how to show the values as in the example in the last column named "Return"

The logic behind the completion of the "Return" column is the following:

After I group the data by the unique combination Section_ID x Route_ID: If I have a return on the Section_ID the value of the "Return" column should be "Yes", else if there are not return it should be "No".

How can I achieve the report output as in the picture?

Thanks.

Upvotes: 1

Views: 421

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

You can use a LookupSet() and Join() functions to get all returns by Section and Route combination in a string. Once you have all returns you can use the InStr() function to check if there is at least one return and return Yes in that case, otherwise return No.

So I've used the following expression:

=IIF(
InStr(Join(LookupSet(Fields!SectionID.Value & "-" & Fields!RouteID.Value,
Fields!SectionID.Value & "-" & Fields!RouteID.Value,
Fields!Return.Value,"DataSet25"),","),"Yes")>0,"Yes","No"
)

Based on the data returned by your query I've recreated your example, this is the result.

enter image description here

Note there are two rows for Section 4 and Route 26 combination, the expression returns Yes because one of the rows has a return.

Let me know if this helps.

Upvotes: 1

Related Questions