Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Crystal Reports Chart Legend Label Aliasing

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
lcallah93
Asked:
lcallah93
  • 7
  • 4
  • 3
1 Solution
 
mlmccCommented:
I don't see an attached file

mlmcc
0
 
lcallah93Author Commented:
Oops - sorry :)
Legend-Aliasing.pdf
0
 
mlmccCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
lcallah93Author Commented:
Yes - you are correct about the aliasing - the second page is the report reading the SQL directly.
0
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
Can you provide the rpt with saved data?  I'd like to test with it.
0
 
lcallah93Author Commented:
Of course - I meant to do that yesterday but forgot.
unv-membership-churn-report-with-data.rp
0
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
lcallah93Author Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
mlmccCommented:
I haven't had a chance to build a report with a chart and try it in our portal or through infoview.

mlmcc
0
 
lcallah93Author Commented:
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
 
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
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
 
lcallah93Author Commented:
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
 
lcallah93Author Commented:
No solution given.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now