?
Solved

Crystal Reports Chart Legend Label Aliasing

Posted on 2014-10-14
14
Medium Priority
?
216 Views
Last Modified: 2014-12-10
Hello experts -

I am having problems with Crystal Reports displaying the label aliasing correctly on charts.  It works when I refresh and/or run it directly in Crystal Reports, but when I run it through Business Objects or Visual Cut, the labels default to the original text.

If you look at the attached report, the first page contains the aliases that should be displayed.  The second page shows what happens when it runs through the scheduler or Visual Cut.  It almost seems like the label aliasing just does not work unless it's published manually.

It is from a SQL code and at first I thought maybe it had to do with a temporary table, but there are two other reports that do not contain temporary tables and the same thing is happening.  I feel like it is a Crystal problem but I don't know how to solve it.  We have Crystal 2008.

Any help is appreciated.

Thanks, the code is below.

Lisa

USE VCUdm

--drop table ##temp_table_churn

begin try drop table ##temp_table_churn end try
begin catch end catch

DECLARE @year int

SET @year = (select year(end_date) from VCU.dbo.fy_dates)

select

Campus,
case 
	when month(JoinDate) in ('7','8','9','10','11','12') then year(JoinDate)+1
	when month(JoinDate) in ('1','2','3','4','5','6') then year(JoinDate)
end as JoinYear,
--[AA Classification],

--case when month(JoinDate)=month(getdate()) then 1 else 0 end as New,
--case when month(ExpireDate)=month(getdate()) then 1 else 0 end as Expired,
--*
sum(case when month(JoinDate)=7 then 1 else 0 end) as JulyNew,
sum(case when month(JoinDate)=8 then 1 else 0 end) as AugNew,
sum(case when month(JoinDate)=9 then 1 else 0 end) as SepNew,
sum(case when month(JoinDate)=10 then 1 else 0 end) as OctNew,
sum(case when month(JoinDate)=11 then 1 else 0 end) as NovNew,
sum(case when month(JoinDate)=12 then 1 else 0 end) as DecNew,
sum(case when month(JoinDate)=1 then 1 else 0 end) as JanNew,
sum(case when month(JoinDate)=2 then 1 else 0 end) as FebNew,
sum(case when month(JoinDate)=3 then 1 else 0 end) as MarNew,
sum(case when month(JoinDate)=4 then 1 else 0 end) as AprNew,
sum(case when month(JoinDate)=5 then 1 else 0 end) as MayNew,
sum(case when month(JoinDate)=6 then 1 else 0 end) as JuneNew,

sum(case when Status='Renew' and month(JoinDate)=7 then 1 else 0 end) as JulyRenew,
sum(case when Status='Renew' and month(JoinDate)=8 then 1 else 0 end) as AugRenew,
sum(case when Status='Renew' and month(JoinDate)=9 then 1 else 0 end) as SepRenew,
sum(case when Status='Renew' and month(JoinDate)=10 then 1 else 0 end) as OctRenew,
sum(case when Status='Renew' and month(JoinDate)=11 then 1 else 0 end) as NovRenew,
sum(case when Status='Renew' and month(JoinDate)=12 then 1 else 0 end) as DecRenew,
sum(case when Status='Renew' and month(JoinDate)=1 then 1 else 0 end) as JanRenew,
sum(case when Status='Renew' and month(JoinDate)=2 then 1 else 0 end) as FebRenew,
sum(case when Status='Renew' and month(JoinDate)=3 then 1 else 0 end) as MarRenew,
sum(case when Status='Renew' and month(JoinDate)=4 then 1 else 0 end) as AprRenew,
sum(case when Status='Renew' and month(JoinDate)=5 then 1 else 0 end) as MayRenew,
sum(case when Status='Renew' and month(JoinDate)=6 then 1 else 0 end) as JuneRenew

into ##temp_table_churn

from membership_VIEW_alumni_campus_degree

group by

Campus,
case 
	when month(JoinDate) in ('7','8','9','10','11','12') then year(JoinDate)+1
	when month(JoinDate) in ('1','2','3','4','5','6') then year(JoinDate)
end
--,[AA Classification]

select 

