Infoset Codes - Cube Query

Infoset Optional Fields

Optional Field - select optional field to attach to this infoset

Optional Field Description - displayed

Value Set

Default Value - enter a default value if required

Value Description

Required - Enter "Yes" if this is a mandatory optional field

Auto Insert - enter "Yes" if this optional field is to be created when an Infoset Record is created

Note: The query is limited to 2500 characters - so keep dimension names and fact names relatively short

Example: by clicking the GO >> button, the following MS-SQL query would be generated for a Information set called "Budget" with three optional fields attached, BUDCOST, BUDQTY, BUDVALUE. The two key values to the infoset are ARCUSTOMER and ICITEM

Copy

Budget Infoset Query

select rTrim(ebinfo.field1) 
 + ' - ' + rTrim(ARCUS_Customer.[NAMECUST]) as Customer, 
rTrim(ebinfo.field2) 
 + ' - ' + rTrim(ICITEM_Item.[DESC]) as Item,     
cast(left(cast(ebinfo.infodate 
 as nvarchar), 4) + '/' + substring(cast(ebinfo.infodate as nvarchar),5,2) 
 + '/' + right(cast(ebinfo.infodate as nvarchar),2) as DateTime) as Infodate, 
     
cast(BUDCOST.value 
 as real) as BUDCOST,     
cast(BUDQTY.value 
 as real) as BUDQTY,     
cast(BUDVALUE.value 
 as real) as BUDVALUE    
from 
 ebinfo left outer join ebinfoo as BUDCOST on ebinfo.infokey = BUDCOST.infokey 
 and BUDCOST.optfield = 'BUDCOST'     
left 
 outer join ebinfoo as BUDQTY on ebinfo.infokey = BUDQTY.infokey and BUDQTY.optfield 
 = 'BUDQTY'     
left 
 outer join ebinfoo as BUDVALUE on ebinfo.infokey = BUDVALUE.infokey and 
 BUDVALUE.optfield = 'BUDVALUE',     
ARCUS 
 as ARCUS_Customer,     
ICITEM 
 as ICITEM_Item    
where 
 ebinfo.infoset = 'BUDGET' and ebinfo.field1 = ARCUS_Customer.IDCUST and 
 ebinfo.field2 = ICITEM_Item.ITEMNO

Actual vs Budget Reporting

Example using Order Entry

To report on Budget vs Actual using the example infoset described above (ie both sets of data in the cube at the same time),

a) put a place holder in the first query for the cost (ACTCOST), quantity (ACTQTY) and value (ACTVALUE) to be returned

Copy

Budget Query

SELECT rTrim(ebinfo.field1) 
 + ' - ' + rTrim(ARCUS_Customer.[NAMECUST]) as Customer, 
rTrim(ebinfo.field2) 
 + ' - ' + rTrim(ICITEM_Item.[DESC]) as Item,     
cast(left(cast(ebinfo.infodate 
 as nvarchar), 4) + '/' + substring(cast(ebinfo.infodate as nvarchar),5,2) 
 + '/' + right(cast(ebinfo.infodate as nvarchar),2) as DateTime) as Infodate, 
cast(BUDCOST.value as real) as BUDCOST,     
cast(BUDQTY.value as real) as BUDQTY,     
cast(BUDVALUE.value as real) as BUDVALUE    
FROM 
 ebinfo left outer join ebinfoo as BUDCOST on ebinfo.infokey = BUDCOST.infokey 
 and BUDCOST.optfield = 'BUDCOST'     
left outer join ebinfoo as BUDQTY on ebinfo.infokey = BUDQTY.infokey and BUDQTY.optfield 
 = 'BUDQTY'     
left outer join ebinfoo as BUDVALUE on ebinfo.infokey = BUDVALUE.infokey and 
 BUDVALUE.optfield = 'BUDVALUE',     
ARCUS as ARCUS_Customer,     
ICITEM as ICITEM_Item    
WHERE 
 ebinfo.infoset = 'BUDGET' and ebinfo.field1 = ARCUS_Customer.IDCUST and 
 ebinfo.field2 = ICITEM_Item.ITEMNO

b) use SQL Server Management Studio to write a query to summarise the actuals to the same level, insert the budget place holders and union the result to the budget result,

