Need all months of year in query structure but only data for year to date.

[Former Member]

HI BW Gurus, we are on BI 7.0.

I am looking to complete the following in a BI Query.

We need to always have all months in year (1-12) in a structure in the colums but only have transaction data for the actual months to date and the remainder of the year months to be zero

So there must be 12 months always (Jan to Dec)

Example- say its "June". Even if there is transaction data for July to Dec it must be blank in the query but the respective columns must be there ie all 12 months.

Thanks in advance. Points will be generously awarded.

Kind regards,

Stevo;)

[pavel_afanasiev]

Hi Stevo,

Another simple solution is to use a combination of structure and variable selection.

So, 1st, you create a structure for the columns, where you will create the 12 months and label them appropriately. Within each column, restrict your selection to that month - whether you are using 0FISCPER or 0CALMONTH.

then, for the query restriction, you will need to choose the variable for the month/posting period. Instead of using a single variable, use the range - from 1 (or 0 if you want beginning balance) to the variable.

Make sure you don't hide structures when the columns have no data, and you would be all set here.

Let me know if you need more detail.

Regards

Pavel

[former_member181964]

Hi,

This kind of reports we call it as Rolling reports, in columns you restrict the keyfigures with 0CALMONTH with Customer Exit Variable (with Intervel Low and High), use customer Exit Variable on 0CALMONTH and write code to fetch the Months from Jan to Dec, it is possible.

Else

You just drao and drop 0CALMONTH and restrict this 0CALMONTH with Customer Exit Variable which will give the data from Jan to Dec. Then just drag & drop Keyfugures.

See for Customer Exit Code...

http://wiki.sdn.sap.com/wiki/display/profile/Surendra+Reddy

Thanks

REddy