Filtering and calculation advice

Comments

• Mike O

  January 2011 edited 4:19PM

  ResidentSD wrote: »

  I'm just evaluating the FastReport.NET software and am pretty impressed with it so far. However, I'm having a couple of issues that I can't find the answer for either in the online user manual or on these forums, so I'm hoping someone here could help me out.
  
  The report I'm designing at the moment as a test is a master-detail report, with the master record being a purchase order number and the detail being the items ordered on it. The first thing I'd like to do is to make it so the user can run the report for only one purchase order. I have created a parameter for entering this and linked it to a text box in a dialogue form, but I don't know how to actually get this 'in' to the report. On some of the example videos, I've seen that you do this on a normal detail report by creating a filter on the data band, but I can't work out how to do this on the group header band.
  
  Secondly, I'd like to do some calculations in the detail data band, which includes an if statements as a different calculation is required depending on the quantity type (could be per tonne, per sheet or lump sum). I can't see a way to create an expressions field in the designer, so the only way I can see to do this is using the code view. This isn't a massive problem, but it does mean that I can't easily do a total of that field. What's the best way of achieving this?
  
  Thanks a lot!

  
  The most efficient way to do this is to create an SQL query to return the data which includes the parameter. Attempting to do it using filtering will result in poor performance because the report will return all data and filter the records on the client side.
  
  If your data source references the base tables, then edit the data source adding an SQL query with a parameter using the correct syntax for your database. You can also use a stored procedure or a view. The wizard will give you the opportunity to define your query parameters when you add the query. You must then add a REPORT parameter. Note that there are TWO different parameters required. The QUERY parameter which you define in the "Query Wizard" and the REPORT parameter which you define using the "Parameters" node in the "Data" window.
  
  Example using an SQL Server stored procedure as a data source:
  
  1.) Add the query and define the QUERY parameter
  Invoke the query wizard by right clicking your data source in the data window
  Enter the query (for example exec pSomeProcedure @QueryParameter)
  Click "Next"
  Add the parameter giving it the same name as you did in the query itself (@QueryParameter)
  Go to the next step of the query wizard and click "Finish"
  
  2.) Add a REPORT parameter
  Right click the "Parameters" node in the "Data" window
  Select "New Parameter"
  Set the data type to the same data type as the QUERY parameter
  Give the parameter a name 'Parameter1' (it doesn't have to be the same name as the QUERY parameter)
  
  3.) Link the QUERY parameter to the REPORT parameter
  Invoke the query wizard as before
  Click "Next" until you get to the 'Define Parameters" step
  Set the "Expression" property of your parameter (@QueryParameter) to [Parameter1] (brackets required)
  
  4.) Link the REPORT parameter to the dialog control
  Open the tab for your dialog
  Select the control which you want to link to your REPORT parameter
  Set the "ReportParameter" property of the dialog control to 'Parameter1' (without quotes)
  
  Adding calculations
  You should add any formula containing conditional logic using the code window. The method must be 'static'. You can then call the method in the expression for the field using the syntax [MyMethod([fieldName])]. It works the same as any other 'built in' method.
  
  private static decimal MyMethod(int quantityType, decimal qty, decimal price)
  {
  decimal result = 0;
  
  switch(quantityType)
  {
  case perTonne:
  // do something
  break;
  case perSheet:
  // do something else
  break;
  }
  return result;
  }
  
  I hope this helps.
  
  P.S. I'm evaluating the product as well. I'm also impressed with it so far, but the support on the forums seems to be a bit lacking. Additionally, product support rejects emails from non-owners of the product. Very bad practice in my opinion. Perhaps you could help me to encourage support to promptly answer these questions by sending an email to member AlexTZ. The delay is beginning to negatively influence my buying decision.
  
  
• ResidentSD

  February 2011 edited 4:19PM

  Thank you very much for your help Mike O. While I had to modify your code slightly as we use MySQL Server here (so the parameter name have to start with a ? not an @) the filtering part works fine.
  

  Mike O wrote: »

  Adding calculations
  You should add any formula containing conditional logic using the code window. The method must be 'static'. You can then call the method in the expression for the field using the syntax [MyMethod([fieldName])]. It works the same as any other 'built in' method.
  
  private static decimal MyMethod(int quantityType, decimal qty, decimal price)
  {
  decimal result = 0;
  
  switch(quantityType)
  {
  case perTonne:
  // do something
  break;
  case perSheet:
  // do something else
  break;
  }
  return result;
  }

  
  However, I am struggling with your second response. I don't actually understand the connection between the method called in the field on my report and the values required in the method's code you published. What I mean by this is in the report, you've specified to call [MyMethod([FieldName])] which says to me that the data in 'FieldName' is being provided to the method. However, the method itself has three overloading values. Would you mind explaining it a little bit more please?
  
  Thanks a lot!