Generate PDF from a BAQ report

system · January 14, 2014, 10:05am

How hard would it be to change this code to preview or print the report instead of exporting it to a PDF?

For some reason the BAQReport Designer has issues exporting my Extended field data to crystal, so I am not seeing any values there even though the BAQ shows values. I am going to try this method to build the table that I want to use and hopefully that works for me.

system · May 10, 2011, 10:35am

Hi, all,

Is there any way to generate PDF files with the result of a BAQ report without user seeing the report on the screen?

Thanks

[Non-text portions of this message have been removed]

system · May 10, 2011, 10:50am

Search for Logicity - not sure it will do what you want but it might.

________________________________
From: Bill Wu <billw@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Tue, 10 May, 2011 15:35:25
Subject: [Vantage] Generate PDF from a BAQ report

Â
Hi, all,

Is there any way to generate PDF files with the result of a BAQ report without
user seeing the report on the screen?

Thanks

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

system · May 10, 2011, 5:01pm

One approach is to run the BAQ via its business object and then pass the resulting data to a crystal report object.

To call a BAQ you use the DynamicQueryAdapter. using this BO you can add filter criteria. It will return a single table dataset. This dataset can be passed directly to the crystal object. Once you have the dataset you can call crystal using very plain vanilla .net code.

I can send you some example code.

Jim Kinneman
Senior Consultant
Encompass Solutions, Inc

--- In vantage@yahoogroups.com, Bill Wu <billw@...> wrote:
>
> Hi, all,
>
> Is there any way to generate PDF files with the result of a BAQ report without user seeing the report on the screen?
>
> Thanks
>
>
> [Non-text portions of this message have been removed]
>

system · May 10, 2011, 5:23pm

Hi Jim,

I'm interested, too, in your code.

Thanks in advance -
Laraine

--- In vantage@yahoogroups.com, "jckinneman" <jckinneman@...> wrote:
>
> One approach is to run the BAQ via its business object and then pass the resulting data to a crystal report object.
>
> To call a BAQ you use the DynamicQueryAdapter. using this BO you can add filter criteria. It will return a single table dataset. This dataset can be passed directly to the crystal object. Once you have the dataset you can call crystal using very plain vanilla .net code.
>
> I can send you some example code.
>
> Jim Kinneman
> Senior Consultant
> Encompass Solutions, Inc
>
>
> --- In vantage@yahoogroups.com, Bill Wu <billw@> wrote:
> >
> > Hi, all,
> >
> > Is there any way to generate PDF files with the result of a BAQ report without user seeing the report on the screen?
> >
> > Thanks
> >
> >
> > [Non-text portions of this message have been removed]
> >
>

system · May 10, 2011, 6:20pm

FOr what is worth there are several examples of this already on the list
look for Email with Subject
Can Grid on a Custom Sheet be bound to a BAQ?

*Jose C Gomez*
*Software Engineer*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>

*Quis custodiet ipsos custodes?*

On Tue, May 10, 2011 at 5:22 PM, laraines422 <laraines422@...> wrote:

>
>
> Hi Jim,
>
> I'm interested, too, in your code.
>
> Thanks in advance -
> Laraine
>
>
> --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@...> wrote:
> >
> > One approach is to run the BAQ via its business object and then pass the
> resulting data to a crystal report object.
> >
> > To call a BAQ you use the DynamicQueryAdapter. using this BO you can add
> filter criteria. It will return a single table dataset. This dataset can be
> passed directly to the crystal object. Once you have the dataset you can
> call crystal using very plain vanilla .net code.
> >
> > I can send you some example code.
> >
> > Jim Kinneman
> > Senior Consultant
> > Encompass Solutions, Inc
> >
> >
> > --- In vantage@yahoogroups.com, Bill Wu <billw@> wrote:
> > >
> > > Hi, all,
> > >
> > > Is there any way to generate PDF files with the result of a BAQ report
> without user seeing the report on the screen?
> > >
> > > Thanks
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>
>
>

[Non-text portions of this message have been removed]

system · May 10, 2011, 6:37pm

Hi Jose,

I found the post that you referred to - but BAQ Views are available in E9 only, correct?

Laraine

