Method Community

I want to export a report to Excel. I designed a pivot grid in Report Designer based on the Invoice table. The report displays fine.

Some parameters:

1. The report is on a custom screen and is generated as HTM onscreen
2. The following filters apply to the report on the screen:
1. Fullname contains "Main Account"
2. TxnDate is between StartDate and EndDate which are selected by the user using date-picker objects. Defaults are Start date = January 1st of the current year, End Date = today's date
3. SalesRep_RecordID is is one of in value from session SharedListSalesRep

I made an "Export To Excel" button and used the "Generate Report" action I run into the following problem: I can't just export what is onscreen, already filtered, can I?

If not, my options are as follows:

Re-apply all the filters above (1-3) in the Generate Report action. The problem is, only the first "Where" allows things like "Dates between" or "Fullname Contains". When you "Add Where", there are no options to use "Greater than", "Contains" or anything like that.

So...that leaves me with using Advanced Script, which I'd have to do all in one shot, and I'm not sure how to do #3. Plus, for the dates, I'd have to put in date placeholders (StartDate, EndDate) and then merge the script and replace the placeholders with the date-picker values selected by the user.

SQL: (Fullname CONTAINS 'Main Account') AND (TXNDATE >= 'StartDate' AND TXNDATE <= 'EndDate') AND ???

Anyway, that's a lot of work for just exporting a report to Excel.

If the original report is Excel (and not HTM), then, sure, it works, but then the report never displays.

Am I missing something here?

Still looking for an answer on this

I believe you can just use the "Export Grid to Excel" Action:

and it should export what is already filtered on the screen.

Thanks, but it's a report, not a grid. There is no grid on the screen.

Hi Mike,

I think I understand what you are trying to do here, please corrtect me if I am wrong.

You are correct in your assumption that you would need to use an advanced script to get this one running. You would need to create an SQL script saved an Action Result, looking something like

 (Fullname CONTAINS ‘<MainAccount>’ AND (TXNDATE >= ‘<StartDate>' AND TXNDATE <= ‘<EndDate>') AND Salesrep_RecordID = ‘<SharedListSalesRep>’

Then, you would need to go though and use the Character Function 'Replace Characters' action to replace all the '<>' values with the intended data. The third statement you were having trouble with should look something like this

The 'Script' Action Result containing the full SQL statement mentioned above

Let me know if Im on the right track here.

Thanks Mike.

Regards,

Ben

I think so - thanks.

I am already replacing the date placeholders - no problem there. I just wasn't sure how to SQL for "is part of a list", but it turns out I won't need to know something like that. It didn't occur to me that I could replace that <SalesRep> placeholder with a result that represented a list.

Thanks, I will try when I have time.

OK, everything is ALMOST working according to the script below:

(FullName CONTAINS ‘Main Account') AND (TxnDate >= ‘<StartDate>' AND TxnDate <= ‘<EndDate>') AND (SalesRepRecordID = ‘<SharedListSalesRep>')

I use character functions to replace <StartDate>, <EndDate> and <SharedListSalesRep>

The report runs, but it does not filter for Fullnames that contain 'Main Account' (hard-coded, no character replacement used here).

As said, the report is generated (no error and no crash) and generates numbers, but includes values from Fullnames that DO NOT contain the text 'Main Account'

Is there something wrong with the "CONTAINS" filter? Should I use "LIKE" instead?

This is the last thing...

Thanks

Hi Mike,

You are correct. You'll need to use LIKE instead of CONTAINS.

FullName LIKE '%Main Account%' 

Let me know if you run into any further issues!

Ben