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


Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Asad Ali
Post New Web Links

Multiselection in Excel filter doesnt work for my calculation

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server
 
Dear all,

I'm having a problem with one of my calculations. It's working perfecty for one selected unit (e.g. year '2010', or quarter 'Q2' for year 2010, or month 'july' for year 2010).
But, in my report in Excel, I MUST have option to select multiple values of date (e.g. quarter 'Q1' and 'Q2', or months 'january' and'february') because some other of my calculation needs multiple values of date. 

What I need is to tell me how to change my calculation when I chose multiple values of date? Is it even posible?
Example:
-if you select five months: 'january', 'february', 'march', 'april', 'may' then I want last month 'may' to be saved in some variable (or if there is some other method, even better) that can be used for making SUM of Amount on period (from begining to latest selected date) described in code below. 



--------
sum(
Ancestor(
[Date].[Year - Quarter - Month - Date].CurrentMember,
[Date].[Year - Quarter - Month - Date].[Year]
        ).FirstSibling.FirstChild.FirstChild.FirstChild:

IIF([Date].[Year - Quarter - Month - Date].CurrentMember.Level.Name="Year" 


View Complete Post


More Related Resource Links

Excel Services: Develop A Calculation Engine For Your Apps

  

The Excel Services architecture lets users design their own algorithms and share workbooks on a server.

Vishwas Lele and Pyush Kumar

MSDN Magazine August 2007


Excel 2007 Filter on multiple values

  
Hi Everyone,How do I use Excel 2007 Pivot Table's label filters/value filters to filter with multiple conditions? for example, I want to get all customers with customer name start with "G" and doesn't include "E" and with measure>100 and measure between 50-60.Regards,George

Enhance SSAS so that a calculation returns multiple cells at once (Excel fill not a single cell but

  
I wonder if somebody can help me with the following question. I would like to create an enhancement to SSAS in a way so that one can pass in multiple values, run a calculation and in return retrieves not one but multiple cell results at the same time e.g. retSet = functionABC(inSET) where inSet is somthing like 5 4 3 6 7 3 4 4 AND retSet is 4 6 7 8 9 3 4 4 All my calculated members in MDX return a single value BUT I need to have a calculation that if called returns multiple results (a set) at once and in a way so that Excel will properly pick up on it and consequently fills multiple cells in the Pivot table. Is that possible at all and if so how? I would not mind to write a little CLR assembly to enhance the SSAS but don't know how to pass the results back. Any ideas are highly appreciated. Dirk

Will the old Excel 2007 Datamining Solution Work With SQL 2008 Express?

  
Or does it need the full version?  Is there a new version of the Datamining add-in for 2008 Thanks Peter

Can´t get the time intelligence filter to work in PerformancePoint Services/Sharepoint

  
Hi all, I have created a scorecard in Dashboard Designer based on an SSAS cube which looks ok in both DD and Sharepoint. As I want to connect a time filter to this I have created a TI filter in DD. In Sharepoint I have connected the filter to the scorecard with "send performancepoint values to". In "configure connection" I set source value to "PerformancePoint Values: Formula" and Connect to to "Endpoint_TIFormula". I only need to filter on year and have set the TI properties on the cube. Selected time dimension is "Year", Hierarchy level is set to Year, Member is 2008, reference date is 1/1/2008 and I have associated member level Year to Time aggregation Year. The filter is set up like this in DD: it uses the same data source as the scorecard and I use a list view. I do get the filter to work if I specify one formula for each year (e.g. Formula: "Year" and "Display Name: "2010", "Year-1" and "2009" making up two rows) but that can´t be the right way to do it, right? What i would like is a multiselect tree view where I can choose the year I want and have them display on columns. I have tried the formula Year:Year-2 and in the preview I see [Date].[Year].&[2008],[Date].[Year].&[2009],[Date].[Year].&[2008]. However, in Sharepoint the filter only show on

Error when programmatically connect filter webpart with excel webpart

  
Hello, On MOSS 2007 SP2 64bit I am trying to programmatically connect a SPSlicerTextWebPart with an ExcelWebRenderer but I am getting the error: "The connection point "IFilterValues" on "g_046273fe_af27_4064_86b1_70f2c57c326c"(the excel webpart)   is disabled" and I can't find a way to enable it... I'm using the folowing code: using(SPLimitedWebPartManager webPartMgr = web.GetLimitedWebPartManager("Stats.aspx", PersonalizationScope.Shared)) { Microsoft.SharePoint.Portal.WebControls.SPSlicerTextWebPart filterWebPart = (Microsoft.SharePoint.Portal.WebControls.SPSlicerTextWebPart)webPartMgr.WebParts[0]; if (filterWebPart.ConnectionID == Guid.Empty) { filterWebPart.ConnectionID = Guid.NewGuid(); } webPartMgr.SaveChanges(filterWebPart); ProviderConnectionPoint providerCP = webPartMgr.GetProviderConnectionPoints(filterWebPart)[0]; Microsoft.Office.Excel.WebUI.ExcelWebRenderer excelWebPart = (Microsoft.Office.Excel.WebUI.ExcelWebRenderer)webPartMgr.WebParts[1]; if (excelWebPart.ConnectionID == Guid.Empty) { excelWebPart.ConnectionID = Guid.NewGuid(); } excelWebPart.AllowConnect = true; webPartMgr.SaveChanges(excelWebPart); ConsumerConnectionPoint consumerCP = webPartMgr.GetConsumerConnectionPoints(excelWebPart)[2]; TransformableFilterValuesToFilterValuesTransformer t = new TransformableFilterValue

Choice Filter Web part and Excel Web Access web part

  
I have a choice filter web part where in I have months like Jan,Feb,Mar etc and a excel web access web part. The choices in the filter web part should determine the URL of the workbook for the excel web access web part. In the filter web part I gave the choices as Jan;http://sitename:port/ExcelList/MyExcel.xlsx where "ExcelList" is the library that stores the excel files for each of the months. But the excel web part always says that "The file selected couldnt not be found". Is it possible for me to know the actual URL getting passed to the excel web access web part? Pl suggest if there is a better way to do this. Cutloo

SharePoint workflow Enable WorkflowModification doesnt work

  
Hi   I'm trying to enable WorkflowModification in a sequential workflow in VS Studio 2008 according to the *** of examples of microsoft but When I run my workflow I get this error: 09/14/2010 10:24:35.78  w3wp.exe (0x11FC)                        0x13A8 Windows SharePoint Services    Workflow Infrastructure        88xr Unexpected WinWF Internal Error, terminating workflow Id# 74f4bd77-7aaa-407e-90ac-f56a6184bc21  09/14/2010 10:24:35.78  w3wp.exe (0x11FC)                        0x13A8 Windows SharePoint Services    Workflow Infrastructure        98d4 Unexpected System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SharePoint.SPException: Error in the application.     at Microsoft.SharePoint.Workflow.SPWinOEWSSService.EnableWorkflowModification(Guid modificationId, String contextData)     --- End of inner exception stack trace ---     at System.RuntimeMethodHandle._InvokeMe

Excel pivot table report filter selections from a cube

  
There are two filters added to the report filter, Date (8/31/2010) and Product (Product A), from a cube I have.  Everything works just fine.  Once the cube is refreshed each day to include the new data, for some reason, only the selection of Date, 8/31/2010, stays but the selection of Product changes to All Products.  Both dimensions are fully processed but the surrogate keys for existing records do not change.  I checked the MDX captured in Profiler and in the where clause, the date selection is passed from the pivot table but the product selection is lost and all products is passed in.  Any thoughts? TIA. 

list view connection filter doesn't work

  
  I have  a custom display form for documents library with two connected List View WebPart. I am trying to filter documents at the second list view with the value from the first one. Both list views contain data from the same documents library. I have configured the connection, but second list doesn’t display any item, and it doesn’t matter which field I use for filtering. Also  for another document s library’s custom  display form  it work perfect.But there second list view contain data from another documents library. Not the same as at first   case. Any ideas with my issue?

"PivotTable.DisplayEmptyRows = false " in Excel pulling from Analysis Server 2008, doesn't work.

  

We display financial information, using Excel pulling from Analysis Server 2008.

 

In vba,  "ptTemp.DisplayEmptyRow = false" works fine for hiding empty rows of data on most pivot tables, but if the "account numbers" parent/child dimension is in the row fields, it doesn't.

 

The row Dimension look like this:

 

Balance Sheet

Assets

Current Assets

Cash

Total Balance Sheet

 

Income Statement

Gross Profit

Revenue

Item A

….

Total Income Statement

Report exported to Excel 2007 is EXTREMELY slow once a filter is applied.

  

I am exporting a 12,000 row, 20 column report to Excel from SSRS 2008.  Once opened in Excel, everything is fine.  If I further filter the data through Excel (no external data connections) performance degrades.  If I filter down to say 20 rows, it nearly completely bogs down my entire PC.  I've emailed this spreadsheet to several others, who then experience the same issue.

If I export this same set of data from a different BI tool, such as Microstrategy, there are no performance issues what so ever.


Creating an excel object but server doesnt have Excel installed

  

Hi,

The objective of my applications sounds quite simple, but with the old technologies, I am getting stuck everytime. I have a ASP web application which has a page to upload a spreadsheet from the client machine and save it on the server. This ASP application then calls a method in a DLL, which is developed in VB6 (not VB.Net). This VB DLL would create a excel object to open this spreadsheet (that has been saved on the server) and saves it as a csv file in the same path. This csv format data is then inserted into the database.

The problem that I am facing is, the VB6 program fails to create the excel object as the server doesnt have Office Excel installed. below is the VB code that I am trying to execute. It fails on thhe second line, where I am trying to create a Excel Application object and I get an error: "run-time error '429' activex component can't create object". I had tried to run the below VB code by adding reference to "Microsoft Excel 12.0 Object Library" (refers to C:\Program Files\Microsoft Office\Office12\Excel.exe) and "Microsoft Office 12.0 Object Library" (refers to C:\Program Files\Common Files\Microsoft Shared\Office12\MSO.DLL), creating a package out of it so that the required

Sendmessage doesnt work in ASP VB IIS6

  

hI ALL

I have an ASP page, that sends data for a local Application on the server...

in my PC which the OS is win XP 32bit ... it works just fine

in the server which is windows server 2008 64bit , with IIS 6

nothing really happens when i send the message in the server, no errors, no data ...   nothing

just like iam pressing a button with no code to handle...!!!!

please , try help me with an answer


Filter out nulls in Excel

  

HI All,

 

Is there a way to use non empty in excel(which is connected to cube) like the way we use when writing MDX? We have a couple of values that have empty metrics and we wan to filter them out.

 

Thanks in advance.


Filter Excel Report based on MDX Filter

  

Hello guys!

Im in trouble when trying to use a MDX Filter to filter an excel report.

Scenario:

I created an excel report and in the pivot table I added a filter based on one Attribute, and set that to me a parameter. I published it at the site document library.
Now with sharepoint designer i create an MDX filter that uses an SSAS Assembly that i created to return the Member Key Set(EX: [Seller].[ID].&[5]). The filter do work right alone, like when i create one dashboard and add two web parts, one for the mdx filter and other to the report. Both of them works nice alone but the excel parameter field dont get the value of the filter.

Example of dashboard:


==========================================
| ID <5>(here it shows the value requested by the mdx)   | Filter Web Part
==========================================
|  ID  | All  |                                                                  |
|--------------------------------------------------------------

MDX Calculation for multi-select in Excel

  
Dear all,

I'm having a problem with one of my calculations. It's working perfecty for one selected unit (e.g. year '2010', or quarter 'Q2' for year 2010, or month 'july' for year 2010).
But, in my report in Excel, I MUST have option to select multiple values of date (e.g. quarter 'Q1' and 'Q2', or months 'january' and'february') because some other of my calculation needs multiple values of date. 

What I need is to tell me how to change my calculation when I chose multiple values of date? Is it even posible?
Example:
-if you select three months: 'january', 'february', 'march' then I want last month 'march' to be saved in some variable (or if there is some other method, even better) that can be used for making SUM of Amount on period (from begining to latest selected date) described in code below. 



--------
sum(
Ancestor(
[Date].[Year - Quarter - Month - Date].CurrentMember,
[Date].[Year - Quarter - Month - Date].[Year]
        ).FirstSibling.FirstChild.FirstChild.FirstChild:

IIF([Date].[Year - Quarter - Month - Date].CurrentMember.Level.Name="Year" 
[Dat
Categories: 
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