Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PowerPivot and Current User Filter Web Part

Posted on 2013-06-11
8
Medium Priority
?
1,918 Views
Last Modified: 2013-07-29
I have PowerPivot successfully installed and working on a SharePoint 2010 environment with SQL Server 2008 R2 in the back end. I have created a PowerPivot workbook, and placed this in a library. The PowerPivot is being displayed on a page using an Excel Web Access web part. I created a filter in the workbook based on username in the format "domain\username".

So far so good.

When I add a "Current User" web part to the page and connect it to the Excel Web Access web part, I get the error "An error occurred while attempting to set one or more of the parameters in this workbook.  As a result, none of the parameters have been set. Click OK to return to the workbook". I want to filter the workbook by current user automatically, but this error is preventing that.

After some googling I found some mention that simple values from these filter web parts are no good and an MDX formatted query is needed. As such I tinkered with things so that instead of 'domain\username' as a value I pass;

'[tableName].[fieldName].&[domain\username]'

I've tried many variations of the above but no success, the error message is always the same. I have also connected a Text Filter web part instead of current user so I can type all the variations and be sure what is being passed. Same error message.

Simple vales don't work, complicated MDX like strings don't work. Where do I go with this?
0
Comment
Question by:Jamie McAllister MVP
  • 5
8 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39325669
Hi Jamie,
I am not sure about the MDX statement how this has to l
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39325674
Sorry, hit accodentially submit.
I am not sure about the MDX statement for PowerPivot, just know it for SSAS.
Question is, do you have the possibility to use custom code?
I have written a web part for  the exact same situation being used by EWA, Excel file and Pivot tables using SSAS cubes. If yes, I can provide you the code. The passing filter is configurable :-)
I will try to see if I have somewhere a working PowerPivot to test the general statement format.
HTH
Rainer
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 39325691
Hi,
where do your data come from? SSAS cube? Perhaps you can use this
http://office.microsoft.com/en-us/excel-help/mdx-abfrage-designer-fur-analysis-services-powerpivot-HA102836091.aspx
to get an idea how the correct filter should look like.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 32

Author Comment

by:Jamie McAllister MVP
ID: 39325734
Hi there,

There's no cube involved. The data is loaded into PowerPivot from a series of standard Relational Tables.

The MDX syntax could be a Red Herring, but it's the only decent lead I turned up whilst investigating.

I would appreciate your sanity check if you have a working PowerPivot setup, and the code would be of interest as a last resort.

Thank you.
0
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 2000 total points
ID: 39363848
Hi,
sorry but had not yet the time to build a PowerPivot environment due some highly critical projects.

But at least attached the code I used to create a user filter web part (as MDX) where the dimension filter can be configured and will replace the specific entry using:
[Users].[Dim Users].&[{0}]
where {0} will be replaced with the user name.

Works with windows authentication and also Forms Based (in our case where the users use SmartCards)

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data;
using System.Globalization;
using System.Web;
using System.ComponentModel;

using System.Collections.Specialized;
using System.Security.Principal;

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

using System.Collections.ObjectModel;

namespace EE.RainerJ.WebControls
{
    public enum UserAuthenticationType
    {
        None = 0,
        PKIForms = 1,
        Windows = 2
    }

    public sealed class CurrentUserFilterWebPart : Microsoft.SharePoint.WebPartPages.WebPart, ITransformableFilterValues
    {
        private string currentSPUserLogin = string.Empty;
        private string userGIDDimensionExpression = string.Empty;
        private string userLogonNameDimensionExpression = string.Empty;
        private bool useDebugMode = false;
        private string filterMDXValue = string.Empty;

        [Personalizable(PersonalizationScope.Shared),
            WebBrowsable(true),
            WebDisplayName("Enable debug mode"),
            WebDescription("Enables the output of additional information as well as exceptions"),
            Category("Configuration")]

        public bool UseDebugMode
        {
            get { return useDebugMode; }
            set { useDebugMode = value; }
        }

        [Personalizable(PersonalizationScope.Shared),
           WebBrowsable(true),
           WebDisplayName("User GID MDX"),
           WebDescription("User GID MDX Dimension expression"),
           Category("Configuration")]
        public string UserGIDDimensionExpression
        {
            get { return userGIDDimensionExpression; }
            set { userGIDDimensionExpression = value; }
        }

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
        }

        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);
            string tempUserName = SPContext.Current.Web.CurrentUser.LoginName;
            UserAuthenticationType currentAuth;

            if (tempUserName.Contains(":") == true)
            {
                currentAuth = UserAuthenticationType.PKIForms;
            }
            else
            {
                if (tempUserName.Contains("\\") == true)
                {
                    currentAuth = UserAuthenticationType.Windows;
                }
                else
                {
                    currentAuth = UserAuthenticationType.None;
                }
            }

            switch (currentAuth)
            {
                case UserAuthenticationType.PKIForms:
                    this.currentSPUserLogin = tempUserName.Split(':')[1];
                    break;
                case UserAuthenticationType.Windows:
                    this.currentSPUserLogin = tempUserName;
                    break;
                case UserAuthenticationType.None:
                    this.currentSPUserLogin = tempUserName;
                    break;
            }
            this.filterMDXValue = String.Format(this.userGIDDimensionExpression, this.currentSPUserLogin.ToUpper());

        }



        // Implementations of the ITransformableFilterValues properties.
        [WebPartStorage(Storage.None)]
        public bool AllowMultipleValues
        {
            get
            {
                return false;
            }
        }

        [WebPartStorage(Storage.None)]
        public bool AllowAllValue
        {
            get
            {
                return false;
            }
        }

        [WebPartStorage(Storage.None)]
        public bool AllowEmptyValue
        {
            get
            {
                return false;
            }
        }

        [WebPartStorage(Storage.None)]
        public string ParameterName
        {
            get
            {
                return "User GID";
            }
        }

        [WebPartStorage(Storage.None)]
        public ReadOnlyCollection<string> ParameterValues
        {
            get
            {
                string[] values = { this.filterMDXValue };
                return values == null ?
                    null :
                    new ReadOnlyCollection<string>(values);
            }
        }


        // Use the ConnectionProvider attribute to specify the method that
        // the Web Part framework should call to allow us to return an instance
        // of our ITransformableFilterValues interface.
        [ConnectionProvider("User GID Filter",
         "ITransformableFilterValues", AllowsMultipleConnections = true)]
        public ITransformableFilterValues SetConnectionInterface()
        {
            return this;
        }

        protected override void CreateChildControls()
        {
            base.CreateChildControls();
            if (this.useDebugMode == true)
            {
                Label output = new Label();
                output.Text = "Current User Name is :" + currentSPUserLogin + "<br/><br/>" + "Filter is: " + this.filterMDXValue;
                this.Controls.Add(output);
            }
        }
    }
}

Open in new window


HTH
Rainer
0
 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 total points
ID: 39364141
Hi,
still no testing environment, but this post looks promising:
http://www.powerpivotpro.com/2012/05/drill-across-in-powerpivot-live-demo/#more-4343

HTH
Rainer
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question