Parameters in a user defined query

rbuser

Hello,

For a report I need to have a complex query:

SELECT DM_DocumentNr, DM_DocumentDatum, DM_Omschrijving, DM_Bedrag,
DM_BetaaldBedrag, DM_DE_ID, DM_Bedrag + DM_BetaaldBedrag AS [Open]
FROM DebiteurenMutaties inner join
(SELECT DM_betaaldocumentnr, sum(DM_Bedrag + DM_BetaaldBedrag) AS [Open]
FROM DebiteurenMutaties
WHERE (DM_DocumentDatum <= CONVERT(DATETIME, '2008-12-31 00:00:00',
102)) AND (DM_BE_ID = 4)and (DM_betaaldocumentnr is not null)
Group by DM_betaaldocumentnr) as debopen on
debiteurenmutaties.dm_id=debopen.dm_Betaaldocumentnr

union

SELECT DM_DocumentNr, DM_DocumentDatum, DM_Omschrijving, DM_Bedrag,
DM_BetaaldBedrag, DM_DE_ID, DM_Bedrag + DM_BetaaldBedrag AS [Open]
FROM DebiteurenMutaties
WHERE (DM_Betaald = 0) AND (DM_DocumentDatum <= CONVERT(DATETIME,
'2008-12-31 00:00:00', 102)) AND (DM_BE_ID = 4)and (DM_betaaldocumentnr is
null);

I want the date to be an autosearchcriteria, how can I do that in RAP?

Regards,
Rob Nowee

nicocizik

Hi Rob,

ReportBuilder 11 now includes the parameterized query support for Dade. You
will need to create the parameter initially using the ParametersEditor
dialog of the report, then enable and define its AutoSearch properties and
search on it in your custom query.

WHERE (DM_DocumentDatum <= :MyDateParam)

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hello Nico,

I have set parameters for the report, f.i. MyDateParam, Bedrijf.
But when I try to use them in my query it says:
"No report parameter found for Bedrijf " or "No report parameter found for
MyDataParam"
Also I do not see them in the param list when making a autosearch critria.

Best regards,
Rob Nowee

nicocizik

Hi Rob,

Are you creating the parameters using the Parameters Editor? In my testing,
simple adding a parameter to the list gives you the option to search on that
when a search criteria is added. Looking at your SQL code however, it looks
like you are using custom SQL and therefore will need to set the AutoSearch
properties of the parameter itself and reference it manually.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hello Nico,

I removed the parameters I had on the ppreport, they were set in version 10,
then I added them again. I now see them when designing in designtime.
however I have a ppdesginer on the form with the report set to ppreport, als
I have a ppreportexplorer with the designer set to ppdesigner.
In runtime I call ppreportExplorer.execute, if I then edit a report I do not
see my parameters.
What can I do about it? I use the parameters also to show in the report.
when running a report that does work so then the parameter is passed.
Only when designing through the ppreportexplorer it does not work. Any
suggestions?

Best regards,
Rob Nowee

nicocizik

Hi Rob,

Are the parameters not showing up when trying to create a search criteria in
DADE or are you not able to access them through the parameters editor in the
designer? If you try editing one of these reports at runtime using only a
TppReport and TppDesigner object, can you see the parameters created then?
Try using the Report Tree (View | Toolbars | Report Tree... in the main
menu) to see the parameters tied to the report. In my quick testing with a
report created in RB 10.x, I was able to see pre-defined parameters in RB
11.

Back to your original question, are you successfully able to create an
autosearch parameter and use it in your custom SQL statement to search on
that query?

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hello Nico,

If I make a project, with an report, a designer and a explorer and I put in
the report through the parameters editor some parameters
I can see them when in designtime call the editor on the report,
In Runtime when i call desinger.showmodal i can see the parameters also.
But when I go and edit a report (or make a new one) using the
ppreportexplorer it does not show me the parameters.
In the Report Tree it shows me no parameters, I can make one there but it
does not show me the paramters I made with the parameters editor.

I made the parameter in the Report Tree (rightclick new) it seems to work
with an integer (param set to integer) but when i make a parameter DatumTM
of the type dtDate and in my query say
(DM_DocumentDatum <=:DatumTM) and then klik on OK I get the message: Could
not convert variant of type string into type date. When I make it an string
it works well.
When Designing the report en klikking on the lookingglas in the preview it
ask me the params.
however when opening the report outside the designer it does not ask met the
params. Can it be because I make some params in code?

I uploade my testing program in a zip file to
www.fsp.eu/downloads/rbtest.zip, in the dir there is a backup
from a sql database wich you can attach to.
you can see that when using button1 (reportexplorer.execute) you cannot see
the parameters
when using button2 (reportdesigner.showmodal) you can see the parameters.

Best regards,
Rob Nowee

nicocizik

Hi Rob,


We fixed an issue similar to this for RB 11.03. Try upgrading and test with
that.



Report parameters are not global, they are tied to each individual report.
If you would like to use report parameters on a new or existing report, they
will need to be created either using the parameters editor or in code for
that specific report.


--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico

How about:
When Designing the report en clicking on the lookingglas in the preview it
asks me the params. (I made in the report tree) however when opening the
report outside the designer it doesn't ask met
the params. Can it be because I make some params in code?

Regards,
Rob Nowee

nardmoseley

Do you set Parameter.AutoSearchSettings.Enabled to True?

When creating the Params via code, are you using the
Report.OnInitializeParameters event?


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hello Nard,

I set the Parameter.AutoSearchSetting.Enabled to True,
The Parameters I create by rightclicking on the report component and then
choosing for paramterseditor.
When Creating my datamodule I fill the parameters with values.
See code Below

Procedure TdmReport.SetParameters;
begin
ppReport.Parameters['BedrijfsID'].Value := ActiveAdministratie.BedrijfsId;
ppReport.Parameters['Gebruiker'].Value := ActiveUser.Naam;
ppReport.Parameters['Bedrijfsnaam'].Value := ActiveAdministratie.Naam;
ppReport.Parameters['DatumVan'].Value :=
ActiveAdministratie.BeginBoekjaar;
ppReport.Parameters['DatumTM'].Value := ActiveAdministratie.EindBoekjaar;
end;

Regards,
Rob Nowee

nardmoseley

Conceptually there is no difference in using the Desigenr or code.

The Report.Parameters are part of the report definition.

Whether report definitions are created via the Designer or via code - it is
all the same. A Report is made of objects - Parameters, Bands, Label,
DBTexts etc. The Designer is a component editor for creating and configuring
the properties.

You might try creating the report using the Designer and then save it as
Ascii text to an .rtm file. Then open it in NotePad or the Delphi editor and
you can see the property values. That might might help you write the code to
do the same thing.

If you would like to create a simple example project that we can build and
run here, we can check it out. Use only standard Delphi components and RB.
Use the DBDemos data or an Access db. Send in zip format to
support@digital-metaphors.com.




--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hi Nico,

Where can I get this 11.03 update?
I only got an email for downloading the 11.02 update.

Regards,
Rob Nowee

nicocizik

Hi Rob,

Send an email to info@digital-metpahors.com with your serial number and
purchasing email address and we'll send the information you need to update.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com