Skip to Content
author's profile photo Former Member
Former Member

Use Sales/Purchase Analysis Rep. in our query ?

Posted on May 26, 2004 at 03:07 PM 83 Views

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

SAP Business One Extensibility
Add comment
10|10000 characters needed characters exceeded

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on May 27, 2004 at 11:02 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 08, 2004 at 06:00 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 11, 2004 at 12:30 PM

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

    I let you know

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 17, 2004 at 12:49 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jun 23, 2004 at 04:57 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Jun 25, 2004 at 09:50 PM

      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.