LOOKUP question

Question

Hi

In the past and whenever I use the LOOKUP function in an Expression I have used to for a single value like the following

=LOOKUP(Fields!StaffId.Value,Fields!StaffId.Value,Fields!Sum_ChargeableMinutes.Value,"ABSStaffTimeYTD")

This works fine majority of the time but I now could do with doing it on two values if possible?  The values are as follows

Year.Value

Period.Value

The reason being there are 12 periods in a year and there are currently 3 years on the database, if I do the LOOKUP on Year it will only show me the 12 Periods in that year and like wise if I did the LOOKUP solely on Period it only displays the first 12 periods of 2011 it doesnt follow on to 2012.

There is a way around this by just have a table next to it and lining it up with the original table however this means the new table adds an extra 11 perdios for accounting year 2013 as they have been created just nothing entered for them.

I need the LOOKUP function as the data is in two seperate Datasets as I can't get the figures to collate correctly if I join the datasets.

Any help would be appreciated.

Cheers

Chris

Answer 1

Hi Chris,

In Reporting Services, we can use the LookupSet function to retrieve a set of values from the specified dataset for a name-value pair where there is a 1-to-many relationship. In this issue, you need to create a table which contains the Year group, and then get the period values for each year by using the LookupSet function. Because LookupSet returns a collection of objects, we need to concatenate the value of each object in the collection as a string. The expression looks like:
=Join(LookupSet(Fields!Year.Value, Fields!Year.Value, Fields!Periods.Value, “DataSet2”), ”,”)

For your concern about how to eliminate the periods for the year 2013, you can add a filter to the newly created table to filter out the records for 2013.

References:

• LookupSet Function
• How to: Add a Filter

If you have any questions, please feel free to ask.

Regards,
Mike Yin