MasterDetailData with MSSQL& Xailer

[Pedro Faro]

Hi Spirin,

99% of my reports, i do creating a DataSet with the data i want to
list and send it to FR with
the SetXailerDataSet function.

I need to create a report with a detail data , and the examples you
have is all with "xBase set relation function).

In any SQL database we can´t have relation between tables like we can
do in Fivewin with dbf Tables.

How can we send the data to FR to print a table (masterdata) and a
detail table with Xailer with MSSQL tables

Regards

Pedro Faro

[Spirin Sergey]

Hello, Pedro,

I already answered you at this question ???

http://groups.google.com/group/fastreport_for_x_harbour/browse_thread/thread/cdeedb58edb91ddf/51e2a1dc3cdea39d?lnk=gst&q=requery#51e2a1dc3cdea39d

If you do not understand something then, please, make a question
more in detail.

---
Spirin Sergey.
"Paritet Soft" Company.
FRH sales: http://www.paritetsoft.ru/frh.htm
FRAX sales: http://www.paritetsoft.ru/frax.htm

[Pedro Faro]

Hi Spirin ,

In fact at some time , i made the same question , but i still don´t ,
understand. My apologies for that.

In a simple report i use the function SetXailerDataSet:

The dataset contains only one table


function SetXailerDataSet(oFrManager, cFrAlias, oDataSet)
//------------------------------------------------------------------------------
local cStr:='', x

for x := 1 to oDataSet:FieldCount()
cStr+=oDataSet:FieldName(x) + ";"
next

oFrManager:SetUserDataSet(cFrAlias, cStr,;
{||oDataSet:GoTop()}, {||
oDataSet:Skip(1)}, ;
{||oDataSet:Skip(-1)}, {||
oDataSet:Eof()},;
{|cField|
oDataSet:oFieldByName(cField):FieldGet()})

Return Nil


For my case , i'have 2 datasets that have the data ,

What is the expression to link the datasets ?

REgards
Pedro Faro

On 10 Set, 21:04, Spirin Sergey <spi...@paritetsoft.ru> wrote:
> Hello, Pedro,
>
>   I already answered you at this question ???
>

> http://groups.google.com/group/fastreport_for_x_harbour/browse_thread...

>
>   If you do not understand something then, please, make a question
> more in detail.
>
> ---
> Spirin Sergey.
> "Paritet Soft" Company.
> FRH sales:http://www.paritetsoft.ru/frh.htm
> FRAX sales:http://www.paritetsoft.ru/frax.htm
>
> On 9 сен, 20:22, Pedro Faro <psf...@gmail.com> wrote:
>
>
>
> > Hi Spirin,
>
> > 99% of my reports, i do creating a DataSet with the data i want to
> > list and send it to FR with
> > the SetXailerDataSet  function.
>
> > I need to create a report with a detail data , and the examples you
> > have is all with "xBase set relation function).
>
> > In any SQL database we can´t have relation between tables like we can
> > do in Fivewin with dbf Tables.
>
> > How can we send the data to FR to print a table (masterdata) and a
> > detail table with Xailer with MSSQL tables
>
> > Regards
>

> >           Pedro Faro- Ocultar texto citado -
>
> - Mostrar texto citado -

[Spirin Sergey]

Hello, Pedro,

Please, first of all, undestand that SetXailerDataSet() is functtion
of Xailer-level. In this function you describe your dataset for FR. So
you need define "link" of datasets at Xailer-level too. How to do it
more comfotable?

Look at function SetMasterXailerDataSet() from my old post to you.
It's not SetXailerDataSet()!! Look attantive at difference. We add new
parameter bMasterBlock for defining our "link".

//-------------------------------------------------------------------------
-----
function SetMasterXailerDataSet(oFrManager, cFrAlias, oDataSet,
bMasterBlock)
//-------------------------------------------------------------------------

-----
local cStr:='', x
for x := 1 to oDataSet:FieldCount()

if oDataSet:aFields[x]:nSQLType = 205
cStr+=oDataSet:FieldName(x) + "^b;"
else
cStr+=oDataSet:FieldName(x) + ";"
endif

next

oFrManager:SetUserDataSet(cFrAlias, cStr,;
{||oDataSet:GoTop(),

Eval( bMasterBlock)},;
{||oDataSet:Skip(1),
Eval( bMasterBlock)}, ;
{||oDataSet:Skip(-1),
Eval( bMasterBlock)},;

{||oDataSet:Eof()},;
{|cField|
oDataSet:oFieldByName(cField):FieldGet()})
Return Nil

//----------------------------------

