Solved

PowerPivot and Current User Filter Web Part

Posted on 2013-06-11
8
1,780 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
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 31

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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