Method Community

Are there any guides on using advanced scripts when generating reports?

I need to produce a report using the transaction table based on a selected customer. The report needs to be filtered using two date ranges and the TxnDate.

Hi Andy,

I believe this forum post will help. I talk about how to filter by the TxnDate with an advanced script. You could use a similar script in your Generate Report action. Also, this post will show you where in the Generate Report action you could put the script. Let me know if you have any further questions!

Jason

Jason,

thanks for the links. I have inserted the necessary date picker fields along with the date and character functions.

These are working as I am displaying the script in a message before generating the report. However, I'm getting an unspecified error when generating the report.

My script is as follows...

WHERE TxnDate BETWEEN '2013-Sep-01' AND '2013-Sep-18'

I’m calling the actions from within the edit customer screen. My reports primary table is customer, with transactions as secondary. Will the script only work with the primary table?

Hi Andy, 

I tried this out and I'm getting the same error. I'll have to look into it a little further. In the meantime though, a workaround could be to put your TxnDate filter directly into the Report Designer. Use the Filter String for the DetailReport band. See my screenshot below:

I'll post here as soon as I have an update. 

Jason

Hi Andy,

I did a little research and found out that you cannot actually filter by the Detail table. Inside the Generate Report action you can only filter by the Master table. I see that the option is there to choose the detail table but it doesn't actually work. I'm going to see if we can get that removed. I suggest making the Transaction table your master table instead of the Customer table. Was there a particular reason why your Customer table was the master? 

Jason

Jason,

thanks for the update. We were going to call the report from the edit customer screen and select the transactions relating only to that customer. I assumed that we would need the customer table in the report to achieve this.

Andy,

You don't need the Customer table in there. You can filter the report by the customer's FullName on screen. You may not even need to use an advanced script as you can set a Within date range filter with a regular script. I set up my Generate Report action like the screenshot below. Yours may look a little different depending on how your screen is set up, but you can use the same ideas.

Jason

Jason,

I didn't realise that if you choose a date field as the main field in the table you get the extra option of 'Within date range'.

It's now working, many thanks.

Jason,

one final query. My transactions are listed under the detail section of the report (using the detail report the report doesn't display correctly). Can this be filtered in any way? I don’t want to include invoice line items.

Thanks.

Andy,

In my screenshot above, you'll see that I included the filter: Where IsDetailLine equals 0. IsDetailLine is a field in the Transaction table that determines if a transaction is a line item or not. This might be all you need.

Jason