VBA IsError function in MDX with SQL Server 2005

In SQL Server 2000 Analysis Services, we use the VBA IsError function to determine if a generated member name actually exists in the cube. See the following example...

StrToMember (Iif (ISERROR(StrToValue("[Start Date].[Month].[" + Format(Now(),"mmm yyyy") + "]")), [Start Date].[Month].Members.Item([Start Date].[Month].Members.Count-3).UniqueName, MemberToStr(StrToMember("[Start Date].[Month].[" + Format(Now(),"mmm yyyy") + "]").Lag(2))))

I tried the same thing in SQL Server 2005 Analysis Services, but when I processed the cube, I got the following error:

MdxScript(Freight Analytics Proto) (36, 10) The '[VBA].[ISERROR]' function does not exist.

Is this because VBA is not installed on my Analsysis Server If so, is there a way to install VBA short of installing office If not, does anyone know what's wrong

Thanks.

Answer this question

VBA IsError function in MDX with SQL Server 2005

George Hatoun
In the Assemblies folder of the server in question, I do find the three assemblies that you referenced (System, VBAMDX, and ExcelMDX). The source for the VBAMDX assembly is "C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\msmdvbanet.dll". I checked that folder and the dll does indeed exist.

But are we looking in the right place The function "IsError" is an Excel function whereas CStr and Len are both VBA functions.

When I look at the properties for the ExcelMDX assembly in object explorer within management studio, the source property does not have a file listed like it does for the VBAMDX assembly. Should it If so what should it be I ran a test with the VBA "Len" function and it worked fine.

Another difference between the properties of these assemblies is that the VBAMDX assembly is set to ImpersonateServiceAccount whereas the ExcelMDX assembly is set to ImpersonateCurrentUser.

One other thing I should mention. I don't remember getting this previously, but now I am getting a secondary error after the "Function does not exist" error. The secondary error is: "Exception of type 'System.Runtime.InteropServices.ComException' was thrown".

Any more ideas about what's wrong
Geoff Van Brunt
With regard to the location of the IsError function. You are right. I overlooked it in the VBA function list when I was searching for it the first time. I see it in both the Excel and VBA lists. So why my Len function is found, but my IsError function does not is truly a mystery.

I am running the June CTP Enterprise edition. Actually, I have had the same problem on two different servers that I have installed on. In both cases, the installation completed successfully with no errors or warnings.

Just to have a very simple and complete example, I created a calculated measure based on the Adventure Works OLAP database available with the Analysis Services Tutorial in SQL Server 2005. I performed this entire test running SQL Server Business Intelligence Development Studio directly the server (not on my workstation). I created a calculated measure called Test first with, and and then without the IsError function. Without the IsError function, the measure was defined as:

[Employee].[Employee Name].CurrentMember.Name

Based on this definition, the calculated measure worked fine. Then I redefined it as follows:

IsError(
[Employee].[Employee Name].CurrentMember.Name,
"No Name",
[Employee].[Employee Name].CurrentMember.Name)

When I try to save (even without processing) I do get the error, "MdxScript(Analysis Services Tutorial)(78, 5) The '[IsError]' function does not exist". If I try to process, I get the above error followed by the second dialog with the error message "Exception of type 'System.Runtime.InteropServices.COMException' was thrown."

Finally, I replaced the definition of the calculated measure with a call to the Len function to verify that it works.

Len([Employee].[Employee Name].CurrentMember.Name)

This measure definition successfully saved, processed and executed.

That's the whole story.

Maybe I just need to wait and see if it works under the next CTP release.
ITJohn
Could someone tell me if there's a better place to post this question
Ritee
Chris,

I'm having a similar problem and in my case I'm attempting to use the Excel Percentile function. On my local MSAS2005 instance this works fine but when I attempt to deploy to my server I get the error.

Error 1 MdxScript(Margin) (18, 5) The '[Percentile]' function does not exist. 0 0

Looking at the Assemblies for ExcelMDX on my machine and the server they both have nothing in the source field.

Can you tell me on your instance where you can call Excel functions what the dll is named that the ExcelMDX assembly is referencing

Thanks,

Keith
tShunnar
This doesn't actually answer your question, but you might be interested to know that you don't need to use IsError in AS2005 to check for missing members any more - this functionality is now built in. See
http://www.sqljunkies.com/WebLog/mosha/archive/2005/06/09/mdx_missing_members_mode.aspx

HTH,
Chris
http://spaces.msn.com/members/cwebbbi/
Dat Dang
pretty sure you need excel installed on your server
PabloSmith
Kendal,

Check to make sure that the .Net assembly for VBA functions got installed correctly. If you connect to Analysis Services using Management Studio, browse down under the server to the Assemblies folder. Expand that and you should see (by default I think) three assemblies loaded: System, VBAMDX, and ExcelMDX.

Right-click on the VBAMDX entry (assuming it exists), and select Properties. Note the path (mine is "C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\msmdvbanet.dll") and check to make sure the .dll is actually at the location specified.

If VBAMDX is not a listed assembly, check the OLAP\bin path where you have Analysis Services installed and see if you have the file listed above. If so, you can try to manually register it with Analysis Services. Just right-click on the Assemblies folder, select New Assembly, and go from there.

I ran a few tests with IsError() as well as a few other VBA functions (like CStr() and Len()) and everything appears to work for me...

HTH,
Dave Fackler
Elisabeth1111
Hmm. Not sure. The IsError function was actually available in both the Excel library as well as the VBA library in Analysis Services 2000. Assuming the same is true for Analysis Services 2005, you should be able to use it based on either or both of the assemblies being registered.

The assembly information sounds correct (or at least it matches my setup).

It sounds like you have have some type of issue with your setup. Any possibility of uninstalling and reinstalling Analysis Services Also, what build are you running (June CTP or other)

Dave Fackler
VBA IsError function in MDX with SQL Server 2005

Answer this question