I have Fact data (Failures by Resource) from DataSource A, and
Dimension data (Owner of Resources) in DataSource B, where A and B are
different Oracle servers. Linked Servers are not an option for at
least 3 months, if ever. These sources return DataSets A and B.
I need to develop a report where I total the Failures from A "Grouped
By" the Owner of a Resource from B. Very simple lookup.
If I use the Report custom Code, I can push the Resource/Owner mapping
into a (Public global variable) Hashtable, and pull it out in the
Details of a table. Data is pushed using a Table using DataSet A with
Detail rows that call a SetData(ID,Owner) function with a non-Visible
Details row. Data is retrieved in a separate table based on DataSet B
using a GetData(ID) function.
But:
* GetData() only appears to function in Expressions in the Details of
a table, even though GetData() is referencing the Hashtable.
* I cannot sort a Table on the GetData() results, data comes back in
random order
* I cannot Group by the GetData() results, the field appears to be set
to the Owner of the first ID (not for each row in the DataSet!!)
* I can add a Calculated Field to DataSet A that calls SetData(), and
it appears to work (I have SetData return the Owner, so I can see the
function is called), but if I add a Calculated Field to DataSet B that
calls GetData(), all the field contents are Nothing.
I am running out of ideas here. Why is an action so common made so
difficult in SSRS?
-- Scott-- Report Properties -> Code Pane
--
Public Shared mapping As New System.Collections.Hashtable()
Public Function StoreValue( ByVal dataSetName As String, ByVal
fieldName as String, _
ByVal idName As String, ByVal fieldValue as Object ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If Not mapping.ContainsKey( hashName ) Then
mapping.Add( hashName, fieldValue )
End If
Return fieldValue
End Function
Public Function GetValue( ByVal dataSetName As String, ByVal fieldName
as String, _
ByVal idName As String ) As Object
Dim hashName As String
hashName = dataSetName & "." & fieldName & "." & idName
If mapping.ContainsKey( hashName ) Then
Return mapping( hashName )
Else
Return Nothing
End If
End Function
-- Report Body, Table Object --
-- Table 1, Details Section --
DataSet dsNames
=Code.StoreValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value,
Fields!ORGNAME.Value )
-- Table 2, Details Section --
DataSet dsResources
=Code.GetValue( "dsNames", "ORGNAME", Fields!RESOURCEID.Value )
No comments:
Post a Comment