How to create a report for returns

[Venimeux]

I've been given a project to develop. First of all, I don't know SQL, and I'm fairly new at CR. Using 8.5 on the desktop, and exporting it to 7.0 for users to run on an internal website.

The project takes two databases, one that contains transactions, the other that contains product information. The two are linked by itemnumber.

I need to create a report that lists transactions which were returned to the store within a user specified period of time.

Input from the user will be the item number with a second parameter of time period in spreadsheet format.

Example:
ItemNumber ReturnPeriod(days)
000000001 7
000000002 30

My current ideas are to put the spreadsheet provided into an access db, then reading that in as a parameter. The report would use a subreport to determine if the item was returned. That's as far as I've gotten and I've already determined that won't work because 7.0 doesn't like subreports.

Thank you for any guidance you can give me.

 

[Venimeux]

Ok, I've made a little headway with this. It was suggested that I add the db twice to the report (giving the second an alias). I linked the account number and the Item numbers.

Then I did a selection for the following:

{Tran.EffDate} >= Date(2004,01,01) and
{Tran2.EffDate} <= {Tran.EffDate}+7 and
{Tran2.ItemNo} in "025000000" to "025999999" and
{Tran2.Code} = "R" //R means returns

Again, the basic idea of the report is to generate a list of items that were returned by clients within a 7 day period after buying them.

Client1 buys a broom. He returns it two days later. Should be in report. Client2 buys a broom and returns it 8 days later. Should not be in the report.

Thanks again for any ideas or assistance you can give to me.

 

[-cush-]

Since {Tran2.Code} is limiting to "R" for returns, it looks like you need limit {Tran.Code} to "S" or whatever means sold.

 

[Venimeux]

I added the line and observed no change:

{Tran.EffDate} >= Date(2004,01,01) and
{Tran2.EffDate} <= {Tran.EffDate}+7 and
{Tran2.ItemNo} in "025000000" to "025999999" and
{Tran2.Code} = "R" //R means returns
{Tran.Code} = "S" //S means sold

I'm positive that I have data within those periods that matches the criteria I'm looking for.

Any other ideas?

 

[synapsevampire]

Check the Database->LShow SQL Query to see what is being passed.

WHat you posted couldn't work as there isn't and AND between:

{Tran2.Code} = "R" //R means returns
{Tran.Code} = "S" //S means sold

Please paste in the exact code.

If you're familiar with Access, then linking the spreadsheet into Access and creating a Parameterized Query in MS Access as the datasource for the report makes sense to me.

Whatever tool gets you there is a good tool.

-k

 

[Venimeux]

To start out, I simplified the code that I was working with. The following is the SQL select code, but I'll explain what we're looking at:

AC9 is a 9 digit account code.
EffDate is the date of the trade.
BuySell is if it is a Buy or Sell.
SQLUser.Trans is the original database for transactions.
SQLUser.Tran2 is a copy of the Trans db, with AC9 and Cusip linked (cusip is a unique stock identifier)

================

SELECT
Trans.AC9, Trans.Cusip, Trans.EffDate,
Tran2.BuySell, Tran2.EffDate
FROM
SQLUser.Trans Trans,
SQLUser.Trans Tran2
WHERE
Trans.AC9 = Tran2.AC9 AND
Trans.Cusip = Tran2.Cusip AND
Trans.Cusip = '922038203' AND
Tran2.EffDate >= {d '2003-09-04'} AND
Tran2.BuySell = 'S' AND
Tran2.BuySell = 'B'

==================

The select code I currently have in there is:

{Tran2.EffDate} = {Trans.EffDate}+7 and
{Trans.Cusip} = "922038203" and
{Tran2.EffDate} >= Date(2003,09,04) and
{Tran2.BuySell} = "S" and
{Tran2.BuySell} = "B"

=================

I hope this clears up the code I'm using. Again, I can't express my gratification enough for any assistance.

Venimeux

 

[-cush-]

Nothing will return because you have trans2.BuySell having to equal 2 values:

Tran2.BuySell = 'S' AND
Tran2.BuySell = 'B'

I think you mead to have Trans.buySell = 'B'

I assume you are linking the tables by Cusip, so that you do not need to limit it in the other table.

 

[Venimeux]

I correct the field to show the following:

{Tran2.EffDate} = {Trans.EffDate}+7 and
{Trans.Cusip} = "922038203" and
{Tran2.EffDate} >= Date(2003,09,04) and
{Tran2.BuySell} = "S" and
{Trans.BuySell} = "B"

And reran it. I'm currently waiting for the results.

The suggestion was made to me to connect the DBs by those two items and I'm assuming that's the reason. Otherwise I'd have to put in something about the cusips having to be the same.

I'll post here after it completes, if it completes.

Thanks,
Venimeux

 

[Venimeux]

The report I ran yesterday never finished. It grayed out the screen and stayed that way. The program shut itself down by the time I got back in this morning.

I changed the select slightly this morning:

{Tran2.EffDate} = {Trans.EffDate}+7 and
{Trans.Cusip} = "922038203" and
{Trans.EffDate} >= Date(2003,09,01) and
{Tran2.BuySell} = "S" and
{Trans.BuySell} = "B"

Reran it and it still grayed out until I 'end tasked' it.

 

[lbass]

You might want to try just running a sample of 10 or 20 records so you can take a look at the records the report is returning, and then provide a sample in your post. You can do this by going to file->print->preview sample->first 20 records. This might clarify the problem.

In your last selection formula, you are using exact date values (the return must have occurred on the seventh day)--is that because you know there is one record meeting this criterion?

P.S. Whatever happened to "R" for returns?

-LB