year(getdate()) as Group1,
temp.Campus,
JoinYear as Year,
--temp.[AA Classification],
temp.JulyNew,
temp.JulyRenew,
seq.JulyExpire,
case when temp.JulyNew=0 then NULL else convert(varchar(5),100*(temp.JulyNew-seq.JulyExpire)/temp.JulyNew)+'%' end as JulyPer,
case when temp.JulyNew=0 then NULL else (temp.JulyNew-seq.JulyExpire) end as JulyNet,
temp.AugNew,
temp.AugRenew,
seq.AugExpire,
case when temp.AugNew=0 then NULL else convert(varchar(5),100*(temp.AugNew-seq.AugExpire)/temp.AugNew)+'%' end as AugPer,
case when temp.AugNew=0 then NULL else (temp.AugNew-seq.AugExpire) end as AugNet,
temp.SepNew,
temp.SepRenew,
seq.SepExpire,
case when temp.SepNew=0 then NULL else convert(varchar(5),100*(temp.SepNew-seq.SepExpire)/temp.SepNew)+'%' end as SepPer,
case when temp.SepNew=0 then NULL else (temp.SepNew-seq.SepExpire) end as SepNet,
temp.OctNew,
temp.OctRenew,
seq.OctExpire,
case when temp.OctNew=0 then NULL else convert(varchar(5),100*(temp.OctNew-seq.OctExpire)/temp.OctNew)+'%' end as OctPer,
case when temp.OctNew=0 then NULL else (temp.OctNew-seq.OctExpire) end as OctNet,
temp.NovNew,
temp.NovRenew,
seq.NovExpire,
case when temp.NovNew=0 then NULL else convert(varchar(5),100*(temp.NovNew-seq.NovExpire)/temp.NovNew)+'%' end as NovPer,
case when temp.NovNew=0 then NULL else (temp.NovNew-seq.NovExpire) end as NovNet,
temp.DecNew,
temp.DecRenew,
seq.DecExpire,
case when temp.DecNew=0 then NULL else convert(varchar(5),100*(temp.DecNew-seq.DecExpire)/temp.DecNew)+'%' end as DecPer,
case when temp.DecNew=0 then NULL else (temp.DecNew-seq.DecExpire) end as DecNet,
temp.JanNew,
temp.JanRenew,
seq.JanExpire,
case when temp.JanNew=0 then NULL else convert(varchar(5),100*(temp.JanNew-seq.JanExpire)/temp.JanNew)+'%' end as JanPer,
case when temp.JanNew=0 then NULL else (temp.JanNew-seq.JanExpire) end as JanNet,
temp.FebNew,
temp.FebRenew,
seq.FebExpire,
case when temp.FebNew=0 then NULL else convert(varchar(5),100*(temp.FebNew-seq.FebExpire)/temp.FebNew)+'%' end as FebPer,
case when temp.FebNew=0 then NULL else (temp.FebNew-seq.FebExpire) end as FebNet,
temp.MarNew,
temp.MarRenew,
seq.MarExpire,
case when temp.MarNew=0 then NULL else convert(varchar(5),100*(temp.MarNew-seq.MarExpire)/temp.MarNew)+'%' end as MarPer,
case when temp.MarNew=0 then NULL else (temp.MarNew-seq.MarExpire) end as MarNet,
temp.AprNew,
temp.AprRenew,
seq.AprExpire,
case when temp.AprNew=0 then NULL else convert(varchar(5),100*(temp.AprNew-seq.AprExpire)/temp.AprNew)+'%' end as AprPer,
case when temp.AprNew=0 then NULL else (temp.AprNew-seq.AprExpire) end as AprNet,
temp.MayNew,
temp.MayRenew,
seq.MayExpire,
case when temp.MayNew=0 then NULL else convert(varchar(5),100*(temp.MayNew-seq.MayExpire)/temp.MayNew)+'%' end as MayPer,
case when temp.MayNew=0 then NULL else (temp.MayNew-seq.MayExpire) end as MayNet,
temp.JuneNew,
temp.JuneRenew,
seq.JuneExpire,
case when temp.JuneNew=0 then NULL else convert(varchar(5),100*(temp.JuneNew-seq.JuneExpire)/temp.JuneNew)+'%' end as JunePer,
case when temp.JuneNew=0 then NULL else (temp.JuneNew-seq.JuneExpire) end as JuneNet

from ##temp_table_churn temp

join

