TM1 Forum

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

- 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

- Is there a way to configure the export to pdf and make him export all the rows?

=> i didnt use PDF export yet, i want to know it too

- Has any of you tried to publish an exploration view in PAW and being able to synchronize the dimensions?

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?

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

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

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 "I
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.

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,

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.

sigh.....

Steve Rowe wrote: ↑Fri Jan 20, 2023 8:27 amsigh.....

In the meantime you can hone your DefineCalc and TM1Set skills!
(AFAIK TM1Set only supported on local)
DefineCalc Reference
TM1Set Reference

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.

@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

gtonkin wrote: ↑Fri Jan 20, 2023 9:02 am (AFAIK TM1Set only supported on local)

Well *that's* annoying.