210
I’m not sure how to modify the existing report, as it uses a class as a data source that is deeply embedded/tied to the FreeTextInvoiceTmp table. I thought to try and create a new FTI template per this link msdn.microsoft.com/.../hh209275.aspx, but my AX client crashes on me when I click the AR, Setup, Free text invoice templates. I also thought about creating a new report. Can I get some guidance on how this should be done? TIA, Steve
*This post is locked for comments
Once I understood what was needed, this wasn't too difficult to do. Unfortunately, the difficult part was what I had to go through to understand what, where and how to do it. Here's the steps needed to make this change:
1. First, add three new fields to the FreeTextInvoiceTmp table, with all the fields being strings. Most all, if not all, SSRS reports use Tmp tables. Note that you should match the ProjCategoryId, ProjId and TaxGroup StringSize property with the StringSize shown for each field in the CustInvoiceLine table so that no data gets truncated/cut off. The string sizes are 30, 20 and 10, respectively. Note that you must Save, Compile then synchronize all changes to get them to sync to the AX database. If you forget to Sync, your new fields will not be available in the next step!
2. The second part was the most difficult to understand initially. If you open the AOT and expand the Visual Studio Projects node, expand Dynamics AX Model Projects, then right click and edit the FreeTextInvoiceReport, it will open the existing report in Visual Studio 2010 (if installed on your machine/server where you're running the AX client). Double-click the FreeTextInvoice node to bring up the report definition, then expand the Designs node and double-click the Report node to bring up the SSRS report designer. This is where you make changes to the report that can be deployed back to AX. Note that at the report definition layer, you can expand the Datasets node to see what datasets and available fields are present. If you need to add fields to the dataset, you do it here. Select the dataset and you can see the Query in the properties window. Click the ellipse and a wizard will be shown where you can select the FreeTextInvoiceDP data provider (it should auto-select for you), then press Next to view/select fields that are pulled from the FreeTextInvoiceTmp table. Your new fields should be available there for you to select and make available in your SSRS report with a checkbox.
NOTE: Microsoft has a new FreeTextInvoiceHeaderFooterTmp table and related FreeTextINvoiceheaderFooterDS dataset in AX 2012 R2! There's also a FreeTextInvoiceLocalizationDS that I didn't need to use. We were using R1 CU4 and deploying the same changes in R2 was confusing. When you expand the report definition, you will have three datasets in R2, not 1 as in R1 CU4. I had to change the report data in several cases to pull from the HeaderFooterDS dataset, so be aware.
Note that we saw the FreeTextInvoiceDP data provider in the above paragraph. That's the class that gets called from the UI when you are printing a Free Text Invoice for the first time, and that class is responsible for temporarily populating the FreeTextInvoiceTmp and FreeTextInvoiceHeaderFooterTmp tables. If you don't modify this class, your new ProjId, ProjCategoryId and TaxGroup fields won't ever get populated. Open the AOT, then expand classes, right-click and choose view code on the FreeTextInvoiceDP class and add this to the class definition:
CustInvoiceTable CIT;
CustInvoiceLine CIL;
then update the InsertIntoFreeTextInvoiceTmp method with the below under the custInvoiceTrans references where it's setting the freeTextInvoiceTmp.Name, Qty and SalePrice (note also Microsoft changed the way the CustInvoiceTrans and CustInvoiceLine table were referenced - in R2, they're now passed in to this method as a parameter, with underscores in front of the name to indicate they're private instead of global like CU4):
select firstOnly CIL join CIT
where (CIT.RecId == CIL.ParentRecId) && CIT.InvoiceId == _custInvoiceTrans.InvoiceId && CIL.LineNum == _custInvoiceTrans.LineNum;
freeTextInvoiceTmp.ProjId = CIL.ProjId;
freeTextInvoiceTmp.ProjCategoryId = CIL.ProjCategoryId;
freeTextInvoiceTmp.TaxGroup = CIL.TaxGroup;
Save and Compile these changes.
3. Now back to the report in Visual Studio 2010 (you'll want to reedit/reopen this to get the latest changes to the data provider that it uses). You can then add the new fields to the dataset at the report definition layer, then you can add those fields to the report. When you're done, save your changes then right-click the report in Visual Studio's Solution Explorer, then select Deploy. That's it. Assuming you've done everything right, it should tell you it deployed successfully, then when you reopen the Free Text Invoice report it should have the new fields shown on the header. Enjoy!
Looks like a possible direction may be to add the three newly needed fields to the FreeTextInvoiceTmp table (hope that's easy) and probably within the AOT somehow. Then add object references to the CustInvoiceTable and CustInvoiceLine tables in the FreeTextInvoiceDP class, and query the CIT and CIL tables inside the FreeTextInvoiceDP's insertIntoFreeTextInvoiceTmp() method based on the currently invoiceid and recid/parentrecid. I'll try this and let everyone know how that worked out...
Also, I created a database view based on this query
select distinct fti.invoiceid, fti.invoicedate, fti.COMANYNAME as CompanyName, fti.COMPANYADDRESS, fti.COMPANYPHONE,
fti.INVOICINGNAME, fti.INVOICINGADDRESS, fti.INVOICEACCOUNT, fti.PAYMENTCONDITION, '' as ShipVia, '' as FOB,
fti.PURCHASEORDER, fti.SALESADMINISTRATOR, fti.INVOICEDATE, cil.PROJID, fti.QTY, cil.PROJCATEGORYID,
fti.NAME as ItemDescription, fti.SALESPRICE, cil.TAXGROUP, fti.LINEAMOUNT,
isnull(CASE when fti.TAXAMOUNT = 0 then fti.LINEAMOUNT end, 0) as NonTaxableSubtotal,
isnull(CASE when fti.TAXAMOUNT > 0 then fti.LINEAMOUNT end, 0) as TaxableSubtotal,
fti.TAXAMOUNT as TaxAmount,
fti.INVOICEAMOUNT
from FREETEXTINVOICETMP fti join CUSTINVOICETABLE cit on (fti.INVOICEID = cit.INVOICEID)
join CUSTINVOICELINE cil on (cit.RECID = cil.PARENTRECID)
that I need to use in a new FTI. When I tried to create a new view in the AOT, it complained that it couldn't resolve the DataAreaID, which is in both the CustInvoiceTable and CustInvoiceLine tables. I added three datasources with the three tables above and added the relations per the above database query, but I couldn't get that work either. Any help would be appreciated. I went through the accelerated AX 2012 development I, II, III and IV with Netcom learning, but I'm very new to AX and know next to nothing about accounting.
#1
Mohamed Amine Mahmoudi
100
Super User 2025 Season 1
#2
Community Member
48
#3
shanawaz davood basha
6
Share
Report
All responses (
Answers (
