Customer History Report

MarkOwen — Wed, 10 Feb 2010 07:34:46 GMT

Is there a customer history report that can be ran in IS. I need to view the past year translations for a customer and having that in one report would be great for me and the customer.

Any ideas would be great

Thanks
Mark

Not Displaying Kit Details on Invoice Form

Mlittman — Fri, 15 Jan 2010 10:16:33 GMT

I am trying to modify the standard CustomerInvoiceFormReportView to not display teh details of teh kit.

Can anyone help?

Here is the standard script:

SELECT a.InvoiceCode, cpv.AmountPaidRate AS Value, cpv.PaymentMethod, a.Ref1 AS Reference, a.Ref2 AS Reference2, taxRates.TaxRate AS TaxRate1,
a.TaxRate AS TaxValue, dbo.CreateAddress(a.BillToName, a.BillToCity, a.BillToState, a.BillToAddress, a.BillToPostalCode, a.BillToCounty, a.BillToCountry)
AS BillToNameAndAddress, dbo.CreateAddress(a.ShipToName, a.ShipToCity, a.ShipToState, a.ShipToAddress, a.ShipToPostalCode, a.ShipToCounty, a.ShipToCountry)
AS ShipToNameAndAddress, CASE WHEN ISNULL(a.BillToPhoneExtension, '') = '' THEN ISNULL(a.BillToPhone, '') ELSE ISNULL(a.BillToPhone, '')
+ ' ext ' + ISNULL(a.BillToPhoneExtension, '') END AS BillToPhoneNumber, CASE WHEN ISNULL(a.ShipToPhoneExtension, '') = '' THEN ISNULL(a.ShipToPhone, '')
ELSE ISNULL(a.ShipToPhone, '') + ' ext ' + ISNULL(a.ShipToPhoneExtension, '') END AS ShipToPhoneNumber, g.TaxNumber AS BillToTaxNumber,
h.TaxNumber AS ShipToTaxNumber, a.InvoiceDate, a.DueDate, a.CurrencyCode, a.PaymentTerm, a.ShippingMethod, x_1.ItemName, x_1.LineNum,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.QuantityShipped END AS QuantityShipped, CASE WHEN x.ItemName = '' THEN NULL
ELSE x.QuantityOrdered END AS QuantityOrdered, CASE WHEN x.ItemName = '' THEN NULL ELSE x.QuantityBackOrdered END AS QuantityBackOrdered,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.UnitMeasureCode END AS UnitMeasureCode, x_1.ItemDescription, x_1.TaxCode,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.SalesTaxAmountRate END AS SalesTaxAmountRate, CASE WHEN x.ItemName = '' THEN NULL
ELSE x.SalesPriceRate END AS SalesPriceRate, CASE WHEN x.ItemName = '' THEN NULL ELSE x.Discount END AS DiscountRate,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.NetPriceRate END AS NetPriceRate, CASE WHEN x.ItemName = '' THEN NULL
ELSE x.ExtPriceRate END AS ExtPriceRate, a.SubTotalRate, a.FreightRate, a.OtherRate, a.TaxRate, a.TotalRate, a.AppliedCreditRate, ISNULL(a.CouponDiscountRate,
0) + ISNULL(a.AmountPaidRate, 0) AS AmountPaidRate, a.BalanceRate, a.SubTotalRate + a.FreightRate AS NetValue, c.EntityName, c.EntityEmail, c.EntityFax,
c.EntityFaxExtension, c.ContactCode, c.ContactFullName, c.Email1 AS Email, c.BusinessFax, c.BusinessFaxExtension, a.IsPrinted, a.PrintCount, a.POCode,
ISNULL(EE.WebSiteCode, N'') AS WebSiteCode, x_1.CustomerPO,
'All goods remain the property of' + ' ' + d.CompanyName + ' ' + 'until paid for in full. Standard terms and conditions of sale apply.' AS NameOfCompany,
CASE WHEN ISNULL(a.WebSiteCode, '') = '' THEN d .CompanyName ELSE EE.WebSiteDescription END AS CompanyName, CASE WHEN EE.WebsiteLogo IS NULL
THEN NULL ELSE EE.WebSiteLogo END AS CompanyLogo
FROM dbo.CustomerInvoiceView AS a LEFT OUTER JOIN
(SELECT a.WarehouseCode, a.Warehouse, a.InvoiceCode, a.LineNum + 0.0 AS LineNum, ISNULL(a.ItemName, N'') + NCHAR(13)
+ CASE WHEN ISNULL(a.CustomerItemCode, '') = '' THEN '' ELSE '(' + ISNULL(a.CustomerItemCode, '') + ')' END AS ItemName, a.QuantityOrdered,
a.QuantityAllocated, CASE WHEN ISNULL(a.IsInventoryDescription, 0) = 0 THEN a.InventoryDescription + NCHAR(13)
+ '(' + a.ItemDescription + ')' ELSE a.ItemDescription END AS ItemDescription, a.UnitMeasureCode, a.TaxCode, 0 AS ItemKitDetailLineNum, a.ItemType,
a.QuantityShipped, a.QuantityBackOrdered, a.SalesTaxAmountRate, a.SalesPriceRate, a.Discount, a.NetPriceRate, a.ExtPriceRate, a.CustomerPO
FROM dbo.CustomerInvoice AS b INNER JOIN
dbo.CustomerInvoiceDetailView AS a ON b.InvoiceCode = a.InvoiceCode
WHERE (a.ItemType IN ('Assembly', 'Matrix Item', 'Stock', 'Kit', 'Non-Stock', 'Electronic Download', 'Service')) AND (b.Type = 'Invoice')
UNION ALL
SELECT a.WarehouseCode, a.Warehouse, a.InvoiceCode, a.LineNum + 0.1 AS LineNum, '' AS ItemName, NULL AS QuantityOrdered, NULL AS QuantityAllocated,
CASE WHEN ISNULL(a.IsInventoryDescription, 0) = 0 THEN ISNULL(' - ' + b.ItemDescription, '') ELSE ISNULL(' - ' + b.ItemDescription, '')
END AS ItemDescription, '' AS UnitMeasureCode, '' AS TaxCode, b.ItemKitDetailLineNum, a.ItemType, NULL AS QuantityShipped, NULL
AS QuantityBackOrdered, NULL AS SalesTaxAmountRate, NULL AS SalesPriceRate, NULL AS Discount, NULL AS NetPriceRate, NULL AS ExtPriceRate,
a.CustomerPO
FROM dbo.CustomerInvoice AS c INNER JOIN
dbo.CustomerInvoiceDetailView AS a ON a.InvoiceCode = c.InvoiceCode INNER JOIN
dbo.CustomerItemKitDetailView AS b ON a.ItemCode = b.ItemKitCode AND a.InvoiceCode = b.DocumentCode AND a.LineNum = b.LineNum
WHERE (c.Type = 'Invoice')) AS x_1 ON a.InvoiceCode = x_1.InvoiceCode LEFT OUTER JOIN
(SELECT a.TaxCode, SUM(c.TaxRate) AS TaxRate
FROM dbo.SystemTaxScheme AS a INNER JOIN
dbo.SystemTaxSchemeDetail AS b ON a.TaxCode = b.TaxCode INNER JOIN
dbo.SystemTaxDetail AS c ON b.TaxDetailCode = c.TaxDetailCode
GROUP BY a.TaxCode) AS taxRates ON x_1.TaxCode = taxRates.TaxCode LEFT OUTER JOIN
(SELECT x.SalesOrderCode, x.PaymentMethod, SUM(x.AmountPaidRate) AS AmountPaidRate
FROM dbo.CustomerPaymentView AS x INNER JOIN
dbo.CustomerTransactionReceipt AS y ON x.SalesOrderCode = y.DocumentCode AND x.ReceivableCode = y.ReceivableCode
GROUP BY x.SalesOrderCode, x.PaymentMethod) AS cpv ON a.InvoiceCode = cpv.SalesOrderCode LEFT OUTER JOIN
dbo.CRMContactView AS c ON a.ContactCode = c.ContactCode INNER JOIN
dbo.Customer AS g ON a.BillToCode = g.CustomerCode INNER JOIN
dbo.CustomerShipTo AS h ON a.ShipToCode = h.ShipToCode LEFT OUTER JOIN
(SELECT WebSiteCode, WebSiteLogo, WebSiteDescription
FROM dbo.WebSite) AS EE ON a.WebSiteCode = EE.WebSiteCode CROSS JOIN
dbo.SystemCompanyInformation AS d
WHERE (a.Type = 'Invoice')

