Creating Custom Reporting Views? > Developer Forum

Unanswered Active Topics

Forums Search

Forums > Connected Business Platform > General

Subject: Creating Custom Reporting Views?

Prev Next

You are not authorized to post a reply.

Author

Messages

Martin M

Posts:1

07/18/2008 3:05 AM
I have been using the "2007.4.3 Report Designer.pdf" manual to try and work my way through some custom reports. I am having to create an Advice Note report which I am using a duplicate of the Order Picking Ticket report, but was needing to add in an extra field - CustomerCode. When creating my custom view I copied the script from the order picking ticket view and then tried to add in my additional field. After having some trouble doing so, with the report not running etc, I consulted the manual again to see if their was a simple way to add in fields. This is an example within the "2007.4.3 Report Designer.pdf" documentation which shows the following: SELECT CIFRV .*, C.ExistingSoftware_C -- more fields can be added here. FROM CustomerInvoiceFormReportView CIFRV LEFT JOIN Customer C ON C.CustomerCode IN (SELECT BillToCode FROM CustomerInvoice WHERE InvoiceCode = CIFRV.InvoiceCode) Note:The CIFRV statement in the script is an alias for the view CustomerInvoiceFormReportView. By using an alias the full view name does not have to be typed each time it is used. Is it neccessary to be very familiar with SQL coding to add in additional fields to reports or create one from scratch? Also, surely the best way to do this would be to code it all within SQL and then bring the code in to Interprise? The reason I ask is that Interprise have been trying to stop you from playing around in the database as much as possible so I thought I might have been missing something. One last thing I was wanting confirmation on was whether or not their was any way to use stored procedures for creating custom results sets instead of views? Thanks in advance Martin

Donna Ruth B User is Offline

Posts:65

07/22/2008 6:51 AM
Hi Martin, Does the Customercode is a custom field? If so, in able to add the custom fields in the new view that you had copied in Order Picking Ticket script, look for the line item a.Isvoided then below that add a.CustomerCode, as shown below. a.PickingNotePrintCount AS PrintCount, ISNULL(e.BusinessPhone, '') + ' ext ' + ISNULL(e.BusinessPhoneExtension, '') AS BusinessPhoneNumber, ISNULL(e.BusinessFax, '') + ' ext ' + ISNULL(e.BusinessFaxExtension, '') AS BusinessFaxNumber, a.DueDate, a.Notes, a.IsVoided, a.CustomerCode, Hope that helps. But if you still have issue on this, please email support@interprisesolutions.com Thanks

Tim S

Posts:30

08/28/2008 11:56 AM
Martin We have done several custom reports for customer and you do need some sql knowledge to amend a view. A word of warning, if you amend a standard report view and report, it is likely to get overwritten when you next upgrade IS. It is better to create a new ciew vis the Data Dictionary Manager, then create a copy report and change the source of the new report to the custom view. You will have to relink the report fields to the custom view (we are planning to develop a plugin to help with this in due course), but at least you wont lose the mod when you next upgrade.

Tim Sheppard, Lerryn Data Technology Ltd

You are not authorized to post a reply.

Forums > Connected Business Platform > General > Creating Custom Reporting Views?

ActiveForums 3.7