--- In vantage@yahoogroups.com, Jose Gomez <jose@...> wrote:
>
> FOr what is worth there are several examples of this already on the list
> look for Email with Subject
> Can Grid on a Custom Sheet be bound to a BAQ?
>
> *Jose C Gomez*
> *Software Engineer*
> *
> *T: 904.469.1524 mobile
> E: jose@...
> http://www.josecgomez.com
> <http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
> <http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
> <http://www.josecgomez.com/professional-resume/>
> <http://www.josecgomez.com/feed/>
>
> *Quis custodiet ipsos custodes?*
>
>
>
> On Tue, May 10, 2011 at 5:22 PM, laraines422 <laraines422@...> wrote:
>
> >
> >
> > Hi Jim,
> >
> > I'm interested, too, in your code.
> >
> > Thanks in advance -
> > Laraine
> >
> >
> > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > >
> > > One approach is to run the BAQ via its business object and then pass the
> > resulting data to a crystal report object.
> > >
> > > To call a BAQ you use the DynamicQueryAdapter. using this BO you can add
> > filter criteria. It will return a single table dataset. This dataset can be
> > passed directly to the crystal object. Once you have the dataset you can
> > call crystal using very plain vanilla .net code.
> > >
> > > I can send you some example code.
> > >
> > > Jim Kinneman
> > > Senior Consultant
> > > Encompass Solutions, Inc
> > >
> > >
> > > --- In vantage@yahoogroups.com, Bill Wu <billw@> wrote:
> > > >
> > > > Hi, all,
> > > >
> > > > Is there any way to generate PDF files with the result of a BAQ report
> > without user seeing the report on the screen?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>

system · May 10, 2011, 7:13pm

Indeed you are right. I didn't realize you were on 8

*Jose C Gomez*
*Software Engineer*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>

*Quis custodiet ipsos custodes?*

On Tue, May 10, 2011 at 6:37 PM, laraines422 <laraines422@...> wrote:

>
>
> Hi Jose,
>
> I found the post that you referred to - but BAQ Views are available in E9
> only, correct?
>
> Laraine
>
>
> --- In vantage@yahoogroups.com, Jose Gomez <jose@...> wrote:
> >
> > FOr what is worth there are several examples of this already on the list
> > look for Email with Subject
> > Can Grid on a Custom Sheet be bound to a BAQ?
> >
> > *Jose C Gomez*
> > *Software Engineer*
> > *
> > *T: 904.469.1524 mobile
> > E: jose@...
> > http://www.josecgomez.com
> > <http://www.linkedin.com/in/josecgomez> <
> http://www.facebook.com/josegomez>
> > <http://www.google.com/profiles/jose.gomez> <
> http://www.twitter.com/joc85>
> > <http://www.josecgomez.com/professional-resume/>
> > <http://www.josecgomez.com/feed/>
> >
> > *Quis custodiet ipsos custodes?*
>
> >
> >
> >
> > On Tue, May 10, 2011 at 5:22 PM, laraines422 <laraines422@...> wrote:
> >
> > >
> > >
> > > Hi Jim,
> > >
> > > I'm interested, too, in your code.
> > >
> > > Thanks in advance -
> > > Laraine
> > >
> > >
> > > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > > >
> > > > One approach is to run the BAQ via its business object and then pass
> the
> > > resulting data to a crystal report object.
> > > >
> > > > To call a BAQ you use the DynamicQueryAdapter. using this BO you can
> add
> > > filter criteria. It will return a single table dataset. This dataset
> can be
> > > passed directly to the crystal object. Once you have the dataset you
> can
> > > call crystal using very plain vanilla .net code.
> > > >
> > > > I can send you some example code.
> > > >
> > > > Jim Kinneman
> > > > Senior Consultant
> > > > Encompass Solutions, Inc
> > > >
> > > >
> > > > --- In vantage@yahoogroups.com, Bill Wu <billw@> wrote:
> > > > >
> > > > > Hi, all,
> > > > >
> > > > > Is there any way to generate PDF files with the result of a BAQ
> report
> > > without user seeing the report on the screen?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > [Non-text portions of this message have been removed]
> > > > >
> > > >
> > >
> > >
> > >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>

[Non-text portions of this message have been removed]

system · May 10, 2011, 7:49pm

Thanks, Jose - admittedly, we are dragging our feet a bit on the upgrade. But with all the grief that I've heard about going from 6 or 8 to E9 - foot-dragging may be a good thing! :-)

We'll let the rest of you guys on E9 figure it out for us - maybe we'll wait for E10....