If I scripot a new one like this:

SELECT a.InvoiceCode, cpv.AmountPaidRate AS Value, cpv.PaymentMethod, a.Ref1 AS Reference, a.Ref2 AS Reference2, taxRates.TaxRate AS TaxRate1,
a.TaxRate AS TaxValue, dbo.CreateAddress(a.BillToName, a.BillToCity, a.BillToState, a.BillToAddress, a.BillToPostalCode, a.BillToCounty, a.BillToCountry)
AS BillToNameAndAddress, dbo.CreateAddress(a.ShipToName, a.ShipToCity, a.ShipToState, a.ShipToAddress, a.ShipToPostalCode, a.ShipToCounty, a.ShipToCountry)
AS ShipToNameAndAddress, CASE WHEN ISNULL(a.BillToPhoneExtension, '') = '' THEN ISNULL(a.BillToPhone, '') ELSE ISNULL(a.BillToPhone, '')
+ ' ext ' + ISNULL(a.BillToPhoneExtension, '') END AS BillToPhoneNumber, CASE WHEN ISNULL(a.ShipToPhoneExtension, '') = '' THEN ISNULL(a.ShipToPhone, '')
ELSE ISNULL(a.ShipToPhone, '') + ' ext ' + ISNULL(a.ShipToPhoneExtension, '') END AS ShipToPhoneNumber, g.TaxNumber AS BillToTaxNumber,
h.TaxNumber AS ShipToTaxNumber, a.InvoiceDate, a.DueDate, a.CurrencyCode, a.PaymentTerm, a.ShippingMethod, x_1.ItemName, x_1.LineNum,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.QuantityShipped END AS QuantityShipped, CASE WHEN x.ItemName = '' THEN NULL
ELSE x.QuantityOrdered END AS QuantityOrdered, CASE WHEN x.ItemName = '' THEN NULL ELSE x.QuantityBackOrdered END AS QuantityBackOrdered,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.UnitMeasureCode END AS UnitMeasureCode, x_1.ItemDescription, x_1.TaxCode,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.SalesTaxAmountRate END AS SalesTaxAmountRate, CASE WHEN x.ItemName = '' THEN NULL
ELSE x.SalesPriceRate END AS SalesPriceRate, CASE WHEN x.ItemName = '' THEN NULL ELSE x.Discount END AS DiscountRate,
CASE WHEN x.ItemName = '' THEN NULL ELSE x.NetPriceRate END AS NetPriceRate, CASE WHEN x.ItemName = '' THEN NULL
ELSE x.ExtPriceRate END AS ExtPriceRate, a.SubTotalRate, a.FreightRate, a.OtherRate, a.TaxRate, a.TotalRate, a.AppliedCreditRate, ISNULL(a.CouponDiscountRate,
0) + ISNULL(a.AmountPaidRate, 0) AS AmountPaidRate, a.BalanceRate, a.SubTotalRate + a.FreightRate AS NetValue, c.EntityName, c.EntityEmail, c.EntityFax,
c.EntityFaxExtension, c.ContactCode, c.ContactFullName, c.Email1 AS Email, c.BusinessFax, c.BusinessFaxExtension, a.IsPrinted, a.PrintCount, a.POCode,
ISNULL(EE.WebSiteCode, N'') AS WebSiteCode, x_1.CustomerPO,
'All goods remain the property of' + ' ' + d.CompanyName + ' ' + 'until paid for in full. Standard terms and conditions of sale apply.' AS NameOfCompany,
CASE WHEN ISNULL(a.WebSiteCode, '') = '' THEN d .CompanyName ELSE EE.WebSiteDescription END AS CompanyName, CASE WHEN EE.WebsiteLogo IS NULL
THEN NULL ELSE EE.WebSiteLogo END AS CompanyLogo
FROM dbo.CustomerInvoiceView AS a LEFT OUTER JOIN
(SELECT a.WarehouseCode, a.Warehouse, a.InvoiceCode, a.LineNum + 0.0 AS LineNum, ISNULL(a.ItemName, N'') + NCHAR(13)
+ CASE WHEN ISNULL(a.CustomerItemCode, '') = '' THEN '' ELSE '(' + ISNULL(a.CustomerItemCode, '') + ')' END AS ItemName, a.QuantityOrdered,
a.QuantityAllocated, CASE WHEN ISNULL(a.IsInventoryDescription, 0) = 0 THEN a.InventoryDescription + NCHAR(13)
+ '(' + a.ItemDescription + ')' ELSE a.ItemDescription END AS ItemDescription, a.UnitMeasureCode, a.TaxCode, 0 AS ItemKitDetailLineNum, a.ItemType,
a.QuantityShipped, a.QuantityBackOrdered, a.SalesTaxAmountRate, a.SalesPriceRate, a.Discount, a.NetPriceRate, a.ExtPriceRate, a.CustomerPO
FROM dbo.CustomerInvoice AS b INNER JOIN
dbo.CustomerInvoiceDetailView AS a ON b.InvoiceCode = a.InvoiceCode
WHERE (a.ItemType IN ('Assembly', 'Matrix Item', 'Stock', 'Kit', 'Non-Stock', 'Electronic Download', 'Service')) AND (b.Type = 'Invoice')
UNION ALL
SELECT a.WarehouseCode, a.Warehouse, a.InvoiceCode, a.LineNum + 0.1 AS LineNum, '' AS ItemName, NULL AS QuantityOrdered, NULL AS QuantityAllocated,
CASE WHEN ISNULL(a.IsInventoryDescription, 0) = 0 THEN ISNULL(' - ' + b.ItemDescription, '') ELSE ISNULL(' - ' + b.ItemDescription, '')
END AS ItemDescription, '' AS UnitMeasureCode, '' AS TaxCode, b.ItemKitDetailLineNum, a.ItemType, NULL AS QuantityShipped, NULL
AS QuantityBackOrdered, NULL AS SalesTaxAmountRate, NULL AS SalesPriceRate, NULL AS Discount, NULL AS NetPriceRate, NULL AS ExtPriceRate,
a.CustomerPO
FROM dbo.CustomerInvoice AS c INNER JOIN
dbo.CustomerInvoiceDetailView AS a ON a.InvoiceCode = c.InvoiceCode INNER JOIN
dbo.CustomerItemKitDetailView AS b ON a.ItemCode = b.ItemKitCode AND a.InvoiceCode = b.DocumentCode AND a.LineNum = b.LineNum[b]+'9999[u][/u][/b]'
WHERE (c.Type = 'Invoice')) AS x_1 ON a.InvoiceCode = x_1.InvoiceCode LEFT OUTER JOIN
(SELECT a.TaxCode, SUM(c.TaxRate) AS TaxRate
FROM dbo.SystemTaxScheme AS a INNER JOIN
dbo.SystemTaxSchemeDetail AS b ON a.TaxCode = b.TaxCode INNER JOIN
dbo.SystemTaxDetail AS c ON b.TaxDetailCode = c.TaxDetailCode
GROUP BY a.TaxCode) AS taxRates ON x_1.TaxCode = taxRates.TaxCode LEFT OUTER JOIN
(SELECT x.SalesOrderCode, x.PaymentMethod, SUM(x.AmountPaidRate) AS AmountPaidRate
FROM dbo.CustomerPaymentView AS x INNER JOIN
dbo.CustomerTransactionReceipt AS y ON x.SalesOrderCode = y.DocumentCode AND x.ReceivableCode = y.ReceivableCode
GROUP BY x.SalesOrderCode, x.PaymentMethod) AS cpv ON a.InvoiceCode = cpv.SalesOrderCode LEFT OUTER JOIN
dbo.CRMContactView AS c ON a.ContactCode = c.ContactCode INNER JOIN
dbo.Customer AS g ON a.BillToCode = g.CustomerCode INNER JOIN
dbo.CustomerShipTo AS h ON a.ShipToCode = h.ShipToCode LEFT OUTER JOIN
(SELECT WebSiteCode, WebSiteLogo, WebSiteDescription
FROM dbo.WebSite) AS EE ON a.WebSiteCode = EE.WebSiteCode CROSS JOIN
dbo.SystemCompanyInformation AS d
WHERE (a.Type = 'Invoice')

