Quick Reports and MDX
-
- Posts: 96
- Joined: Mon Jul 29, 2019 5:02 am
- OLAP Product: Planning Analytics
- Version: 2.0.9.x
- Excel Version: Office 365 16
Quick Reports and MDX
There is nothing in the PAX user interface which allows modification of MDX behind Quick Reports however in looking at the PAX API it seems it is possible to extract and modify MDX for a Quick Report. I'm wondering why the functionality is not in the UI, I'm wondering if there are some tricks or issues with it. Has anyone used the PAX API to do this?
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Quick Reports and MDX
I think that if you go onto the worksheet objects part of the PAfE task pane then there is some opportunity to interact with the definitions there.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Quick Reports and MDX
Have not used Quick Reports too much yet but everything looked to be linked in Named ranges - check in Name Manager to see if you get what you need.
-
- Posts: 96
- Joined: Mon Jul 29, 2019 5:02 am
- OLAP Product: Planning Analytics
- Version: 2.0.9.x
- Excel Version: Office 365 16
Re: Quick Reports and MDX
Thanks but I don't see any opportunity to edit MDX there.Steve Rowe wrote: ↑Fri Nov 08, 2019 9:05 am I think that if you go onto the worksheet objects part of the PAfE task pane then there is some opportunity to interact with the definitions there.
- Steve Rowe
- Site Admin
- Posts: 2417
- Joined: Wed May 14, 2008 4:25 pm
- OLAP Product: TM1
- Version: TM1 v6,v7,v8,v9,v10,v11+PAW
- Excel Version: Nearly all of them
Re: Quick Reports and MDX
It may have dropped off in later releases of PAfE...at one time you could change the MDX subset references to reference a hierarchy for example and I assume to any valid MDX.
I don't seem to be able to do it now though, which is a shame as it was one of the few ways you could get to a hierarchy in Excel (COUGH COUGH COUGH *LUNG*).
Credit for the original tip to Ryan D Clapp.
I don't seem to be able to do it now though, which is a shame as it was one of the few ways you could get to a hierarchy in Excel (COUGH COUGH COUGH *LUNG*).
Credit for the original tip to Ryan D Clapp.
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- Posts: 13
- Joined: Mon Mar 09, 2015 11:54 pm
- OLAP Product: TM1
- Version: 9.1 to 10.2.2
- Excel Version: Office 2010
Re: Quick Reports and MDX
While I 100% agree this should be done in the GUI, you can still make an Quick Report to function as an Active Report with minimal VBA
1. Create a dynamic MDX generator in Excel. You can use Exploration Report to give you an idea on how to create the MDX
2. Create a Quick Report where it needs to be
3. Use Reporting.QuickReports.CreateFromMDX which references the dynamic MDX you created in step 1.
You will need to be careful of order of operations and a bunch of other silly stuff but it actually works quite well.
1. Create a dynamic MDX generator in Excel. You can use Exploration Report to give you an idea on how to create the MDX
2. Create a Quick Report where it needs to be
3. Use Reporting.QuickReports.CreateFromMDX which references the dynamic MDX you created in step 1.
You will need to be careful of order of operations and a bunch of other silly stuff but it actually works quite well.
-
- Posts: 96
- Joined: Mon Jul 29, 2019 5:02 am
- OLAP Product: Planning Analytics
- Version: 2.0.9.x
- Excel Version: Office 365 16
Re: Quick Reports and MDX
Thanks, that's the confirmation I was seeking. I can see all sorts of opportunity with it. The question is whether IBM will come up with an enhancement to the UI to provide this function. Given the rate of development to date with PAX it seems unlikely in the near term but given they are talking up a major release in 2020 who knows. I have been told that they are working on expansions in quick reports. I'm tempted to build something as an add-in but it would be a waste of time if IBM get their act together.a1m80t wrote: ↑Thu Nov 14, 2019 4:02 pm While I 100% agree this should be done in the GUI, you can still make an Quick Report to function as an Active Report with minimal VBA
1. Create a dynamic MDX generator in Excel. You can use Exploration Report to give you an idea on how to create the MDX
2. Create a Quick Report where it needs to be
3. Use Reporting.QuickReports.CreateFromMDX which references the dynamic MDX you created in step 1.
You will need to be careful of order of operations and a bunch of other silly stuff but it actually works quite well.