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.
2. A report settings screen will open. Enter the Report Name for the custom report. Then save.
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.
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
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.
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.
4. You may click the Execute button and check if the new fields added on the query will display on Data Preview tab.
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.
Or highlight the report and click the Settings button on the toolbar to open the Report Settings form.
On Data Source ID field of Report Setting form, select the custom report script (e.g.412-Custom AR Datasource) and save. Then close.
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.
Or right click on the report and select Edit Report Design.
2. When Report Designer opens, start adding the fields on the custom report layout.
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.
DataField strFax pertains to the field added on custom report script.
For strFullCity box, strFullCity is selected on DataField drop down.
3. Then if you want to preview the report, you may click the Preview button at the bottom.
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.
5. After saving your report layout, go back to the Report Setting and select the new custom report layout and save.
Finally, you can run a preview of the customized report.
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.