TM1 Forum

Being the novice I am, the only reporting I have done has been single row. Has anyone done multi-row report per parent element which does change per reporting period, i.e. top 100?

Example:

Line 1 Parent Line (select and sort; changes per reporting period)
Line 2 MONTH-TY xxxxx xxxxxx xxxxx xxxxxx xxxxx
Line 3 MONTH-LY xxxxx xxxxxx xxxxx xxxxxx xxxxx
Line 4 YTD - TY xxxxx xxxxxx xxxxx xxxxxx xxxxx
Line 5 YTD - LY xxxxx xxxxxx xxxxx xxxxxx xxxxx

Any advice or feedback would be appreciated. Thanks, Rick.

You've left off a lot of key information here but I'll give it a try. I assume when you say "Parent" element, you mean a consolidated node from some other dimension other then the time dimension and you are talking about mixing the hierarchy of this "other" dimension with those time elements. I also am going to assume "reporting" means an active form report. Good news and bad news. Bad news: you aren't going to be able to mix theses two dimensions in the same column. Good news: if you separate them into two columns it will be rather easy using active forms. Create a subset of the "other" dimension that has the parent elements you want in it and also a subset of the time dimension elements you want. Reference those two subsets in the respective TM1RPTROW formulas.

tomok , thank you for the reply and apologize for being vague. I'm living and learning!

This report is a supplier performance based report and the suppliers will be the parent. I am creating a top 100, bottom 100, and also be able to select by division, buyer, etc.

I am currently working on your idea using active forms and see what I get. As our company only provided the basic training when we started three + years ago and haven't really been active until some turnover, I am at a loss at any 'advance' type development and trying to get up to speed on the fly.

The forum is awesome but at the same time 'greek' as we haven't been exposed to many of the concepts presented.

Thanks to the once again outstanding assistance provided on this forum I do have the base report created and working using Active Forms as recommended and working great. Next curve ball that has been throw at me is the ability to sort by one of the measure elements. Is this possible within the active form and if so, can someone give me another nudge in the right direction?

When creating the form I created a group dimension, values 1 thru 4, with appropriate alias for each for the line display. That is 1=MTH-TY, 2=MTH-LY, 3=YTD-TY, and 4=YTD-LY. The request is that the user good select one of those measures and list descending or ascending.

I did not see anything related on the toolbar Is an action button creating a subset an option?

Thanks once again. Rick.

rfielden wrote:When creating the form I created a group dimension, values 1 thru 4, with appropriate alias for each for the line display. That is 1=MTH-TY, 2=MTH-LY, 3=YTD-TY, and 4=YTD-LY. The request is that the user good select one of those measures and list descending or ascending.

If you click on the "filter" button in the active form toolbar or ribbon group you get the same filter dialog as if you were to right click a column in the cube viewer and select the filter option from the context menu. Chose the measure (column) to sort on and ascending/descending. You will notice that if you unhide row 9 you will see that to the right of the TM1RptView formula a TM1RptFilter formula has been inserted. Rather than having to train users on the filter function you can "parse" values into the filter formula from other cells (like linking to a cell validation drop-down listing your 4 column measures, and other cells to sort ascending or descending, and maybe another for top 5, 10, 50, all, etc, etc). Much easier for the user and also much more functional and not too much effort in terms of building the report. If you want to go the extra yard you can add conditional formatting to highlight the column that the sort/filter has been applied to. Et voila.

lotsaram, this is all good stuff and learning thru the school of hard knocks along with the expert guidance provided on this forum!

I tried your suggestions and not obtaining the results described. I did unhide all columns and rows and attempted a filter on one of the columns which did not produce the desired results and did not see any formula created in row 9. In the report there are four rows per supplier. Still not clear if I select say the Net Sales column and want to sort descending on the MTH-TY value, how that would properly work.

Little frustrating as the advice I have received has gotten me so close.....

I think with tomok and my suggestions you should be able to get there. Can't really help further unless I can see what you're doing wrong. Some screenshots would be helpful.

lotsaram wrote:I think with tomok and my suggestions you should be able to get there. Can't really help further unless I can see what you're doing wrong. Some screenshots would be helpful.

Apologize and now fully understand the cans and cannots of this function.

You can't filter with nested dimensions on rows. If you move your Month, YTD, Prev Yr measures to columns then you should be able to do the filter quite easily! (I think tomok said as much in his first reply)