L.

--- In vantage@yahoogroups.com, Jose Gomez <jose@...> wrote:
>
> Indeed you are right. I didn't realize you were on 8
>
> *Jose C Gomez*
> *Software Engineer*
> *
> *T: 904.469.1524 mobile
> E: jose@...
> http://www.josecgomez.com
> <http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
> <http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
> <http://www.josecgomez.com/professional-resume/>
> <http://www.josecgomez.com/feed/>
>
> *Quis custodiet ipsos custodes?*
>
>
>
> On Tue, May 10, 2011 at 6:37 PM, laraines422 <laraines422@...> wrote:
>
> >
> >
> > Hi Jose,
> >
> > I found the post that you referred to - but BAQ Views are available in E9
> > only, correct?
> >
> > Laraine
> >
> >
> > --- In vantage@yahoogroups.com, Jose Gomez <jose@> wrote:
> > >
> > > FOr what is worth there are several examples of this already on the list
> > > look for Email with Subject
> > > Can Grid on a Custom Sheet be bound to a BAQ?
> > >
> > > *Jose C Gomez*
> > > *Software Engineer*
> > > *
> > > *T: 904.469.1524 mobile
> > > E: jose@
> > > http://www.josecgomez.com
> > > <http://www.linkedin.com/in/josecgomez> <
> > http://www.facebook.com/josegomez>
> > > <http://www.google.com/profiles/jose.gomez> <
> > http://www.twitter.com/joc85>
> > > <http://www.josecgomez.com/professional-resume/>
> > > <http://www.josecgomez.com/feed/>
> > >
> > > *Quis custodiet ipsos custodes?*
> >
> > >
> > >
> > >
> > > On Tue, May 10, 2011 at 5:22 PM, laraines422 <laraines422@> wrote:
> > >
> > > >
> > > >
> > > > Hi Jim,
> > > >
> > > > I'm interested, too, in your code.
> > > >
> > > > Thanks in advance -
> > > > Laraine
> > > >
> > > >
> > > > --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> > > > >
> > > > > One approach is to run the BAQ via its business object and then pass
> > the
> > > > resulting data to a crystal report object.
> > > > >
> > > > > To call a BAQ you use the DynamicQueryAdapter. using this BO you can
> > add
> > > > filter criteria. It will return a single table dataset. This dataset
> > can be
> > > > passed directly to the crystal object. Once you have the dataset you
> > can
> > > > call crystal using very plain vanilla .net code.
> > > > >
> > > > > I can send you some example code.
> > > > >
> > > > > Jim Kinneman
> > > > > Senior Consultant
> > > > > Encompass Solutions, Inc
> > > > >
> > > > >
> > > > > --- In vantage@yahoogroups.com, Bill Wu <billw@> wrote:
> > > > > >
> > > > > > Hi, all,
> > > > > >
> > > > > > Is there any way to generate PDF files with the result of a BAQ
> > report
> > > > without user seeing the report on the screen?
> > > > > >
> > > > > > Thanks
> > > > > >
> > > > > >
> > > > > > [Non-text portions of this message have been removed]
> > > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>

system · May 10, 2011, 8:53pm

I haven't done a VB version in quite awhile and wasn't able to locate any. The steps are pretty straight forward so converting to VB shouldn't be too involved. This example was originally run from a report screen but the code is generic enough to show how to call from any button.

1. Create BAQ and associated Crystal Report as normal. Easiest if you just use BAQReportResult table. Otherwise you will need to build the other tables.
2. Get the BAQ you want to run
3. Add any where criteria
4. Run the BAQ, it returns a single table dataset
5. Do any table tweaking to match what the crystal report expects
6. Use the crystal report you built for this BAQ using the standard BAQ Designer
7. Point crystal to use the dataset return by the BAQ.

// Custom C# code for BAQReportForm
// Created: 4/12/2010 3:52:57 PM
//**************************************************
using System;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using System.ComponentModel;
using Epicor.Mfg.UI;
using Epicor.Mfg.UI.FrameWork;
using Epicor.Mfg.UI.ExtendedProps;
using Epicor.Mfg.UI.FormFunctions;
using Epicor.Mfg.UI.Customization;
using Epicor.Mfg.UI.Adapters;
using Epicor.Mfg.UI.Searches;
using Epicor.Mfg.BO;

// you will need to copy the dlls for these into your \client directory, probably don't need all of them, never went back to see what we can do without
using CrystalDecisions;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Windows.Forms;

public static class Script
{
// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
// Begin Wizard Added Module Level Variables **
// End Wizard Added Module Level Variables **
// Add Custom Module Level Variables Here **
private static CrystalReportViewer crystalReportViewer1 = new CrystalReportViewer();
private static ReportDocument cryRpt = new ReportDocument();
private static DataSet dsTempReport = new DataSet();

public static void InitializeCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
// Begin Wizard Added Variable Initialization

// End Wizard Added Variable Initialization
// Begin Wizard Added Custom Method Calls

btnCreatePDF.Click += new System.EventHandler(Script.btnCreatePDF_Click);
// End Wizard Added Custom Method Calls

}

public static void DestroyCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
// Begin Wizard Added Object Disposal
btnCreatePDF.Click -= new System.EventHandler(Script.btnCreatePDF_Click);
// End Wizard Added Object Disposal
// Begin Custom Code Disposal
// End Custom Code Disposal
}

private static void btnCreatePDF_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
// defaults to client directory, this is what it is on my system
// could add path to file name to override default location
// C:\Program Files\Epicor Software\Epicor904\client
using(oTrans.PushDisposableStatusText("Creating PDF", true))
{
WritePDF("Test FileName.PDF");
}
}

private static void WritePDF(String FileName)
{
// this is the BAQ we want to run
String BaqID = "BAQThatIwantToRun";
QueryDesignDataSet ds;
// this is used with the whereitem call to adjust the selection criteria
// these have to be tables that are in your baq.
String pcQueryID = BaqID;
String pcTable = "BOLHead";
String pcField = "BOLNum";
String pcCondition = "=";
Boolean pbIsConst = true;
String pcValue = String.Empty;
String pcToTable = String.Empty;
String pcToField = String.Empty;
String pcLeftParentheses = String.Empty;
String pcRightParentheses = String.Empty;
String pcAndOr = String.Empty;
Boolean pbNeg = false;

// crystal report
ReportDocument cryRpt = new ReportDocument();

// this is the location of the crystal report file
// replace it with the location of the crystal report you want to use
string reportname = @"C:\Epicor904\Server\reports\CustomReports\My Crystal Report.rpt";
// load the report
cryRpt.Load(reportname);

// If seletion criteria is needed we need
// to load the query and then add selection criteria before running it instead
// of directly executing it.
DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(BAQReportForm);
adDynamic.BOConnect();

// this woudl be used for BAQ that require NO additional selection criteria
//adDynamic.ExecuteByID(BaqID);

adDynamic.GetByID(BaqID);
// this is used for interactive use, scheduled would lookup any values it needs
EpiDataView edvReport = (EpiDataView) oTrans.EpiDataViews["ReportParam"] ;
ds = adDynamic.QueryDesignData;

// This is the BOL number taken from the screen
// this shows that we can dynamically alter the BAQ selection criteria
// for example we may need to run the report for a differant customer
// for this example we are limiting it to the BOL number entered by the user at the moment
// replace this with values from the screen you are working with
pcValue = edvReport.dataView[edvReport.Row]["Field1"].ToString();
adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);
adDynamic.Execute(ds);

// if we have followed the normal BAQ report design path the crystal report
// is expecting a three table dataset that we need to build
// "BAQReportResult", this is the result of the BAQ
// "BAQReportParameter", has the values passed to report from the report screen
// includes any filter information entered by user i.e. Order Number, etc
// and "Report Title"
// "Company" has company information

// if you are NOT using ANY fields from "Company" and "BAQReportParameter" you do not
// need to build them. Frequently I do not bother using these other fields.
DataSet dsTempReport = new DataSet();

DataTable dtSelect = new DataTable("BAQReportResult");
// need to clone structure so that importrow works
dtSelect = adDynamic.QueryResults.Tables[0].Clone();
dsTempReport.Tables.Add(dtSelect);
// table from query is called "result", we need to rename it
dsTempReport.Tables[0].TableName = "BAQReportResult";
DataTable dt1 = new DataTable("Company");
// add columns by code
dt1.Columns.Add("Company", typeof(string));

DataTable dt2 = new DataTable("BAQReportParameter");
// add any columns/fields that the report is using
dt2.Columns.Add("Company", typeof(string));
dt2.Columns.Add("ReportTitle", typeof(string));
// add to dataset
dsTempReport.Tables.Add(dt1);
dsTempReport.Tables.Add(dt2);

// populate with appropirate values
// in this case report doesn't use compnay so we leave it blank
// reporttitle is used so make it somthing the user will like
DataRow newRow = dsTempReport.Tables["BAQReportParameter"].NewRow();
newRow["Company"] = String.Empty;
newRow["ReportTitle"] = "Summary Shipment Report";
dsTempReport.Tables["BAQReportParameter"].Rows.Add(newRow);

newRow = dsTempReport.Tables["Company"].NewRow();
newRow["Company"] = String.Empty;
dsTempReport.Tables["Company"].Rows.Add(newRow);
// our dataset now looks the way the crystal report is expecting it
try
{
DataTable dtUnq = adDynamic.QueryResults.Tables[0].DefaultView.ToTable(true,"BOLHead.BOLNum");
//clear table, this was left over from a looping experiment
dsTempReport.Tables["BAQReportResult"].Clear();
// to see what the input table looks like uncomment this line
//adDynamic.QueryResults.Tables[0].WriteXml("BAQ Report DataSet.xml",XmlWriteMode.WriteSchema);

String BOLHead = String.Empty;
foreach (DataRow MyDataRow in adDynamic.QueryResults.Tables[0].Rows )
{
BOLHead = MyDataRow["BOLHead.BOLNum"].ToString();
dsTempReport.Tables["BAQReportResult"].ImportRow(MyDataRow);
}
// just to see what things look like, uncomment this line
//dsTempReport.WriteXml("Report DataSet.xml",XmlWriteMode.WriteSchema);
// now build the report
cryRpt.SetDataSource(dsTempReport);

try
{
ExportOptions CrExportOptions ;
DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
CrDiskFileDestinationOptions.DiskFileName = FileName;
CrExportOptions = cryRpt.ExportOptions;
{
CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
}
cryRpt.Export();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
catch (Exception err)
{
MessageBox.Show(err.ToString());
}
}
}

--- In vantage@yahoogroups.com, "laraines422" <laraines422@...> wrote:
>
> Hi Jim,
>
> I'm interested, too, in your code.
>
> Thanks in advance -
> Laraine
>
>
>
> --- In vantage@yahoogroups.com, "jckinneman" <jckinneman@> wrote:
> >
> > One approach is to run the BAQ via its business object and then pass the resulting data to a crystal report object.
> >
> > To call a BAQ you use the DynamicQueryAdapter. using this BO you can add filter criteria. It will return a single table dataset. This dataset can be passed directly to the crystal object. Once you have the dataset you can call crystal using very plain vanilla .net code.
> >
> > I can send you some example code.
> >
> > Jim Kinneman
> > Senior Consultant
> > Encompass Solutions, Inc
> >
> >
> > --- In vantage@yahoogroups.com, Bill Wu <billw@> wrote:
> > >
> > > Hi, all,
> > >
> > > Is there any way to generate PDF files with the result of a BAQ report without user seeing the report on the screen?
> > >
> > > Thanks
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>

system · May 11, 2011, 12:05pm

This is exactly I was looking for. Thank you very much for the code, I will convert it to VB. We are on 803.407.

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of jckinneman
Sent: Tuesday, May 10, 2011 8:53 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Generate PDF from a BAQ report

I haven't done a VB version in quite awhile and wasn't able to locate any. The steps are pretty straight forward so converting to VB shouldn't be too involved. This example was originally run from a report screen but the code is generic enough to show how to call from any button.

1. Create BAQ and associated Crystal Report as normal. Easiest if you just use BAQReportResult table. Otherwise you will need to build the other tables.
2. Get the BAQ you want to run
3. Add any where criteria
4. Run the BAQ, it returns a single table dataset
5. Do any table tweaking to match what the crystal report expects
6. Use the crystal report you built for this BAQ using the standard BAQ Designer
7. Point crystal to use the dataset return by the BAQ.

// Custom C# code for BAQReportForm
// Created: 4/12/2010 3:52:57 PM
//**************************************************
using System;
using System.Data;
using System.Diagnostics;
using System.Windows.Forms;
using System.ComponentModel;
using Epicor.Mfg.UI;
using Epicor.Mfg.UI.FrameWork;
using Epicor.Mfg.UI.ExtendedProps;
using Epicor.Mfg.UI.FormFunctions;
using Epicor.Mfg.UI.Customization;
using Epicor.Mfg.UI.Adapters;
using Epicor.Mfg.UI.Searches;
using Epicor.Mfg.BO;