Copy

Budget UNION Actual Query

--Budget from Info Manager Optional Tables (as above)
SELECT 
 rTrim(ebinfo.field1) + ' - ' + rTrim(ARCUS_Customer.[NAMECUST]) as Customer, 
 rTrim(ebinfo.field2) 
 + ' - ' + rTrim(ICITEM_Item.[DESC]) as Item,        
cast(left(cast(ebinfo.infodate 
 as nvarchar), 4) + '/' + substring(cast(ebinfo.infodate as nvarchar),5,2) 
 + '/' + right(cast(ebinfo.infodate as nvarchar),2) as DateTime) as Infodate,      
cast(BUDCOST.value 
 as real) as BUDCOST, cast(BUDQTY.value as real) as BUDQTY, cast(BUDVALUE.value 
 as real) as BUDVALUE, 0 as ACTCOST, 0 AS ACTQTY, 0 AS ACTVALUE          
FROM 
 ebinfo 
 left outer join ebinfoo as BUDCOST on ebinfo.infokey = BUDCOST.infokey 
 and BUDCOST.optfield = 'BUDCOST'        
left outer join ebinfoo as BUDQTY on ebinfo.infokey = BUDQTY.infokey and BUDQTY.optfield 
 = 'BUDQTY'                    
left outer join ebinfoo as BUDVALUE on ebinfo.infokey = BUDVALUE.infokey and 
 BUDVALUE.optfield = 'BUDVALUE',                    
ARCUS as ARCUS_Customer, ICITEM as ICITEM_Item                   
WHERE 
 ebinfo.infoset = 'BUDGET' and ebinfo.field1 = ARCUS_Customer.IDCUST and 
 ebinfo.field2 = ICITEM_Item.ITEMNO  
      
UNION ALL
-- Actual
SELECT 
 rtrim(customer) + ' - ' + rtrim(max(arcus.namecust)), 
rtrim(item)  + ' - ' + rtrim(max(icitem.[desc])),        
cast(left(cast(trandate as nvarchar), 4) + '/' + substring(cast(trandate as nvarchar),5,2)+ '/' 
 + right(cast(trandate as nvarchar),2) as datetime),         
0, 0, 0,         
sum(fcstsales), sum(qtysold), sum(famtsales)-sum(fretsales)         
FROM 
 OESHDT, ARCUS, ICITEM
 WHERE OESHDT.CUSTOMER = ARCUS.IDCUST AND OESHDT.ITEM = ICITEM.ITEMNO 
GROUP BY CUSTOMER, ITEM, TRANDATE

c) add the ACTCOST, ACTQTY, ACTVALUE to the fact list and save the query

Example using General Ledger

Step 1: Create an infoset called FORECAST

It has one viewtype called GLACCOUNT, and one optional field called BUDVALUE.

Clicking the query GO (>>) button gives you the following query. It results in a cube with one dimension for the full account, budget values only.

Copy

GL Budget Infoset query

SELECT  
 rTrim(ebinfo.field1) + ' - ' + rTrim(GLAMF_GLAccount.[ACCTDESC]) as GLAccount, 
 cast(left(cast(ebinfo.infodate as nvarchar), 4) + '/' + substring(cast(ebinfo.infodate as nvarchar),5,2) 
 + '/' + right(cast(ebinfo.infodate as nvarchar),2) as DateTime) as Infodate,
cast(BUDVALUE.value as real) as BUDVALUE     
FROM 
 ebinfo 
 left outer join ebinfoo as BUDVALUE on ebinfo.infokey = BUDVALUE.infokey 
 and BUDVALUE.optfield = 'BUDVALUE', GLAMF as GLAMF_GLAccount     
WHERE 
 ebinfo.infoset = 'FORECAST' and ebinfo.field1 = GLAMF_GLAccount.ACCTID

Step 2: Add a field for each G/L Account segment

To have a dimension for the 3 segments of the account, replace the query with the following. This splits the account into the 3 segments - account, division and region, and gets the associated descriptions and puts a placeholder for the actual values as 0

Copy

GL Query with segments as fields

 SELECT  
 rtrim(ACSEGVAL01) + ' - ' + rtrim(naccount.acctdesc) as Account, 