Ok. Let's we have two datasets MasterDS and DetailDS. Now we can
write like:
-------------------------
SetXailerDataSet(oFr, "Detail", DetailDS)
SetMasterXailerDataSet(oFr, "Master", MasterDS, {||
DetailDS:SetFilter("bla-bla " + MasterDS:SomeFieldValue()) }
------------------------
or
-------------------------
SetXailerDataSet(oFr, "Detail", DetailDS)
SetMasterXailerDataSet(oFr, "Master", MasterDS, {||
DetailDS:Close(), DetailDS:SetSQLParams(MasterDS:SomeFieldValue),
DetailDS:Open() } )
------------------------
or may be some other variants
-------------------------
SetXailerDataSet(oFr, "Detail", DetailDS)
SetMasterXailerDataSet(oFr, "Master", MasterDS, {|| Type here your
variant! ) } )
------------------------


I think idea is clear?

---
Spirin Sergey.
"Paritet Soft" Company.
FRH sales: http://www.paritetsoft.ru/frh.htm
FRAX sales: http://www.paritetsoft.ru/frax.htm

[Pedro Faro]

Hi Spirin,

The ideia is clear now how to link the 2 datasets. Tanks a lot .

I define,

//cod_ctc is the field that i need to link

SetXailerDataSet(APPDATA:oFastReport , "Detail", aData2)
SetMasterXailerDataSet(APPDATA:oFastReport, "Master", aData, {||
aData2:SetFilter("cod_ctc='" + aData:cod_ctc+"'") })

and in FR

It returns : BASE/1081 Argument error +


I think the expression is correct

REgards.

> > > - Mostrar texto citado -- Ocultar texto citado -

[Spirin Sergey]

Hello, Pedro,

Is cod_ctc numeric field? May be:

...:SetFilter("cod_ctc="+ STR(aData:cod_ctc))....

Pedro, please understand, FR eval your code block only. So it can be
your error only (at xHarbour/Xailer level of defenition of your
codeblock )

---
Spirin Sergey.
"Paritet Soft" Company.
FRH sales: http://www.paritetsoft.ru/frh.htm
FRAX sales: http://www.paritetsoft.ru/frax.htm

[Pedro Faro]

Hi Spirin,

My expression is ok (is Varchar).


I think the problem is in the SetMasterXailerDataSet Function.

I send to FR

SetMasterXailerDataSet(APPDATA:oFastReport, "Master", aData, {||

msginfo(aData:cod_ctc) }) // Prints master's cod_ctc


...{|| aData2:SetFilter("cod_ctc='"+aData:cod_ctc+"'") }


After it print 's the masterdata line , "cod_ctc" becames NIL , and it
compares with the expression in SetFilter() and returns error.

I'm not familiarizied with CodeBlocks , and i'm not be able to see the
real problem. I tink we need to test if EOF somewhere .

Regards

Pedro

[Pedro Faro]

Hi Spirin,

I think the Xbase problem is solved.

By Logic , it seem that misses the eval after testing "eof()" in
SetMasterXailerDataSet

.... {||oDataSet:Eof() , Eval( bMasterBlock)},;


the problem now is other:

i send to FR:
{|| aData2:SetFilter("cod_ctc='"+aData:cod_ctc+"'") }) //because
cod_ctc is Varchar , ithink i need to STring It like i do in SQL
Commands.

and FR returns:

"Could not convert variant of type (String) into type (Boolean)

I try with all method's

{|| aData2:SetFilter("cod_ctc="+aData:cod_ctc) }) //with quotes


What is the correct syntax ?

[Spirin Sergey]

Hello, Pedro,

> .... {||oDataSet:Eof()   , Eval( bMasterBlock)},;

No, no, no! But you are right about additionals checking
Eof() :))

Ok. I has tested function, correct version:

function SetMasterXailerDataSet(oFrManager, cFrAlias, oDataSet,
bMasterBlock)

local cStr:='', x
for x := 1 to oDataSet:FieldCount()
if oDataSet:aFields[x]:nSQLType = 205
cStr+=oDataSet:FieldName(x) + "^b;"
else
cStr+=oDataSet:FieldName(x) + ";"
endif
next
oFrManager:SetUserDataSet(cFrAlias, cStr,;

{||oDataSet:GoTop(), if(!oDataSet:Eof(),
Eval(bMasterBlock), )},;
{||oDataSet:Skip(1), if(!oDataSet:Eof(),
Eval(bMasterBlock), )},;
{||oDataSet:Skip(-1), if(!oDataSet:Eof(),
Eval(bMasterBlock), )},;

{||oDataSet:Eof()},;
{|cField|
oDataSet:oFieldByName(cField):FieldGet()})
Return Nil

