“ALCH_DATE_DIM” table exist in the Sage 300 database.
This table is used in the sales analysis Container.
at what time this table is created, (when activating or through an import)?Is there a script to create this table? how this table will be updated automatically upon creating new year?
The issue is this table only have 3 Financial years at our customer,and if we create the new GL financial year this table is not updated, so the sales analysis report doesn’t have the option to select the new fiscal year when running the report..
Thank you for your help
What Sales Analysis container is this? Is it for a standard report?
It's the Sales Analysis Container3-0(AE-SQL), yes it is for the Sales Master 3-0(AE-SQL) standard report.
Hi Tony, im looking at the container u mentioned above and this is what i see. I don't see that ALCH_DIM table. Are you looking somewhere else? Or was this container edited by someone?
LEFT JOIN [ARCUS]
ON [OESHDT].[CUSTOMER] = [ARCUS].[IDCUST]
LEFT JOIN [ARGRO]
ON [ARCUS].[IDGRP] = [ARGRO].[IDGRP]
LEFT JOIN [ICITEM]
ON [OESHDT].[ITEM] = [ICITEM].[ITEMNO]
LEFT JOIN [ICCATG]
ON [ICITEM].[CATEGORY] = [ICCATG].[CATEGORY]
LEFT JOIN [ICLOC]
ON [OESHDT].[LOCATION] = [ICLOC].[LOCATION]
LEFT JOIN [ARSAP]
ON [OESHDT].[SALESPER] = [ARSAP].[CODESLSP]
INNER JOIN [CSCOM]
ON [CSCOM].[CONAME] = [CSCOM].[CONAME]
that's what i saw, and it wasn't modified by anyone:
LEFT JOIN [alch_view_sales_anal]
ON [alch_date_dim].[date_value] = CAST(CAST([alch_view_sales_anal].[INVDATE] AS varchar) AS datetime))
INNER JOIN [CSCOM]
ON [CSCOM].[CONAME] = [CSCOM].[CONAME])
also if you go to the field Fin Year:
SELECT DISTINCT [alch_date_dim].[fin_year] FROM [alch_date_dim] ORDER BY [alch_date_dim].[fin_year]
is this the report you are looking at?
and this is the container:
sorry i did a mistake,
the report is the Sales analysis cube
aha i c, i thought that might be the case, is there any reason why you are using that report vs the standard sales master report?
it's my customer who is using it,but we wanted to understand the logic for that table, because it doesn't seems to update when creating the new financial year, and what procedure would create that table?
if my memory serves me correct, you would have to run the cube in olap manager first, then run it in report manager. Sorry but my memory is very fuzzy on this, those cube reports are pretty old.
the customer doesn't have the olap manager.
hmmm, then the only other thing i would suggest is contacting support, they would have to remote it in to get specific detail on the set up and hopefully can find your customer a solution.