Report on ALL Referrers?
Printed From: ProductCart E-Commerce Solutions
Category: ProductCart
Forum Name: Using ProductCart
Forum Description: Running your store with ProductCart
URL: https://forum.productcart.com/forum_posts.asp?TID=4304
Printed Date: 04-March-2025 at 8:28pm
Software Version: Web Wiz Forums 12.04 - http://www.webwizforums.com
Topic: Report on ALL Referrers?
Posted By: TrekLightGear
Subject: Report on ALL Referrers?
Date Posted: 09-February-2011 at 1:36pm
Does anyone know of an easy way to run a report showing all referrers selected during checkout during a specific time period?
Most other reports in the report center have the option of 'All' but for some reason the Referrer reporting requires you to run an individual report for each option and it makes much more sense to be able to see all referrers chosen during a given time period for comparison.
On a side note, being able to see this data in a pie chart form (like the sales data) would be a great way to see which Referrals are the most effective. Two pie charts would probably be best - one showing the breakdown by # of referrals and the other showing it by sales totals.
-------------
Seth Haber
|
Replies:
Posted By: Brett
Date Posted: 09-February-2011 at 4:13pm
Try these two files and let me know if they fix your problem. It adds an "All Referrers" option to the dropdown, and if it is chosen then the resulting report behaves a bit differently, reporting all referrers in order instead of just one. No guarantees it will work, since I don't have any orders tied to referrers to verify it against. Also, I'm not sure if these files have been modified aside from this change, so run a compare against them before overwriting your files. uploads/1159/srcOrdByDate_RefsalesReport.zip - srcOrdByDate_RefsalesReport.zip
Basically I modified srcOrdByDate.asp to change the dropdown for referrers to this:
<select name="IdRefer">
<option value="0">All Referrers</option>
<%do while not rsCustRef.eof%>
|
...so that adds a new option for all referrers, with value 0
Then, in RefsalesReport, I added a couple if statements to change the database queries:
if IdRefer=0 then
mySQL="SELECT Name,IdRefer FROM Referrer;"
else
mySQL="SELECT Name,IdRefer FROM Referrer WHERE IdRefer=" & IdRefer
end if
|
and a new variable:
do while not rs1.eof
Referrer=rs1("Name")
newIdRefer=rs1("IdRefer")
if IdRefer=0 then
query="SELECT orders.idorder,orders.idcustomer,orders.total,orders.shipmentDetails,orders.paymentDetails,orders.taxAmount,orders.discountDetails,orders.orderDate,orders.processDate,orders.shipDate,orders.ord_VAT FROM Orders INNER JOIN customers ON (orders.idcustomer=customers.idcustomer) WHERE (orders.IDRefer =" & newIDRefer & " OR customers.IDRefer =" & newIDRefer & ") AND ((orders.orderStatus>2 AND orders.orderStatus<5) OR (orders.orderStatus>6 AND orders.orderStatus<9) OR (orders.orderStatus=10 OR orders.orderStatus=12)) " & TempSQL1 & TempSQL2 & TempSpecial & " ORDER BY " & tmpD & " DESC;"
else
query="SELECT orders.idorder,orders.idcustomer,orders.total,orders.shipmentDetails,orders.paymentDetails,orders.taxAmount,orders.discountDetails,orders.orderDate,orders.processDate,orders.shipDate,orders.ord_VAT FROM Orders INNER JOIN customers ON (orders.idcustomer=customers.idcustomer) WHERE (orders.IDRefer =" & IDRefer & " OR customers.IDRefer =" & IDRefer & ") AND ((orders.orderStatus>2 AND orders.orderStatus<5) OR (orders.orderStatus>6 AND orders.orderStatus<9) OR (orders.orderStatus=10 OR orders.orderStatus=12)) " & TempSQL1 & TempSQL2 & TempSpecial & " ORDER BY " & tmpD & " DESC;"
end if
|
I believe these will accomplish what you want, but no guarantees!
|
Posted By: TrekLightGear
Date Posted: 10-February-2011 at 1:48pm
Thanks for the quick reply Brett. I'm swamped at the moment but I'll compare the files and test out the new code as soon as I get a chance. I'll let you know how it goes - I sincerely appreciate the help!
-------------
Seth Haber
|
Posted By: TrekLightGear
Date Posted: 24-February-2011 at 1:38pm
Hi Brett -
Just wanted to let you know I finally had a chance to merge the code and it looks like it's working successfully! The only weird glitch I noticed is that above each section/referral code in the report it's displaying the SQL code:
SELECT orders.idorder,orders.idcustomer,orders.total,orders.shipmentDetails,orders.paymentDetails,orders.taxAmount,orders.discountDetails,orders.orderDate,orders.processDate,orders.shipDate,orders.ord_VAT FROM Orders INNER JOIN customers ON (orders.idcustomer=customers.idcustomer) WHERE (orders.IDRefer =1 OR customers.IDRefer =1) AND ((orders.orderStatus>2 AND orders.orderStatus<5) OR (orders.orderStatus>6 AND orders.orderStatus<9) OR (orders.orderStatus=10 OR orders.orderStatus=12)) AND orders.orderDate >='1/1/2011' AND orders.orderDate <='2/24/2011' ORDER BY orders.orderDate DESC;
Any idea what's causing that to display in the report?
Many thanks for your help!!!
-------------
Seth Haber
|
Posted By: Brett
Date Posted: 24-February-2011 at 2:12pm
Hey TrekLight,
I'm glad the solution I posted works for you :) The glitch you noticed is from some testing code I had in there that I forgot to remove. Go to line 163 of RefsalesReport.asp and remove lines 163 through 167:
<tr class="normal">
<td colspan="8">
<%=query%>
</td>
</tr>
|
Then you shouldn't get the query displaying on your reports any more.
*edit*
Here's the updated file in case you don't want to get into the code: uploads/1159/RefsalesReport_new.zip - RefsalesReport_new.zip
|
Posted By: TrekLightGear
Date Posted: 24-February-2011 at 3:17pm
That did it, thanks again Brett!
-------------
Seth Haber
|
|