.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSAS - Excel client does not take into account defined FORMAT_STRING

Posted By:      Posted Date: September 27, 2010    Points: 0   Category :Sql Server

Hi all

I set the FORMAT_STRING property of a calculated member. Excel does not take into account the set format in a pivot table. Do I have to check a box somewhere in Excel? Thanks for your answer

View Complete Post

More Related Resource Links

With Excel pivot table client using SSAS cube, how to change the measures to rows?

I want the measures as rows instead of columns in Excel pivot table on a SSAS 2008 cube. How do I do that? Thanks.

Sign outgoing body from client with custom endpoing behavior defined in code

I'm trying to sign (and sign only) the body of every outgoing message that uses this custom endpoint behavior.  When I first created this it was for signing a custom SOAP header.  We are moving away from that and going to just sign the body.  We have some applications that have 10+ web service references.  We don't want to touch the reference.cs for anything.  Below is the code I had before for signing the custom soap header.  I'm trying to modify it to just sign the body.  My modified code is below, and the error I get. Private Class CustomHeaderBehavior Implements ServiceModel.Description.IEndpointBehavior Public Sub New() End Sub Private Sub AddBindingParameters( _ ByVal endpoint As ServiceModel.Description.ServiceEndpoint, _ ByVal bindingParameters As ServiceModel.Channels.BindingParameterCollection) _ Implements ServiceModel.Description.IEndpointBehavior.AddBindingParameters Dim body As New Xml.XmlQualifiedName("Body", "http://schemas.xmlsoap.org/soap/envelope/") Dim BodyMsgPartSpec As New ServiceModel.Security.MessagePartSpecification() BodyMsgPartSpec.IsBodyIncluded = True Dim requirements As ServiceModel.Security.ChannelProtectionRequirements = bindingParameters.Remove(Of ServiceModel.Security.ChannelProtectionRequirements)() requirements

connect client certificate to an account in a membership database

Hello I have created a web service that authenticates with username and password, works fine.Basically this one, http://msdn.microsoft.com/en-us/library/ff649647.aspxNow I also want to connect to this web service using client certificates, works finehttp://msdn.microsoft.com/en-us/library/cc948997.aspx But I would like to when authenticated via client certificates, connect that certificate to a user in the membership database.So that I can use Roles.IsUserInRole(...) and such.I thought that, well if I implement a Custom certificate Validatorhttp://msdn.microsoft.com/en-us/library/ms733806.aspxthen I could check for example subject and map that against a created username in the membership database.But in the class X509CertificateValidatorpublic override void Validate(X509Certificate2 certificate)I don't have the same ability as when the user is authenticatedlike  void OnAuthenticateRequest(object source, EventArgs eventArgs)HttpApplication app = (HttpApplication)source;Basically how can I do this app.Context.User = new GenericPrincipal(new GenericIdentity(username, "Membership Provider"),roles);withinpublic override void Validate(X509Certificate2 certificate)and if that is not possible, can this be solved differently?Bottom line, how do I connect a client certificate to a user account in the membership database. Is there a MSDN article

