"Syntax error near a" and the Acinta_Split() function

I have come across this error while compiling the view/dynamic view "ACINTA_DV LedgerOfAccountsRelation" in the C5 data model:
"Syntax error near a".

It happens in this line of the view:

    SELECT COUNT(*)
    FROM Acinta_Split(';', a.TXLLEVXRK) x

The error occurs because SQL Server does not recognize the function "Acinta_Split", which is part of the Acinta Shortcut package.

Now, the function was actually compiled and present in the system, so the error seems mysterious at first.
It turns out that the database was configured to have compatibility level 80 = SQL Server 2000.

And since table-based functions were introduced in SQL Server 2005 it is also the natural explanation why SQL Server throws a syntax error at us.

Solution

To get out of this error you have two choices:

1. Set the compatibility level of the database to at least 90 (SQL Server 2005)
2. Comment out the parts of the view that make use of the Acinta_Split function. Block comments have the form: "/* <commented code> */

Obviously, you should choose solution 1. whenever possible. But under some circumstances this is not possible. One such circumstance is when you have a C5 version 3.0 solution, because this version of C5 is only compatible with SQL Server 2000; It does not work with later versions of SQL Server.

Applies to

Please note that this error and its solution applies generally and not just to the C5 solution. I.e. it may apply to:

• Acinta Shortcut for C5
• Acinta Shortcut for Ax
• Acinta Shortcut for Nav
• Acinta Shortcut for XAL
• Acinta Shortcut for Visma

Datashop crashes at startup

If you are experiencing that Datashop crashes before it even displays its splash screen and you get a message from Windows that the application crashed then the cause is probably restrictions on the user's permissions.


The possible scenario is that you are trying to make an installation without running the Acinta installer, e.g. a network installation. The installation works fine for you (because you use a classified account, e.g. Administrator) but it fails on end-users' PCs.

 FIX >   Right-click on the exe-file or shortcut and choose "Run as administrator...". You only need to do this once. Next time you open Datashop it will work fine.

Project takes long time to load - slow loading

Sometimes when you have installed a new project or you are opening the project from a different computer you may experience that the loading of the project is notably slow.
There are some likely causes for this situation.

Possible causes

Old version of Acinta Intelligence Suite

Check if the version of Intelligence Suite you are trying to open is up to date. If metadata was edited using a newer version then it may have stored properties that are not understood by older versions. When this happens, an error trapping mechanism is invoked that writes all the unrecognized properties to the log file. You can verify the problem by investigating the log file.

FIX >  Update the .exe files to a new version.

Slow network

A lot of metadata must be transferred over the network when Datashop, Modeller etc. opens a project. A slow network may cause this process to take a very long time. Note: Look out for wireless networks since these are notably slower than wired networks.

FIX >  Wire the PC to a cable or upgrade/fix problems in the (wireless) network.

Fiscal/financial year date calculations

As a followup to my previous article on calculation of dynamic dates I will demonstrate how we can make similar calculations that take the company's financial year into consideration.

This is important for companies that have a financial year that is not coinciding with the calendar year. In Denmark the financial year typically starts at the beginning of a quarter, i.e.:

• 1st of January
• 1st of April
• 1st of July, or
• 1st of October

Thus, if in our dashboard we want to use a "year-to-date" filter then we actually want the filter to span from the beginning of the financial year till today's date.

Such a filter condition can be implemented using this SQL:

DATEADD(dd, 0, DATEADD(mm, 6, DATEADD(yy, CASE WHEN MONTH(getdate()) >= 7 THEN YEAR(getdate()) ELSE YEAR(getdate()) - 1 END - 1900, 0)))