it works in the query but will not let me save it.

Any ideas?

Fields Not Bound

Mlittman — Wed, 09 Sep 2009 10:31:55 GMT

I made a copy of the Picking Ticket Report and went in and modified each filed I could locate including the Criteria builder to use a new dataset (CustomerSalesOrderPickTicketFormreportView_C) . I needed to add a custom field and I did not want to interfere with the original.

I have checked and rechecked all the fields that I could find and I still get a message when trying to print that something is not bound and to recheck all the fields. Any ideas on how to verify which field is not bound?

Calculated Fields?

Greg Sergeant — Tue, 01 Sep 2009 08:24:40 GMT

Is it possible to use formulas in fields so that the end result is a value that is calculated at report time?

Thanks

Sales Report for Manufature

MarkOwen — Wed, 29 Jul 2009 13:19:04 GMT

I need to create a quarterly sales report for one of our brands that we sale. I have played with the reports but can't seem to figure it out.

Can any one offer, I hope easy steps to do this?

Thanks

Changing the sort order of the "Stock Valuation by Warehouse" Report

Eric Sooy — Tue, 30 Jun 2009 11:50:27 GMT

By default it sort by Item ID. How can I copy that report but change it so it sorts by total valuation? I know I can export a CSV to excel but that's a pain.

Any easy way to stop teh margin mesaage from displaying when going to a printer?

