I am hoping someone can point me in the right direction here.
I have to get our Bank Details on these two forms and in particular, BankAcctID, Description, BankName, BankBranchCode, IBANCode, CheckingAccount, BankIdentifier, Address etc.
I can easily get this info in a BAQ by using Company/Company and OurBank/BankAcctID from BankAcct and either InvcHead or Orderhed but I cannot get these to appear on the print despite creating relationships and un-excluding all the fields in the RDD and editing the report Dataset properties/adding fields in the normal way.
I can see from the BAQ warning that there are no Indexes between BankAcct and these two fields so I am a bit stuck.
Is anyone else displaying this info and if so, how did you do it?
And that’s all very well and good. Until Finance want to update the bank account details and there is no-one available to fix it, because they thought it would update automatically when they updated the details in Kinetic. Now it has become an emergency.
I know sounds like sour grapes. After spending more hours than I would like to think, fixing “Hard coded stuff on reports”. Just please don’t do it, in the long run it saves time, money and most importantly stress. Think about those that come after you
Thanks, yes I could have but we will be adding another 3 companies after we go live and there will be different bank accounts between them so I really need to find how to link the Bank details to the Invoice now so a change will flow automatically.
No definately not spend the time to work out the relationship to identify the related AR bank account the the customers will pay to.
If you get it working for one company then you won’t have to do anything with the report style for the later companies, it will just work. Provided they don’t want any company specific formatting in the RDL Which I would be steering them away from, apart from the logo…
Oh hold on just forgot the Proforma Invoice does not work with the "Report Style Logo, so you would have to add some logic in based on the report style to display the logo based on company.
Ah, OK yes this is what I was thinking would be best.
One question I was asked is “what happens if the sale was to a European customer as Euro’s go to a different bank” which muddied the water a bit.
Actually as an aside, the logo worked fine on the Proforma.
Edit: Actually I think I understand what you meant. All 4 companies will have the same Logo, just different footers which is just Text so I would imagine they will probably have their own Report Style in the end. Maybe we will do that by permissions and just let them see the one relevant to them.
Hmmm Intersting, KB0050698 mentions otherwise… But there again, I’ve been working on some reports that have been dragged up through the ages.
As far as Currency goes… The customer can have a default currency other that the system base and it can also be altered on the sales order or in invoice entry, along with manually altering the exchange rate and locking it as well.
Theory being that if the customer pays in Euro and your bank account it GPB then the conversion should be done at the time of cash receipting, that’s how I underrstand it…
I think all you need to do is:
Include the OrderHead.OurBank
Add the the OurBank as the linked table
Add in the description fields that you need
Save the RDD
Then from the OrderHed, you can add in the relevant fields to the report
Example of the results from running the report with the updated RDD and querying the OrderHed Table.
By the way the OurBank gets its information from the Prerffered Bank Account on the Customer/Detail/Billing Tab. So that resolves the issue of trying to work out the account to use on the fly. It just does it based on that field.
Thanks Simon, I have selected OurBank as a Linked table in InvcHead (for the ARInvoice Form), I have selected all the required Description fields, I have saved the RDD then try to add them into the SSRS but they are still coming up blank when I print an Invoice.
I declared them as OurBank_field etc, I also tried adding them into the SQL as T1.OurBank_fields but still no joy.
Any ideas where I am going wrong?
Actually I think I know what the issue is the preferred bank needs to be on the customer record before the order is created… Create a baq on orderhed to show the ourbank column.
The xml file just use the report data definition app/screen and import it.
In Erp.OrderHed there is a field named OurBank. Create a simple baq that shows the order number and the ourbank field for debugging purposes.
So make sure your customer has the preferred bank setup, then create and order, for that customer and run the baq to confirm the results. If you see the ourbank field populated then the next place to look is the RDD, confirm that it is configured with the links correctly… (Suggest using the kinetic version of the Report Data Definistion app, it appears be a bit more forgiving.) Then if you believe the rdd is correct then look at the query on your RDL and confirm that the fields are all there. Hint if they don’t exist in the in the temp tables that are created. Then the report will error out.
Ok got you, I created the BAQ with InvcHead with OurBank and InvoiceNumber and found only one Invoice had the bank details so I printed it and all fields now appearing
I am going to apply the same logic to the ProForma as I can see when doing a BAQ with OrderHed that there is only one Order that has the bank.
As we are not live yet and the data I have on the Third environment is not complete it is understandable that these fields are not populated I suppose!
