Search code examples
reporting-servicesssrs-2008

Select statement inside SSRS textbox

I am trying to assign values to textboxes in SSRS but am running into a bit of trouble.

My dataset "dsStepInfo" groups and sums a number of rows together based on "ID", query below.

SELECT       ID, SUM(Target) AS Target, SUM(Actual) AS Actual
FROM         tblStepComplete
WHERE        (CompleteID = @ParamID)
GROUP BY     ID

And returns:

   ID      Target     Actual
-------------------------------
|  10   |  44418   |  44418   |
-------------------------------
|  12   |  13193   |  13123   |
-------------------------------
|  22   |  1411    |  1411    |
-------------------------------
|  50   |  160     |  80      |
-------------------------------
|  52   |  68      |  34      |
-------------------------------
|  101  |  12120   |  12119   |
-------------------------------
|  105  |  875     |  868     |
-------------------------------
|  140  |  40      |  40      |
-------------------------------
|  560  |  2985    |  3418    |
-------------------------------

Report Grid

I want to assign cells in the grid in the picture to a certain ID. The Cell for tank 110 would always be the Target of ID=50 and Actual of ID=50 for example. Many of the IDs returned aren't going to be populated in the table, just the 10 displayed in this table. Is there any way to perform a SELECT, or equivalent in the textbox as an expression to get these specific values from the dataset?

Solution

  • You can use the Lookup function.

    This should get the ID #50 and output its Target:

    =Lookup(50, Fields!ID.Value, Fields!Target.Value, "DSStepInfo")