Quick Reports and MDX

Post Reply
JohnO
Posts: 92
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

Post by JohnO »

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?
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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
User avatar
gtonkin
MVP
Posts: 1192
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

Post by gtonkin »

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.
JohnO
Posts: 92
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

Post by JohnO »

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.
Thanks but I don't see any opportunity to edit MDX there.
User avatar
Steve Rowe
Site Admin
Posts: 2410
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

Post by Steve Rowe »

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.
Pafe QuickReport.png
Pafe QuickReport.png (7.51 KiB) Viewed 3465 times
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
a1m80t
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

Post by a1m80t »

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.
JohnO
Posts: 92
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

Post by JohnO »

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.
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.
Post Reply