Skip to Content
author's profile photo Grzegorz Meus
0
Grzegorz Meus

Use Sales/Purchase Analysis Rep. in our query ?

Posted on May 26, 2004 at 03:07 PM 116 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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

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

    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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Grzegorz Meus
    Grzegorz Meus
    Posted on Jun 08, 2004 at 06:00 PM
    0

    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 a comment
    10|10000 characters needed characters exceeded

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

    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 a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Grzegorz Meus
    Grzegorz Meus
    Posted on Jun 17, 2004 at 12:49 PM
    0

    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 a comment
    10|10000 characters needed characters exceeded

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

    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 a 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.

    Comment on This Answer

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.