| Author |
Message |
chudok
Groupie
Joined: 18 May 2010
Online Status: Offline
Posts: 70
|
   Topic: SQL table field dissappears/ when adding new table
Posted: 27 Nov 2012 at 3:14am |
I have a table from SQL that shows blank (no fields) when I add another table to the report. (whenI only grab this table in report I see the field) This table only has 1 field. This table is not linked to any other table in the database (is that my problem?, I would not think so when I can take tables from different databases and link them in Crystal)
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 27 Nov 2012 at 5:21am |
|
Are you linking the two tables together? If so, what kind of join do you have?
From what you have said, I am not sure that the tables should be linked at all (you will get a warning from Crystal, but that may be okay).
I hope this helps.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 28 Nov 2012 at 9:07am |
are you talking about displaying the field in the report preview or in design mode? If it is in preview, if the first value of the table is blank or null, it will show as blank. Since it is not linked, CR will never advance the table to the next row...it will just continue to read the first row...it's just the way that reporting systems seem to work. If it is in design time, then there is an issue, though I have never encountered this, and so don't really know how to respond/trouble shoot it. HTH
|
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 28 Nov 2012 at 9:19am |
|
Never ran into this unless I forget to update all tables along with database connection in set db loc. The database connection didnt change the corresponding tables.
Quite honestly, I would avoid CRYSTAL to build your joins, make this a command, create your sql command and work out a way to get output first, then migrate to Crystal.
Troubleshooting within Crystal can go nowhere and make you more confused.
Cut out the middle man. Build your select command, with your table joins, run in SQL MGMT STUDIO, verify you get data and populate in a command.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 28 Nov 2012 at 9:39am |
|
Actually a lot times I go the other way. Throw in the tables I need for the report (manually do the joins), then take the SQL and put it into a command. Unfortunately I have one data source (it is an odd beast) that runs slower as a command (by a lot) then by using tables.
|
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 28 Nov 2012 at 9:45am |
|
rolling up sleeves, Lock, tie breaker, what's your recommendation. Otherwise I still like my way better, but I really am not a crystal fan, with the linking methods it uses...
so I am biased, I am a hacker by nature. I like to see what I am making
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Nov 2012 at 11:01am |
my two cents...
If you add two tables to a report and do not join them I believe it will cross join the tables and give you every permutation of the row combinations from the two tables.
If you do join them but do not enforce the join your will results will vary based on if you use fields from both tables or not.
If you only use fields from one table or the other it will pull all rows from either table. Once you use fields from both tables it will enforce the join and your data set will change accordingly. (This often comes up when a designer says that rows just started diappearing from the report preview after they added a new field.)
If you do enforce the join the results will be based on the enforcement type. (I have never really found a reason to add a table and not enforce the join.)
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 29 Nov 2012 at 4:48am |
my preference, you all should know by now: make a stored proc and then just display the results of the stored proc...since that is how all my reports are created otherwise, I think I use a combination...joining to the tables for the most part and using commands to populate parameter lists...but again, I didn't write too many reports without store procs. as to DBlank's comments, I'm not sure...I frequently would create a report with 2 tables, 1 for details and 1 for what the parameters were. I wouldn't link them (since there is no correspondence/dependencies between them) and I don't think that I got a cross-join...though it was only 1 record in my second table. In the other reporting system that I work in, it specifies that it only print the 'current' record of a table that is not primary...you can change the row, but I don't know how, nor do I need to...as I am only displaying the values of the parameters. I've been a bit confused about enforcing joins, so I will defer to DBlank's opinion...again, I tend bring out of the stored proc just 1 table, so there is no join to enforce. On a side note, why would you pull in a table, join to it, and then not use the result? The only thought is that it would connect to a third table that you are using on the report, but wouldn't that be a defacto use of the second table? Well there's my two cents. Probably doesn't help much at all, since it doesn't really vote for any option, but gives a third.
|
|
IP Logged |
|
comatt1
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 337
|
Posted: 29 Nov 2012 at 4:56am |
|
and customers wonder why things take so long to develop, ask for a tie break and get two more enhancement or hybrid ideas... or flat out new ones...
This is why our meetings last too damn long :)... Gotta love knowing that no matter how right you think you are, there is always, at the least, a different option.
--- Love this forum sometimes.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 29 Nov 2012 at 5:08am |
well, my company gives the report writers the freedom to write stored procs. I know that not all companies are so, 'nice' shall we say. Nor does everyone know how to write a stored proc... Luckily, CR let's all different forms work together. In all honesty, I never thought of using comatt1's process. Given some of the policies the some companies impose, it is a perfectly valid path to development, and probably gives more control over the data. Again I've never thought of it. Along the same lines, I have said in the past, that I believe that purpose of the Command object, as developed/envisioned by the CR developers was to populate the parameters. I say this from past posts where people drag in tables AND use commands to create tables to link to AND they use parameters in the command object...and CR asks for the same parameter value to be input by the user multiple times. That is not to say that it isn't a valid way to write reports as that is how comatt1 writes them, and does so successfully...and since I have never read anything about what the command object was designed/intented to do, this is purely my opinion, and I have been wrong before...alot. Given that I might have a stored proc that is thousands of lines long to accommodate the design request, I doubt that the command object could handle it, but I equally doubt that joining tables together would work any better. So yeah, clear as mud.
|
|
IP Logged |
|
|
|
