Page 1 of 1
Quick Reports and MDX
Posted: Fri Nov 08, 2019 3:36 am
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?
Re: Quick Reports and MDX
Posted: Fri Nov 08, 2019 9:05 am
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.
Re: Quick Reports and MDX
Posted: Fri Nov 08, 2019 9:19 am
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.
Re: Quick Reports and MDX
Posted: Mon Nov 11, 2019 12:45 am
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.
Re: Quick Reports and MDX
Posted: Mon Nov 11, 2019 9:22 pm
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 (7.51 KiB) Viewed 3540 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.
Re: Quick Reports and MDX
Posted: Thu Nov 14, 2019 4:02 pm
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.
Re: Quick Reports and MDX
Posted: Wed Nov 20, 2019 7:04 am
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.