Solved

PowerPivot and Current User Filter Web Part

Posted on 2013-06-11
8
1,831 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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 500 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 500 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

Windows running painfully slow? Try these tips..

Stay away from Speed Up Computer Programs that do more harm than good.
Try these tips instead.
Step by step instructions in trouble shooting Windows Performance issues.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

734 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