.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

MDX Query In Dashboard Designer

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

Hi all

I have decided to use Performance Point to Develop My KPI's and i am trying to create a KPI where the actual amount shows the total Number of quotes generated for the current month, My Time dimension is as follows

Time.Year - Month - Date

Member = Sat January 01 2005
Hierarchy Level = Month
Reference Date = 1/1/2005

I have created the KPI but i am unsure of the MDX Statement to return the current month could anybody please help.

i have tried

 [Measures].[Total Quotes],[Time].[Month].CurrentMember but this just returns the total for all quotes



View Complete Post

More Related Resource Links

Creating a KPI Using MDX Query In Dashboard Designer


Hi all i have decided to use Performance Point to create my KPI's and i am trying to create a KPI where the actual amount shows the total Number of quotes generated for the current month, My Time dimension is as follows

Time.Year - Month - Date

Member = Sat January 01 2005
Hierarchy Level = Month
Reference Date = 1/1/2005

I have created the KPI but i am unsure of the MDX Statement to return the current month could anybody please help.

I have tried

[Measures].[Total Quotes],[Time].[Month].CurrentMember

but this just returns all the quotes ever generated i have also tried

([Measures].[Total Quotes], StrToMember("[Time].[Year - Month - Date].&["+CStr(Format(Now(),"yyyy-MM-dd"))+"T00:00:00]"))

but this returns nothing



Use Dashboard designer in Sharepoint Foundation 2010

Hi, I want to Install Dashboard Designer in Sharepoint Foundation 2010(WSS 4). I can only find articles on insatllation in Sharepoint Server 2010..like this one.. http://blogs.msdn.com/b/performancepoint/archive/2009/11/24/deploying-performancepoint-2010-soup-to-nuts.aspx?wa=wsignin1.0 Can you please let me know how I can use Performance Point in Sharepoint Foundation?   Thanks  

How to deploy dashboards made in dashboard designer to diferent site collections?

