Adding Reports into large exportable category

1. Check this report as example – Downloadable.ActivitiesByLeads
   1. It has been created by using the original ActivitiesByLeads and making minor changes to it

2. Create such a report and send it to me
   1. I will make minor changes to the query (SQL) based on the tool I have built.
   2. You can also make those changes. Check this table in ConnectorDB

• SELECT * FROM CustomerData.DumpReportSQL
• All values between [[<>]] are mail merged based on the data posted.

3. This is how it works:
   1. Customer goes and “submits” a export request
   2. The request gets added to DumpReportJob table
   3. I have an application which picks a record from DumpReportJob, picks the necessary query to run from DumpReportSQL, runs it, creates a CSV, uploads to S3, and sends an email to the customer.

So for every large exportable request, all we need is three things:

• The modified LGX
• Corresponding SQL that is to be inserted in DumpReportJob
• Enable DumpReportQuota for the specific OrgID

Changes needed In lgx file, The Modified lgx:

1. Adding 3 Hidden input elements :

<InputHidden DefaultValue="R4" ID="ReportID" />
<InputHidden DefaultValue="Custom Activities By Leads" ID="ReportTitle" />
<InputHidden DefaultValue="Downloadable.ActivitiesByLeadsCustomFields" ID="LogiReportName" />

2. Remove the RUN button after the date selector and add a new division called – divEmailAndSubmit and change the Target.Report of the element –             btnRunReport2

3. In body add a division called – divSampleOutput and restrict the query to LIMIT 10

4. Remove the PDF/Excel export options and Remove the bookmarking options.

Corresponding SQL that is to be inserted in DumpReportJob

Replace all the @Request elements inside [[#]]  eg. 

AND P.CreatedOn BETWEEN '[[FromDate]]' AND '[[ToDate]]' AND
AND P.OwnerID IN ([[selectOwner]])
AND P.ProspectStage IN ([[selectStage]])

Changes in Downloadable.SubmitJob file

Add an extra static row with the report Id and pass all the request parameters.

<LocalData ID="localCreateFilter">

FromDate=@Request.FromDate~&ToDate=@Request.ToDate~&FromDate2=@Request.FromDate2~&ToDate2=@Request.ToDate2~&selectOwner=@SingleQuote.Request.selectOwner~&selectStage=@SingleQuote.Request.selectStage~&selectLeadColumn=@Request.selectLeadColumn~&selectActivities=@SingleQuote.Request.selectActivities~