I am trying to generate a filtered list with CUBESET based on multiple filters on an Excel sheet, and I am getting nowhere even if Google is my friend.
I am working in Excel 2019 on a local OLAP cube (Excel Data Model, tables are imported from other Excel tables, modifying the input is not an option)
The task is to "upgrade" an existing report table with dynamic queries from the underlying cube. The user can define three inputs:
- Number of the article group
- Start date of the query
- End date of the query
In the data model there are (in this example) three tables:
- Return table: List of all the article numbers which were returned, along with the date of return
- Article list lookup table: lists all the articles over all article groups and the corresponding group number for each article
- Date lookup: a general date lookup table
The lookup tables have a "1 to many" connection with the fact table
The table that needs to be filled, is a top-10 table, listing the articles with the most returns in the chosen group, between the start and end date. There is a measure, which gets the number of returns for each article. The first column is a sequence of numbers from 1 to 10. The second column should be a list of article numbers starting with the one with the most returns.
It is clear, that I can get the Article numbers with CUBERANKEDMEMBER but for that I need a properly filtered CUBESET. And that's where the problems start.
I was able to generate a list of articles with CUBESET:
=CUBESET("ThisWorkbookDataModel";
"{([Returns].[Group].[All].["&GroupNum&"],
[Returns].Article].children)}";
"ArticleNr";
2;
"[Measures].[Number of Returns]")
where "GroupNum" is a named field, where the user can select the desired group from a list.
This code generates a list, but it completely disregards the start and end dates and I wasn't able to insert that filter.
My question is how can I create a properly filtered CUBESET. (Properly means filtered to article group, starting and ending date).
Thank you for the help in advance.
Peter
