| Author |
Message |
_Bash_
Newbie
Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
|
   Topic: Comparing data from 2 SQL queries
Posted: 23 Jan 2014 at 3:10am |
|
Hi,
I'm getting better but I'm still quite new to Crystal Reports (using 2008). Here's what I need to do :
I need to make a report that compares 2 lists of items from some projects. Could be a list of items from project version A to B or a list or items from 2 different projects. I then need to show the differences between the first and second list (this item has been added/removed, that item's quantity has gone up/down by x). The user has to enter the informations concerning both projects to compare.
I was wondering if I can do that using only Crystal Reports or if I need to create a stored procedure or something like that.
Any help would be welcomed.
Thanks !
|
 IP Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 23 Jan 2014 at 6:03am |
|
I think that the issues would revolve around how the 2 lists are joined and if duplication is likely. If there is always a 1 to 1 correspondence between the 2 list, you can probably do it all in Crystal with a full join (that would give you added/deleted items as well as the changes).
I would do it in stored proc, but I prefer to all my reporting in a stored proc.
HTH
|
|
IP Logged |
|
_Bash_
Newbie
Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
|
Posted: 23 Jan 2014 at 6:17am |
|
Well all the data from both queries are from the same tables and same fields. It's the same query with 2 different sets of parameters and I want to compare both results and display the differences.
The main table looks like this:
ListNb
RevNb
ItemNb
Qty
The user enters both List numbers and rev numbers he wants. This will generate 2 sets of item numbers and I want to compare the difference between those two lists.
Right now I've got 2 sets of the same tables in my report. Both main tables are linked via the "ItemNb" field (Full Outer Join) but this only displays items that are the same between the 2 lists...and I need the ones that are different.
I'd probably prefer doing a stored proc as well since I've done some before. Unfortunately, I don't have access to this database and getting it is complicated.
|
|
IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 23 Jan 2014 at 9:22am |
|
you could use a command instead
which is basically like a stored procedure with some limitations.
|
|
IP Logged |
|
_Bash_
Newbie
Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
|
Posted: 24 Jan 2014 at 3:13am |
|
Just spent a couple of hours trying to use a command and I gotta say it really doesn't seem to be made for complex SQL queries (especially one with 6 user input parameters). Everytime I try to enter a string input with a space character it throws an error 102 so I have to use ''. Plus I can't use the parameters I've already created on my report I had to create them all over again. Also, very simple queries seem to return no data at all.
Am I doing something wrong here ?
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
Posted: 24 Jan 2014 at 4:53am |
|
Commands are tricky...I think that they were developed for dynamic parameters, and as such are a bit more limited, though I have seen plenty of reports that use them as the main source of data.
With that said, since the data is from the same table, I would add the table twice into the same report. Then in the filter criteria you should be able to apply your parameters and your logic so that the correct rows from each table (even though they are the same one) gets selected. Then you just need to link the 2 tables.
Again, it should work, no guarantees.
Basically, I would do the same think in a stored proc, where you can have complex SQL, temp tables, and the freedom to do as many passes through the data as you want.
HTH
|
|
IP Logged |
|
_Bash_
Newbie
Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
|
Posted: 24 Jan 2014 at 5:45am |
|
I've managed to do it with a command and some formulas.
Thanks for the help !
|
|
IP Logged |
|
|
|
