Refreshing a Pivot Table connected to SSAS in Excel refreshes the whole workbook

We have a user with an Excel workbook with multiple pivot tables connected to SSAS.We've found that when we do a Refresh on a pivot table (not Refresh All), it updates all pivot tables in the workbook.This is causing issues as it takes too long to refresh the whole workbook.Any idea why? I believe that Refresh on a pivot table will refresh all data dependent on that SSAS connection. So one solution for you would be to make multiple connections to the same SSAS source rather than building all the pivot tables off the one connection. [XXX]This link explains it nicely:ht...

Excel file into SSAS cube

What is the easiest way to get data from Excel file into a cube in SSAS?I got an excel file with 4 tables and I want to build a cube with that data.Is the only way to build a SSIS package moving the excel file to my DB and then connect to my DB with SSAS, or is there a faster/easier way?(Not interested in performance since its only 10 000 rows of data.) Not required. You may choose you excel spreadsheet as a source to ingest data.Read this: data sources for SSAS [XXX]

substituting an Excel worksheet for an Access database

Sorry in advance for the rookie-ness you are about to hear.....I have VB application that uses an Access DB, call the database: db_1.For reasons best left unsaid, I have to substitute an Excel worksheet for db_1 - as far as the VB application is concerned.I hope to populate this new data on the worksheet, link the sheet into the Access database, name it the same as the DB that the Application is currently using, and basically not have the application know that its DB has been swapped out from under it.Now I know I am off here - no way its that straightforward - for starters...

SSAS Dimension Security Result in Excel Slicer

I have created a role in my Analysis Services cube (SQL Server 2012) and applied a dimension security so that only one of the dimension attribute's member is selected. When I use the cube in Excel 2013 with a slicer for that particular dimension attribute against a pivot table, i get to see only one item, which is exactly what i require. However, when I click on the slicer item, the values of the pivot table do not change, it shows the total values instead of values filtered for that specific dimension attribute member. The weird thing is that when I view the numbers by bro...

Count of Rows Based on Their Most Recent Value in Excel PowerPivot Using DAX

Is it possible to get a distinct count of rows based on a field's most recent value in a PowerPivot table using DAX?I have a transactional table that tracks professionals with hire and transfer dates. I would like to get a count of the professionals based on their most recent office. This way as a profesional transfers through the organization we can see where they are at a given moment.Transaction Table:Name | Action | EffectiveDate | Office-----------------------------------------Peter | Hire | 1/10/2014 | STLJohn | Hire | 2/5/2014 | STLJohn | Transf...

How can I make the cell colour RGB value change based on cell's value? (in Excel)

I'm new to VBA and have tried the below which doesn't work. Could you help me fix it. I'm trying to make each cell have a different RGB colour based on it's value:Sub ColorCells()Dim rwIndex As IntegerDim colIndex As IntegerDim r As IntegerDim g As IntegerDim b As IntegerFor rwIndex = 1 To 1000 For colIndex = 1 To 1000 r = Cells(rwIndex, colIndex).Value Mod 256 g = Cells(rwIndex, colIndex).Value \ 256 Mod 256 b = Cells(rwIndex, colIndex).Value \ 65536 Mod 256 Cells(rwIndex, colIndex).Interior.ColorIndex = RG...

Alternative front-end to Excel 2007 for SSAS Cube

A few months ago I built a set of SSAS cubes to be accessed via Excel 2007 for our team. Excel was then preferred as a front-end because business users were already familiar with excel and with the pivot table interface. But now complaints have risen about the interface being too 'basic' and not user-friendly enough - specifically people would like to see hover-over tool-tips (or something similar) when they hover over fields in the pivot table, that will be able to give them a bit more information. A custom .NET front-end solution would probably be ideal, but a) I don't ha...

Excel Import Data from Analysis Services - Date comes across as text

I'm trying to use an (exercise) SSAS tabular model in Excel. The model contains a column DATE, in the Proporties window its Data Type is Date and the Date Format is General. When I import the data into Excel the date column seems to come across as text.To import, I open Excel, go to Data -> Get External Data -> From Other Sources -> From Analysis ServicesAfter the data gets imported, the DATE column contains text representations of the date. The "Group" section of the PivotTable Options tab is greyed out, and the sort only does A-Z. How do I get the data type to come across...

Save and print PDF from a FDF file

