I need to add the Subtotal (InvcHead.DspDocSubTotal), Tax (InvcHead.DocDspTaxAmt) and Freight costs (InvcMisc.DspMiscAmt) to the AR invoice form. These fields are in the report data definition and are unexcluded (included). The InvcMisc table has a relationship in the RDD already. I am attempting to add these fields into the Query expression of the ARForm dataset as T1.FieldName but I keep getting errors such as, " Invalid column name ‘DocDspTaxAmt’" For the Freight (misc charge), I added a LEFT OUTER JOIN to the InvcMisc table at the end of the query as T9, but I got the same error of invalid column name. Is this because they are not displayable fields? If I go into Data Dictionary Viewer, these fields do not appear.
How do I go about getting them to show up on the invoice form?
Thanks for the reply. I will look into using InvcTax.DocTaxAmt. DocInvoiceAmt is the total amount of the invoice. I would like to display the subtotal or sum of all the lines before taxes so this field will not work.
I gotten closer to solving this but there are still a few issues. There were a few Calculated fields in the RDD that were useful to me. Subtotal is InvcHead.Calc_InvSubTotal. I am not sure if a previous consultant created this or if it was already there but it seems to work. Total Taxes is InvcHead.Calc_DocDspTaxAmt.
The Freight still seems to be an issue. I am attempting to join the InvcMisc table in the query expression in order to access the DocMiscAmt value. This works (after including MiscCode and TaxCatID in InvcDtl). I get the correct amount for the Freight charges but I am now getting duplicate line entries in the invoice. I am using a LEFT OUTER JOIN just like all of the other tables and linking on Company, InvoiceNum, and InvoiceLine with InvcDtl. This join is surely the issue because the duplicate lines go away after removing it.
LEFT OUTER JOIN InvcMisc_" + Parameters!TableGuid.Value + " T9 ON T2.Company = T9.Company AND T2.InvoiceLine = T9.InvoiceLine AND T2.InvoiceNum = T9.InvoiceNum
Does anyone know the correct way to join this table to InvcDtl?
Have you tried joining the InvcMisc table via the data definition, and then syncing the dataset (instead of manually editing the query expression in SSRS)?
I’ve heard concerns as well but haven’t had any real issues. I always try that first and then edit the query expression manually if everything I needed didn’t actually sync. When that happens it’s usually just one of the fields in the select statement that I have to add, and then you have to add it as a field in the dataset properties too.
Thanks for the reply. When I click the Sync Dataset button it gives an error saying “The path is not of legal form.” I have not changed the Report location/path and it is currently ‘reports/CustomReports/ARInvoiceForm/ARForm,reports/CustomReports/ARPBForm/ARPBForm’
I saw that thread as well. It appears that the datasources are the correct SharedDataSources for the test environment on our SSRS server. And the path is only 86 characters long. Not sure what is going on.
Regardless of this, maybe you can help me with another issue if you are familiar with editing reports in Report Builder.
This is what a section of the report looks like in Report Builder.
The values are correct but there is a big white space between Freight and Total Taxes, but it doesn’t appear this way in the editor. What could cause this jump in formatting?
One thing you could check is the padding setting on each text box (specifically the Freight bottom padding and Total Taxes top padding). In text box properties, that’s under the Alignment tab.
There could be something going on with the Payment Schedule box. Maybe try hiding that to see if it changes anything.
If none of that work, you might be able to put those values in a table instead of individual boxes, which should keep it all together.
That fixed that! There was a little bit of padding on a few of the textboxes. After making them all 0pt, the blank space went away. Thanks for that easy fix! Now to fix the other problem…
Edit: Actually it was the Payment Schedule box like you mentioned. The real fix was putting these elements in their own Rectangle!
I solved this issue of duplicating lines by using a subquery in the query expression to select the DocMiscAmount from InvcMisc similar to this thread:
Here is the what my added part of the subquery looked like:
(SELECT DocMiscAmt FROM [EPICORTEST10].[ERP].[InvcMisc] WHERE InvoiceNum = CONVERT(INT,T2.InvoiceNum) AND InvoiceLine = ‘1’ AND Company = T2.Company AND MiscCode = ‘0001’) as DocMiscAmt,
The Freight is always marked as the MiscCharge of 0001 on Line 1.
The SubTotal and Total Taxes were added as calculated fields from the RDD like normal.
