cancel
Showing results for 
Search instead for 
Did you mean: 

Bankdata in XL Reporter

Former Member
0 Kudos

Hi@all

where can i find the bankdatas in XL Reporter? i want to make a report to show, wich partners dont have any bankdatas. i know that it is part of the OCRD but i cant find it... please help...

regards

Bernhard

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Problem is solved by using an other way... if someone has an direktway, please post thanks

Former Member
0 Kudos

Hello Bernhard Maier,

To get a business partners default bank data, - OCRD -> BankCode field, you will need to work with the XL Report Designer (XL format of the XL Reporter) rather than the report composer. In the Report Designer there is a tool called the 'Formula Builder'. This tool can be used to access data in a table which is not available in the 'Report Composer'.

The 'Formula Builder' can be used to get the information you need in 'XL Reporter'. To do this, in the Formula Builder go to the 'Functions' tab. Expand the option 'Other' and then double click on the 'Get Other Data' title. This functionality gives access to the database tables and acts very much like an sql query in the xl reporter.

The 'Get Other Data' window has 5 fields:

User ID, Passwrod:

This is the user id and access password to the database.

Table:

Enter the table from which you want to pull information. In this case you should enter, OCRD

Fields:

Enter the fields which should be displayed in the report. The field names should be the field name visible in the table. For your question enter . Cardcode, BankCode.

Condition:

This section corresponds to the 'WHERE' section of an SQL query. The information written here should be exactly the same as the code written in the WHERE section of an SQL Query.

For your Question enter Bankcode = '-1'. The Bank Code is set to '-1' as this implies that there is no default bank defined for the Business Partner.

Use the Get Other Data functionalityin a cell where no other data exists as it will overwrite any other data in a cell. There is more detailed information on this functionin the XL Reporter on-line help.

I hope this helps

Kind Regards

Noreen

Former Member
0 Kudos

Hey Noreen Armstrong,

first of all, your explaining was perfekt. i was able to reconstruct your solution without problems and was hopeful that it works...

but it dosent... the report designer gave me an errormessege:

-


An Error occurred while Executing the report!

Description: Rows: 1033, Columns: 10

Unbekannter Fehlern (i think in english it means Unknown Failure)

Details:

OK

-


what can i do against this?

regards

Bernhard

Former Member
0 Kudos

i tried it again, and now the report was created succsessfull. but he gave me at each BP a '-1'. but there are some BO which have bankdatas... if i thought right, ther must be only the BO without bankdatas, or not?

regards

Bernhard

Former Member
0 Kudos

Post your query here. The logic may not be right in your query.

Former Member
0 Kudos

Hello Bernhard Maier ,

It might be a good idea to check the correctness of these resutls in the 'Query Generator' in SAP Business One. Run the query

SELECT T0.[CardCode] FROM OCRD T0 WHERE T0.[BankCode] <>'-1'

in the 'Query Generator' to see if any business partners have a Bank code. This should then correspond to the results of XL Reporter when the condition 'BankCode <> '-1' is used in the 'Get Other Data' - 'Condition' section.

I hope this helps with the issue

Regards

Noreen

Edited by: Noreen Armstrong on Sep 22, 2008 10:32 AM

Former Member
0 Kudos

Hey Noreen,

i generated the query and hey gave me 973 BP. if i compare the several list, no one of theme is the same...

the list of the xlreporter shows me after the modification from you the same like before, just withe the different, that in every line is -1 at the end. so he dont show me the BP without Bankdatas. Only the query is on the right way. but there i cant ad the neccessary information like DocDate, address and so on, its dufficult to describe...

to point the situation i give you a link with the exported reportdefinition:

[Definition for XLR|www.neopheus.de/lieferantenabfrage.zip]

regards

Bernhard

Former Member
0 Kudos

Its all done... i finished the hole thing by using a query. its a pity, that iam wasnt able to solve the problem with the xlr, but all in all, iam finish 😃

thanks for all help and post

Former Member
0 Kudos

Hi Bernhard,

A query is much beter tool to find the information you need. XLR will only link the table with relationship. You can query OCRD table directly to fulfill the task.

Thanks,

Gordon

Former Member
0 Kudos

okay, than with query...

i tried it with the xlr, cause my boss asked me if it is possible an if it needs less time. im just a apprentice in the third week and my task is to work with the xlr...

anyway if someone has a idea to show the things, please post it...

thanks for help

regards

Bernhard

Former Member
0 Kudos

I understand your situation now. The alternative is using Excel directly to create this report. An database source can be created to run query within Excel, so your boss will be happy to open that file since it is excel. Search in this forum to find out how.

Thanks,

Gordon

Former Member
0 Kudos

k, than i try it this way... thanks for help

Former Member
0 Kudos

Hi,Bernhard Maier .

You can Find Under Banking Module in XL Report but here you

can Retreive Data for Incoming Payments and Outgoing

Payments and Banking also.OCRD for BP Master Table and

Bank for RCT1 through Igoing Payamnetncoming payment and

VPM1 table for Bank through Outgoing Payment. May i knw

what are the Fields you want to get in Report.

Regds,

Samath kumar Devunuri.

Former Member
0 Kudos

yes, i found the several parts, but if i want to show the bankcode hes just show me the header of tha table and nothing else... i think its cause the table is empty, but some BP have bankdatas, so it cant be empty...

so i tell you my situation: i have to create a report which just shows me the supplier who dont have any bankdatas and who has open docs. reason is a customer who wants to use the payment engine of BusinessOne and so she hase to know which BP dont have bankdatas.

i hope you understand the situation and it may help to solve the problem...

regards

Bernhard

Edited by: Bernhard Maier on Sep 18, 2008 3:54 PM

Former Member
0 Kudos

Bank Details of BP are stored in OCRB table

Former Member
0 Kudos

sure? but BO shows me at the systeminformation that it is in OCRD... or is it dependent from the language, im using german...

Former Member
0 Kudos

okay, i know waht you mean (sorry, but im working just for 2weeks with the programms)... but how can i get access to them in the reporter? in BP there is no possibility, there is no attribute with bankcode etc...