The result of this expression (assuming today's date is January 13th 2012) is:

'2011-07-01'

i.e. an ISO formatted date, which should be valid in any SQL Server.

The above example assumes that the financial year starts on July 1st, but obviously you can use the approach for any start day of the financial year.

To change the starting month of the financial year you simple have to modify the script in two places:

• 6: Replace this value with the last month in the financial year
• 7: Replace this value with the first month in the financial year

Filter modifiers
As usual, date functions are very useful with filter modifiers. Using a filter modifier you can implement a report where the user selects a date and then your filter modifier transforms the selection into a "year-to-date" date range using the user's selection.

To use the above script in a filter modifier you simply have to replace the occurences of "getdate()" with the desired filter modifier placeholder, {Value1} or {Value2}.

I.e. you need to use this variation of the SQL expression:

DATEADD(dd, 0, DATEADD(mm, 6, DATEADD(yy, CASE WHEN MONTH({Value1}) >= 7 THEN YEAR({Value1}) ELSE YEAR({Value1}) - 1 END - 1900, 0)))

How to calculate dynamic dates in SQL Server

Quite often we need to calculate some kind of dynamic date based on the current date or the user's selection in a date filter. Examples include "First day of the year", "First day of the month" and so on.

To help you build such date formulas I have assembled a set of useful calculations for you to use.

Note: To use the calculations in Filter Modifiers you simply replace "getdate()" with the desired filter placeholder, e.g. "{Value1}".

First Day of Month

DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

Monday of the Current Week

DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

First Day of the Year

DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

First Day of the Quarter

DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

Midnight for the Current Day

DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

Last Day of Prior Month

DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0))

Last Day of Prior Year

DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

Last Day of Current Month

DATEADD(dd,-1,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

Last Day of Current Year

DATEADD(dd,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1, 0))

First Monday of the Month

DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())), 0)

Fejl: "Repository databases are not correctly configured" - problem med danske tegn

Hvis du får beskeden:

---------------------------
Warning
---------------------------
Repository databases are not correctly configured.
You may re-configure repository from menu: File|Configure repository.

når du åbner Datashop/Modeller, så skyldes det muligvis problemer med tegnsæt og ukendte tegn i Projects.dspx filen.


Åbn din Projects.dspx fil og kontrollér den for danske bogstaver og andre specialtegn og fjern dem.


En anden mulighed - men dette har jeg dog ikke testet - er at gemme filen i utf8 Unicode format.

ISO ugenumre i AcTimeDim - rettelse til fejl i ugenumre

Nogle ældre versioner af metadata havde desværre fejl i ugenumrene.
Det er let at rette ugenumrene til korrekte ISO ugenumre, dvs. de ugenumre, der anvendes bredt i Europa med nedenstående scripts:


For at rette ugenumrene skal du gøre flg.:

1.       Åbn SQL Server Management Studio
2.       Åbn en query
3.       Vælg databasen ”AcintaXXMeta” (NN er systemnavnet)
4.       Indsæt nedenstående SQL og eksekvér det

create function dbo.F_ISO_WEEK_OF_YEAR
  (
  @Date      datetime
  )
returns int
as
/*
Function F_ISO_WEEK_OF_YEAR returns the
ISO 8601 week of the year for the date passed.
*/
begin
declare @WeekOfYear int
select
  -- Compute week of year as (days since start of year/7)+1
  -- Division by 7 gives whole weeks since start of year.
  -- Adding 1 starts week number at 1, instead of zero.
  @WeekOfYear =
  (datediff(dd,  case  -- Case finds start of year
    when NextYrStart <= @date then NextYrStart
    when CurrYrStart <= @date then CurrYrStart
    else PriorYrStart
  end,@date)/7)+1
from
  (
  select
  -- First day of first week of prior year
    PriorYrStart =
      dateadd(dd, (datediff(dd,-53690,dateadd(yy,-1,aa.Jan4))/7)*7,-53690),
  -- First day of first week of current year
    CurrYrStart =
      dateadd(dd,(datediff(dd,-53690,aa.Jan4)/7)*7,-53690),
  -- First day of first week of next year
    NextYrStart =
      dateadd(dd,(datediff(dd,-53690,dateadd(yy,1,aa.Jan4))/7)*7,-53690)
    from
    (
       select
       --Find Jan 4 for the year of the input date
         Jan4 =
dateadd(dd,3,dateadd(yy,datediff(yy,0,@date),0))
    ) aa
  ) a
return @WeekOfYear
end

go

UPDATE AcTimeDim
SET CalendarWeek = CAST(CASE WHEN (dbo.F_ISO_WEEK_OF_YEAR(AcDate) > 50) AND(Month(AcDate) = 1) THEN YEAR(AcDate) - 1 ELSE YEAR(AcDate)
END AS CHAR(4)) + '-' + RIGHT('0' + CAST(dbo.F_ISO_WEEK_OF_YEAR(AcDate) AS VARCHAR(2)), 2)