(select

Campus,
case 
	when month(ExpireDate) in ('7','8','9','10','11','12') then year(ExpireDate)+1
	when month(ExpireDate) in ('1','2','3','4','5','6') then year(ExpireDate)
end as ExpireYear,
--[AA Classification],

--case when month(ExpireDate)=month(getdate()) then 1 else 0 end as Expire,
--case when month(ExpireDate)=month(getdate()) then 1 else 0 end as Expired,
--*
sum(case when month(ExpireDate)=7 then 1 else 0 end) as JulyExpire,
sum(case when month(ExpireDate)=8 then 1 else 0 end) as AugExpire,
sum(case when month(ExpireDate)=9 then 1 else 0 end) as SepExpire,
sum(case when month(ExpireDate)=10 then 1 else 0 end) as OctExpire,
sum(case when month(ExpireDate)=11 then 1 else 0 end) as NovExpire,
sum(case when month(ExpireDate)=12 then 1 else 0 end) as DecExpire,
sum(case when month(ExpireDate)=1 then 1 else 0 end) as JanExpire,
sum(case when month(ExpireDate)=2 then 1 else 0 end) as FebExpire,
sum(case when month(ExpireDate)=3 then 1 else 0 end) as MarExpire,
sum(case when month(ExpireDate)=4 then 1 else 0 end) as AprExpire,
sum(case when month(ExpireDate)=5 then 1 else 0 end) as MayExpire,
sum(case when month(ExpireDate)=6 then 1 else 0 end) as JuneExpire

from membership_VIEW_alumni_campus_degree

group by

Campus,
case 
	when month(ExpireDate) in ('7','8','9','10','11','12') then year(ExpireDate)+1
	when month(ExpireDate) in ('1','2','3','4','5','6') then year(ExpireDate)
end
--,[[AA Classification]
) seq on temp.JoinYear=seq.ExpireYear and temp.Campus=seq.Campus --and temp.[AA Classification]=seq.[AA Classification]

where temp.JoinYear >= @year-4 and temp.JoinYear <= @year

order by Year

Open in new window

0
Comment
Question by:lcallah93
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 40379893
I don't see an attached file

mlmcc
0
 

Author Comment

by:lcallah93
ID: 40380009
Oops - sorry :)
Legend-Aliasing.pdf
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40380072
I don't know.  I will have to build a report and publish it in our viewer to see if I get the same result.

I assume what the second page shows is what you get in Crystal before aliasing the legend.

mlmcc
0
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

 

Author Comment

by:lcallah93
ID: 40380939
Yes - you are correct about the aliasing - the second page is the report reading the SQL directly.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 40382120
Can you provide the rpt with saved data?  I'd like to test with it.
0
 

Author Comment

by:lcallah93
ID: 40382143
Of course - I meant to do that yesterday but forgot.
unv-membership-churn-report-with-data.rp
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 40385149
Shows perfectly fine in Visual CUT:  http://screencast.com/t/b0eH86xp

Perhaps the version you are opening in Visual CUT is not the same version you provided above.  Or perhaps the Legend settings were applied to the Preview so they get lost when you refresh.  

What happens when you open the rpt with saved data and elect NOT to refresh?
0
 

Author Comment

by:lcallah93
ID: 40385190
I have only ever applied the legend settings on the Preview pane.  How do you apply then in design mode?  When I am in design mode I don't see the actual legend text but just a template of the chart.
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 40385521
mlmcc, did something change in Expects-Exchange?  I no longer receive emails when someone posts to a thread I participated in.

Lisa, I didn't look at the problem closely (editing is indeed available only in Preview) because Visual CUT displayed everything fine for me. Please test opening the rpt with saved data without refreshing and confirm you see the same behavior.  If so, your next step is to try to modify your command so it returns summaries and use formulas to map those summaries to desired names.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40385744
I haven't had a chance to build a report with a chart and try it in our portal or through infoview.

mlmcc
0
 

Author Comment

by:lcallah93
ID: 40394924
I did what you suggested - opened the report with saved data in Visual Cut - and everything rendered correctly with the legend aliases.  

Your next suggestion was to rename the fields in the SQL command but I am not sure that would work (?) since Crystal adds the words 'Sum of Command.' to the entry.

Am I missing something?

-- Lisa
0
 
LVL 23

Expert Comment

by:Ido Millet
ID: 40396604
My suggestion above was to "modify your command so it returns summaries and use formulas to map those summaries to desired names.".  This means that you set the chart to NOT sum the numbers. Hence Crystal will not add "Sum of..."
0
 

Accepted Solution

by:
lcallah93 earned 0 total points
ID: 40482883
I do not see a solution for this issue.  The workaround suggested does not work as Crystal itself put in the 'sum of' text in the legend, it has nothing to do with the SQL code (that I can see).
0
 

Author Closing Comment

by:lcallah93
ID: 40490887
No solution given.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question