Enhance SSAS so that a calculation returns multiple cells at once (Excel fill not a single cell but

I wonder if somebody can help me with the following question. I would like to create an enhancement to SSAS in a way so that one can pass in multiple values, run a calculation and in return retrieves not one but multiple cell results at the same time e.g. retSet = functionABC(inSET) where inSet is somthing like 5 4 3 6 7 3 4 4 AND retSet is 4 6 7 8 9 3 4 4 All my calculated members in MDX return a single value BUT I need to have a calculation that if called returns multiple results (a set) at once and in a way so that Excel will properly pick up on it and consequently fills multiple cells in the Pivot table. Is that possible at all and if so how? I would not mind to write a little CLR assembly to enhance the SSAS but don't know how to pass the results back. Any ideas are highly appreciated. Dirk

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

Font didn't change in excel, after change was made on SSAS

Hello!   After I wrote MDX, I gave to my calculated field a format of “courier New” and font size of ‘9’. When I connected to SSAS using excel, and pulled my data but I don’t see the right format that I used. It is not working for any format. I used 2008 SSAS. What is the issue? Thx, Beney

Cannot List SSAS Catalogs in Excel 2007

I am trying to access SSAS 2005 through excel on a client computer.  I get the following error message:  The data connection wizard cannot obtain a list of databases from the specified source.  It looks like I connect, but I cannot list any of the catalogs.  Both catalogs are migrated from a sql2000 database.    I ensured that the account has a browsable role through the roles in SSAS.  Interestingly if I create a UDL file with a connection to ssas and choose existing connections and specifiy the udl file I can access the specific ssas catalog.  In creating the udl file I cannot see the catalogs, but I can type in the catalog name which connects me and allows me to browse the cube using excel.  Unfortunatley, my users use other applications besides excel which don't allow access through udls.  Any help would be appreciated.

How to change connection string of a pivot table pointing to SSAS 2005 cube using excel 2003?

Hi All,I am not sure if I should have posted this query to Excel 2003 forum. But posting it here as it applies to SSAS 2005 as well.Ok, let me give the background before I tell the actual problem.We have users on ABC domain and the SSAS server is also on ABC domain. Users on this domain can acess the excel pivots by connecting to cube to browse the data. They leave the Userid & password field blank while they setup the connection string and it works fine. Thanks to windows authentication that takes the credentials of user logged in. Let's say I have two users A and B, they login to ABC domain with their own windows ids.  Now when user A creates a excel file having a cube pivot and then sends this file to user B, user B can refresh and modify the same excel file (he can select new measures to pivot, new hierarchies in filters and so on).Now, let's say I have another user, user C. He has excel 2003 installed on his PC and cannot migrate to excel 2007. He is on different domain XYZ but have a valid windows userid on domain ABC. The domain ABC & XYZ can not be setup to have trusted relationship. Now, when user A sends the same excel file to user C. When user C opens the file and try to refresh it or try to modify the pivot by selecting/deselecting any elements, he gets below error prompt:" An error was encountered in the transport layer." and "Errors in the

Generate Report Excel from Client??

Hi all, is it possible to create an Excel report from client?i have a webform that uses the libraries Interop.Excel.dll, Interop.Office.dll, Interop.VBIDE.dll to create an excel report (with statistics graphics) but it do it using excel from server. By company policies we must uninstall office from the server. But the Excel's reports are still useful and i need a way to remake it. then, is possible use the client's Excel to make the reports? exists another tool that allow me make this reports without using excel???Thx in advance!!

Client (Excel) crashes on exit with C# Interop Server (in-proc)

Hello, I'm having a generic problem understanding how to free resources when writing .net COM Servers (using CCW). Excel 2007 VBA is my client in this case. I believe it to be a problem that resources aren't immediately freed and on exit of Excel, a crash occurs which is probably due to invalid resources. The code server code I have is as follows: using System; using System.Collections.Generic; using System.Text; using System.Runtime.InteropServices; using System.Windows.Forms; using System.Threading; using System.Diagnostics; namespace myclass { [Guid("84F0D8F6-AF73-43fe-92D3-C7264EDBDB18")] [ComVisible(true)] public interface IProgress { [DispId(1)] void Show(); [DispId(7)] void Finished(bool WaitOnOk); } [Guid("1195C591-8592-4533-BFFD-C383B33B2976"), InterfaceType(ComInterfaceType.InterfaceIsIDispatch)] [ComVisible(true)] public interface IProgressEvents { } [Guid("9773AD88-E17C-4c27-94AE-BA3FC330F162"), ClassInterface(ClassInterfaceType.None), ComSourceInterfaces(typeof(IProgressEvents))] [ComVisible(true)] public class Progress : IProgress { System.Diagnostics.BooleanSwitch trace = null; System.IO.FileStream log = null; private bool m_InitForm = false; public Progress() { log = System.IO.File.Create(@"c:\hbasElvis.txt"); Trace.Listeners.A

Excel 2007 connectivity to SSAS 2005 cube

Hello - I am using an excel 2007 odc file to connect to an analysis services 2005 cube (on windows server 2003 R2 OS). I receive the following error message   "Excel was unable to get necessary information about this cube. The cube might have been reorganized or changed on the server" Have already attempted the following fixes but running out of ideas now 1. Added localeIdentifier to ODC file    <odc:ConnectionString>Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=bisql;Initial Catalog=SLAM ACT BUD; LocaleIdentifier = 1033 </odc:ConnectionString> 2. Ensured that all dimensional attributes which are used in MDX calculations (named sets, calculated members) are set to "AttributeHierarchyEnabled  = True" Running out of steam so any help is greatly appreciated.

Range or List Filtering via SSAS cube in Excel 2007

Hi All, A client has posed an interesting question, as current users of business objects webi they can filter the results of a cube/universe by copy and pasting a range or list of values seperated by a ';' into a list filter that is availble. e.g. product2;product533,;product029;product8389 etc etc. The list can sometimes be hundreds long. How is that same function acheived in browsing a ssas cube in excel 2007, all I can see is that a user has to use the drop down list and individualy select the values they want. This does not seem like a great method! Any ideas? They are using SQL / SSAS/ SSIS/ SSRS 2008 R2 Cheers DC

HOWTO: Authenticate Active Directory account over VPN from C# client?



I have some code that authenticates a user in Active Directory from a C# client but it does not work through a VPN connection, though it works just fine when I'm connected to the network.  How can I get this to work over a VPN?

These are the three functions I've used that ALL work, just not when I'm connected through a VPN.


        public static bool ValidateUser1(string strDomain, string strUsername, string strPassword)
                DirectoryEntry entry = new DirectoryEntry("LDAP://" + strDomain, strUsername, strPassword);
                object nativeObject = entry.NativeObject;
                return true;
            catch (DirectoryServicesCOMException) { }

SSAS - Besides 2 big user defined hierarchies, plently of small hierarchies


Hi All

I am working on a project where we have to define an Organization Dimension (we defined a standard dimension not parent-child for performance reasons).

The company I am working for is using codeblocks at the lowest level of the hierarchy (combination of business unit, operating unit, etc...).

We created two user-defined hierarchies on that dimension

- One organizing the codeblocks Geographycally (we have attributes like Area, Subarea, CountryGroup, Country)

- One organizing the codeblocks from a Service perspective (ServiceLine, Subserviceline)

The table looks like:

Codeblock, Area, SubArea, CountryGroup, Country, ServiceLine, SubserviceLine, ...

An additional business requirement we have is to define plenty of very small hierarchies besides those two main hierarchies.

One example would be to have a hierarchy that combines values for Country(Switzerland) and Country(Germany). Of course it would be possible to create calculated members in the MDX Script but we have the requirement to be able to drill down from the sum to the individual countries. Since calculated members are not allowed to have children, they are not an option.

We would like to avoid using a parent-child dimension (I know the possibility of using a parent-child with multiple hierarchies).

One ugly option I found is to add dum

SSAS 2008 R2 Cube being queried by Excel 2010 Pivot Table - 60x slower than with Excel 2003!


SQLServer 2008 R2

Excel 2010 x64

Hi All,

I have a fairly complex Excel report where a raw data sheet is updated from 10 or so PivotTables pulling from an Analysis Services Cube, (2008 R2), with a VBA script that refreshes all pivottables, and then does some post-processing, formatting etc.

This was originally developed in Excel 2003, and the updating of all the pivot tables ran in under 10 seconds

I've now recreated in Excel 2010 and *each table* is taking up to a minute to refresh!

I've also observed that I can get the refresh time of a single table back down to 1sec, if I delete all other pivottables from the worksheet/book.

Its deeply frustrating, as I'm touting 2010 as being the way for our business to go...it won't help the cause if I deliver something with a x60 increase in runtime!

I've run traces of both the 2003 and 2010 versions executing against the SQL Server, the only thing I can see is that the 2003 version passes 1 single MDX query, whilst the 2010 version appears to be firing it in sections.

I've posted this in the Excel forum too.

Please can anyone help?

Many thanks,





Excel 2007/2010 - SSAS 2008 R2 Offline cubes


Hi all. I have a question regarding offline cubes (offline data files) in Excel 2007/2010 connecting to SSAS 2008 R2. When I try to make an offline data file in Excel I get a OLE DB error. The server I am trying to connect to is at a hosting partner on a different (but trusted) domain but I have admin rights on it. I then took a copy of the SSAS database and moved it to my own pc and could then create offline data file. I thought it might be a firewall issue on the server and tried turning it off, but the OLE DB error came again. I therefore think that there is something that happens over the network and is blocked by the hosting partner. I probably should tell that I am also SSAS admin, so it is not because of lack of rights on the SSAS that I get the error.

What I like to know if there is anyone who knows this problem or knows what happens when you try to create offline data file against SSAS in Excel? Specifically what is transferred between the client and the server, what ports and are there any programs run or code executed to create the offline cube on the client that might be blocked by typical network/domain settings/rules?

Best SSAS client??


Hi Experts,

I was wondering what the most popular/best SSAS client is used in the work environment for reporting? I Know Excel has limited abilities, and i was wondering what better options are out there that support drillthrough, KPI's etc.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend