Hi All,
I have a requirement to develop a perspectives report. The report has a complexity to report at a lower level depending on the last level (char) of the Project. I will attach sample data.
Like you see in the full data sheet, all projects end with an alpha char which implies to what expense type it is.
All Ending With A - Uniform
All Ending With B - Supplies
All Ending With C - Chemicals
All Ending With D - Spare Parts
Using the full data, we want to report at level 2 of the project - what is shown in second pic.
Can someone please help with the logic on getting the report built? Any direction will be helpful.
Thanks,
Luther.
Perspectives Report
-
- Posts: 124
- Joined: Tue Jun 13, 2017 3:20 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Perspectives Report
- Attachments
-
- ReportData.csv
- (151 Bytes) Downloaded 141 times
-
- fullDataSample.png (25.94 KiB) Viewed 1398 times
-
- ReportData.png (7.21 KiB) Viewed 1398 times
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Perspectives Report
How is it set up in the dimension, more precisely, a hierarchy might better suit your purposes although Perspectives is out then and PAfE will be better. Do you have your rollups to aggregate in a cube ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 124
- Joined: Tue Jun 13, 2017 3:20 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Perspectives Report
Hi Wim,
Yes, Project dimension hierarchy is in place and attached the image. can you give me a hint on getting the data at level 2?
Hope I was able to explain.
Depending on the level 4 of the project, we report at level 2.
All Ending With A - Uniform
All Ending With B - Supplies
All Ending With C - Chemicals
All Ending With D - Spare Parts
Thanks,
Paul.
Yes, Project dimension hierarchy is in place and attached the image. can you give me a hint on getting the data at level 2?
Hope I was able to explain.
Depending on the level 4 of the project, we report at level 2.
All Ending With A - Uniform
All Ending With B - Supplies
All Ending With C - Chemicals
All Ending With D - Spare Parts
Thanks,
Paul.
- Attachments
-
- ReportData.png (7.21 KiB) Viewed 1336 times
-
- Proj_Hierarchy.png (20.84 KiB) Viewed 1336 times
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Perspectives Report
Can you show a picture of the dimension ? Only the relevant part and no alias for exanple. Then we see better the element names and what is at play.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Perspectives Report
What you try to do is exactly alternate hierarchies. Do you have PAW and PAfE as well ? It will be much easier. If not, is A through D a real dimension in the cube of data ?
An example output would be useful too ?
An example output would be useful too ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 124
- Joined: Tue Jun 13, 2017 3:20 pm
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2010
Re: Perspectives Report
Thanks Wim, I created another cube and a dimension (for level 4 for proj dim - idea from you) and it works like a charm.
Could we go without a cube? maybe we could, but in a cube form, it data looks good for verification.
Thanks for that idea.
Paul.
Could we go without a cube? maybe we could, but in a cube form, it data looks good for verification.
Thanks for that idea.
Paul.
-
- MVP
- Posts: 3120
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Perspectives Report
You can do without a cube. You use alternate hierarchies and use PAfE.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly