Solved

Crystal Reports Chart Legend Label Aliasing

Posted on 2014-10-14
14
175 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
  • 7
  • 4
  • 3
14 Comments
 
LVL 100

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 100

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
 

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 22

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 22

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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 22

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 100

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 22

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now