企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1333|回复: 5

Data extract where leaf value 0, but consol = 0

[复制链接]

96

主题

400

帖子

617

积分

高级会员

Rank: 4

积分
617
QQ
发表于 2014-6-28 20:45:34 | 显示全部楼层 |阅读模式
Hi

We have a TI process that currently extracts all natural accounts entries over each month for a year from a cube view. Currently the extract includes all values, no matter if they are zero or not.

I am trying to get the output file that includes a line for the natural account for each month & full year total if there is a value somewhere in at least one month, even if it is an in & out transaction over different months, so if say, $1,000 is shown in Jul, but -$1,000 is shown in Aug, with a full year total of $0, we still need the line to appear in the extract.

I am having trouble finding how to do this, within the one TI process. I have basic knowledge of TI Processes - this process was written by an ex employee, & I am trying to modify it to get it working properly. Originally, it was only extracting natural account lines with values in them, & I have included the ViewExtractSkipZeroesSet to now include zeroes. I just need it to go that extra step!

Greatly appreciate any help on this.

Regards & many thanks
Terri
回复

使用道具 举报

79

主题

412

帖子

581

积分

高级会员

Rank: 4

积分
581
QQ
发表于 2014-6-28 22:40:45 | 显示全部楼层
If i understand correctly, you want to extract lines for months only if they have non zero values, and for total_Year - extract the line in both the cases, whether it has 0 or any value.

create the extract view as you are doing it now
just before exporting the data into file, put a IF condition (assumption that months elements, Total_Year element is in same dimension)

--> check if it is month (leaf level elements), then extract only those which have measure value <>0  against them
--> For total_year ( consolidation element), no condition, every line will be exported into file.

Hope it helps..
回复 支持 反对

使用道具 举报

79

主题

384

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-6-28 22:59:39 | 显示全部楼层
tez wrote:Hi

We have a TI process that currently extracts all natural accounts entries over each month for a year from a cube view.  Currently the extract includes all values, no matter if they are zero or not.

I am trying to get the output file that includes a line for the natural account for each month & full year total if there is a value somewhere in at least one month, even if it is an in & out transaction over different months, so if say, $1,000 is shown in Jul, but -$1,000 is shown in Aug, with a full year total of $0, we still need the line to appear in the extract.

I am having trouble finding how to do this, within the one TI process.  I have basic knowledge of TI Processes - this process was written by an ex employee, & I am trying to modify it to get it working properly.  Originally, it was only extracting natural account lines with values in them, & I have included the ViewExtractSkipZeroesSet to now include zeroes.  I just need it to go that extra step!

Greatly appreciate any help on this.

Regards & many thanks
Terri

Think it sounds like you are using an asciioutput() on the data tab of your process?

In order to show:
Months where the value <> 0
Full Year where the value of any child month <> 0

I would simply have ViewExtractSkipZeroesSet to 0 (i.e. show all rows regardless of whether there is a value or not)

Then add the following at the top of your data tab (and/or metadata if you are doing similar things in there)

Code: If ( DTYPE ( 'Month', vMonth ) @= 'N' );
     If ( Value <> 0 );
           nSkip = 0;
     Else;
           nSkip = 1;
     EndIf;
Else;
     nSkip = 1;
     iCount = 1;
     iMax = 12;
     While ( iCount <= iMax );
         sMonth = ElComp ( 'Month', vMonth, iCount );
         If ( CellGetN ( sCub, v1, sMonth, v3, v4, v5 )<>0);
              nSkip = 0;
              iCount = 12;
         EndIf;
         iCount = iCount + 1;
     End;
EndIf;

If ( nSkip = 1 );
     ItemSkip;
EndIf;


sCub is the cube in your data source.
vMonth is the variable of your month dimension
v1, v3, v4, v5 are the other variables (assuming its a 5 dim cube and month is dim 2)

HTH

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

85

主题

427

帖子

621

积分

高级会员

Rank: 4

积分
621
QQ
发表于 2014-6-28 23:17:52 | 显示全部楼层
declanr wrote:I would simply have ViewExtractSkipZeroesSet to 0 (i.e. show all rows regardless of whether there is a value or not)
Depending on the sparsity in the cube this could be quite expensive, time-wise. Another option would be to add another element to the measure dimension, called ABS_Amount and calculate it as ABS(RegularMeasure). Build your view, zero-suppressed, on this measure. Then in the data tab have a CellGetN formula to get the original measure to output.
回复 支持 反对

使用道具 举报

85

主题

414

帖子

607

积分

高级会员

Rank: 4

积分
607
QQ
发表于 2014-6-28 23:22:44 | 显示全部楼层
Thanks for all your posts....I've tried some solutions & they don't seem to be working (could be because of my lack of knowledge though)!

Just to clarify, I need an asciioutput txt file, comma delimited, that gives me a line for each natural account number, along with Jul Amt, Aug Amt, Sep Amt......Jun Amt for the financial year if any of those months contain a value <> 0.  If only one month has a value <> 0, I need the zeros from all the other months also showing in the extract.  The only time a natural account number will not appear in the extract is when all months for that account = 0.

The heading of the txt file currently looks like:

         "ACN","01/AMT1","02/AMT1","03/AMT1","04/AMT1","05/AMT1","06/AMT1","07/AMT1","08/AMT1","09/AMT1","10/AMT1","11/AMT1","12/AMT1","FullYear"

where ACN is natural account number, & then the values for each month.  I had put the FullYear on the end to test that it was bringing through ACNs where the FullYear = 0.

I'm not sure if this changes some of your solutions you have given me, or has given you some extra info that helps.

Many thanks
Terri
回复 支持 反对

使用道具 举报

86

主题

402

帖子

589

积分

高级会员

Rank: 4

积分
589
QQ
发表于 2014-6-29 00:37:39 | 显示全部楼层
Ok, I worked it out! I have added in the code Code: if(AMT01<>0 % AMT02<>0 % AMT03<>0 % AMT04<>0......) after the extract of all values & before the asciioutput line, it will export all month values for only those natural account numbers that have a value in any AMTxx column <> 0.

Thanks for all your ideas & help.   

Cheers
Terri

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-10-27 23:16 , Processed in 0.172593 second(s), 11 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表