Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Administration & Installation

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
37%
Installation Wizard into new VRC
39%
Manual into existing VRC
3%
Manual into new VRC
21%
Total votes: 38

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 5th December 2016, 04:29
shahromi shahromi is offline
Member
 
Join Date: Mar 2014
Posts: 45
shahromi is on a distinguished road
Baan: 10.8.2 - DB: oracle - OS: windows2008 server
To display max & min date at report layout
Baan: ERP LN 6.1 FP7 (Infor ERP 10.1)
DB: Other/Unknown
OS: Other/Unknown
C/S: None/Unknown

Hi All,

I want to display the oldest & latest date in report header which is TDSLS400.ODAT.

I found that there is 1 function called Aggregate Function in report layout.
Try use this function but it's display nothing.

Is there any way to display the MAX & MIN date.

Thank You.
Reply With Quote
  #2  
Old 5th December 2016, 07:27
Ajesh's Avatar
Ajesh Ajesh is offline
Guru
 
Join Date: Feb 2009
Posts: 445
Ajesh is on a distinguished road
Baan: LN 10.4 - DB: Oracle - OS: HP-Unix
You mean you tried putting maximum and minimum in Aggregate Function but you didnt get the result?

If that is the case then it would make sense because being in Header, the Report writer obviously doesnt have all the values for it to decide which is the maximum and minimum value for it.

What you could do, in the report script you could write a function in header.1, before.layout which could calculate the min and max value beforehand and assign that value to the report layouts..
Reply With Quote
  #3  
Old 5th December 2016, 09:03
pillai.ganesh's Avatar
pillai.ganesh pillai.ganesh is offline
Senior Member
 
Join Date: Apr 2011
Location: United Arab Emirates
Posts: 105
pillai.ganesh is on a distinguished road
Baan: ERP LN 6.1 - DB: Oracle, SQL, Informix - OS: Windows
Quote:
Originally Posted by shahromi View Post
Hi All,

I want to display the oldest & latest date in report header which is TDSLS400.ODAT.

I found that there is 1 function called Aggregate Function in report layout.
Try use this function but it's display nothing.

Is there any way to display the MAX & MIN date.

Thank You.
If you want to print the min and max dates; from the range of order dates, of the orders that the report is printing.... then the easiest way will be to get the values in two different variables - using a simple select statement with min and max aggregate functions in the UI script itself. Send these variables to the report as input fields and print it on header layout of the report.
Reply With Quote
  #4  
Old 6th December 2016, 04:30
shahromi shahromi is offline
Member
 
Join Date: Mar 2014
Posts: 45
shahromi is on a distinguished road
Baan: 10.8.2 - DB: oracle - OS: windows2008 server
Hi All,

How to pass the parameter value from Query Data, which the user select From & To SO Date to report script. Also I did not find the select statement in report script where the syntax using select between two dates.

Thank You in advance.
Reply With Quote
  #5  
Old 6th December 2016, 05:56
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 184
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
Need more details

Aggregate function on the report field can be use only in after.field, footer, after.report.

Latest and oldest order date is for what? Is it based on customer or some other criteria?

Sample Code:

Code:
declaration:

            domain tcdate	min.odat
            domain tcdate	max.odat

header.1:
before.layout:

            select MIN(a.odat):min.odat,
                   MAX(a.odat):max.odat
            from tdsls400 a
            where a._index2 = {:tdsls400.ofbp}         
            as set with 1 rows
            selectdo
            endselect
__________________
Thanks & Regards,
Manish
Reply With Quote
  #6  
Old 6th December 2016, 07:05
shahromi shahromi is offline
Member
 
Join Date: Mar 2014
Posts: 45
shahromi is on a distinguished road
Baan: 10.8.2 - DB: oracle - OS: windows2008 server
Hi Manish,

Latest & Oldest date is based on user selection/entry from Execute Query.
I want to display the user selection from this.
Please refer attachment for further explanation.

TQVM.
Attached Images
File Type: gif report.gif (191.1 KB, 13 views)
Reply With Quote
  #7  
Old 6th December 2016, 10:14
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 184
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
For SQL Query I would try below things..

