Modifying SBO reports

[Former Member]

Hi!

What would be the simplest way to modify an SBO reprt?

I have added a user field to OITM (item master data) and I want to modify the Sales Analysis report so that I have the field as one of the selections criteria for items.

My idea is to put in some new items on the form for the user to enter their selection criteria for this field (which is pretty straight foreward)...

... but then how do i get the report generated to include the selection criteria for my user field?

Can i somehow trap the query before it is executed and modify it using SQL?

This is pretty urgent, any help is much appreciated.

[former_member185703 · Accepted solution]

Hi Wilfred,

Unfortunately there is no API to modify the built-in reports to your needs today (layout, queries, results). Instead you can only:

- Use Queries to collect the data and define some lauyout for printing - or export the data to Excel for further processing OR

- Intercept the launch of the report (from the Selection Criteria from) + do the same as above OR

- make XML copies of the forms + plug-in your functionality (will look a bit different as far as the report is concerned) + define a layout etc etc

Maybe someone else had better ideas?

Sorry,

Frank

[Former Member · Accepted solution]

> Hi Wilfred,

>

> Unfortunately there is no API to modify the built-in

> reports to your needs today (layout, queries,

> results). Instead you can only:

> - Use Queries to collect the data and define some

> lauyout for printing - or export the data to Excel

> for further processing OR

> - Intercept the launch of the report (from the

> Selection Criteria from) + do the same as above OR

> - make XML copies of the forms + plug-in your

> functionality (will look a bit different as far as

> the report is concerned) + define a layout etc etc

>

> Maybe someone else had better ideas?

>

> Sorry,

> Frank

Hi Moebius i pretty much figured out there is no way to do what i need through the API. So I was thinking about your second suggestion: Intercept the launch of the report from the selection criteria, could you pass some sample code so I have somewhere to start?

Thanks.

[former_member185703 · Accepted solution]

In the ItemEvent handler just capture the "OK":

' In the "Inactive Customers" report capture pressing the "OK" button:

If pVal.FormTypeEx = "92" And _
  pVal.ItemUID = "1" And _
  pVal.EventType = SAPbouiCOM.BoEventTypes.et_ITEM_PRESSED And _
  pVal.BeforeAction Then

      ' Action!
      ' run your report here...

  ' suppress further handling through B1 application
  BubbleEvent = False 
End If

If you would need further initial guidance, I recommend looking at the SDK E-learning:

https://www.sdn.sap.com/irj/sdn/developerareas/businessone?rid=/webcontent/uuid/6207e283-0a01-0010-6... [original link is broken])

+ then (if necessary at all) ask more questions on SDN.

For further documentation on SDK please consult the SDK Helpcenter package.

You can download it through a link on the SAP Business One Developer Area on SDN as well.

However, exporting the data to Excel - or running the reports from there (reading B1 data) may of course be another option - but I guess you were already aware of that and wanted to have a seamless integration?

HTH though!

Message was edited by: Frank Moebius

[Former Member]

I faced the same problem 6 months ago when I started working with SAP; by far the best and simplest solution was to use Excel - it's a bit slower but you can write whatever query you want, no need to muck about intercepting messages, etc.

Normally I either put a button on an Excel toolbar or just use the default "Refresh Data" button, pop up a dialog box asking for whatever criteria, and run a stored procedure to get my report data. If you are not sure what the SQL should be, put a trace on the server when you run a report, and copy the SQL, then modify it to add your own extra criteria.

The beauty of using Excel is that once the data is on the sheet, you can do whatever you like with it using VBA. For example most of my reports have several grouping levels which use all kinds of summing and averaging functions (e.g some columns summed, some averaged, others with even weirder functions which even XLReporter can't provide), and although I have to write the code to process the data row by row in VBA its worth it as the reports do exactly what I want, are not to difficult to change if you build a decent template-like framework in your code (e.g a generic routine for looping through the rows abnd grouping data then performing the maths), and can be run outside of SAP which is a big plus for portability - I can publish them on our extranet, push them out to our rep's handheld computers, and so on, plus you can slap in graphs and stuff to keep your boss happy.

Of course, if your reports are not doing anything fancy, did you consider trying something like XLReporter, its handy for some kinds of reports.

[Former Member]

Thanks for the pointer but i am looking for a solution that will be somehow intrinsic to SBo instead of running the report outside of SBO.