Comparing data from 2 SQL queries
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=20384
Printed Date: 01 May 2024 at 10:55am
Topic: Comparing data from 2 SQL queries
Posted By: _Bash_
Subject: Comparing data from 2 SQL queries
Date 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 !
|
Replies:
Posted By: lockwelle
Date 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
|
Posted By: _Bash_
Date 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.
|
Posted By: kostya1122
Date Posted: 23 Jan 2014 at 9:22am
you could use a command instead
which is basically like a stored procedure with some limitations.
|
Posted By: _Bash_
Date 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 ?
|
Posted By: lockwelle
Date 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
|
Posted By: _Bash_
Date Posted: 24 Jan 2014 at 5:45am
I've managed to do it with a command and some formulas.
Thanks for the help !
|
|