qty on hand using sift tables from sql

ara3n

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)

DenSter

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?

ara3n

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.

Shenpen

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.

kriki

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.

ara3n

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.

kriki

[Topic moved from Navision Tips & Tricks forum to SQL Performance forum]

girish.joshi

How does this method perform compared to just using C/Front and running calcfields?

ara3n

you could run sql profiler and see the sql statement. my guess it runs similar statement.