Mlittman — Tue, 30 Jun 2009 05:53:51 GMT

My customer's forms give the invalid margin message when printing to their printers. If they hit continue...everything prints correctly. Is there anyway of disabling these messages>

How to Supress an Entire Band Based on a Field Value

BJ Dimes — Thu, 23 Apr 2009 11:01:17 GMT

Select the band you wish to hide, click on the scripts, and add the following logic in the following method:

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)

{

// Get the gl amount
decimal glAmount = (decimal)GetCurrentColumnValue("GLAmount");

// If the gl amount is zero, do not print the band
if (glAmount == 0)
e.Cancel = true;

}

The example above simply shows that we dont want a particular band to be printed if the GLAmount field is zero.

Note: The GLAmount field will need to exist on the report in order for this to work. The GetCurrentColumnValue method will fail if the field doesnt exist. Even if you do not want to show the field, it needs to exist on the report. Simply mark this field as not visible and you are good to go.

How to Supress a Field Based on Another Field

BJ Dimes — Thu, 23 Apr 2009 10:56:39 GMT

Select the field you wish to hide, click on the scripts, and add the following logic in the following method:

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)

{

// Cancel label render if fields are the same

e.Cancel = GetCurrentColumnValue("FieldName").ToString() ==
GetCurrentColumnValue("FieldName").ToString();

}

Note: Both fields will need to exist on the report in order for this to work. The GetCurrentColumnValue method will fail if the field doesnt exist. Even if you do not want to show the field, it needs to exist on the report. Simply mark this field as not visible and you are good to go.

How to PreZero a Check Number on a Supplier Check

BJ Dimes — Thu, 23 Apr 2009 10:50:48 GMT

Get a reference to the label containing the field value, convert it to an integer, and replace the label text with the formatted number.

Put this in the OnBeforePrint script:

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{
// Get typed reference to label
DevExpress.XtraReports.UI.XRLabel checkNo = sender as DevExpress.XtraReports.UI.XRLabel;

// If the check number isn't null or empty
if (!string.IsNullOrEmpty(checkNo.Text))
{
// Convert to integer and format
checkNo.Text = string.Format("{0:000000}", int.Parse(checkNo.Text));
}

}

Interprise Suite Community / Interprise Suite Community Forums / Reports