AdHoc report Help

[Anonymous]

Hi All,

I need to create some Ad-Hoc kind of report.In my scenario I will be having list box

i)PLAN/Budget

ii)PLAN

iii)BUDGET

So when User clicks PLAN/BUDGET I need to show the first 6 months of PLAN & Last 6 Months of Budget in pivot table.

If Budget is not available I need to show 12 months of PLAN.

To say clearly in some of cases I may not have Budget value at all. So in that scenario I need to show all 12 months of PLAN Values.

I have enclosed sample data set

Kindly help me on this.

-Jai

[balar025]

Hi Ij,

This scenario you need to handle in expression/variable from you are picking formula it self.because To Create adhoc , might be you are using macro. and macro doesn't allow to calculate and check the values.

Regards,

Ravi

[its_anandrjs]

Hi,

Try to load the inline table for your values

i)PLAN/Budget

ii)PLAN

iii)BUDGET

and then link this to your field for display the data according to this selection.

Regards

Anand

[Anonymous]

Hi Anand,

I am already using inline table only. My question is when there is no value present  beyond June how to show all 12 months data. Kindly read my question carefully.

-Jai

[its_anandrjs]

Please share some sample data to have a look or you can use joins here to show rest of the month budget data.

Regards

Anand

[Anonymous]

Hi,

I have enclosed the sample data in the original post. However I am attaching again

-Jai

[MayilVahanan]

Hi

Please clarify below questions

1. Is same month contains Plan / Budget?

2. If the user select Jun 2017, Is chart display from Jul 2016 to Jun 2017 ?? In that case, first 6 month (i.e. Jul 2016 to Dec 2016) Plan and remaining month is Budget?

3. What is the expected output from ur sample data?

[Anonymous]

Hi Mayil,

Find my comments below

1. Is same month contains Plan / Budget?-  Yes

2. If the user select Jun 2017, Is chart display from Jul 2016 to Jun 2017 ?? In that case, first 6 month (i.e. Jul 2016 to Dec 2016) Plan and remaining month is Budget?

-No by default when user selects some Location code it should show First 6 Months of PLAN and Next 6 Months of Budget

3. What is the expected output from ur sample data?

i) When user selects 072 it should show PLAN/UPDATE data together

ii) When user selects 073 it should all 12 months of PLAN,as it dont have the BUDGET data

Let me know if I am missing still

-Jai

[MayilVahanan]

Hi

Try like this

=If(Month(Period)*1 <= 6, Sum({<Type ={'PLAN'}>}Value), If(Sum({<Type ={'BUDGET'}>}Value) = 0, Sum({<Type ={'PLAN'}>}Value), Sum({<Type ={'BUDGET'}>}Value)))

[Anonymous]

Hi mayilvahanan,

  Your solution fixed the issue,however in default scenario (Both PLAN & BUDGET exists) it showing both together.

Here I need to show first 6 Months of PLAN & next 6 months of BUDGET

Kindly let me know

-Jai