cancel
Showing results for 
Search instead for 
Did you mean: 

Use Sales/Purchase Analysis Rep. in our query ?

Grzegorz102
Explorer
0 Kudos

Hello,

can we use Sales/Purchase Analysis Rep. (Stored Procedures) with some parameters in our user queries/stored procedures ?

What are the possibilities ? What are the parameters ?

Has anyone have code of the Analysis procedures ?

Our client wants almost the same information as on Sales Analysis windows but with some added info from User Fields from documents.

The best (for them) would be with filtering function on that UDFs and with general and detailed view by clicking in the standard user query window but I know it's not simple or possible.

We use 7.50.15.22 ver. (based on 6.2 international)

Thanks in advance,

Grzegorz Meus

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

The guts of the query will be returned to you via the profiler if you have the correct Events set. It is just a matter of copying the select query from the profiler results.

In regards to encrypted SP - There are ways to de-crypt them if you look hard enough. But in saying this.. I do not recomend changing any of the SAP B1 SP in any manner. Use it for learning then create your own SP.

Former Member
0 Kudos

Rayner

Could you outline the correct settings to use in the profiler. I have used this to good effect in the past to look at what SBO is doing when running reports, but for stored procedures I have only seen it reference the Stored Procedure. From your posting, it looks possible to get the profiler to display the contents of the Stored Procedure.

Former Member
0 Kudos

Hi Paraic,

You want to look at the TSQL Events as well as the SP is used to build a dynamic SELECT statment (which is displayed in the profiler).

Be aware that the SP does a few iterations so you may see the same SELECT statement a few times. Copy all and run until you match what your B1 shows.

Once you have it you can modifiy to suit.

Grzegorz102
Explorer
0 Kudos

Ok,

let me know if you'll have the new ones

If it will not be available then I'll want to have a look at the old ones, just to make sure I do my own reports in good way...

Thanks in advance,

Grzegorz

Former Member
0 Kudos

i have code for this reports from previous version. Meybe its help You. I'll try to get newest source.

I let you know

Grzegorz102
Explorer
0 Kudos

Thanks for your information,

I have similar output in Profiler

EXECUTE TmSp_Sales_An_Crd_Sng_Y N'INV',N'SNG',N'20040401',N'20040413',N'',N'',N'LYRECO',N'LYRECO',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'0',N'',N'0',N'NON'

EXECUTE TmSp_Sales_An_Crd_Det_Y N'INV',N'SNG',N'20040401',N'20040413',N'',N'',N'LYRECO',N'LYRECO',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'0',N'',N'0',N'NON'

Finally, theses SPs are useless as they are encrypted and I can't use/change them to report also some additional UDFs.

Former Member
0 Kudos

Hi,

(B-One 6.6)

I have tried/want to do a simular task, but the problem comes when you try to pass multiple parameters(particularly dates types).

SAP are aware of these limitations.

If you want to find out what the parameters are you can run SQL profiler (run the SA report) then find the sp_XXX. this will show you the parameters passed.

It will look simular to this:

EXECUTE TmSp_Sales_An_Itm_Grp_Y N'INV',N'SNG',N'20040101',N'20041231',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'',N'0',N'',N'0',N'CRD'

But if you want to create a report on this you may run into problems. The B1 query tool can be limited when you want to do complex queries.

Regards