Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1956
  • Last Modified:

PowerPivot and Current User Filter Web Part

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
Jamie McAllister MVP
Asked:
Jamie McAllister MVP
  • 5
2 Solutions
 
Rainer JeschorCommented:
Hi Jamie,
I am not sure about the MDX statement how this has to l
0
 
Rainer JeschorCommented:
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
 
Rainer JeschorCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jamie McAllister MVPSharePoint ConsultantAuthor Commented:
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
 
Rainer JeschorCommented:
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
 
Rainer JeschorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now