Code:
declaration:

        string field.from(30) 
        string field.to(30)

            domain tcdate	min.odat
            domain tcdate	max.odat
            domain tcdate       i.date.f
            domain tcdate       i.date.t

before.program:
        get.indexed.var(parent, "field.input.from", field.from, 1)
	get.indexed.var(parent, "field.input.to", field.to, 1)


header.1:
before.layout:

            |***************************************************
            |* Convert your SQL Query parameter to date format using date.to.utc or inputstr.to.utc
            |* (I dont know format of field.from, field.to)
            |***************************************************
            i.date.f = inputstr.to.utc (field.from, <dateformat>)
            i.date.t = inputstr.to.utc (field.to, <dateformat>)
            
            select MIN(a.odat):min.odat,
                   MAX(a.odat):max.odat
            from tdsls400 a
            where a.odat between :i.date.f and ::i.date.t         
            as set with 1 rows
            selectdo
            endselect
__________________
Thanks & Regards,
Manish
Reply With Quote
  #8  
Old 6th December 2016, 13:31
pillai.ganesh's Avatar
pillai.ganesh pillai.ganesh is offline
Senior Member
 
Join Date: Apr 2011
Location: United Arab Emirates
Posts: 105
pillai.ganesh is on a distinguished road
Baan: ERP LN 6.1 - DB: Oracle, SQL, Informix - OS: Windows
Quote:
Originally Posted by shahromi View Post
Hi All,

How to pass the parameter value from Query Data, which the user select From & To SO Date to report script. Also I did not find the select statement in report script where the syntax using select between two dates.

Thank You in advance.
I understand that you have "from and to date selection fields" in your screen.
You can pass these fields as "Input Fields" to your report (Select the report and click on the specific option "Input Fields"). You can then print the fields on your report header.

If you want to find the select statement where select between two dates code is written... Please check the UI script of the session. There is remote possibility of the programmer writing the main select statement in report script.
Reply With Quote
  #9  
Old 7th December 2016, 03:13
shahromi shahromi is offline
Member
 
Join Date: Mar 2014
Posts: 45
shahromi is on a distinguished road
Baan: 10.8.2 - DB: oracle - OS: windows2008 server
Hi Manish,

1. For <dateformat>, what should I put, as the system prompt error if I put
dd/mm/yyyy. My input from and to as attached.

2. In the report layout for oldest date, form field = min.odat and for latest date
form field = max.odat. Am I right?

TQVM.
Attached Images
File Type: gif DateFormat.gif (91.7 KB, 5 views)
Reply With Quote
  #10  
Old 7th December 2016, 07:45
manish_patel's Avatar
manish_patel manish_patel is offline
Senior Member
 
Join Date: Apr 2007
Location: Surat-->Bangalore, INDIA
Posts: 184
manish_patel is on a distinguished road
Baan: Baan IVc4, Baan 5.0 b - DB: Oracle 11g R2 - OS: HP-UX 11.31
Quote:
Originally Posted by shahromi View Post
Hi Manish,

1. For <dateformat>, what should I put, as the system prompt error if I put
dd/mm/yyyy. My input from and to as attached.

2. In the report layout for oldest date, form field = min.odat and for latest date
form field = max.odat. Am I right?

TQVM.
For Point 2,
As per your earlier screen shot that shows "Demand by SO Date (from 25/11/2016 To 05/12/2016)"; date range from and to entered by user are available at report level. What variables are used to show that Date range?

After that you have to find the min and max order date from tdsls400 table by matching tdsls400.odat between from and to date range.

Could you please attach few things?
report input fields
report script
layout fields
__________________
Thanks & Regards,
Manish
Reply With Quote
Sponsored Links
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Programming Question mr_suleyman Tools Development 37 11th November 2016 09:14
Printing Layout Twice in a Report MilindV Tools Development 3 2nd December 2010 13:32
display session - date in reverse order Smiffy Tools Development 5 11th September 2003 21:31


All times are GMT +2. The time now is 08:30.


©2001-2017 - Baanboard.com - Baanforums.com