Hide columns in a crosstab but not that straight fwd... 1

[abhi900]

**************** SORRY FOR REPOST - I saw similar type of queries posted in this thread and hence my double post as I cannot delete from the other ****************** APOLOGIES


Hi,
basically my cross tab is based on YTD , FY > So please have a look at the crosstab picture along with this post.

1. I want to remove the ACTUAL column from YTD but want it in FY parent Column and likewise I want to display FORECAST only in FY column but not in YTD.

My YTD and FY are coming from a field Periodicity.
So how do I acheive this ?

2. I want to add a field SCI but that is not contained under field Periodicity > so when I try to put that in cross tab it shows up in YTD and FY columns but thats not how i want.
WOuld like to see as YTD | FY | SCI_TARGET | SCI STATUS. Both SCI are individual fields hence I cannot group it to YTD OR FY.

3. Can I format fields individually in the columns ? As from the list you can see I have some small nos. but some very large nos. I want to make those Large numbers to be in $xx.xM and some of the other numbers as % while leaving the rest as it is.

Can I achieve this sort of combinations.

hope it makes sense?
Please advise.

 

[CoSpringsGuy]

What version of Crystal?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[abhi900]

Hi I am using crystal 2008 version.

 

[CoSpringsGuy]

in crosstab manager .. how do you have your columns set up?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

1. You could just suppress each value include the column header for this column . That will leave you with some dead space but after we fix your number 3, it wont be as noticeable. right click. select format. select common tab. check suppress

2. This can be accomplished but I need to know more about SCI_TARGET | SCI STATUS

3. This would fix your millions format. From that you should get the ide how to fix your % issue as well. If you need additional help with that let me know
[ul]
[li]Right click any field in the target column[/li]
[li]select the column tab[/li]
[li]click X2 next to display screen to open formula workshop[/li]
[li]Enter teh following formula[/li]
[/ul]

if GridValueAt (CurrentRowIndex,CurrentColumnIndex ,CurrentSummaryIndex ) > 999999.99 then 
totext(GridValueAt (CurrentRowIndex,CurrentColumnIndex ,CurrentSummaryIndex )/1000000)&"M"
else
totext(GridValueAt (CurrentRowIndex,CurrentColumnIndex ,CurrentSummaryIndex ))

You should now be able to resize the columns so that the dead space from number 1 is not so evident




_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[abhi900]

1. You could just suppress each value include the column header for this column . That will leave you with some dead space but after we fix your number 3, it wont be as noticeable. right click. select format. select common tab. check suppress >>>>>

I have done this but what it does is make the values for this col as '0.00' and selecting suppress wont have any effect. I can still see the header and values as '0.00' but of course I cannot delete the "forecast" header under YTD as that will get deleted from FY region as well.


2. This can be accomplished but I need to know more about SCI_TARGET | SCI STATUS >>>>>

SCI_TARGET / SCI_STATUS > contains values for both YTD and FY. Its a universe measure (well this report is based of universe). However I need to show in the cross tab only for FY and not YTD AND alongside of YTD and FY.
But when importing it under crosstab as Summarized fields it will come up under YTD and FY.

My cross tab is layered as :-

ROW :- KPI_ID and then KPI NAME

Column :- Periodicity

Summarized values:- Actual / Forecast / Target / STATUS/ SCI Target / SCI Status.

I have measures under YTD class / FY Class / SCI Class but when trying to import the query the crystal throws the error "Crystal cannot handle multiple sql queries" hence I have to then use the generic Actual/forecast/target values and then use Periodicity Dimension to give me YTD and FY values.

 

[CoSpringsGuy]

1. for each field (including the header) you need suppressed, right click, select edit field, common tab, click X2 next to Supppess enter the following fomula

CurrentColumnIndex=0

save and close
If the correct column value is not suppressed, change the 0 to the correct column (note: 0 is the first column, 1 is the second column, etc.)

