4

I'll separate this post in 3 sections to be as clear as possible. The first one will be informations you need to know. Second one will be the solutions I tried so far to get this working. Third section will be the questions. I also have to precise that's the first time I'm using CrystalReports.

Informations

I'm currently working with Visual Studio 2010 with C# and I'm using SAP Crystal reports. I'm displaying the data using MySQL database.

Now, here are the tables I use to display the report:

Table : orders
Fields : id(primaryKey), date(DATE), quantity(INT), fksizes(INT), fkclients(INT)

Table : sizes
Fields : id(primaryKey), name(VARCHAR(30)), price(FLOAT)

Relationship between these 2 tables : orders.fksizes with sizes.id

Basically, I'm passing a range of dates as parameters to the Crystal Reports to display informations only between those dates. Then, I created 2 parameters : StartDate and EndDate both of type Date.

What I've tried

Here's the original query I'm using to display what I want without the date range condition :

SELECT sizes.name, SUM(orders.quantity) AS totalQty,
   (SUM(sizes.price) * orders.quantity) AS totalPrice, 
   orders.date 
FROM orders 
    INNER JOIN sizes ON orders.fksizes = sizes.id
GROUP BY sizes.name, orders.date

This query works correctly and display the total quantity sold and the total price for each size name. At the report's footer, I'm using a Summary field in which I got the total sum of all totalQty named sumTotalQty. I have another field for the same purpose for totalPrice named sumTotalPrice.

I have 2 rows of data test which are :

Size name                     Quantity sold                      Total Price
------------------------------------------------------------------------------
Big                           2                                  $6.00
XBig                          7                                  $28.00

The field sumTotalQty displays 9 and sumTotalPrice displays $34.00 which is exact results. I have to precise that the first row has 2013-10-29 as value for orders.date and the second one 2013-10-30.

Now, I want to select a range of dates for which I want to display the results. As an example, I select from 2013-10-30 to today, I should get only the second row with sumTotalQty displaying 7 and sumTotalPrice displaying $28.00. I got the single row correctly displayed, but sumTotalQty displaying 9 and sumTotalPrice displaying $34.00 which are incorrect following the date range.

I then tried to add a WHERE clause to my sql query to specify the date range like this (in Database --> Database expert...):

SELECT sizes.name, SUM(orders.quantity) AS totalQty,
   (SUM(sizes.price) * orders.quantity) AS totalPrice, orders.date 
FROM orders 
    INNER JOIN sizes ON orders.fksizes = sizes.id
WHERE orders.date BETWEEN '{?StartDate}' AND '{?EndDate}'
GROUP BY sizes.name, orders.date

I got no result displayed with it. So, I think {?StartDate} and {?EndDate} are just not set but I'm really not sure. The same goes for WHERE orders.date BETWEEN @StartDate AND @EndDate.

Questions

  1. Why aren't {?StartDate} and {?EndDate} set even if I have entered a date range when I'm prompted to give them ?
  2. With the original query (without the WHERE clause), how can I get the right result in my summarize fields with the date range given ? It's like it sums from the database and not from the report fields.

EDIT After many searches, 2 other questions came in my head :

  1. Is it possible to add DateTimePicker in the report so the user will be able to enter a starting date and an end date easily ? The report would be refreshed automatically after that.

  2. Is there a way to create or use Crystal Reports events (OnLoad, AfterLoad,...) ?

Thank you for all help I'll get.

Gabriel L.
  • 4,678
  • 5
  • 25
  • 34
  • 1
    Good start, but now consider providing an sqlfiddle of same. Also, note that in general you have to GROUP BY all the non-aggregated columns referenced in your SELECT. MySQL forgives the omission but it can lead to unwanted or unexpected results. – Strawberry Nov 11 '13 at 18:24
  • 1
    Very well laid out question. I wish everyone was this thorough! – campagnolo_1 Nov 11 '13 at 18:30
  • @Strawberry I Group by all the non-aggregated columns, but nothing changed. But I'll take note of that tip, thanks. – Gabriel L. Nov 11 '13 at 21:08

1 Answers1

0

Question 1: You either use BETWEEN (? AND ?) or BETWEEN (@StartDate AND @EndDate) Question 2: You would have to group your data and then use totals in your report.

Edit: After discovering that the issue is with the parameter declaration, I suggest taking a look at some of the following posts:

http://www.dotnetperls.com/sqlparameter

Why do we always prefer using parameters in SQL statements?

Do some more research and you will find lots of info on the net. What I would suggest is to add some input boxes (TextBox, DateTimePicker, etc.) to your page where the user can input the parameter values and then pass them programmatically to the report. That way you can pass the parameters to the SQL statement and get the correct data to your report. You also won't need parameters in your report. As far as CR events go, there is the Init event, which is public

http://msdn.microsoft.com/en-us/library/aa691447(v=vs.71).aspx

and then there are some Viewer events that could be useful

http://msdn.microsoft.com/en-us/library/ms227097(v=vs.80).aspx

Community
  • 1
  • 1
campagnolo_1
  • 2,710
  • 1
  • 17
  • 25
  • 1. I edited my post : neither `BETWEEN (? AND ?)` nor `BETWEEN (@StartDate AND @EndDate)` work. 2. There are already grouped in the Details section of the report (size_name, Quantity_sold and Total_Price). – Gabriel L. Nov 11 '13 at 21:22
  • Let me ask you this: the SQl query is in your VS code-behind page, correct? The parameters are in your Crystal Report, correct? If that is the case, then your SQL doesn't know the value of the parameters because the report hasn't run yet. So you would have to declare your paramter values in the code-behind for the SQL query to run properly. – campagnolo_1 Nov 11 '13 at 21:56
  • You are correct for both questions. How should I do that ? In what event ? Do you have an example ? – Gabriel L. Nov 11 '13 at 22:46
  • See my revised answer and I hope this will help you. – campagnolo_1 Nov 12 '13 at 01:49
  • Good to know about the `@field` against SQL injections, thanks. The use of text fields can results to errors of typing and bad date format from the user. I really want to avoid that. See my edited question post. Thanks again for your time and patience, I appreciate. – Gabriel L. Nov 12 '13 at 14:46
  • As I said in my answer, you can use pretty much anything (TextBox, DateTimePicker, etc.) on your page for the user to enter the parameters and then pass them programmatically to the report. As far as CR events go, there is the Init event, which is public (http://msdn.microsoft.com/en-us/library/aa691447(v=vs.71).aspx) and then there are some Viewer events that could be useful (http://msdn.microsoft.com/en-us/library/ms227097(v=vs.80).aspx) – campagnolo_1 Nov 12 '13 at 14:56
  • I suggest you to add your previous comment in your answer because this is very useful. Well, this helps me to solve my issue. Thanks :) – Gabriel L. Nov 12 '13 at 15:38
  • I'll add it to the answer. Glad this all was helpful to you. Please make sure to mark as helpful or answered so other users can also benefit from it. – campagnolo_1 Nov 12 '13 at 15:40