How to achieve this report ?

[Former Member]

experts,

i have data something like this...

                         001/2013    002/2013     ......012/203

Cust01                    1

cust02                     1

Cust03                                   1

.

.

.

son on...

basically, each customer has one entry for ONLY one Fiscperiod. User enters the Range for a fiscperiod ( in this example i have taken 001/2013 to 012/2013)

Now, my user wants report something like this....

                          001/2013    002/2013     ......012/203 

Cust01                    1               1     ......               1

cust02                     1               1     .......               1

Cust03                                     1     .......               1

So, here Cust01 has entry for 001/2013, so for this customer report should show 1's for all following Fiscperiod..

same for the cust02, and if you see Cust03 has entry on 002/2013, so for this cust03, all 1's should start from 002/2013 to 012/2013.

According to data model, we have only 1 record for each customer.....

Any thoughts ? Appreciated...

[Former Member · Accepted solution]

Hi Sandy,

I would try with this...create three key figures:

1) Restricted key figure A -->

          If Fiscal period equal to 001/2013, pick up the value of the key figure that you need (the 1 or the 0, depending on the value of the keyfigure in the cube)

2) Restricted key figure B -->

  If Fiscal period different to 001/2013, constant value of 1.

3) Calculated key figure (this one will be show in your report)

              Restricted key figure A + Restricted key figure B.

     So for Fiscal period 001/2013, you would have the value of the cube, for any different you would have the constant of 1.

I hope that it helps!

[Former Member · Accepted solution]

Hi Die,

Thanks for your reply. but it wont work, as i have variable created on 0Fiscper. so it depends on users what to put. It may be 005/2013 to 006/2013.

Not all the time i001/2013 will be in the report.

And not all customer will have the same entry for fisper. If some one keep 001/2013 to 012/2013 then there will be customers who will have entry on any of the fiscper.

The goal is, to get entry in all following fiscper from the main ones...

so if the varial is set 001/2013 to 012/2013 and customer1 has entry on 005/2013 ( According to data) then this customer i have to show entry for 006/2013, 007/2013 till 012/2013

I myself think , its not possible to achieve, but still i am open to any options you guys have..

Thanks a loot for lookng in to it.

[umashankar_poojar · Accepted solution]

Hi Sandy,

        As you mentioned there is only one entry per customer, below is the simulation in excel and it can be achieved at Bex Designer.

• Change here is, always restrict as <=12.2013 by doing this values will be shown as summation of complete year for a customer. As there is only one entry for entire year in Cube report columns shows same value as 1.

Now next requirement is; if user enters 2.2013, then results should be shown from 2 onwards. and by assuming Jan should show blank.

• Create a 12 columns and name as shown in report section. i.e., 1.2013 to 12.2013. Create 12 customer exits, In each exits put a condition to check if month entered < current month(2) then return 00.2013 if month entered >= current month(2) then return 12.2013. Hardcode the current month (2,3,4..) in respective month's customer exits.

In other words, if user enters 3.2013, design a customer exits to return 00.2013 for Jan and Feb columns to show blank values. From 3 onwards customer exit should return 12.2013.

Hope it helps!

Thanks,

Umashankar

[Former Member]

Hi,

Try using Cell Definitions in your report and for each Fiscper read the values from its adjacent cell from the left. You may use 'If then else' clause to achieve this result.

Thanks,

Amit Sinha

[yasemin_kilinc]

Hi Sandy,

Will you please try selecting cumulated check in your key figure. Hope it helps

Yasemin...

[Former Member]

Hi Sandy,

For how many fiscal periods you have to copy that entry!

E.g is it for next year or for next 5 years?

I would suggest you to create another DSO or Cube and load data to this after creating records. Do it through APD's.

Then you don't have do anything in Query and performance will be good!

regards