2. I still feel that this can be done. right click the header field in the last column. Select calculated member. Select insert column. A new column will appear at the end of your crosstab. right click one of those fields. select calculated members. select edit formula. Note: any formula you create to bring in your data must be in the same format (or reformatted) to the field type of the corresponding columns)
Maybe this will get you started. Unfortunately, I still do not have an understanding of how your fields relate to one another so I cant advice from there.

3. Did the suggestion re formatting the cell work?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[abhi900]

1. If the correct column value is not suppressed, change the 0 to the correct column (note: 0 is the first column, 1 is the second column, etc.)

That did work... >> but I am left with few of dead spaces and cannot shrink the individual cols as that has effects on the cols where I do need to show the values. And then there is a wide gap in other cols where the suprressed fields are in the middle of 2 fields.

2. I still feel that this can be done. right click the header field in the last column. Select calculated member. Select insert column. ******

The values and datatype are exactly the same but when i try to edit the formula to just {KPI.SCI_TARGET} it gets blank. I have to undo it to be able to select the col else I cannot even select the field. But overall does not work.

3. This would fix your millions format. From that you should get the ide how to fix your % issue as well.

This did work but then I have to use KPU Name and use that as a condition to display as % or $.

So yea.. All I need to work is on point 1 and 2.

I have attached the report itself to make more sense. C if you can open it and see the layout.

 

[abhi900]

Can anyone advise on the issue ?

 

[abhi900]

Can anyone or you "CoSpringsGuy" advise on the issue ?

 

[abhi900]

Hi,

This cross tab never worked the way I wanted it. It still leaves a lot of dead space between the col and there is no way you can minimize the dead cols.

So Stuck to normal design layout and calculating fields manually than using the crosstab.

So yea.. thanks to all and I still did learn quite a few stuff along the way. which was nice.

 

[CoSpringsGuy]

Sorry I haven't been able to get back to this in a while. Still plan to help if I can but I have been spending a lot of time at the hospital with my wife. Not great wifi here and impossible to do (for me anyway) from iPhone. Let you know if I come up with an alternate solution.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[abhi900]

No worries mate...!!

You did help me a lot. and I guess its a bit tough to get that feature as its not incorporatable in crystal.

Also issues with other fields to be displayed alongside with Periodicity.

So not worth spending sooo much time when its nto gonna happen and you do have another workaround.But yes we can try and work and see if we do come up with a solution. Might benefit others.

I wish you all the best, and a speedy recovery to your wife.

thanks a lot again. really appreciate it.

 

[CoSpringsGuy]

in the first sample crosstab you posted to this thread, the values in the entire YTD Forecast column and FY Actual column was 0. If that is always the case and if I read what you wrote correctly

I want to remove the ACTUAL column from YTD but want it in FY parent Column and likewise I want to display FORECAST only in FY column but not in YTD.

Go to crosstab expert, customize style and check the suppress empty column box. That will remove the columns completely if all values are zero.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[abhi900]

yes you are correct in a way but... values are still there.. and I used this formula under col where I want to supress for YTD and likewise for FY too :-
IN SUPRESS condition :- (CurrentColumnIndex=1) OR (CurrentColumnIndex=3)

So your above advise wont have any affect on this. Cause ultimately it does have value and we cannot pass the current col as "empty" to be supressed.

Hope you got what i mean ?
Also in terms of formatting I was using the following formula and it works fine for all the field values but jst wnt work for one value inside the col :-
if ({KPIPerformanceManagement_query.Periodicity} ="YTD"
and {KPIPerformanceManagement_query.KPI Name} = "Operating Profit after Tax (OPAT) ($M)"
then ("$" + ToText({KPIPerformanceManagement_query.Actual},1) + "M")
else if {KPIPerformanceManagement_query.KPI Name} = "STPIS Reliability Reward/Penalty ($M)")
then ("$" + ToText({KPIPerformanceManagement_query.Actual},1) + "M")

The code works for "OPAT" to show as $100.55M
but for STPIS it is = $12,345,678.00 M