I has tested it at \xailer\Samples\DataControls\ADOData3 sample. Such
code works OK:

METHOD Button1Click( oSender ) CLASS TForm1
LOCAL FrPrn
FrPrn := frReportManager():new()

SetXailerDataSet(FrPrn, "Orders", ::oSqlOrders)
SetMasterXailerDataSet(FrPrn, "Clients", ::oSqlClients,;
{|| ::oSqlOrders:SetFilter("IdCliente='"+::oSqlClients:IdCliente
+"'") } )

FrPrn:DesignReport()

RETURN Nil

---
Spirin Sergey.
"Paritet Soft" Company.
FRH sales: http://www.paritetsoft.ru/frh.htm
FRAX sales: http://www.paritetsoft.ru/frax.htm

[Pedro Faro]

Hi Spirin ,


Thank you so much . it's now working. --)

One more thing in the report i have to do .

I need (for example), a report like this

Masterdata :

111111 A
111111 B

MasterDetail

111111 10 10 10
111111 10 10 20

If i define in FR studio


Master data

Put the fields here

Master Detail
Put the fields here

It Prints

1 Master data
N master details
...

How can be defined to print

All Masterdata of the same code
MasterDetail of the code of Masterdata

> ...
>
> mais informações »- Ocultar texto citado -

[Spirin Sergey]

Hello, Pedro,

> Thank you so much . it's now working. --)

Ok. :)

> One more thing in the report i have to do .

Ok, but firstly about terminology

>If i define in FR studio

Do you use "FR studio" or FRH(FR for xHarbour)? I think FRH, FR
studio is different product.

>1 Master data
>N master details

MasterDetail is relation - one-to-many. Master data can exists, detail
data can exists, but MasterData is not "data" but db-relation.

>How can be defined to print
>All Masterdata of the same code
>MasterDetail of the code of Masterdata

You need "Groups with special SubReport-footers"

Place groups-bands and so on. On GroupFooter place SubReport. In
SubReport place second MasterBand, at GroupFooter-OnBeforeBrint event
handle :SetFilter(...) at Xailer-side.

---
Spirin Sergey.
"Paritet Soft" Company.
FRH sales: http://www.paritetsoft.ru/frh.htm
FRAX sales: http://www.paritetsoft.ru/frax.htm

> > > > > > > > > Hello, Pedro,...
>
> продолжение »

[Pedro Faro]

Hi Spirin,

Sorry the terms:

I thought the "designer mode" in FR was called Studio.

I think it 90% done.

I create the group footer and place the subreport there. i think the
only thing that is missing is the "filter"

Can you detail :

..at GroupFooter-OnBeforeBrint event
> handle :SetFilter(...) at Xailer-side.

Regards

pedro

> > > > > > > > > In fact at some time , i made the same question , but i still don´t ,- Ocultar texto citado -
>
> - Mostrar texto citado -...
>
> mais informações »

[Spirin Sergey]

Hello, Pedro,

> I thought the "designer mode" in FR was called Studio.

:)) No, "FR Studio" is a different product, it's a COM-oriented
variant of FR.

> Can you detail :
>
> ..at GroupFooter-OnBeforeBrint event
>
> > handle :SetFilter(...) at Xailer-side.

Ok. Create OnBeforeBrint of GroupHeader (not Footer, sorry). For it,
select GroupHeader, select "Events" at ObjectInspector, and double
click at empty space at "OnBeforeBrint". FR create code for you like:

procedure GroupHeader1OnBeforePrint(Sender: TfrxComponent);
begin

end;

Now, 2 way. You can use EvalMacro() to set filter, in this case you
only type script like:

-------------
const
FilterStr =
'MyForm:oSqlOrders:SetFilter("IdCliente=''%s''")';

procedure GroupHeader1OnBeforePrint(Sender: TfrxComponent);
begin
EvalMacro(Format(FilterStr, [<Clients."IdCliente">]));
end;
--------------

Or you can prefer to write Xailer-code for it. In this case you can
use CallHbFunc(), like:

procedure GroupHeader1OnBeforePrint(Sender: TfrxComponent);
begin
CallHbFunc('MyXailerFunction', [<Clients."IdCliente">]);
end;

---
Spirin Sergey.
"Paritet Soft" Company.
FRH sales: http://www.paritetsoft.ru/frh.htm
FRAX sales: http://www.paritetsoft.ru/frax.htm

> > > > > > > > > DetailDS:Close(),...
>
> продолжение »

[Pedro Faro]

Hi Spirin,


It's working perfect. Many thanks for your support.


Best Regards

Pedro Faro

> > > > > > > > > >     for x := 1 to oDataSet:FieldCount()...
>
> mais informações »