Display MTH.YTD and FY values at same grp level.. 1

[abhi900]

Hi,

I have 2 groups :-
GH1 - KPI ID (using only selected KPI ID) & GH2 - KPI Name (Only those names that are assigned to KPI ID)
Need to display MTH / YTD and FY Actual/Target values for each KPI ID based on KPI NAME(GH2) on same line.
eg:- KPI NAME MTH_Actual MTH_Target YTD_Actual YTD_Target FY_Actual FY_Target
X1 xx xx xx xx xx xx
X2 xx xx xx xx xx xx
We will worry only about Actuals for MTH / YTD and FY...
Thus I have 3 formulas -
@YTD Actual -
if {KPI.Period} ="YTD"
then if({KPI.KPI Name} = "X1")
then (ToText ({KPI.Actual},1) & "%")
else if {KPI.KPI Name} = "X2"
then ("$" + ToText({KPI.Actual},1) + "M")

This works Fine and I can see the Values across the row for each KPI Names and the format i choose in Formula.

@MTH Actual -
if {KPI.Period} ="MTH"
then if({KPI.KPI Name} = "X1")
then (ToText ({KPI.Actual},1) & "%")
else if {KPI.KPI Name} = "X2"
then ("$" + ToText({KPI.Actual},1) + "M")

This does not lists me values against each KPI NAMES, it returns blank(NULL).

@FYActual - totext(Sum ({KPI.Forecast}, {KPI.KPI Name}))

This returns correct values as well. However There is a difference between @YTD Actual and this as I have to implement format settings in DISPLAY Properties of the Field and not in the formula. Cause if I do that in formula then it returns Blank (NULL).

All The Formulas and Fields Are Placed In GF2.
Do I need to reset the values for MTH or FY to get the values ? if so then any guidelines on how, I know they needs to be placed as hidden fields on same level.

Just want to display MTH values how can I acheive this ?

cheers
A.B

 

[abhi900]

I am using BO XI R3 SP5, Crystal Reports 2008.

 

[IanWaterman]

@MTH Actual is very confusing and I think that is why you are getting nulls

if {KPI.Period} ="MTH" then
if({KPI.KPI Name} = "X1")
then (ToText ({KPI.Actual},1) & "%")
else
if {KPI.KPI Name} = "X2"
then ("$" + ToText({KPI.Actual},1) + "M")

You should wrap associated if ..then..else in brackets

To which IF is the else assocaiated

Also you should really have another two else's otherwise you will get a Null

What happens if {KPI.Period} <>"MTH"
What happens if {KPI.KPI Name} <> "X2"

Ian

 

[abhi900]

hi Ian,
if Period is <> MTH then its null for sure and likewise for KPIName as in the groups I have specified list of values to be choosen and discarded all other values.

So my data is restricted to what I have selected in the groups.
If there are no values against anyone of them for FY / YTD / MTH then be it, it can show null.

So by above eg. do you mean I can solve the null value from appearing (even though there are values against it in the DB) by wrapping the If .. then .. else in brackets?

 

[IanWaterman]

I thought you did not want nulls?

You need to add brackets so that its clear to which If the else belongs.

Ian

 

[abhi900]

Hi Ian,
Irrespective of the brackets or use of just KPI ID and not NAMES and vice versa I cannot see the values against MTH.

If i place the same @formula Field in Details then YES I can see the values. But thats not the place I want.

I tried using Case Statement too but of little help.

When I insert Field- PEriod onto GH1 I can see only YTD coming against it and hence my MTH values are NULL. So do I need to reset the values or something like that ?

The report is working of Universe hence it wont save data for you to look at if I did attach the report to this post.

regards
A.B.

 

[abhi900]

HI,
I can say that Basically if I just have KPI ID as GH1 and when I insert @Period = "MTH" it returns FALSE but for "YTD" its TRUE.

Why would it return me FALSE, where the only condition I have for Record Selection is {KPI.Calendar Month (YYYYMM)} = "201303".

I want to run the report on a Calendar month Parameter.

 

[IanWaterman]

the group header returns the contents of the first record, unless you are performin a summary.

Look at the same formula in the first detail line and you will see why.

Ian

 

[abhi900]

Hi Ian

I get what you mean but then how do I resolve this ?

I do want to view MTH YTD and FY values side by side and in Groups than viewing it in DETAIL section.

Thus how can I satisfy those individual conditions for @MTH @YTD and @FY formulas and then make it work?

advise please....

cheers AB.

 

[IanWaterman]

To bring data into Group headers or footers you must use summaries, like Max, Min, Sum etc
Or you can use variables to capture data at a certain point and then display the variable in footer, NB variables can only be displayed in a footer.

Ian

 

[abhi900]

yes you are correct in terms of using only Summaries and that does work for my FY values.

But I cannot do a summary on MTH values as for some reason it gives me only MAX value in the field across each Group and SUM or AVG actually sums everything and likewise for Average.
So you think I can get around this by using summaries and display MTH value correctly ?

I doubt I need to create variables in this instance to capture data at a certain point. So is summaries the only way to go ?

 

[IanWaterman]

If you want values use a formula like

@MTH Value
if {KPI.Period} ="MTH" then {valuefield} else 0

You can then sum and average this type of formula

Ian

 

[abhi900]

Thanks for all your help mate.

All working perfectly... Probably I will have few queries with respect to Graphs but will post it here and wait for it to get commented on.

Thanks again.