<< Click to Display Table of Contents >>

Customizing Summary Report like A/R Summary Detail

This document includes three parts:

 

I. Add New Report Settings

 

II. New Custom Report Data Source

III. Adding fields: Phone, Fax, State and City on Custom Report Layout

 

I. Add New Report Settings:

1.  Right Click on A/R Summary Detail and click Edit Report Settings on context menu.

report1

2.  A report settings screen will open. Enter the Report Name for the custom report. Then save.

report2

3. Close the Report Settings form.

II. New Custom Report Data Source

Now, let’s start customizing the report data source that will expose Phone and Fax on the query. Later, on this document we will discuss how important this is on custom report layout.

1. After saving the report settings, open the report on Custom Reports Group. Then highlight the report and click the Data Source button at the toolbar area.

report3

 

2.  A Report Script screen will open with a duplicate copy of standard A/R Summary Detail SQL Query. You may now begin adding fields that will be needed on the custom report layout.

 

In the screen shot below, we added

 

,tblARCustomer.strPhone

,tblARCustomer.strFax

 

report4

Since, City and State (declared as strFullCity) is already in the duplicate copy of the query, we will not need to enter new line for those fields.

 

report5

 

Here’s the complete query on the sample report script on the screenshot:

 

SELECT vyu_SMReportPreference.*, X.*

,X.dblOrderTotal - X.dblAmountPaid - X.dblDiscount dblAmountDue

,INV.dblOrderTotal dblInvoiceTotal

,ISNULL(INV.dtmDate,GETDATE())dtmDate

,ISNULL(INV.strTransactionType,'Invoice')strTransactionType, INV.strCustomerID

,INV.strCustomerPO, INV.dtmDueDate, ISNULL(INV.ysnPaid,1)ysnPaid

,INV.strTerms, ISNULL(tblSMTerm.intDaysDue,0) intDaysDue

,ISNULL(INV.strCustomerID,'') + '-' + ISNULL(tblARCustomer.strCompanyName,'') strCustomerIDCompany

,ISNULL(dbo.fnInvoiceShipTo(ISNULL(INV.strOrderNumber,'')),'')strCustInfo

,tblARCustomer.strFirstName

,tblARCustomer.strLastName

,tblARCustomer.strPhone

,tblARCustomer.strFax

,ISNULL(strCity,'') + ',' + ISNULL(strState,'') strFullCity

,INV.ysnPosted

,ISNULL(tblARCustomer.strFirstName,'') + ' ' + ISNULL(strLastName,'') strFullName

,strCompanyNameCustomerID = CASE WHEN  tblARCustomer.strCompanyName = '' THEN ISNULL(strFirstName,'')  + ' ' + ISNULL(strLastName,'')

              ELSE ISNULL(INV.strCustomerID,'')+' - '+ISNULL(tblARCustomer.strCompanyName,'') END

,CASE WHEN (ISNULL(X.dblOrderTotal,0)-ISNULL(X.dblAmountPaid,0))>=0 THEN 0

              ELSE (ISNULL(X.dblOrderTotal,0)-ISNULL(X.dblAmountPaid,0)) END AS dblUnappliedAmount

,ISNULL(CASE WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=0 THEN 0

              ELSE DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE()) END, 0) intAging

,CASE WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=0

              THEN ISNULL(X.dblOrderTotal,0)-ISNULL(X.dblAmountPaid,0) ELSE 0 END dblCurrent,

      CASE WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>0 AND DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=30

              THEN ISNULL(X.dblOrderTotal,0)-ISNULL(X.dblAmountPaid,0) ELSE 0 END dbl1,

      CASE WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>30 AND DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=60

              THEN ISNULL(X.dblOrderTotal,0)-ISNULL(X.dblAmountPaid,0) ELSE 0 END dbl30,

      CASE WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>60 AND DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=90

              THEN ISNULL(X.dblOrderTotal,0)-ISNULL(X.dblAmountPaid,0) ELSE 0 END dbl60,

      CASE WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>90

              THEN ISNULL(X.dblOrderTotal,0)-ISNULL(X.dblAmountPaid,0) ELSE 0 END dbl90

,CASE WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=0 THEN 'Current'

              WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>0 AND DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=30 THEN '01 - 30 Days'

              WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>30 AND DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=60 THEN '31 - 60 Days'

              WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>60 AND DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())<=90 THEN '61 - 90 Days'

              WHEN DATEDIFF(dayofyear,INV.dtmDueDate,GETDATE())>90 THEN 'Over 90'

              ELSE 'Current' END strAge

,DATEADD(DAY,30,GETDATE())dtmDateBy

FROM vyu_SMReportPreference,

(

SELECT strOrderNumber, strCompanyName, SUM(dblOrderTotal) dblOrderTotal, SUM(dblAmountPaid) dblAmountPaid, SUM(dblDiscount) dblDiscount

FROM (

--*SCSTART*--

SELECT * FROM vyu_SMRptReceivablesAging

--*SC*--

--Special Case Codes--

) tbl

GROUP BY strOrderNumber, strCompanyName

) X

LEFT JOIN tblARInvoice INV

ON INV.strOrderNumber = X.strOrderNumber

LEFT JOIN tblARCustomer

ON tblARCustomer.strCustomerID = INV.strCustomerID

LEFT JOIN tblSMTerm

ON tblSMTerm.strTerm = INV.strTerms

 

3.  Save the Report after you’ve finalized your SQL query and then enter unique name for your custom report script.

report6

4. You may click the Execute button and check if the new fields added on the query will display on Data Preview tab.

report7

5. Close the Report Script form.

Before going further on customizing report layout, let’s go back to the Report Settings of the custom report and select the custom Report Script (e.g. Custom AR DataSourse).

 

To do this, right click on the report and select Edit Report Settings.

report8

Or highlight the report and click the Settings button on the toolbar to open the Report Settings form.

report9

 

On Data Source ID field of Report Setting form, select the custom report script (e.g.412-Custom AR Datasource) and save. Then close.

 

report10

III. Adding fields: Phone, Fax, State and City on Custom Report Layout

1. To open the custom report in Report Designer, highlight the report and click the Designer button.

report11

Or right click on the report and select Edit Report Design.

report12

2. When Report Designer opens, start adding the fields on the custom report layout.

report13

 

In the above screenshot, you will see 3 text boxes were added that will display the Phone, Fax and StrFullCity (City,State ) and equivalent DataField should be set on each box.

 

For strFax box, strFax is selected on DataField drop down. report14

 

DataField strFax pertains to the field added on custom report script.

 

report15

For strFullCity box, strFullCity is selected on DataField drop down.

 

report16

report17

 

3. Then if you want to preview the report, you may click the Preview button at the bottom.

 

report18

 

4. Save your report by clicking Ctrl+S or from File Menu click Save. On Save Report As mini form, enter unique Report Name for your custom report layout.

report19

5. After saving your report layout, go back to the Report Setting and select the new custom report layout and save.

 

report20

 

Finally, you can run a preview of the customized report.

report21

 

Info Note: You can always go back to the custom report script or report designer if you wanted to add more fields on the report script and report layout.