TM1 Forum

Hello All,

I know it might be a silly question, but still, i am looking for answer.

We have created an Active form and there we have function TM1RPTVIEW for Cube view reference along with all Context filter. Now i want to add one more context filter on one dimension for the view which is part of rows dimension in Cube view as shown in below example:

Existing Function def:
=TM1RPTVIEW("debt forecasting uat:Debt Test:1", 1, TM1RPTTITLE("debt forecasting uat:Ess_Years",$C$11), TM1RPTTITLE("debt forecasting uat:Ess_Scenario",$C$12), TM1RPTTITLE("debt forecasting uat:Ess_DTS",$C$13), TM1RPTTITLE("debt forecasting uat:Currency Type",$C$14), TM1RPTTITLE("debt forecasting uat:Country_dim",$C$15),TM1RPTFMTRNG,TM1RPTFMTIDCOL)

Updated one:
=TM1RPTVIEW("debt forecasting uat:Debt Test:1", 1, TM1RPTTITLE("debt forecasting uat:Ess_Years",$C$11), TM1RPTTITLE("debt forecasting uat:Ess_Scenario",$C$12), TM1RPTTITLE("debt forecasting uat:Ess_DTS",$C$13), TM1RPTTITLE("debt forecasting uat:Currency Type",$C$14), TM1RPTTITLE("debt forecasting uat:Country_dim",$C$15),TM1RPTTITLE("debt forecasting uat:Manager_Dim",$C$16),TM1RPTFMTRNG,TM1RPTFMTIDCOL)

and when i hit recalculate button based on selection from newly added filter, it shows nothing in view. Well i have also added subset function for that new filter in respective cell(C16).

So Not sure, if i am missing anything. Please guide me.

Thanks,
Manoj

If you add a TM1RPTTITLE to the TM1RPTVIEW formula then you are telling TM1 that dimension is title element. If the report is set up such that the dimension is used as a rowset then of course the active form won't work. Why would you expect anything different?

You can have a cell-validation dropdown or SUBNM cell in your report that is referenced by the TM1RPTROW formula to drive either a subset or MDX expression to determine the rowset. This will work just fine. But don't add the dimension to the TM1RPTVIEW!

You can move a dimension from the rowset to be a title element in an active form but you have to modify the report a good bit before it will work. It looks like you handled the TM1RPTVIEW part OK. Now you have to delete the column in the active form that the dimension was originally being displayed in and modify all the DBRW formulas to point the Manager_Dim reference to cell $C$16 instead of the original column it was in. As long as deleting the column did not remove other parts of the active form you should be able to rebuild the form and recalc.

Actually I have created a websheet, and beside having that dimension in rowset, we also want to provide a flexiblity to filter out on dimensions which are part of rowset in websheet.

In Active Form, this is quite possible by using Excel data filter functionality, but in Websheet, It is not. So was trying for workaround on that.

manoj928 wrote:Actually I have created a websheet, and beside having that dimension in rowset, we also want to provide a flexiblity to filter out on dimensions which are part of rowset in websheet.

In Active Form, this is quite possible by using Excel data filter functionality, but in Websheet, It is not. So was trying for workaround on that.

You don't need a workaround. The Active Form functionality can do this as part of the TM1TPTROW function. You can filter what you show for the dimension via subsets, are better yet, an MDX query. Look up TM1RPTROW in the TM1 Reference Guide. It will work in both Excel and TM1Web.

Yup, that was my take on the original question.