PAW/PAX reports, need some advice
-
- Posts: 13
- Joined: Tue Jun 14, 2022 4:01 pm
- OLAP Product: TM1
- Version: TM1 10.2.2 - PA 2.0.79
- Excel Version: Excel 365 64bit
PAW/PAX reports, need some advice
Hi all,
I need to create some reports but I can't find the right way to do them, because every option ha something missing for my purpose. I hope that maybe you could have some advice
What I need to do is to create a report with these considerations:
- The report has virtual hierarchies
- The rows must be dynamic
- There could be some element attributes as well on the rows
- Some dimensions in the context must be synchronized across tabs
I started to create the views directly in PAW, but I'm having issues with the export: If I export in excel I lose the formatting and if I try with PDF, it exports only the top rows and not the full report. Also the layout is quite messy
So I tried to use the excel addin:
Using the exploration view I can obtain a good result, however, i cannot show any attribute (i can easily solve this by converting them to virtual hierarchies) but the main problem is that if I publish the websheet in PAW, the dimension sync doesn't work ( i enabled the dimension sync in the properties)
I can't use the quick report because I cannot make the rows dynamic and I can't use the dynamic report because I can't show the virtual hierarchies.
So the view in PAW or the exploration view in excel appear to be the only way.
What I need to know is:
- Is there a way to configure the export to pdf and make him export all the rows?
or alternatively
- Has any of you tried to publish an exploration view in PAW and being able to synchronize the dimensions?
Thanks in advance
I need to create some reports but I can't find the right way to do them, because every option ha something missing for my purpose. I hope that maybe you could have some advice
What I need to do is to create a report with these considerations:
- The report has virtual hierarchies
- The rows must be dynamic
- There could be some element attributes as well on the rows
- Some dimensions in the context must be synchronized across tabs
I started to create the views directly in PAW, but I'm having issues with the export: If I export in excel I lose the formatting and if I try with PDF, it exports only the top rows and not the full report. Also the layout is quite messy
So I tried to use the excel addin:
Using the exploration view I can obtain a good result, however, i cannot show any attribute (i can easily solve this by converting them to virtual hierarchies) but the main problem is that if I publish the websheet in PAW, the dimension sync doesn't work ( i enabled the dimension sync in the properties)
I can't use the quick report because I cannot make the rows dynamic and I can't use the dynamic report because I can't show the virtual hierarchies.
So the view in PAW or the exploration view in excel appear to be the only way.
What I need to know is:
- Is there a way to configure the export to pdf and make him export all the rows?
or alternatively
- Has any of you tried to publish an exploration view in PAW and being able to synchronize the dimensions?
Thanks in advance
-
- Posts: 52
- Joined: Mon Sep 26, 2022 11:10 am
- OLAP Product: Planning analytics
- Version: PA 2.0.74.23
- Excel Version: Excel 2016
Re: PAW/PAX reports, need some advice
- The report has virtual hierarchies => Exploration view and quick report can do
- The rows must be dynamic => Dynamique view and Exploration view can do
- There could be some element attributes as well on the rows => Dynamique view and Exploration view can do
- Some dimensions in the context must be synchronized across tabs => Dynamique view and Exploration view can do
So as long as the hierarchie is not supported by Dynamique view, you have only 1 option right now, it is Exploration view
To add attribut in exploration view, you can change the MDX of exploration view and add
WITH MEMBER
[FG_Mois].[FG_Mois].[TOTAL_ANNEE].[Societe] AS [FG_Section].[FG_Section].CURRENTMEMBER.PROPERTIES("Societe") => it will add a colum attribut of FG Section on the colum FG_Mois
- The rows must be dynamic => Dynamique view and Exploration view can do
- There could be some element attributes as well on the rows => Dynamique view and Exploration view can do
- Some dimensions in the context must be synchronized across tabs => Dynamique view and Exploration view can do
So as long as the hierarchie is not supported by Dynamique view, you have only 1 option right now, it is Exploration view
To add attribut in exploration view, you can change the MDX of exploration view and add
WITH MEMBER
[FG_Mois].[FG_Mois].[TOTAL_ANNEE].[Societe] AS [FG_Section].[FG_Section].CURRENTMEMBER.PROPERTIES("Societe") => it will add a colum attribut of FG Section on the colum FG_Mois
=> i didnt use PDF export yet, i want to know it too- Is there a way to configure the export to pdf and make him export all the rows?
it should work well, i didnt try but i see my friend do it. do you add the widget selector? and enable the dimension synchro?- Has any of you tried to publish an exploration view in PAW and being able to synchronize the dimensions?
- Steve Rowe
- Site Admin
- Posts: 2415
- 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: PAW/PAX reports, need some advice
Also, worth mentioning that Dynamic Quick Reports are supposed to be released soon. This is much much, more flexible.
Not sure on your timescales but this is supposed to be out in Q1, usual disclaimers apply. Don't stake your job / reputation on this..
Believe DQR will meet your report specifications....
Not sure on your timescales but this is supposed to be out in Q1, usual disclaimers apply. Don't stake your job / reputation on this..
Believe DQR will meet your report specifications....
Technical Director
www.infocat.co.uk
www.infocat.co.uk
-
- 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: PAW/PAX reports, need some advice
The delivery of DQR was originally "promised' for late 2020, then early 2021, then late 2021, then at the start of 2022 was promised for end of 2022. Then at the end of 2022 the rubbery promise was sometime in 2023 H1. I think the comment was something along the lines of "ISteve Rowe wrote: ↑Tue Jan 17, 2023 5:57 pm Also, worth mentioning that Dynamic Quick Reports are supposed to be released soon. This is much much, more flexible.
Not sure on your timescales but this is supposed to be out in Q1, usual disclaimers apply. Don't stake your job / reputation on this..
Believe DQR will meet your report specifications....
m not gonna give timelines on these"
I don't know if this is still the published ROADMAP
https://bigblue.aha.io/published/30f7b7 ... 10d?page=4
But DQR has disappeared from it!
Maybe someone can ask in the AMA session in the next 24 hours.
So back to the OP question and what option may currently exist:
1) An exploration view is an analytic function rather than a report, you can edit the MDX on an Exploration view using the UI and you can modify it using the API. I would ask whether you think a Quick Report would work if you had a way of controlling the set of members in the axes. If so then I think with a combination of a Quick Report and the use of the PAfE/PAX API to regenerate the report using MDX created by a custom VBA form MIGHT be an avenue. It would need someone with better knowledge/ experience of this than me to comment.
I was reading the following the other day to answer this same question for my self and I was intending to invest some personal time on this given the gap
https://ibm.github.io/paxapi/#introduction
Specifically
Reporting.QuickReports.Replace Reporting.GetCurrentReport(ActiveCell).<Quick Report ID>, <MDX statement>
Does anyone have a comment on this?
2) Alternatively see what one of the third party vendors have.
-
- 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: PAW/PAX reports, need some advice
To add, you can have dimension MDX which refers to the contents of a cell in a cube. So this is a way of creating dynamic MDX in a PAW view - where one or more of the dimensions subsets is MDX based. But of course it depends on how complete the dimension MDX needs to be,
-
- 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: PAW/PAX reports, need some advice
So from the Jan AMA session: DQR (They are considering renaming) timeline is that Q2 is optimistic, Q3 more likely. And they will be releasing the new hierarchy aware worksheet functions over time.
- Steve Rowe
- Site Admin
- Posts: 2415
- 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
- gtonkin
- MVP
- Posts: 1198
- 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: PAW/PAX reports, need some advice
In the meantime you can hone your DefineCalc and TM1Set skills!
(AFAIK TM1Set only supported on local)
DefineCalc Reference
TM1Set Reference
-
- Posts: 13
- Joined: Tue Jun 14, 2022 4:01 pm
- OLAP Product: TM1
- Version: TM1 10.2.2 - PA 2.0.79
- Excel Version: Excel 365 64bit
Re: PAW/PAX reports, need some advice
Hi,
Thank you for all your comments!
The dynamic quick report seems pretty cool! But I can't rely on that for the moment.
During these days I did more tests and I came with this solution that is indeed what you are proposing:
Publish a websheet in exploration view, and the view has specific MDX subsets that user can choose from a form.
At the moment I'm not very skilled with MDX that reads from other cubes so I created a button triggering a TI that is populating the subsets
I will try to make it more dynamic
Many thanks for your help
Regards
[EDIT] I also did some tests with the export to PDF.
The problem is that the export only covers the workspace area, so If you have a page size of 1024x760 and thousands of rows, the page won't change and the PDF will contains only the rows that you are displaying in the area.
I tried to increas the height to 8000px but the export is not split to different pages, all rows are always in one page; in addition, PAW will become very slow.
So what I understood is that the export pdf will print one page for each PAW tab, no matter what the sizes are.
So I recommend to use the export to PDF only if you have dahsboards or fixed size tabs.
Thank you for all your comments!
The dynamic quick report seems pretty cool! But I can't rely on that for the moment.
During these days I did more tests and I came with this solution that is indeed what you are proposing:
Publish a websheet in exploration view, and the view has specific MDX subsets that user can choose from a form.
At the moment I'm not very skilled with MDX that reads from other cubes so I created a button triggering a TI that is populating the subsets
I will try to make it more dynamic
Many thanks for your help
Regards
[EDIT] I also did some tests with the export to PDF.
The problem is that the export only covers the workspace area, so If you have a page size of 1024x760 and thousands of rows, the page won't change and the PDF will contains only the rows that you are displaying in the area.
I tried to increas the height to 8000px but the export is not split to different pages, all rows are always in one page; in addition, PAW will become very slow.
So what I understood is that the export pdf will print one page for each PAW tab, no matter what the sizes are.
So I recommend to use the export to PDF only if you have dahsboards or fixed size tabs.
-
- Posts: 13
- Joined: Tue Jun 14, 2022 4:01 pm
- OLAP Product: TM1
- Version: TM1 10.2.2 - PA 2.0.79
- Excel Version: Excel 365 64bit
Re: PAW/PAX reports, need some advice
@mrdauduong you said that your friends are able to synchronize the dimensions in a published pax exploration report?
I tried in every possible way but nothing.
I even added the SUBNM formulas in the same websheet of the exploration view and in paw the synchronization is enabled
I tried in every possible way but nothing.
I even added the SUBNM formulas in the same websheet of the exploration view and in paw the synchronization is enabled
-
- Posts: 12
- Joined: Thu Feb 09, 2023 11:12 am
- OLAP Product: PAW
- Version: 2.0.84
- Excel Version: Microsoft 365
- Location: UK
Re: PAW/PAX reports, need some advice
Well *that's* annoying.
An accountant having fun pretending to be a software developer
PAW 2.0.84
PAW 2.0.84