Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Dynamically Add Fields? Post Reply Post New Topic
Author Message
slash85
Newbie
Newbie


Joined: 31 Mar 2009
Location: United Kingdom
Online Status: Offline
Posts: 6
Quote slash85 Replybullet Topic: Dynamically Add Fields?
     Posted: 16 Apr 2009 at 11:50am
Hi Guys,
 
Hopefully someone can point me in the right direct with this and confirm it's achievable?
 
I have an SQL stored procedure that returns names and their age eg:
 
Joe    Jon    James   Katy
10      21      14         9
 
 
I then use crystal reports an add the fields:
 
Joe
Jon
James
Katy
 
to the report  which is then run and displayed as:
 
Joe    Jon    James   Katy
10      21      14         9
 
 
Great works fine. But here comes the tricky bit the name field in the stored procedure is dynamic so today it can contain:
 
Joe
Jon
James
Katy
 
But tomorrow can contain
 
Joe
Jon
James
Katy
Dave
Paul
Fred
 
 
If i run the report again these additonal fields arent available.
 
How can i dynamically insert all fields into the report?  Is it even possible?
 
Many Thanks for any advice!!!
Slash.
 
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 17 Apr 2009 at 7:08am
no, not possible, Crystal reads from a template, and you can't change the template.  If the number of columns is known, you can work around it, but it is truly dynamic and an infinite number of columns might be displayed, you might want to try rewriting the stored proc and making the report a cross tabs report.
 
I haven't used a cross tabs, but if it works like a matrix report in Reporting Services, it might just fit the bill.
 
Alternatively, can the stored proc be rewritten to return rows of data with names in a column, instead of columns named after the name?
IP IP Logged
slash85
Newbie
Newbie


Joined: 31 Mar 2009
Location: United Kingdom
Online Status: Offline
Posts: 6
Quote slash85 Replybullet Posted: 17 Apr 2009 at 9:51am
Hi,
 
Thanks for the reply.
 
What is the work around?  I've spent a week writing a complex stored procedure and wouldn't like that to be a waste of time if possible.
 
Thanks,
Slash.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Apr 2009 at 12:19pm
I would say use a crosstab.
You can still use your stored proc but I doubt you really need to and is most likely a lot of extra processing for nothing (assuming it is converting rows into columns).
You can remove the grid lines to mimic the sdesign you were wanting.
It would dynamically grow as new names are added.
IP IP Logged
slash85
Newbie
Newbie


Joined: 31 Mar 2009
Location: United Kingdom
Online Status: Offline
Posts: 6
Quote slash85 Replybullet Posted: 17 Apr 2009 at 2:07pm
Hi,
 
Thanks for the response.
 
I've set about trying a cross tab but came across a background colour format issue.
 
eg
 
i have a background formula of:
 
if {@concat} like "*1" then crGreen else
if {@concat} like "*2" then crYellow else
if {@concat} like "*3" then crRed
 
 
my cross tab looks like:
 
                         Subject
Name           {max of concat}
 
 
But when previewed its not displaying colours correctly eg:
 
                          Art        English       Maths
Joe Blogs           ??1           ??2           ??3
 
 
Art result should be highlighted GREEN
English result should be highlighted YELLOW
Maths result should be highlighted RED
 
Instead every subject result is GREEN?  Has anyone seen this before?  Have any ideas to resolve ?
 
Thanks Again,
Slash
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 17 Apr 2009 at 2:37pm

I am not sure you can conditionally format cells in a crosstab like this. The highlight expert works well for this but is much more limited in the syntax you can use (no LIKE function).

I'll keep messing around with it but I am not hopeful that I can find anything that will work for you. Sorry.
IP IP Logged
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