Hi All,
I'm quite new to SSRS (and .net development as well) so any help would be useful. My problem is this: I'm running a report from a Sybase ASE datasource to pull data into a table control. One of the fields I'm pulling in is called assignment group. Now, in the same report output I also need to display who the manager of that group is... unfortunately this information is not stored in the same database. We store the manager information in a completely seperate Oracle database. So, I need some method of 'linking' the data from my Sybase dataset to a table in Oracle and I can't quite figure out how to go about it. Every record would have a manager and there could be 10k records returned so I need to be carefull about performance issues.
I was starting to go down the path of creating a .net dataset in a custom assembly which would be filled via a query to the Oracle database. What I was thinking is to create and fill a dataset with my lookup data from Oracle then, in the report, call a function from the table control which would search through the dataset in memory and return the actual manager name.
Am I on the right track or am I overcomplicating the solution? Will this be too inefficient from a performance perspective? Would a subreport be a more efficient solution?
There are a couple of solutions for you:
1) you can write a custom data extension that does the magic merge under the covers. A custom assembly won't help here because it operates on one row at a time - if you have a reasonable nuumber of rows, it might work, but with lots of rows it will probably be inefficient.
2) you can use the Linked server feature of SQL Server. This allows you to build a table/view that uses ODBC connections to other data bases to retrieve data. It has pretty reasonable performance and you don't have to create and maintain custom code. Downside, is you need to add a SQL database in addition to your existing Oracle and Sybase ones :-).
Hope that helps,
-Lukasz
No comments:
Post a Comment