Creating a reference to a data value in another table based on an ID?

  • I see a way to accomplish but the method is kind of cludgy and I was hoping someone else has done this successfully more like I want. I first off do this in an ASP generated report myself which works great but I am wanting to move the whole thing to RS for SQL 2k.

    I have a report with multiple datasets and generate multiple tables. What I need is the details value of a specific row/column based on an ID value which is in the dataset.

    Ex.

    Table 2

    A         B         C            (ID which is not seen on the report)

    Test1   1         2             1

    Test2   8         5             2

    Test3   4         1             8

    Test4   7         5             15

    Total    20       13

    Table 8

               Escalation

    A         1st       2nd          3rd         % Performed In Escalation    (ID matches as previously)

    Test1    0         1             0            100%

    Test2    2         2             1            62.5%

    Test3    1         0             2            75%

    Test4    2         0             5            100%

                25%     15%         40%

     

    Now in Table 8 the bottom row is the total for the escaltion row / the total for column B in Table 2. I have this. But % Performed In Escalation  is the total for the Escalations row / the coresponding value in column B of Table 2. I am looking for how to get "the coresponding value in column B of Table 2" thru a reference or to Table 2 or other better than what I am currently doing, especially since the list can grow and the method I am currently using relies on it being static?

    Hopefully this makes sense and someone has come across it. BTW I do know how to reference the last value in Table 2 Column B that isn't the total line but that doesn't complete what I need to know.

     

    Thanks in advance for any help. Also, I know I haven't said how I am doing it now with the report as I want to see if anyone else is doing the same.

  • Spoke too soon. I think I found a way that meets my needs. However could be I missed an easier solution so still post if you think you have a potential solution.

  • In case anyone has the same need here is what I did.

    In the report I opened the Reports properties dialog and selected the Code tab. Then I inserted the following code: (Note first revision so may go back and sharpen a bit later)

    Dim arX(1,0)

    Function SetValue(valID, valAmt)

     If IsArray(arX) Then

      Dim x

      x = UBound(arX,2) + 1

      

      ReDim Preserve arX(1, x)

      arX(0, x) = valID

      arX(1, x) = valAmt

     End If

    End Function

    Function GetValue(valID)

     Dim x

     For x = 0 to UBound(arX,2)

      If arX(0,x) = valID Then

       GetValue = arX(1,x)

       Exit Function

      End If

     Next

    End Function

     

    From there in Table 2 when I am outputing the column A value I do like so.

    Expression: =Fields!A.Value & Code.SetValue( Fields!Col_ID.Value , Fields!B.Value)

    Which allows me to store the data I need (note: before you answer I should have joined or anything Table 2's data is not matching what I have, what I needed was to get the calculated value of serveral columns and to build the data for Table 2 takes 351 Lines of query which I don't want to have to run again for table 8).

    Then in Table 8 I do the following

    =(Fields!V1st.Value + Fields!V2nd.Value + Fields!V3rd.Value) / (Code.GetValue( Fields!Col_ID.Value))

    Which allows me to retrieve the stored value and bring into my calculation for the "% Performed In Escalation" column in that table.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply