Showing posts with label datasource. Show all posts
Showing posts with label datasource. Show all posts

Monday, March 12, 2012

Joining DataSets with Hashtables

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 )

Monday, February 20, 2012

Join Problem

Hello, I have 3 tables in my datasource and I am trying to Join them with the
following
{ oj TABLE2 RIGHT OUTER JOIN
TABLE3 TABLE1 ON TABLE2.ACCTNO = TABLE1.ACCTNO }
At least this is what the designer translates it to. I basically want
Table1 as the primary table (all records) and table 2 and table3 are just
little look up tables for parameters.
When I do this I get an ODBC error,
'Token was not valid: Vaid Tokens: on left join inner joins exception.'
At this point if there is no matching key field in table3 it is not showing
me that record.. And I need to see it. HelpI am accessing an AS400 and it looks like an ODBC problem so I will create
the base query and get rid of the SQL joins.
"SLB" wrote:
> Hello, I have 3 tables in my datasource and I am trying to Join them with the
> following
> { oj TABLE2 RIGHT OUTER JOIN
> TABLE3 TABLE1 ON TABLE2.ACCTNO = TABLE1.ACCTNO }
> At least this is what the designer translates it to. I basically want
> Table1 as the primary table (all records) and table 2 and table3 are just
> little look up tables for parameters.
> When I do this I get an ODBC error,
> 'Token was not valid: Vaid Tokens: on left join inner joins exception.'
> At this point if there is no matching key field in table3 it is not showing
> me that record.. And I need to see it. Help
>|||try creating a view and selecting from that view. I do that on SRS somtimes
when .net tries to rewrite my query to often. That way you can do what you
want on the database side and it seems to run faster also.
SLB wrote:
>I am accessing an AS400 and it looks like an ODBC problem so I will create
>the base query and get rid of the SQL joins.
>> Hello, I have 3 tables in my datasource and I am trying to Join them with the
>> following
>[quoted text clipped - 10 lines]
>> At this point if there is no matching key field in table3 it is not showing
>> me that record.. And I need to see it. Help
--
Gene Hunter
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1