I am very new to writing VBA so apologies if this is pretty basic!! I have the below macro that fills the data from excel to a PDF. I would like to enhance the code to save the PDF and also print it. The name for the file is located in cell A5. The current code saves the FDF to my directory. Below is the code used and is taken from the following link: http://blog.excelhero.com/2010/04/14/excel_acrobat_pdf_form_filler/:Option ExplicitPrivate Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As...

Create a Running Total Measure in DAX from another Measure (not Calculated Column)

First of all, though it might be somehow irrelevant, this is my first post in the community, which is due to the fact that I've only started to learn DAX last week. I consider myself fairly advanced in Excel, however I'm just starting to understand the capabilities of DAX and I'm here to learn.I'll start with an explanation of what I'm trying to do.I have 2 different tables, one DimensionsExchangeRate table and one FactsSales table.The FactsSales table contains sales from 2 countries, USA (in USD) and UK (in GBP), while the DimensionsExchangeRate table contains by-monthly D...

Italics in Formatting Illustrator from Excel Spreadsheet using VBA

I'm creating a series of tags in Illustrator, using VBA in excel (the excel worksheet has the information that populates the tags), and I cannot find a way to specify that the font which appears in Illustrator is italicized and a particular font.Using:.TextRange.CharacterAttributes.TextFont = appIll.TextFonts.Item("Arial") lends the same result as using:.TextRange.CharacterAttributes.TextFont = appIll.TextFonts.Item("Monotype Corsiva")And needless to say, I also can't get italics. I'm very new to this, but would appreciate anyone letting me know how to specify the font and ...

Excel Macro - combine Multiple Columns Into One

I have an excel 2007 worksheet with 12 columns (each column is corresponding to a month) and every column includes +/-30000 rows of daily rainfall data. What I need to do is combine these columns of data into one new column (one continuous rainfall series) as follows:Copy the first 31 (the number of days of January) rows “A1:A31” from column 1 to the new columnCopy the first 28 (the number of days of February) rows from column 2 and place it beneath the previous values in the new column, and, etc.…. [The first 31 rows (March) from column 3, 30 from column 4, 31 from column ...

DAX Count Down Over (remaining)

I wanted to calculate the column RestOrder in DAX. Can you guys please help? The TotalOrderDay column was calculated with the Allexcept() formula. I need something like calculate the orders for Statedate remaining or so...or a SQL "rownumber over partition by kinda thing".Thanks for help! The following DAX can do:RestOrder = COUNTROWS( FILTER( 'Order', 'Order'[StateDate].[Date] = EARLIER('Order'[StateDate].[Date]) && 'Order'[StateDate] >= EARLIER('Order'[StateDate]) ))Results:So what you're trying to achieve is to count the number of...

Possible to use a running total row in powerpivot for calculation in a calculated field?

If I have a pivot table and I set the row to be a running total according to date and right now i will like to use this row to create a calculated field. Is it possible? If not then is there a formula for cumulative calculations for calculated field?Will supply more examples if need more clarification. I want to do something like this week 1 2 3 4 5 6 7 8 9 10count 20 20 21 25 26 27 28 29 21 21cumulative count 20 40 61 86 112 139 167 196 217 238If the week is the base field then can I create a calculated field that does something like ...

How to: Coloring cells that have matching text

I have a set of required items spread across A1:H40, and I have a buylist on col. J, I was reading this, but I'm a bit overwhelmed. How do I color the cells across A1:H40 with text that match the ones in col. J? Is there a way to incorporate this function into a button so that it can be applied to other spread sheets as well (or any other efficient way)? In addition, is there a way to design a fool-proof script that eliminates the excessive spaces at the end of each cell due to typing errors? Clear existing CF from and select A1:H40 and HOME > Styles - Conditional Format...

Excel Power Query 2016 custom column for notes

I would like to add a notes column to a merged query table, so that when I refresh the data the notes that I've made on records continue to line up. How can I add a column to do this? See my answer to this question:Inserting text manually in a custom column and should be visible on refresh of the reportIt includes a link to an explanatory video:https://youtu.be/duNYHfvP_8U?list=PLmajzIMNl6yH7MvMLmlgGUW5dOsKg74mQ [XXX]MySQL provides several variations on INSERT and UPDATE to allow inserting and updating exactly the desired data. These features provide a lot of power an...

Page 1 of 117  |  Show More Pages:  Top Prev Next Last