Hi Andrea,
Few years ago, I have used a quick report with a macro wich change the MDX of a view.
First with Pafe create an exploration and copy the MDX of this view.
Create a quick report based on this view
import the vba automation CognosOfficeAutomationExemple.bas and CognosOfficeMessageSuppressor.cls
(ask to IBM support where to fin files and documentation)
You are able to modify the MDX of the view with a macro like :
Public Sub Replacer()
'
' Replacer Macro
'
Dim MDX As String
Dim IDReport As Integer
MDX = "SELECT TM1SubsetToSet([month].[month], ""ss_mois_annee"") DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 0, HEAD(TM1SubsetToSet([region].[region], ""ss_Mes_pays""), 500) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY ON 1 FROM [PNLCube] WHERE ([actvsbud].[Actual] , [account2].[Sales]) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME, MEMBER_NAME, MEMBER_CAPTION, LEVEL_NUMBER, CHILDREN_CARDINALITY , [account2].[account2].[compte2]"
Worksheets("RapRapide").Activate
Cells(8, 2).Activate
IDReport = Reporting.GetCurrentReport(ActiveCell).ID
Reporting.QuickReports.Replace IDReport, MDX
End Sub
for sample change the hierarchy.
When, on quick report, a user change the point of vue, an action button can throw the macro and the quick report is ..... perhaps .... as you want.
Regards,
Philippe
------------------------------
Philippe CHAMPLEBOUX
------------------------------
Original Message:
Sent: Fri September 20, 2024 09:52 AM
From: Andrea Bonelli
Subject: MDX - Hierarchy Change by context
Hi Stuart,
Thank you somuch for your answer. Unfortunately, the approach you described reflects the way I am currently handling the issue, but the behavior I aim to achieve aligns more closely with what Declan has described.
I am working with a product dimension that is used within a budget input cube. This dimension is recreated for each version of the budget with different aggregations:
- Budget 2024 (which I intend to transform into the hierarchy [Product].[BDG2024]):
- Commercial Group A
- Product A
- Product B
- Product D
- Commercial Group B
- Budget 2025 (hierarchy [Product].[BDG2025].Members):
- Technical Line A
- Technical Line B
As such, for each scenario, there will be a hierarchy in the product dimension, as the aggregation is reshuffled each time a new budget is created.
Is there a way to automatically adjust the hierarchy in the rows of a cube viewer when the user selects a different element in the Scenario Budget dimension?
I hope this clarifies my question..
Thank you for your time and consideration.
Best regards,
Andrea
------------------------------
Andrea Bonelli
Original Message:
Sent: Tue September 17, 2024 07:19 AM
From: STUART KING
Subject: MDX - Hierarchy Change by context
Hi Andrea,
I'm not 100% sure I understand the question correctly. I think you are asking if the set of members in one hierarchy (dimension) can be determined by the selection of a member in a different hierarchy (dimension). I can provide a simple example of this. Consider this view:
The MDX is this used to render the view:
SELECT
{
TM1SubsetToSet([Measures].[Measures],"Default","public")
} ON 0,
{
FILTER(
[Store].[Store].MEMBERS , [Store Locations].(
[Store].[Store].CURRENTMEMBER,
[Locations].[Locations]) = 1)
} ON 1
FROM
[Dynamic Sales Cube]
WHERE (
[Locations].[Locations].[East],
[People].[People].[Sandy],
[Channels].[Channels].[Retail])
As I change the Location, I will see a different set of stores on the row axis of the view. For example (when I change Location from East to South):
To make this work I use another cube called Store Locations that flags what stores belong to which location (some stores belong to two or more locations). This is often attribute data instead of a separate cube in production TM1 models.
Let me know if this helps or if I didn't understand the problem you are trying to solve.
------------------------------
Stuart King
Product Manager
IBM Planning Analytics
Original Message:
Sent: Wed September 11, 2024 01:45 PM
From: Andrea Bonelli
Subject: MDX - Hierarchy Change by context
Good evening everyone,
Maybe is a dumb question but I've hit a wall in trying to achieve the following behavior:
I have a budget application with several scenarios (2024, 2025, etc.). Every scenario use a custom set of elements created in the scenario initialization, where the user define the aggregation for product element where input the budget (can be any attribute and it can choose moltiple level of aggregation).
I've tried to manage the product dimension with different hierarchies, one for each scenario, and tried to dynamically change the hierarchy in the budget input view by defining the view by MDX.
My idea was to define on rows the product as:
StrToSet("[product].["+[scenario].currentMember.name+"].members")
But unfortunately the compiler doesn't like this definition.
Is it the right approach? Is there any other method to dynamically change the hierarchy based on context without using a TI?
Thanks alot,
Andrea
------------------------------
Andrea Bonelli
------------------------------