Quick Reports and MDX

Post Reply
JohnO
Posts: 4
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.8
Excel Version: Office 365 16

Quick Reports and MDX

Post by JohnO » Fri Nov 08, 2019 3:36 am

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: 1962
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Quick Reports and MDX

Post by Steve Rowe » 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.

User avatar
gtonkin
MVP
Posts: 736
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Quick Reports and MDX

Post by gtonkin » Fri Nov 08, 2019 9:19 am

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: 4
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.8
Excel Version: Office 365 16

Re: Quick Reports and MDX

Post by JohnO » Mon Nov 11, 2019 12:45 am

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: 1962
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Quick Reports and MDX

Post by Steve Rowe » Mon Nov 11, 2019 9:22 pm

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 46 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.

a1m80t
Posts: 9
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 » 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.

Post Reply