Print Page | Close Window

Merge Data / groups

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19556
Printed Date: 29 Apr 2024 at 12:11am


Topic: Merge Data / groups
Posted By: EricG
Subject: Merge Data / groups
Date 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!


Replies:
Posted By: DBlank
Date 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)

Posted By: EricG
Date 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

Posted By: lockwelle
Date 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.

Posted By: DBlank
Date 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

Posted By: EricG
Date 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.

Posted By: EricG
Date 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


Print Page | Close Window