Solved

Crystal Reports Chart Legend Label Aliasing

Posted on 2014-10-14
14
205 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
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 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