Page 1 of 1
Export periods across columns subset for source view
Posted: Thu May 31, 2012 2:50 am
by BigG
Hi, trying to create a export out of cube to text. The output view has 24 months across columns. The source view is therefore without the months so we dont repeat 24 times. After any suggestions on methods for creating the 'zero supress' view for the source without bringining in the 24 columns of possible cell values?
Any suggestions would be great.
Cheers
G
Re: Export periods across columns subset for source view
Posted: Thu May 31, 2012 3:16 am
by winsonlee
Re: Export periods across columns subset for source view
Posted: Thu May 31, 2012 3:43 am
by BigG
possibly, that was just a screenshot of the view definition in TI you sent though, is that what you were implying I should use? I think I may use that to get me started, but will manage the subset for applicable dims in the prolog. Problem with the screenshot is Jan is the only month selected, and with supress zero, if jan is not populated with all possibilities for feb to dec values then I would probably skip a bit of data...
Re: Export periods across columns subset for source view
Posted: Thu May 31, 2012 3:51 am
by winsonlee
you wanted to have a suppress zero using TI code instead ?
have you tried using
ViewExtractSkipZeroesSet (Cube, ViewName, Flag);
or
ViewColumnSuppressZeroesSet(Cube, ViewName, Flag);
Re: Export periods across columns subset for source view
Posted: Thu May 31, 2012 8:10 am
by Duncan P
ViewColumnSuppressZeroesSet won't work as TI uses an extract view which ignores it.
I am presuming that you want one record for each set of 24 months and you want to skip all records for which all 24 months have no data. I have done this in the past by creating a calculated extra item ( =1; will do) in the periods dimension and then feeding it from all of the other periods. Then your view should have just that item selected from the periods dimension and ViewExtractSkipZeroesSet( cube, view, 1 ) and ViewExtractSkipRuleValueSet( cube, view, 0 ). Then for each record read the values for the individual periods with CellGetN.
Re: Export periods across columns subset for source view
Posted: Thu May 31, 2012 8:31 am
by lotsaram
Duncan P wrote:ViewColumnSuppressZeroesSet won't work as TI uses an extract view which ignores it.
I am presuming that you want one record for each set of 24 months and you want to skip all records for which all 24 months have no data. I have done this in the past by creating a calculated extra item ( =1; will do) in the periods dimension and then feeding it from all of the other periods. Then your view should have just that item selected from the periods dimension and ViewExtractSkipZeroesSet( cube, view, 1 ) and ViewExtractSkipRuleValueSet( cube, view, 0 ). Then for each record read the values for the individual periods with CellGetN.
I would do it exactly the same way with one artificial "month" that is used for the view processing only and then 24 CellGetN lines (or a while loop over months). In therms of the artificial month for the view you can do it N level with rule & feeders from the component months or have a rollup of the 24 months and make sure that ViewExtractSkipCalcs is zero. This means more complexity setting up the subsets for every other dimension but you save on the rules & feeders. which one to use depends on situation & personal preference.
