Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Comparing data from 2 SQL queries Post Reply Post New Topic
Author Message
_Bash_
Newbie
Newbie


Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
Quote _Bash_ Replybullet 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 IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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 IP Logged
_Bash_
Newbie
Newbie


Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
Quote _Bash_ Replybullet 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 IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 23 Jan 2014 at 9:22am
you could use a command instead
which is basically like a stored procedure with some limitations.
IP IP Logged
_Bash_
Newbie
Newbie


Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
Quote _Bash_ Replybullet 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 IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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 IP Logged
_Bash_
Newbie
Newbie


Joined: 06 Jan 2014
Online Status: Offline
Posts: 7
Quote _Bash_ Replybullet Posted: 24 Jan 2014 at 5:45am
I've managed to do it with a command and some formulas.

Thanks for the help !
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.