qty on hand using sift tables from sql

ara3nara3n Member Posts: 9,255
edited 2007-07-09 in SQL Performance
Hello. A lot companies use outside systems to grab qty on hand for items. Most of the use item ledger. This store procedure uses the sift tables just like navision
Just change Company_Name to your company name and run the store procedure.


CREATE PROC GetQuantity @ItemNo varchar(20)='%',@Variant varchar(10)='%', @Location varchar(10)='%'
AS
SELECT f2 as ItemNo, SUM(s12) AS Quantity
FROM [Company_Name$32$0]
WHERE (bucket = 4) AND (f8 like @Location ) and (f5402 Like @Variant)
GROUP BY f2
HAVING (f2=@ItemNo)
Ahmed Rashed Amini
Independent Consultant/Developer


blog: https://dynamicsuser.net/nav/b/ara3n

Comments

  • DenSterDenSter Member Posts: 8,304
    Very cool! That works only as long as nobody touches the SIFT key in Navision. Don't you think it would be safer to do something similar from the Item Ledger?
  • ara3nara3n Member Posts: 9,255
    yes you could use the item ledger instead of sift table. It's not effecient to use item ledger when you have million of records.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ShenpenShenpen Member Posts: 386
    I think reading FlowFields from SQL can be generalized into a general template.

    1) Choose an Entry table. Put down the number of the table, this number will be called A.
    2) Look at Keys.
    3) Assume that you want a FlowField for a key that has SumIndexFields AND SIFTLevels, these are the "popular" key, however, if your choice doesn't have either of them, you have to create both.
    4) Count (from 0) the number of keys that have both, and put down which one is the key you want - f.e. if it's the second, put down 1. This will be called B.
    5) Drill into SIFTLevels and choose the level that contains all the flowfilters
    you wish to filter for. Put down the Bucket number, this will be called C.
    6) Now go to the table definition. Put down the number of the field which is
    related to the master data, the foreign key (I mean Item No., G/L Account No., Customer No.) etc. this will be called D.
    7) Put down the number of the Decimal field you want to sum for (it must be included in the SumIndexField). It will be called E.
    8 ) Put down the numbers of all flowfilters you wish to filter for, these will be called F,G,H...

    The SQL template is the following:
    select 
      fD,  
      sum(sE) 
    from 
      dbo.[Company_Name$A$B]
    where
      bucket=C
      --and you can filter for F,G,H here  
    group by
      fD 
    
    For example, to show the G/L balance by G/L Account No:

    - A = table numbr = 17
    - B = the first key with both SumIndexFields and SIFTLevels counted from zero = 0
    - C = bucket, if you don't need filtering for Posting Date, its = 1
    - D = the field number of G/L Account No. = 3
    - E = the field number of Amount = 17

    So the query is:
    select 
      f3,  
      sum(s17) 
    from 
      dbo.[Company_Name$17$0]
    where
      bucket=1
    group by
      f3 
    


    Although for reporting, as Analysis Services is for free, throwing all f-fields into dimensions and all S-fields into measures and throwing it into Excel looks like an easier solution... :)

    Is there a text representation of an OLAP cube in a similar way like a Navision object exported to Text? Because if yes, I could write a utility to autogenerate these cubes and then we would have powerful Navision reporting in Excel for free.

    Do It Yourself is they key. Standard code might work - your code surely works.
  • krikikriki Member, Moderator Posts: 9,086
    ara3n wrote:
    yes you could use the item ledger instead of sift table. It's not effecient to use item ledger when you have million of records.
    You can use "Remaining Quantity" to sum and put also a filter on Open=TRUE.
    In general in SQL it is a good idea to redesign the keys to your needs.
    E.g.
    In case you don't use variants, you can create a new key or put property SQLIndex of 1 key = "Item No.,Location Code,Open"
    This SQL-key can be used for a lot of Navision-keys.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • ara3nara3n Member Posts: 9,255
    No doubt kriki, I've suggested the same idea to a client a few years ago. Navision is using sift tables for a reason, and I just put the code just in case somebody wanted to know. I know I did a few years back.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • krikikriki Member, Moderator Posts: 9,086
    [Topic moved from Navision Tips & Tricks forum to SQL Performance forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • girish.joshigirish.joshi Member Posts: 407
    How does this method perform compared to just using C/Front and running calcfields?
  • ara3nara3n Member Posts: 9,255
    you could run sql profiler and see the sql statement. my guess it runs similar statement.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.