// you will need to copy the dlls for these into your \client directory, probably don't need all of them, never went back to see what we can do without
using CrystalDecisions;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.ReportSource;
using CrystalDecisions.Windows.Forms;

public static class Script
{
// ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
// Begin Wizard Added Module Level Variables **
// End Wizard Added Module Level Variables **
// Add Custom Module Level Variables Here **
private static CrystalReportViewer crystalReportViewer1 = new CrystalReportViewer();
private static ReportDocument cryRpt = new ReportDocument();
private static DataSet dsTempReport = new DataSet();

public static void InitializeCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
// Begin Wizard Added Variable Initialization

// End Wizard Added Variable Initialization
// Begin Wizard Added Custom Method Calls

btnCreatePDF.Click += new System.EventHandler(Script.btnCreatePDF_Click);
// End Wizard Added Custom Method Calls

}

public static void DestroyCustomCode()
{
// ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
// Begin Wizard Added Object Disposal
btnCreatePDF.Click -= new System.EventHandler(Script.btnCreatePDF_Click);
// End Wizard Added Object Disposal
// Begin Custom Code Disposal
// End Custom Code Disposal
}

private static void btnCreatePDF_Click(object sender, System.EventArgs args)
{
// ** Place Event Handling Code Here **
// defaults to client directory, this is what it is on my system
// could add path to file name to override default location
// C:\Program Files\Epicor Software\Epicor904\client
using(oTrans.PushDisposableStatusText("Creating PDF", true))
{
WritePDF("Test FileName.PDF");
}
}

