March 27, 2006 at 4:02 pm
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.
March 27, 2006 at 4:24 pm
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.
March 29, 2006 at 8:23 am
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