Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Merge Data / groups Post Reply Post New Topic
Author Message
EricG
Newbie
Newbie


Joined: 17 May 2013
Online Status: Offline
Posts: 4
Quote EricG Replybullet Topic: Merge Data / groups
    Posted: 21 May 2013 at 2:58am
Hi,
 
I'm trying combine two sets of data that have some data in common ( French and English values). This is a bit complicated so I'll start by giving you some background info.
 
My data is coming from sql and the tables have the following info:
 
1.Ticket Table (this is what I'm reporting on)
- Region number field
 
 
2.Region Table (english and french records are two separate entities but the region number would stay the same)
-Region number field
-Region name
-language
 
In my report, I am grouping by a formula. What I need to do is set the group title to represent the french and english value ie. British-Columbia / Colombie-Britanique.
 
I am using a formula to perform the action but I only get " / British-Columbia".
 
here's the formula:
 
if ({Ticket_form.Region_Code} = {Region_Table.Region_Code}) then
((if {Ticket_form.Region_Code} = {Region_Table.Region_Code} and
{Region_Table.Language} = "fr_FR" then
{Region_Table.Region_Name}) & " / " &
(if {Ticket_form.Region_Code} = {Region_Table.Region_Code} and
{Region_Table.Language} = "en_US" then
{Region_Table.Region_Name}))
 
Thank you!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 May 2013 at 4:04am
can not really do it by that process. the two values are on two different rows which cannot be combined like that and be in a header and definately not for a grouping.
think about just grouping on the region field and then for display purposes use the maximum() and minimum() functions at the group level for the text you want.
Other options, use a stored proc to combine your records into one row outside of Crystal and then use that as your source.
Use a subreport for displaying (still does not help with grouping).
Display in a footer instead of a header (still does not help with grouping)


Edited by DBlank - 21 May 2013 at 4:05am
IP IP Logged
EricG
Newbie
Newbie


Joined: 17 May 2013
Online Status: Offline
Posts: 4
Quote EricG Replybullet Posted: 21 May 2013 at 4:33am
Thank you for your answer.
 
Using a store procedure would definitely be the easyest way to go but it's unfortunately not possible.
 
I'm pretty new at crystal and not sure how I would apply the min max function in there.
 
thanks again
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 May 2013 at 4:40am
if a stored proc is out, what you might try is to build a table that has the french and english values on the same line...a reference table (id, englishValue, frenchValue) or all ids...then you could link the table in the report and group on the id.

It's not a solution I like, overly much, but it would be a work around that would not involve changing any data in the existing tables.

HTH

DBlank's solutions were completely valid...and as he knows, my first option is always to build a stored proc.

Edited by lockwelle - 21 May 2013 at 4:40am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 May 2013 at 5:01am
for the min or max functions you will need to create 2 formulas to make a blank string or the version string.
//Fr_version
if {Region_Table.Language} = "fr_FR" then
{Region_Table.Region_Name}
 
//En_Version
if {Region_Table.Language} = "en_US" then
{Region_Table.Region_Name}
 
now use the MAximum function on each of these formula fields adn set them to be at the group footer
 
MAXIMUM(@FR_version,groupfieldhere)
 
 
MAXIMUM(@En_version,groupfieldhere)
 
these results can be used in the group header
IP IP Logged
EricG
Newbie
Newbie


Joined: 17 May 2013
Online Status: Offline
Posts: 4
Quote EricG Replybullet Posted: 21 May 2013 at 8:24am
Amazing!
 
The last solution worked perfectly.
 
I understand that the best way would have been to have "clean" data coming in but in this case, it wasn't possible or at least, not in my power.
 
Thanks a lot for all your help, much appreciated.
IP IP Logged
EricG
Newbie
Newbie


Joined: 17 May 2013
Online Status: Offline
Posts: 4
Quote EricG Replybullet Posted: 21 May 2013 at 8:45am
Also, I ment to mention that I added another formula field that concatenates the two maximum values into one. I can then place that field in my group header and hide the actual group title header.
 
Works like a charm
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.