rtrim(ACSEGVAL02)  + ' - ' + rtrim(division.segvaldesc) as Division,        
rtrim(ACSEGVAL03)  + ' - ' + rtrim(region.segvaldesc) as Region,       
cast(left(cast(ebinfo.infodate as nvarchar), 4) + '/' + substring(cast(ebinfo.infodate as nvarchar),5,2) 
 + '/' + right(cast(ebinfo.infodate as nvarchar),2) as DateTime) as Infodate, 
cast(BUDVALUE.value as real) as BUDVALUE, 0.00 as ACTVALUE    
FROM 
 ebinfo left outer join ebinfoo as BUDVALUE on ebinfo.infokey = BUDVALUE.infokey 
 and BUDVALUE.optfield = 'BUDVALUE',    
GLAMF AS naccount LEFT OUTER JOIN GLASV AS division ON ACSEGVAL02 = division.SEGVAL 
 AND DIVISION.IDSEG = '000002'          
LEFT OUTER JOIN GLASV AS REGION ON ACSEGVAL03 = DIVISION.SEGVAL AND DIVISION.IDSEG 
 = '000003'          
WHERE 
 ebinfo.field1 = naccount.ACCTID and ebinfo.infoset = 'FORECAST'

Step 3: UNION ALL to add Actual Data from GLPOST table

Tip: multiply "i" income accounts by -1 to show a positive number in the cube

Copy

UNION Budget and Actual Query

SELECT rtrim(ACSEGVAL01) + ' - ' + rtrim(naccount.acctdesc) as Account, 
rtrim(ACSEGVAL02)  + ' - ' + rtrim(division.segvaldesc) as Division,        
rtrim(ACSEGVAL03)  + ' - ' + rtrim(region.segvaldesc) as Region,       
cast(left(cast(ebinfo.infodate as nvarchar), 4) + '/' + substring(cast(ebinfo.infodate as nvarchar),5,2) 
 + '/' + right(cast(ebinfo.infodate as nvarchar),2) as DateTime) as Infodate, 
cast(BUDVALUE.value as real) as BUDVALUE, 0.00 as ACTVALUE    
FROM 
 ebinfo left outer join ebinfoo as BUDVALUE on ebinfo.infokey = BUDVALUE.infokey 
 and BUDVALUE.optfield = 'BUDVALUE',    
GLAMF 
 AS naccount LEFT OUTER JOIN GLASV AS division ON ACSEGVAL02 = division.SEGVAL 
 AND DIVISION.IDSEG = '000002'          
LEFT 
 OUTER JOIN GLASV AS REGION ON ACSEGVAL03 = DIVISION.SEGVAL AND DIVISION.IDSEG 
 = '000003'          
WHERE 
 ebinfo.field1 = naccount.ACCTID and ebinfo.infoset = 'FORECAST'      
 
UNION ALL

SELECT rtrim(ACSEGVAL01) + ' - ' + rtrim(naccount.acctdesc) as Account, 
rtrim(ACSEGVAL02) + ' - ' + rtrim(division.segvaldesc) as Division,        
rtrim(ACSEGVAL03) + ' - ' + rtrim(region.segvaldesc) as Region,       
cast(left(cast([JRNLDATE] as nvarchar), 4) + '/' + substring(cast([JRNLDATE] as nvarchar),5,2) + 
 '/' + right(cast([JRNLDATE] as nvarchar),2) as DateTime) as Infodate,         
0.00 AS BUDVALUE,        
case naccount.acctbal when 2 then (cast(transamt as real) * -1) else cast(transamt 
 as real) end as ACTVALUE        
from 
 glpost,       
GLAMF 
 AS naccount LEFT OUTER JOIN GLASV AS division ON ACSEGVAL02 = division.SEGVAL 
 AND DIVISION.IDSEG = '000002'      
LEFT 
 OUTER JOIN GLASV AS REGION ON ACSEGVAL03 = DIVISION.SEGVAL AND DIVISION.IDSEG 
 = '000003'          
WHERE GLPOST.ACCTID = naccount.ACCTID

Step 4: And add the new dimensions Account, Division, Region, Infodate as show below

Step 5: Ensure the facts are BUDVALUE and ACTVALUE as shown below