qty on hand using sift tables from sql

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?

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.

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