Solved

PowerPivot and Current User Filter Web Part

Posted on 2013-06-11
8
1,735 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
Comment Utility
Hi Jamie,
I am not sure about the MDX statement how this has to l
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 31

Author Comment

by:Jamie McAllister MVP
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now