I have an MDX query that runs fine on everything except my newest instance of AS 2005. The query is...
WITH
MEMBER [Measures].[Pct] AS ' CDbl([Measures].[Page Views]) / 100.0 '
SELECT
{
[Measures].[Page Views], [Measures].[Pct]
} ON COLUMNS ,
LastPeriods(
1,
[Time].[Calendar].[Month].&[200701]
) ON ROWS
FROM [Web Hit Total]
... and the error is ...
The '[CDbl]' function does not exist.
Thanks in advance for any help!
Keith Spitz, Wall Street On Demand, Inc.
The CDbl function is in the VBA external library. You need to reference it like VBA!CDbl([Measures].[Page Views])
HTH,
Martin
Excellent! That works just fine.
Now for the next part of the question: what I'm actually doing is using CDbl() (and, by the way, IsNull()) in a calculated member. Prepending with "VBA!" doesn't work there -- any ideas
Thanks again!
Keith...
P.S. Seems to be an IA64 thing -- read somewhere the code as I have it will work fine in 32 bit environments but not on IA64.
I once had a weird situation on an IA64 machine where I could only get the VBA functions working by prefixing them with VBAMDX instead of VBA (VBAMDX is the name of the assembly on the server). I found that this worked on both IA64 and 32bit editions. Not sure why this is, as far as I understand the VBA prefix is meant to work on IA64, but I don't have any other IA64 servers that I can check this against.
I'm not sure if IsNull() exists as a function or not, but you will find that the IsEmpty() function is equivalent.
Prepending with VBA is not mandatory and in AS 2005 there is no limitation on using VBA on IA64. I recall fixing a problem when server randomly failed to find valid VBA functions. What version are you using Is it SP1 Could you try this with SP2
Calling VBA functions is somewhat expensive, so if you can avoid doing it, please do (you can read more about that here
http://www.e-tservice.com/Files/vba_functions_in_as2005.doc). What is the data type of Page Views If it's a numeric type, perhaps type conversion is not necessary
We are running this on AS 2005 SP2 (9.00.3042.00). However, I was mistaken -- we're running on a 32-bit box, not a 64-bit box. The OS is Windows Server 2003 Enterprise SP1.
Interestingly, the values I am converting to doubles are integers, but it turns out not to be important as the division of two integers appears to yield a float in my result set.
It sounds like eliminating VBA functions is a good idea, but for the short term I need to try to make them work to support an existing code base.
Thanks for your thoughts! Got any others ;-)
Keith...
Keith, if one the same server CDBL works in some cases, but doesn't work in other , it's a bug and you should contact Customer Support.
I do believe I found the problem.
AS2005 was recently upgraded to SP2 on the server in question. It looks like the installation directory changed in the process, and the DLL associated with VBANET was moved. However, by drilling into VBANET in the Assemblies folder on the server, I noted that AS2005 thinks the DLL is still in it's *old* location. I can't find how to change the "Source" property of an Assembly, but in the meantime copying the DLL from the new location to the old one works -- the VBA functions now work.
Now to figure out how to get AS2005 to point at the right place...
Thanks everyone for your help!
Keith...
Oh, I know what might happen. The directory where AS looks up for dll is stored in the data directory (it's generated on the very first start up of the server). If you have installed SP2 into a different folder then SP1, that path still points to the old location.
To fix this, go to the data directory (by default C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data) and modify VBAMDX.0.asm.xml file. The following line should point to the new bin directory. <Name>D:\Sputnik\debug\x86\msmdvbanet.dll</Name>
Hope this helps,
Irina
Irina,
You are a wizard! That did the trick. Thanks very much!
Keith...