Bex Query Designer

[Former Member]

Hi All,

We have a requirement where we have fields Employee ,Plan ,Start date ,end date.

An employee can have more than 1 Plans in a year.

We only need to show those employees in report who have more than one Plan in a year.

Sample Data:

Employee Plan  Start Date       End Date

101           A       01.01.2014     31.03.2014

101           B       01.01.2014     31.07.2014

101           C       01.08.2014     31.12.2014

102           D       01.01.2014     30.04.2014

103           E       01.01.2014     31.12.2014

104           F       01.01.2014     30.06.2014

104           G       01.07.2014     31.12.2014

Expected Output:

Employee Plan Start Date       End Date

101           A       01.01.2014     31.03.2014

101           B       01.01.2014     31.07.2014

101           C       01.08.2014     31.12.2014.

104           F       01.01.2014     30.06.2014

104           G      01.07.2014     31.12.2014

Is there any code which can be written to achieve this?

I tried doing this at report level using exception aggregation but its not working since we require Plan field in output and we r able to count correctly only when plan is removed from report output.

Thanks,

Tanvi

[ccc_ccc]

Hi Tanvi,

After few trails, we can bring desired output 90% , after that we need to write small macro to make 100% output.

Steps

Data in Infocube

Lets Design Report - Format

For First three object Result Rows "Always Suppress"

For Test 1 (in your case plan) keep “Always Display”

Create new formula

here formula 5

Here I used Number (w/o unit)1 dummy basic key figure should always one for each record in infocube.

Properties for formula 5

Aggregation Use standard aggregation

                         Ref char

                         Test 1 (plan)

Properties for different TABS

Properties For Number (w/o unit) (Basic key figure.

Create Condition

Properties for different TABS

First output

Need to write small macro to delete where test 1 cell contain “Result”

After Macro , output look like this.

Try this.

Regards,

Nanda

[ccc_ccc]

Hi Tanvi,

Please check above logic it works or not.

Thank you,

Nanda

[cornelia_lezoch]

Hi Tanvi,

you had this other thread about counting the plan per employee. Did you get this query to work?

Then use this one with a condition on the counter >1 as a prequery for this result query you want to build here.

Create a replacement path variable for the employee and read the employees from the count-query.

regards

Cornelia

[KodandaPani_KV]

Hi,

it is not work for the exception aggregation and will think anothre approach.

you can create the based on the Key figures on codtions- may be it will work for you.

Thanks,

Phani.