olap

Hi Everyone, I have a workbook that contains a lot of OLAP pivot tables and so the goal for me is to create a macro that will automatically change the filter known as "month year" for all of the pivot tables in this workbook. The good news is that the macro I wrote manages to do that, but now...

Sometimes you have slicers with a long list of items, and need to find out what are the selected items. Normally, it is necessary to loop through all items to get this information, as shown on the first code. However, if the pivot table is created with the Data Model option checked, it will be...

I brought in a table and loaded it to data model. I created a pivot and then used OLAP tools to convert it to Cube formulas (allowing me to insert columns, etc where a pivot table won't let you). My problem arises when I bring in the next month's data. I've written the formula that hould work...

Hi, Does anyone have any experience of applying formats to pivot table measures conditionally based on their name? For example, if my measure has a (£) in its name, then apply a currency format, else apply a simple numeric format. I've done this kind of thing successfully with ordinary pivot...

Hello, This is the 1st time I am working with OLAP Cube Pivots. Need help please. I have a OLAP Cube pivot that has years from 1900 to current year. However it is missing few years in between. I need to be able to select all the years except for the last 15 years (2004 to 2019 needs to be...

Hi team, I have to filter more than 100 items to olap cube. I can't it manually, can i do this with vba ? the items are in a row (Q2:Q150) I want that vba add it to olap cube filter. I found a code, but doesn't work. sub test() Dim MyArray as variant Dim rcnt as Integer rcnt =...

Good Afternoon, I have an excel workbook in which there is a pivot that is connected to power BI. When I attempt to refresh this pivot I get stuck in an infinite refresh loop forever. It just says the following things over and over again: Running OLAP query Refreshing the OLAP cube Waiting...

I've followed instructions on this thread : https://www.mrexcel.com/forum/excel-questions/982591-slicer.html, But it only works on a standalone pivot table, not one connected to an OLAP cube. Is there any way to generate Pdf files of a pivot table that's connected to an OLAP cube, based on...

Hello everyone, Below is a simplification of a pivvottable (OLAP!) I've created. I'm having trouble adding a column that shows the difference between the two periods I cannot use a calculated column because the source is OLAP/cube I cannot figure out how to use an MDX measure because there is...

I'm looking for a way to paste a list of values into a filter search on an OLAP pivot rather than click each value. I saw a way to do this years ago but cannot find it. I saw mention of an add-in, but not sure which one. - Pete

I have =CUBEVALUE() functions referencing twenty Lists created with Data Validation (Bolded below). =CUBEVALUE("ThisWorkbookDataModel",$B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$E$2,$E$3,$E$4,$E$5,$H$2,$H$3,$H$4,$K$2,$K$3,$K$4,$K$5,$N$2,$N$3,$H$7,$A$1,$A12,B$11) The limitation is that only one value per...

When removing some field items from your pivot table based on an olap query, I noticed some filters automatically clear, where as others remain and you must find them on the field list and clear them manually. I'm not sure what the driver here is... does anyone have an idea? google has not...

Hi! all my previous question I found answers on on this forum, but on the following question I couldn't find any related topics: I'm trying to write a VBA code to copy paste 2 sheets of my workbook to a new book. So far no problem with VBA code below: Sub Button1_Click() With ThisWorkbook...

Hello Mr Excel, I have a tricky VBA code I'm trying to figure out, but cannot quite get the code to recognize my code for the dates. I'll try to clarify my issue and show blogs I've tried. I've build a table that includes 12-20 dates in the format YYYY-PMM so "Year"- "P" (Period) and "month"...

Hello, I have an OLAP Pivot. There is a Value in one of the rows that I want to move to the end. The field is called "Top Customer", the value is "All Other". The vba recorder produces this: ActiveSheet.PivotTables("PivotTable3").PivotFields( _ "[Sales Org Hierarchy].[Top...

I currently have a workbook designed that uses data from several reports within our POS system. The problem is I have to run reports and export to excel, merge some reports so they are in one clean table, then copy and paste into my workbook. From there my pivot tables automatically refresh...

Hello members, I have just starting to write <acronym title="visual basic for applications">vba</acronym> code and would appreciate anyones help on this :smile: I have tried looking in various forums for the answers but to no success. I am currently running code as per below for a...

Hi, I am trying to get a macro to run to automatically change the filters in a Pivot table linked to a Cube. I recorded the macro to begin with but I always get the error listed in the title of this thread. My code is: Sub CubeReconcile() Application.ScreenUpdating = False...

In my pivot table that is connected to a SSAS OLAP cube, I have a dimension with several values that are the same, but have two different values defined for the same property. For example, dimension value "ABCD" has a property 1 value of both "123" and "789". As a result of this, the pivot...

Hi there, I hope someone can help me. I really need to filter my pivot table based on OLAP cube with list of styles. Is there a way to do that? I would have a list of items in column b1:b10 and I expect that the filter gets updated anytime I change a value on in the list. Much appreciated with...