Really Advanced End User Report Designing
I saw a previous thread on this and looked at the demos, but they are
too simplistic. I want to design a data view with custom SQL and
linking. I also would like to specify an autosearch field when using
custom SQL.
Is it possible to specify an autosearch field in the SQL? Example:
select * from customer where customer_name like :CustomerName would
prompt the user for CustomerName. Maybe ? would be better than : to
allow for custom master/detail linking. See below.
I would also like to be able setup master/detail links using a
parameterized query.
Query 1 on the data tab would have something like:
select state_cd from state
Query 2 on the data tab would have something like:
select * from customer where exists (select 1 from Orders where
Destination_State_Cd=:State_Cd)
This touches on a problem I'm having now. I need to design a report
using only the end user designer with the following queries.
Query 1:
select distinct Practice from Physician
Query 2:
select CS.*
from CLIENT_SERVICE CS
where exists (select 1
from CLIENT_DIAGNOSIS CD
left join PHYSICIAN P1 on CD.TREATING_PHYSICIAN_ID=P1.PHYSICIAN_ID
left join PHYSICIAN P2 on CD.RADIATION_PHYSICIAN_ID=P2.PHYSICIAN_ID
where CS.CLIENT_ID=CD.CLIENT_ID
and (P1.PRACTICE=:PRACTICE or P2.PRACTICE=:PRACTICE))
The first query is Query Designer compatible. The second query requires
editing the SQL. How can I link these two?
too simplistic. I want to design a data view with custom SQL and
linking. I also would like to specify an autosearch field when using
custom SQL.
Is it possible to specify an autosearch field in the SQL? Example:
select * from customer where customer_name like :CustomerName would
prompt the user for CustomerName. Maybe ? would be better than : to
allow for custom master/detail linking. See below.
I would also like to be able setup master/detail links using a
parameterized query.
Query 1 on the data tab would have something like:
select state_cd from state
Query 2 on the data tab would have something like:
select * from customer where exists (select 1 from Orders where
Destination_State_Cd=:State_Cd)
This touches on a problem I'm having now. I need to design a report
using only the end user designer with the following queries.
Query 1:
select distinct Practice from Physician
Query 2:
select CS.*
from CLIENT_SERVICE CS
where exists (select 1
from CLIENT_DIAGNOSIS CD
left join PHYSICIAN P1 on CD.TREATING_PHYSICIAN_ID=P1.PHYSICIAN_ID
left join PHYSICIAN P2 on CD.RADIATION_PHYSICIAN_ID=P2.PHYSICIAN_ID
where CS.CLIENT_ID=CD.CLIENT_ID
and (P1.PRACTICE=:PRACTICE or P2.PRACTICE=:PRACTICE))
The first query is Query Designer compatible. The second query requires
editing the SQL. How can I link these two?
This discussion has been closed.
Comments
Check out the custom dataview examples, RBuilder\Demos\EndUser\Custom
DataViews. Run the example, access the Data tab and select File | New...
The dialog contains a Templates tab that contains custom dataviews. Custom
DataViews can contain multiple datapipelines and you can link either the
datapipeline or datasets.
--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Is linking the following queries in a custom data view the only way to
link them? There is no way to link them on the Data tab?
Query 1:
select distinct Practice from Physician
Query 2:
select CS.*
from CLIENT_SERVICE CS
where exists (select 1
from CLIENT_DIAGNOSIS CD
left join PHYSICIAN P1 on CD.TREATING_PHYSICIAN_ID=P1.PHYSICIAN_ID
left join PHYSICIAN P2 on CD.RADIATION_PHYSICIAN_ID=P2.PHYSICIAN_ID
where CS.CLIENT_ID=CD.CLIENT_ID
and (P1.PRACTICE=:PRACTICE or P2.PRACTICE=:PRACTICE))
- Once you manually edit the SQL Text, the visual drag-and-drop linking is
not supported
- the DBPipeline.AddLink method can be used to programmatically define
links. This method can be called from Delphi code and from RAP code. You can
use the Report.BeforeOpenDataPipelines event to do something like
myDetailPipeline.AddLink(myMasterPipeline, 'MasterFieldAlias',
'DetailFieldAlias');
- for DataPipeline linking to work, the detail query must be sorted on the
linking field(s).
--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com