DataSources based on dynamic sql queries
Greetings,
Is it possible to create datasources in runtime based on dynamic sql quires which
are stored as plain text and keeps some hierarchy?
I got huge database with thousands of tables and views.
Each table has millions of rows and some relations.
My datamodel for report is something like:
master dataset 1:
- parent: null
sql:
select a.*, a.id as parent_id
from some_huge_master_table a
where id in (someparameter-from-entry-call)
detail dataset 1.1:
-parent: master dataset 1
sql:
select b.*, b.id as id
from another_huge_table b
where master_id = :parent_id
detail dataset 1.2:
-parent: master dataset 1
sql:
select c.*, c.id as id
from and_another_huge_table c
where master_id = :parent_id
...
Those tables cannot be fetched by Table/View Datasources (they are too big and
somehow dynamic) - but I can say before executing report how this model looks
like (what column and datatypes there are). I just cannot fetch the data
beforehand. And I need to open detail datasets after fetching rows in Masterdataset
because of given relation, detail datasets depend on actual data of master data
rows.
Is this reachable somehow? Is it reachable by standard objects (Table/View
DataSources or by BusinessObjectDatasource)? Or should I prepare my own descendant
of BaseDataSource? Or should I prepare some fake Connection?
Regards,
Martin
Comments
Hello!
When using dynamic SQL queries configured manually outside the designer (for example, when creating a DataSet or TableDataSource in code), nested Master-Detail DataBands work correctly provided that a DataRelation is explicitly defined and both data sources are included:
If the query is parameterised on the TableDataSource side within the .frx file, update the SelectCommand and CommandParameterCollection parameters before calling Prepare(), rather than replacing the table with an incompatible schema.