private static void WritePDF(String FileName)
{
// this is the BAQ we want to run
String BaqID = "BAQThatIwantToRun";
QueryDesignDataSet ds;
// this is used with the whereitem call to adjust the selection criteria
// these have to be tables that are in your baq.
String pcQueryID = BaqID;
String pcTable = "BOLHead";
String pcField = "BOLNum";
String pcCondition = "=";
Boolean pbIsConst = true;
String pcValue = String.Empty;
String pcToTable = String.Empty;
String pcToField = String.Empty;
String pcLeftParentheses = String.Empty;
String pcRightParentheses = String.Empty;
String pcAndOr = String.Empty;
Boolean pbNeg = false;

// crystal report
ReportDocument cryRpt = new ReportDocument();

// this is the location of the crystal report file
// replace it with the location of the crystal report you want to use
string reportname = @"C:\Epicor904\Server\reports\CustomReports\My Crystal Report.rpt";
// load the report
cryRpt.Load(reportname);

// If seletion criteria is needed we need
// to load the query and then add selection criteria before running it instead
// of directly executing it.
DynamicQueryAdapter adDynamic = new DynamicQueryAdapter(BAQReportForm);
adDynamic.BOConnect();

// this woudl be used for BAQ that require NO additional selection criteria
//adDynamic.ExecuteByID(BaqID);

adDynamic.GetByID(BaqID);
// this is used for interactive use, scheduled would lookup any values it needs
EpiDataView edvReport = (EpiDataView) oTrans.EpiDataViews["ReportParam"] ;
ds = adDynamic.QueryDesignData;

// This is the BOL number taken from the screen
// this shows that we can dynamically alter the BAQ selection criteria
// for example we may need to run the report for a differant customer
// for this example we are limiting it to the BOL number entered by the user at the moment
// replace this with values from the screen you are working with
pcValue = edvReport.dataView[edvReport.Row]["Field1"].ToString();
adDynamic.AddWhereItem(ds, pcQueryID, pcTable, pcField, pcCondition, pbIsConst, pcValue, pcToTable, pcToField, pcLeftParentheses, pcRightParentheses, pcAndOr, pbNeg);
adDynamic.Execute(ds);

// if we have followed the normal BAQ report design path the crystal report
// is expecting a three table dataset that we need to build
// "BAQReportResult", this is the result of the BAQ
// "BAQReportParameter", has the values passed to report from the report screen
// includes any filter information entered by user i.e. Order Number, etc
// and "Report Title"
// "Company" has company information

// if you are NOT using ANY fields from "Company" and "BAQReportParameter" you do not
// need to build them. Frequently I do not bother using these other fields.
DataSet dsTempReport = new DataSet();

DataTable dtSelect = new DataTable("BAQReportResult");
// need to clone structure so that importrow works
dtSelect = adDynamic.QueryResults.Tables[0].Clone();
dsTempReport.Tables.Add(dtSelect);
// table from query is called "result", we need to rename it
dsTempReport.Tables[0].TableName = "BAQReportResult";
DataTable dt1 = new DataTable("Company");
// add columns by code
dt1.Columns.Add("Company", typeof(string));

DataTable dt2 = new DataTable("BAQReportParameter");
// add any columns/fields that the report is using
dt2.Columns.Add("Company", typeof(string));
dt2.Columns.Add("ReportTitle", typeof(string));
// add to dataset
dsTempReport.Tables.Add(dt1);
dsTempReport.Tables.Add(dt2);

// populate with appropirate values
// in this case report doesn't use compnay so we leave it blank
// reporttitle is used so make it somthing the user will like
DataRow newRow = dsTempReport.Tables["BAQReportParameter"].NewRow();
newRow["Company"] = String.Empty;
newRow["ReportTitle"] = "Summary Shipment Report";
dsTempReport.Tables["BAQReportParameter"].Rows.Add(newRow);

newRow = dsTempReport.Tables["Company"].NewRow();
newRow["Company"] = String.Empty;
dsTempReport.Tables["Company"].Rows.Add(newRow);
// our dataset now looks the way the crystal report is expecting it
try
{
DataTable dtUnq = adDynamic.QueryResults.Tables[0].DefaultView.ToTable(true,"BOLHead.BOLNum");
//clear table, this was left over from a looping experiment
dsTempReport.Tables["BAQReportResult"].Clear();
// to see what the input table looks like uncomment this line
//adDynamic.QueryResults.Tables[0].WriteXml("BAQ Report DataSet.xml",XmlWriteMode.WriteSchema);

String BOLHead = String.Empty;
foreach (DataRow MyDataRow in adDynamic.QueryResults.Tables[0].Rows )
{
BOLHead = MyDataRow["BOLHead.BOLNum"].ToString();
dsTempReport.Tables["BAQReportResult"].ImportRow(MyDataRow);
}
// just to see what things look like, uncomment this line
//dsTempReport.WriteXml("Report DataSet.xml",XmlWriteMode.WriteSchema);
// now build the report
cryRpt.SetDataSource(dsTempReport);

try
{
ExportOptions CrExportOptions ;
DiskFileDestinationOptions CrDiskFileDestinationOptions = new DiskFileDestinationOptions();
PdfRtfWordFormatOptions CrFormatTypeOptions = new PdfRtfWordFormatOptions();
CrDiskFileDestinationOptions.DiskFileName = FileName;
CrExportOptions = cryRpt.ExportOptions;
{
CrExportOptions.ExportDestinationType = ExportDestinationType.DiskFile;
CrExportOptions.ExportFormatType = ExportFormatType.PortableDocFormat;
CrExportOptions.DestinationOptions = CrDiskFileDestinationOptions;
CrExportOptions.FormatOptions = CrFormatTypeOptions;
}
cryRpt.Export();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
catch (Exception err)
{
MessageBox.Show(err.ToString());
}
}
}

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "laraines422" <laraines422@...> wrote:
>
> Hi Jim,
>
> I'm interested, too, in your code.
>
> Thanks in advance -
> Laraine
>
>
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "jckinneman" <jckinneman@> wrote:
> >
> > One approach is to run the BAQ via its business object and then pass the resulting data to a crystal report object.
> >
> > To call a BAQ you use the DynamicQueryAdapter. using this BO you can add filter criteria. It will return a single table dataset. This dataset can be passed directly to the crystal object. Once you have the dataset you can call crystal using very plain vanilla .net code.
> >
> > I can send you some example code.
> >
> > Jim Kinneman
> > Senior Consultant
> > Encompass Solutions, Inc
> >
> >
> > --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Bill Wu <billw@> wrote:
> > >
> > > Hi, all,
> > >
> > > Is there any way to generate PDF files with the result of a BAQ report without user seeing the report on the screen?
> > >
> > > Thanks
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>

[Non-text portions of this message have been removed]