peach info Homepage
Forum Home Forum Home > PeachNet Reports > RDL Reports Information
  New Posts New Posts
  FAQ FAQ  Forum Search   Register Register  Login Login

RDL's / EmailINvoice / EmailStatements

 Post Reply Post Reply
Author
Message Reverse Sort Order
peach View Drop Down
Admin Group
Admin Group

THE BOSS

Joined: 01-Jan-2006
Status: Offline
Points: 1295
Post Options Post Options   Thanks (0) Thanks(0)   Quote peach Quote  Post ReplyReply Direct Link To This Post Topic: RDL's / EmailINvoice / EmailStatements
    Posted: 11-Nov-2013 at 15:21

Adding a field to an SQL report from a table that is not in the report.

I want to add a customer’s Fax No to the Invoice layout but the Layout does not contain the Customers Table, therefore I have to add a join command to the Customer Table from the PBSTRANS dataset.

To do this you add a left join into the query which calls the fields from PBSTRANS, (datasets) that are available. The left join will connect the FAX field from the Customer Table.

HOW TO: When you open the report in MSQL ReportBuilder, the Datasets are down the left side of the workspace. Pick a table that you want to join the new field too.
R Click – query – open up the query window to see the entire query.

Here is an example of a Left join to call up the Branch Email address from the CSGN.area field.

trim(BranchEmail) as BrchEmail from (pbstrans left join csgnarea on pbstrans.area = csgnarea.area)

aSo to call up the Customers fax number from the customers table Ill add the following

trim(BranchEmail) as BrchEmail, customer.fax from ((pbstrans left join csgnarea on pbstrans.area = csgnarea.area)left join customer on pbstrans.custcode = customer.custcode)

**The trim command above is how you change the name of the dataset as it appears in the list of datasets.

***Adding another table to the dataset may mean that some of the tables referred to elsewhere in the query need to have the table they refer to put before them in case of confusion….  E.g.

select id, invno, duedate, custcode
would need to be changed to
select pbstrans.id, invno, duedate, pbstrans.custcode

Once done click the Explanation mark to ensure the query runs without errors.

So OK to the Query screen and you should be able to find the Field (fax) in the PBSTRANS dataset now.



Edited by peach - 11-Nov-2013 at 15:25
Back to Top
peach View Drop Down
Admin Group
Admin Group

THE BOSS

Joined: 01-Jan-2006
Status: Offline
Points: 1295
Post Options Post Options   Thanks (0) Thanks(0)   Quote peach Quote  Post ReplyReply Direct Link To This Post Posted: 23-Sep-2013 at 14:01
The PBSRPTWS.MDB is in the c:\pbsvb\peachonline\reports folder. Ignore the one in the PBSVB Folder.
Back to Top
peach View Drop Down
Admin Group
Admin Group

THE BOSS

Joined: 01-Jan-2006
Status: Offline
Points: 1295
Post Options Post Options   Thanks (0) Thanks(0)   Quote peach Quote  Post ReplyReply Direct Link To This Post Posted: 09-Sep-2013 at 10:58

New Program:  NetPrint.exe     Controls printed documents

Not Email Documents...
 

Netprint.exe now prints RDLs from: POS / IE / SO / CO Screens, as long as you have an RDL layout in setup business > Layout files.

Here are a list of current layouts you can use as a base, however rename to 4 characters first, eg: InvLayP.rdl = InvL.rdl

Invoice = InvLayP.rdl (portrait)  InvLayL = (Landscape)

Customer Order Layout = COLay.rdl

Quotation Layout = QTLay.rdl

Supplier Order Layout = SOLay.rdl (Portrait)  SOLayLS (Landscape)

Remittance Layout SRLay.rdl

Workshop Layout = WSLay.rdl

Batch Creditor Invoice = BCILay.rdl

RFC / GIR Layout = RFCLay.rdl
Invoice

 

New POS Docket Layouts in RDL format :  (New field in setup business > layout files > Docket Layout [top right corner])

 

·         DKT_FULL.rdl …………………… (Prints LineNo, Part, Descr, Qty, Price, Nett)

·         DKT_Desc.rdl……………………  (Prints Descr, Qty, Nett)

·         DKT_AP.rdl………………………… (same as DKT_FULL, just with Autopro Logo)

 

Please update to latest 99801.exe + then download netsute.exe on your own machines, and test:

 

1.    Netmail.exe

2.    Netstmt.exe

3.    Netprint.exe

 

If you are to upgrade a client, please confirm with Rob or myself first.



Edited by peach - 20-Aug-2014 at 09:49
Back to Top
peach View Drop Down
Admin Group
Admin Group

THE BOSS

Joined: 01-Jan-2006
Status: Offline
Points: 1295
Post Options Post Options   Thanks (0) Thanks(0)   Quote peach Quote  Post ReplyReply Direct Link To This Post Posted: 06-Sep-2013 at 16:17
1. Don't run any reports from within the report builder as this will lock the database.
2. Reports are in c:\pbsvb\peachonline\reports
3. We don't make custom reports, just improve on what we have but ask Rob First.
 
Interactive Sorting on reports......
1. Open Report in the Report Builder
2. Right Click on the field that you want to be able to sort and select Tex Box Properties
3. Click Interactive Sorting
4. Tick the option to sort
5. Select the field that you want to sort
6. In the bottom box select Tablix
 
Save and close.
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.04
Copyright ©2001-2015 Web Wiz Ltd.

This page was generated in 0.113 seconds.