SSRS Report Parameters - populating other parameters after selecting from a list

Question

I have a menu page that has a query that retrieves a list of item records for a given date range.

The query returns the item ID, a start date, and an end date.

The coil ID populates a list. The user will select an item from that list and make a report selection.

What needs to happen, is that when the user selects a report to run, the start date and end date of the item selected needs to be passed to the selected report.

I set up two extra parameters that pointed to a query that used the selected item id to retrieve the start and end time for that item. This sort of worked, but did not retrieve the proper start and end times.

I feel I should be able to grab the start and end times for the selected item from the original query as they are returned in the result set.

I want to populate the start date and end date parameters with the values for item the user selected from the list.

I am new to SSRS and am not sure the best way to do this.

======================================================= Update

Not sure how much more clear I can make it.

I will use different terminology for the dates as it may be confusing having both the main query, and the items that are returned contain a start and end date.

I have a query that retrieves data based on a date range the user selects. This date range is only used to limit the results of the main query.

The result set includes the fields: ItemID, ProcessStart, ProcessEnd.

The itemID represents something we want to get performance data on from a separate system. Every item has a date processing started, and a date that processing ended.

I use the ItemID field to populate a list. The user will select the item they want from this list that they want to run additional reporting on. The additional reports, however, require the date range for that specific item they selected(i.e. ProcessStart, ProcessEnd) the ProcessStart and ProcessEnd needs to be passed to those reports.

I need to know how to get the ProcessStart and ProcessEnd for the item that the user selected.

Since this information is returned in the initial query, I thought it would be easy to access once the user makes their selection. I did not see a way to directly access that data from the list. The list only contains the ItemID. I thought that the list would allw the addition of hidden values to tie result columns to.

I set up a second query that uses the value from the list parameter to get the ProcessStart and ProcessEnd of the selected item and added two paramters called ProcessStart and ProcessEnd to hold these values. My first attempt did populate those parameters, but they were always from the first Item no matter what I selected from the list. I then added a filter to the second query to limit the results using the selected value from the list. That also always returns the values from the first item in the list no matter what item is selected in the list.

I tried using the lookup function in the parameter, but the error that I get makes it appear that you cannot use fields when doing a lookup from a parameter. I could maybe try using a lookup function when populating the parameters to call the other report, but I am thinking I will get the same error.

============================================================================= a bit more illustration:

I have the following parameters:

Parameters

The following datasets:

Datasets

GetCoilList dataset query:

SELECT 
      [CoilId]
      ,[StartTimestamp]
      ,[FinishTimestamp]
  FROM SomeTable

WHERE StartTimestamp BETWEEN @StartDate AND @EndDate

@StartDate and @EndDate come from the first two parameters. These two parameters are used to get a list of items for a given time range.

GetCoilTimes recordset query:

SELECT 
    [CoilID]
      ,[StartTimestamp]
      ,[FinishTimestamp]
  FROM SomeTable

WHERE CoilID = @CoilID

@CoilID is the value selected from the Coil parameter by the user.

The coil Parameter default value:

CoilID Parameter

The Coil list gets populated properly, and I can select an ID from the list.

Start Parameter default value: (End parameter set up similarly)

Start Parameter

Start and end do get populated, but they always get populated with the dates from the first item in the list.

Answer 1

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.