Hello to All,

I've to create a Report for sales quotations (tdsls100). I also Need to get the related Quotation lines for each Quotation. For that I've created in the Report script a dynamic SQL structure like:

before.program:
SQL.id = SQL.parse("SQL-Statement")

after.program:
SQL.close(SQL.id)

detail1:
before.layout:
SQL.exec(SQL.id)

detail2:
before.layout:
SQL.fetch(SQL.id)

after.layout:
layout.again()


But now I Need the related Quotation lines for creating an IER-File.
So I have to create a subquery for the session or program script within the form Editor. But there I've not the possibility with dynamic SQL and "layout.again()".
How can I program a Sub query with the necessary results for all related Quotation lines in the Output?
I've used a separate function but it doesn't works. Only one (the last one) Quotation line was associated to each Quotation in the Output.

Thanks in Advance.

Best Regards
Bernd

:-o how can I deactivate the thumbs down Icon?

Hi,

Is this required for SQL Query or print session? If its a customized print session, the you can use normal select statements.

select tdsls100.*,tdsls101.*
from tdsls100,tdsls101
where tdsls101._index1 inrange {:qono.f} and {:qono.t}
and tdsls101._index1 refers to tdsls100
selectdo
rprt_send()
endselect

This will print all the lines of the quotation. You won't need a report script for this.

Hi Bhushan,

Thanks a lot for help!
You're right, it is for the print session!
This rprt_send() I've overseen and it seems to be the Point I've missed.

But now I have the following Situation:
after using your select, I will get 2 Positions back. But these positions are identical. Only Position 20 is send as result but twice. Position 10 I've still missing.
The full data set will be send twice. But I'd like to have only once but with both positions. Is that possible?

Here I write my code, maybe I've still an error inside?:

| ********** Call of the Main function *******************
choice.print.data:
on.choice:
if rprt_open() then
read.main.table()
rprt_close()
else
choice.again()
endif

| ********* function section select quotations ***************
function read.main.table()
{
select tdsls100.*,tdsls101.*
from tdsls100,tdsls101
where tdsls101._index1 inrange {:qono.f} and {:qono.t}
and tdsls101._index1 refers to tdsls100
order by tdsls100._index1
selectdo
rprt_send(|)
endselect
}


Bhushan, maybe you have a further idea for me?

Best Regards
Bernd

Hi,

If you simply use the code I gave in the session,you will just need header and a detail layout without any report script. Make sure you don't have any report script. Also, check what fields are added in the layout.

Debug the code and for each execution of rprt_send() check the position numbers. I am assuming that you have after.field layout instead of detail layout and hence only one row is printed for one quotation.

Hi Bhushan,

sorry, I made still some changes to my reply. And you was so fast in answering :-).
I've got the full data set twice.
I've checked the data set not with the Report by printing to device "D". I've printed by device "IER_DESIGN" as an XML-File. Here I'll get the data set as selected from the print session. There I have no influences by the Report script or layout.

Best Regards
Bernd

Hi Bhushan,

now I also changed my Report script. After recompiling the select Returns the values as expected.

Thank you for help a lot.

Best Regards
Bernd

Glad to help! Can you explain what was the issue so that anyone else facing similar issue may get an idea.

Hi Bhushan,

sorry for correction. I think your select is also not my required solution.
I try to explain it in other words for clarification also for getting my issue or aim:

I've one or more quotations. For each Quotation I can have one or more Quotation lines (= items/positions).

In the first select I've got (Version1):
quot1 / pos1 => one Position for that quotation
quot2 / pos2 => two Positions for that Quotation (only the last one will be shown)
quot3 / pos1 => one Position for that quotation
....

In the select you offerd me I've got (Version2):
quot1 / pos1 => one Position for that quotation
quot2 / pos1 =>
quot2 / pos2 => Quotation2 will be shown for each Position.
quot3 / pos1 => one Position for that quotation
....

The issue/aim I Need (Version3):
quot1 / pos1
quot2 / pos1, pos2
quot3 / pos1
....

or Version3 in another layout:
quot1
- pos1
quot2
- pos1
- pos2
quot3
- pos1
.....

Sorry for insufficient Explanation. I hope the upper Explanation is more helpfull.

Best Regards
Bernd

Hi,

Do you mean you want to print like this -

Header Detail
Line Detail

e.g.

Quotation 1
Line 1
Line 2
...

Quotation 2
Line 1
Line 2
..

If this is the case, then you need to add 3 layouts to your report.

First create the Header Layout with your labels and then follow the following steps -


1. Make the Sorting of the report input field tdsls100.qono as Ascending
2. Create a before.field layout with Header fields i.e. Fields from tdsls100 table
2. Create a detail layout with the line fields.

Now, keep the selection same in program script and try again.

Please post a screen shot of your layout to verify if its fine.

I hope I am assuming it right.

Hi,

Here are some snapshots for your reference.

Hi Bhushan,

yes, that layout is exactly what I like to have. But not only in the Report. As mentioned in my first thread Position: I've could do that for reports with dynamic SQL and layout.again().
But when I don't use the Report tool and Export the data sets out of the print session into a XML-file (with the IER_DESIGN-Printer) then I Need another query in the print session.

Another explanation:
VersionA:
1.) print session with form editor: selection of Quotation (Nr. / Id)
2.) Report tool: selection of Quotation lines by dynamic SQL and layout.again()
=> this works fine!

VersionB:
1.) print session with form Editor: selection of Quotations and the related lines.
2.) Printing as XML-File: no further Manipulation possible! Data set have to be completed in the print session.
=> now I Need the data sets in the layout we've discussed above for this VersionB.

In the print session tdsls1419m000 there it seems to work. But for me it's not totally clear what happens there. There it's possible to Show Details for the Quotation lines in an additional tab. In the form Editor for the progr.- script the programmers made a function call on library tccom.dll0010. But I've thought it should be possible within a normal SQL-Statement.

Best Regards
Bernd

Hi Bhushan,

thanks for the snapshots. I've answered in the thread line above.
Yes that what I like to get but not only for Report also for XML-File. See my other post.

Best Regards
Bernd

Hi,

Are you printing the output to your SSRS designer device? You need to define Groups in SSRS designer. Check this thread (http://www.baanboard.com/baanboard/showthread.php?t=66915) I guess this is the exact problem you are facing.

Also, there is this KB 1474731 on InforXtreme which has a solution for the same.

I am not sure if you can suppress the data as SSRS will send all the fields everytime (at least without the solution which I have not checked as I don't often work on SSRS)

Here is the way to define groups in SSRS (BIDS) Link (http://selectstatements.blogspot.com/2013/01/repeating-headers-in-ssrs.html)

Also, these links -

Link 1 (https://msdn.microsoft.com/en-us/library/dd220467.aspx)
Link 2 (https://msdn.microsoft.com/en-us/library/ms170712.aspx)

Hello Bhushan,

yes I'd like to print the Report out of SSRS cause we Need additional data out of another data source. And with SSRS it is possible to connect to another data base.

Thanks a lot for your advices. I will check these links.

Best Regards
Bernd

:-o how can I deactivate the thumbs down Icon?

Not sure you can once you set it on a thread. But a moderator can take it off by editing the thread.