Hi There
In my active form, one of my filters is for the month number.
After writing the form, I have realised that I need to add another column, which actually displays data for ALL months (whilst leaving the other columns to display only the month filtered)
I added the extra column - all worked fine. Then however I changed the formula in the first row for the new column, and replaced the reference to the months cell with a hardcoded value - ie "All Months"
Am getting strange results - upon changing the formula, all the values changed correctly to reflect all months. However, when I refresh my worksheet, the values all go completely haywire. Also, when I then save my spreadsheet, they come right again.
Any ideas (I would hate to have to rewrite a cube view, and start from scratch)??
Regards
Chris
Active Forms - Different Filters for Different Columns
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Active Forms - Different Filters for Different Columns
I'm assuming that by "filter" you mean "context filter" or "title element" as opposed to filter in terms of a top count, or sort ...
The cached stargate view that gets built for your active form uses the specified month as a title element, therefore "All months" is not part of the view (and you will get nonsensical results if you try to query that member). The simplest way to fix your active form is to edit the TM1RptView formula and completely remove the TM1RptTitle reference to the month dimension. This will convert the month dimension from a title element (context filter) to a column dimension.
You could also write a separate VIEW function to link the DBRW to in your additional column rather than linking it to the TM1RptView celll, but I think for where you are at right now the 1st solution is easier and more appropriate.
The cached stargate view that gets built for your active form uses the specified month as a title element, therefore "All months" is not part of the view (and you will get nonsensical results if you try to query that member). The simplest way to fix your active form is to edit the TM1RptView formula and completely remove the TM1RptTitle reference to the month dimension. This will convert the month dimension from a title element (context filter) to a column dimension.
You could also write a separate VIEW function to link the DBRW to in your additional column rather than linking it to the TM1RptView celll, but I think for where you are at right now the 1st solution is easier and more appropriate.
- jpm_de
- Posts: 22
- Joined: Thu Jun 10, 2010 5:19 pm
- OLAP Product: TM1
- Version: 10.2.2 FP3
- Excel Version: 2010
Re: Active Forms - Different Filters for Different Columns
Hi there,
I do have a similar question.
I do have an active form with two column blocks, one that shows year-to-date KPIs and another block that shows total-to-date KPIs.
That way I got a mix of 17 values of two dimensions (KPI, TIME with year-to-date and total-to-date consolidations). Along with some other context-filter dimensions, the stargate view is quite large. Therefore supress zero is slow. And worse, it doesn't provide the expected result, because the zero suppression takes all KPI/TIME combinations of all KPIs and TIME elements involved into account. Returning a lot of rows with no data on any of the 17 report values.
Is it wise to create a rule-based 0/1-KPI for each row? TIME and KPI could become title elements of the stargate view, I reduce the view size and get rid of the unexpected supress zero rows having now data.
Problem is, how do I feed such a 0/1-KPI rule, because I would have to cycle through every TIME/row dimension combination and check the consolidations of the 17 values to decide for 0 or 1.
Any suggestions or totally different approaches?
I do have a similar question.
I do have an active form with two column blocks, one that shows year-to-date KPIs and another block that shows total-to-date KPIs.
That way I got a mix of 17 values of two dimensions (KPI, TIME with year-to-date and total-to-date consolidations). Along with some other context-filter dimensions, the stargate view is quite large. Therefore supress zero is slow. And worse, it doesn't provide the expected result, because the zero suppression takes all KPI/TIME combinations of all KPIs and TIME elements involved into account. Returning a lot of rows with no data on any of the 17 report values.
Is it wise to create a rule-based 0/1-KPI for each row? TIME and KPI could become title elements of the stargate view, I reduce the view size and get rid of the unexpected supress zero rows having now data.
Problem is, how do I feed such a 0/1-KPI rule, because I would have to cycle through every TIME/row dimension combination and check the consolidations of the 17 values to decide for 0 or 1.
Any suggestions or totally different approaches?
Why TM1? Because ...with great dimensionality there must also come -- great responsibility!
(http://www.quotecounterquote.com/2012/0 ... great.html)
(http://www.quotecounterquote.com/2012/0 ... great.html)
Re: Active Forms - Different Filters for Different Columns
Works perfectly
Thanks a million!
Thanks a million!