Hi I just migrated from sharepoint 2007 to 2010. We used to have performance point dashboards spread across libraries in subsites all across our primary site collection "Http://server:x" after I migrated I understand that I must migrate all performance point content to a new site or site collection made with the business intellingence template. I created a new site collection with this template (Http://server:x/sites/pps) and succesfully migrated all performance point content. Now I want to redeploy all the dashboards to their respective subsites in the  "Http://server:x" site collection so that everything looks exactly as it did when we where using SharePoint 2007. But when I try to deploy to a site I only get the option to deploy it to the ("Http://server:x/sites/pps") site collection and its respective libraries. I wish to manage all dashboard content centralized in (Http://server:x/sites/pps) but I want to deploy dashboards to libraries across  "Http://server:x" is this posible or how is Performance Point services supossed to work?   I also having trouble with DashBoard designer. I have everthing running on a Virtual Machine test enviroment. With windows server 2008 r2, 4 gb ram. Everything installed in one machine. And Suddently DashBoard Designer Crashes In the event log I get the error Dashboa

BIDS Query Designer very slow in VS 2008

I am in the process of upgrading my Report Server projects from 2005 to 2008.  These reports use an Analysis Services 2005 data source and have queries that are anywhere from 20 to 100 lines of MDX.  After upgrading a report, when I open or save a dataset in the Query Designer, it takes up to 20 seconds.  In 2005, this would happen almost instantly. Running SQL Server Profiler, I see that there is an extensive number (600+) of Discover events going on with the Analysis Services server while the Query Designer loads. My version of Visual Studio is 9.0.30729.1 SP and I am running Windows XP SP2. Thanks, Mitch

BIDS Query Designer very slow

BIDS Query Designer seems to be very slow while saving the query or opening the query designer when you've got quite a lot of Query Parameters. When I remove all the query parameters, the query designer seems to be running smoothly. Editing a query with about 10 to 15 query parameters takes ages this way! Is there something I can do about this?!

What is "semester" in Dashboard Designer?

I am setting time properties on an AS data source. When associating time members, I encountered "semester". Is this a 4-month period (Jan-Apr and so on)? Thanks 

MDX works fine in SSMS but not in query designer

Hi all,   I am trying to include this query in my query designer in SSRS. The query below is as follows:WITH MEMBER [Time].[Fiscal Month].[All].[ Subtotal] AS ' AGGREGATE( EXISTING { [Time].[Fiscal Month].[All].CHILDREN }) ', SOLVE_ORDER = 1000  SELECT NON EMPTY { { { { [Time].[Fiscal Month].[All].CHILDREN }, { [Time].[Fiscal Month].[All].[ Subtotal] } } * { { [Measures].[SKU Distinct Count] } } } } ON COLUMNS , NON EMPTY { [Sales Force].[Retailer Code].[All].CHILDREN } ON ROWS  FROM [Sales]CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL The error i am getting isThe query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..Please let me know where am i going wrong.Thanks and regards,ram

Query Designer question

I am not sure where to post this question, so I guess it is here: I have a VERY large query that joins quite a few tables. I am using the graphical designer to get the tables all linked together. However the graphical designer seems to place the order of the tables in a random order resulting in a hodge podge of left and right joins.   In the graphical designer, is there a way to order the from clause?

Dynamic member selection filters in Dashboard Designer/PPS2010

Question regarding member selection filters in Dashboard Designer (PPS 2010): When selecting the filter members, is there any way to make them dynamic? I.e. when a new member is added (e.g. a new year when we enter one), can it be included without having to manually add them in the filter? Thanks.

How do I nest dimensions in scorecards in Dashboard Designer?

I have a scorecard where I have a KPI on columns and a dimension on rows. That works fine. I can also add another dimension on rows and add it as a parent for example. The only thing I miss is a drilldown "+". What I see is basically a scorecard showing all possible rows, while I want one where I can drill down (or through) and expand rows if I want. How do I get the parent to be expandable? Thanks

How to change the size (width) of scorecard columns in Dashboard Designer?

Is there any way to change the size (width) of scorecard columns in Dashboard Designer?

Query Designer - Pane - Results grayed-out?

In Query Designer, right click, select pane -- the option for Results is unavailable (grayed-out)?

Dashboard Designer: KPI's - Scoring Pattern "Decreasing is Better" returns confusing values

Good day, I started using Sharepoint a few weeks back. I jumped straight into PerformancePoint Services, and have now now gotten the hang of it, r at least I think. Far from mastering anything though. Problem is: I have a table with three fields, namely ProjectName, ProjectBudget and ProjectCost put very simply. Say for example I have these records... ProjectName ProjectBudget ProjectCost Project A 15 000 20 000 Project B 10 000 20 000 Project C 15 000 15 000 Project D 15 000 10 000 Using ProjectBudget as my Target and ProjectCost as my Actual. How can I ensure that Project A and Project B are displayed as off target, while Project C and D are displayed as on target. Lastly, the percentages that are referred to in the "Thresholds" section, what are they referring to? Please let me know, if I need to provide more info. Thank you very much.  

SharePoint 2010 - Performance Point & Dashboard Designer

We have successfully installed SharePoint 2010 onto our dev machine; we have also followed the documented steps for configuring Performance Point; http://blogs.msdn.com/b/performancepoint/archive/2009/11/24/deploying-performancepoint-2010-soup-to-nuts.aspx.  All seems fine however we do not seem to be able to create a connection from Dashboard designer back into SharePoint.  When we check the logs we get the following error Critical   An unexpected error occurred.  Error 5678.  Exception details: Microsoft.SharePoint.Upgrade.SPUpgradeCompatibilityException: There is a compatibility range mismatch between the Web server and database "WSS_Content_PerformancePoint_1", and connections to the data have been blocked to due to this incompatibility. This can happen when a content database has not been upgraded to be within the compatibility range of the Web server, or if the database has been upgraded to a higher level than the web server. The Web server and the database must be upgraded to the same version and build level to return to compatibility range. We have a farm config on a single 64 bit box and SQL Server 2008 loaded.  We have un-installed and re-installed the site collection. Does anyone have any ideas? Eddie Barrett

Dashboard Designer Connection Errors

Hi,SharePoint 2010 single box deployment on my laptop running Server 2008 R2 and SQL 2008 R2.I can access the SharePoint BI site just fine and can launch the dashboard designer with no problems.   When I try to create a KPI, indicagtor or dashboard I see the following error:***The list no longer exists or you do not have permissions to view it.  The dashboard cannot be saved.  Additional details have been logged for your administrator.***I look in event viewer and see:***The user "SHAREPOINT\system" attempted to save an item in the following location: http://alanserver/sites/BI/Lists/PerformancePoint Content Verify that the location exists and that the user has the "Add Items" permission.Exception details:Microsoft.PerformancePoint.Scorecards.BpmException: An unexpected error occurred. Error 16858. Additional details have been logged for your administrator. at Microsoft.PerformancePoint.Scorecards.BIMonitoringServiceApplicationProxy.ExecuteOnChannel(CodeBlock codeBlock)at Microsoft.PerformancePoint.Scorecards.BIMonitoringServiceApplicationProxy.GetSettingValue(String key)at Microsoft.PerformancePoint.Scorecards.ServerCommon.AppSettings.GetValueAsInt(String key)at Microsoft.PerformancePoint.Scorecards.Store.SPFcoCache.Add(Guid webSiteID, RepositoryLocation location, FirstClassElement element, SPListItem item)at Microsoft.PerformancePoint.Scorecards.Store.Dao.

SSRS Query Designer (in BIDS) does not generate ANSI SQL

Hello, I have a problem when using SQL Server 2005 BIDS (Business Intelligence Development Studio) when connected to an Oracle data source.  The Query Designer generates the old Oracle SQL syntax instead of ANSI SQL.  This is with the Oracle 10g client installed. So, I would really appreciate it if anybody knows how to get the Query Designer to generate the ANSI SQL.   Thanks, Stuart

Generic Query Designer displaying non standard syntax

I've just discovered a problem with the Generic Query Designer on my machine. When I do a simple join of two tabels it does not display in the SQL pane as standard syntax, so instead of SELECT a.1 from ainner join b on a.1=b.1It does SELECT a.1from a, bwhere a.1 = b.1I am doing this against an oracle 10g database, it will run the query, but I want the query designer to out put at standard SQLI am presuming that it is a setting in SSBIDS, but I don't know why it is doing this. Any suggestions?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend