report parameters – using other parameters after selecting a list of values from a list

There are several ways to do this.

I created some test data as an example.

 CREATE TABLE so.ItemData (ItemID INT, ProcessStart date, ProcessEnd date)
 CREATE TABLE so.SomeOtherData (ItemID INT, SomeActionCode varchar(10), SomeDate date)

INSERT INTO so.ItemData VALUES
    (1, '2020-01-01', '2020-01-31'),
    (2, '2020-02-01', '2020-02-28'),
    (3, '2020-03-01', '2020-03-31'),
    (4, '2020-04-01', '2020-04-30'),
    (5, '2020-05-01', '2020-05-31'),
    (6, '2020-06-01', '2020-06-30')

INSERT INTO so.SomeOtherData VALUES
    (1, 'A', '2019-12-31'),
    (1, 'B', '2020-01-15'),
    (1, 'C', '2020-01-16'),
    (1, 'D', '2020-01-25'),
    (1, 'E', '2020-02-01'),
    (1, 'F', '2020-02-02'),
    (2, 'G', '2020-04-01'),
    (3, 'H', '2020-05-01'),
    (4, 'I', '2020-06-01')

To start with. The most obvious way would be to simply join the two sets of data in your dataset query so you only need to pass the ItemID.

So you datset query for your seconds dataset would look something like

SELECT b.*
    FROM
        (SELECT * FROM so.ItemData WHERE ItemID = @pItemID) a
        JOIN (SELECT * FROM so.SomeOtherData) b 
            on a.ItemID = b.ItemID and b.SomeDate BETWEEN a.ProcessStart AND a.ProcessEnd

Another way is by adding hidden parameters to your report.

Add two parameters one for startdate and one for enddate. Call them sDate and eDate. Set the parameter type to Date/Time

Create a dataset called ‘dsItemDetail` with a query something like

SELECT * FROM so.ItemData WHERE ItemID = @pItemID

@pItemID is the name of your report parameter containing the itemid.

Set dsItemDetail as the query for the default values property of both parameters, set the value field property of each parameter to the corresponding field from dsItemDetail.

Optional: Set both parameters to hidden (Once it’s working)

Create a dataset called dsMain

Set the dataset query to

SELECT * 
FROM so.SomeOtherData
WHERE ItemID = @pItemID
AND SomeDate BETWEEN @sDate AND @eDate

Add a table to show the results from dsMain

In this quick report I put together using the sample data above I’ve added two tables. One show the data from the first query and the other shows the data from the second (dsMain). If yo look at the sample data, the values that are not within range are excluded.