Reporting Components

Karbon · Post by **Karbon** » Thu Dec 28, 2006 9:24 pm

Reporting as in Crystal Reports...

Would anyone know of anything like that for PureBasic?

the.weavster · Post by **the.weavster** » Wed Jan 03, 2007 10:06 am

Check out Report Manager on sourceforge.

It has a visual report designer and a web server that serves reports as *.pdf files.

Karbon · Post by **Karbon** » Wed Jan 03, 2007 2:36 pm

Very interesting! Have you used this with PB?

the.weavster · Post by **the.weavster** » Wed Jan 03, 2007 3:13 pm

I've only used it with REALbasic's HTTPSocket object but I don't see any reason why it wouldn't work with PureBasic.

Your URL specifies a certain report (and passes any parameters) and it is returned as a *.pdf file across the network.

If you don't want to work this way there are also command line tools that allow you to create reports by calling an executable and passing program parameters - your requested report will simply be created as a *.pdf in the current folder.

Karbon · Post by **Karbon** » Fri Mar 02, 2007 5:13 pm

I'm resurrecting this topic as I finally did get around to looking at ReportMan and though it's impressive, it isn't what I'm looking for.

It has the same limitation as other report designers in that it wants direct access to the data. I can't (or won't), do that.

My ideal product would be one with a WYSIWYG editor like ReportMan's, but that supports "disconnected" data sets, like XML files.

Ideally I'd like to pass it a template file (in whatever format), and a data file (in XML format) and have it spit out the PDF (and options for other formats would be *fantastic*). Crystal Reports can do some or all of that but it's *really* expensive and I don't know about getting it to work with PB seeing as there is no ActiveX support (I'm using PB 3.93, not sure of the state of ActiveX in 4.0).

Looks like it's time to hire someone to do my reporting system the way I want it

Anyway, if anyone has any other suggestions, let me know!

the.weavster · Post by **the.weavster** » Mon Mar 05, 2007 9:18 am

It can work with XML files and/or in memory data. Check out the Mybase driver.

the.weavster · Post by **the.weavster** » Mon Mar 05, 2007 3:54 pm

I decided to try ReportManager with an xml file as I realised that would be useful for me too.

I created a little test xml file like this:

Code: Select all

<?xml version="1.0" standalone="yes"?>
<DATAPACKET version="2.0">
<METADATA>
<FIELDS>
<FIELD attrname="fld1" fieldtype="string" WIDTH="20"/>
<FIELD attrname="fld2" fieldtype="string" WIDTH="20"/>
<FIELD attrname="fld3" fieldtype="r8" subtype="Money"/>
<FIELD attrname="fld4" fieldtype="i4" />
</FIELDS>
</METADATA>
<ROWDATA>
<ROW fld1="mydata1" fld2="myData2" fld3="150.25" fld4="100" />
<ROW fld1="mydata3" fld2="myData4" fld3="1500.75" fld4="200" />
</ROWDATA>
</DATAPACKET>

then I created a report (testtext.rep) using the Mybase driver and the above xml file.

Then I used the printrepxp command line utility to call the report:

Code: Select all

e:\temp>printrepxp -preview testtext.rep

and hey presto, it works...

Karbon · Post by **Karbon** » Mon Mar 05, 2007 4:00 pm

Weeeeell lookie there

Karbon · Post by **Karbon** » Mon Mar 05, 2007 5:02 pm

Now just to figure out how to get the library linked into PureBasic and it'll be quite the package!

The report server is a good idea but the XP and Vista security "features" are going to scare end users with all the permissions it will have to get (first to execute at all, then to "listen" on a port). The command line tools are good too, but again, Vista is going to yell and scream if I try to 2 EXEs every time someone prints!

So I used the OLE/COM Interface generator from Stefan Moebius to generate Interface code for the Reportman OCX.

I have it working but am unsure about how to set a "property" on an object created with CreateObject()

To call a function it's easy :

Code: Select all

Define.IReportManX oRepMan = CreateObject("ReportMan.ReportManX")

oRepMan\AboutBox()

but some VBScript shows some basics :

Code: Select all

rp.filename = "c:\sample2.rep"
rp.Report.Params.Items(2).Value = 1920
rp.Preview = True
rp.Execute

... and I'm unsure how to set "properties" like filename in that example using the PB interface.

Additionally, the generated functions like :

Code: Select all

SetParamValue(paramname.p-bstr,paramvalue.p-variant)

.. Use variable types I've never seen. Can anyone give me a hint as to how to pass a variable of type "p-bstr"?

The Interface generated :

Code: Select all

; Created with OLE/COM Interface generator v0.2  Date: 05/03/2007

; Class: 
; Version: 2.1
; ProgID: ""
; TypeLib: "C:\Projects\ReportMan\reportmanax2_6k\reportman.ocx"

; ##########################################
; #               Interfaces               #
; ##########################################

CompilerIf Defined(IReportManX,#PB_Interface) = #False
Interface IReportManX ; Dispatch interface for ReportManX Control
  QueryInterface(riid.l,ppvObj.l)
  AddRef()
  Release()
  GetTypeInfoCount(pctinfo.l)
  GetTypeInfo(itinfo.l,lcid.l,pptinfo.l)
  GetIDsOfNames(riid.l,rgszNames.l,cNames.l,lcid.l,rgdispid.l)
  Invoke(dispidMember.l,riid.l,lcid.l,wFlags.l,pdispparams.l,pvarResult.l,pexcepinfo.l,puArgErr.l)
  SetDatasetSQL(datasetname.p-bstr,sqlsentence.p-bstr)
  SetDatabaseConnectionString(databasename.p-bstr,connectionstring.p-bstr)
  GetDatasetSQL(datasetname.p-bstr)
  GetDatabaseConnectionString(databasename.p-bstr)
  SetParamValue(paramname.p-bstr,paramvalue.p-variant)
  GetParamValue(paramname.p-bstr)
  Execute()
  PrinterSetup()
  ShowParams()
  SaveToPDF(filename.p-bstr,compressed.w)
  PrintRange(frompage.l,topage.l,copies.l,collate.w)
  get_filename(frompage.l)
  put_filename(frompage.p-bstr)
  get_Preview(frompage.l)
  put_Preview(frompage.w)
  get_ShowProgress(frompage.l)
  put_ShowProgress(frompage.w)
  get_ShowPrintDialog(frompage.l)
  put_ShowPrintDialog(frompage.w)
  get_Title(frompage.l)
  put_Title(frompage.p-bstr)
  get_Language(frompage.l)
  put_Language(frompage.l)
  get_DoubleBuffered(frompage.l)
  put_DoubleBuffered(frompage.w)
  get_AlignDisabled(frompage.l)
  get_VisibleDockClientCount(frompage.l)
  DrawTextBiDiModeFlagsReadingOnly()
  get_Enabled(frompage.l)
  put_Enabled(frompage.w)
  InitiateAction()
  IsRightToLeft()
  UseRightToLeftReading()
  UseRightToLeftScrollBar()
  get_Visible(frompage.l)
  put_Visible(frompage.w)
  get_Cursor(frompage.l)
  put_Cursor(frompage.w)
  get_HelpType(frompage.l)
  put_HelpType(frompage.l)
  get_HelpKeyword(frompage.l)
  put_HelpKeyword(frompage.p-bstr)
  SetSubComponent(IsSubComponent.w)
  AboutBox()
  ExecuteRemote(hostname.p-bstr,port.l,user.p-bstr,Password.p-bstr,aliasname.p-bstr,reportname.p-bstr)
  CalcReport(ShowProgress.w)
  Compose(Report.l,Execute.w)
  SaveToText(filename.p-bstr,textdriver.p-bstr)
  get_Report(filename.l)
  SaveToExcel(filename.p-bstr)
  SaveToHTML(filename.p-bstr)
  SetRecordSet(datasetname.p-bstr,*Value.IDispatch)
  SaveToCustomText(filename.p-bstr)
  SaveToCSV(filename.p-bstr)
  SaveToSVG(filename.p-bstr)
  SaveToMetafile(filename.p-bstr)
  SaveToExcel2(filename.p-bstr)
  get_DefaultPrinter(filename.l)
  put_DefaultPrinter(filename.p-bstr)
  get_PrintersAvailable(filename.l)
  GetRemoteParams(hostname.p-bstr,port.l,user.p-bstr,Password.p-bstr,aliasname.p-bstr,reportname.p-bstr)
  SaveToCSV2(filename.p-bstr,separator.p-bstr)
  get_AsyncExecution(filename.l)
  put_AsyncExecution(filename.w)
EndInterface
CompilerEndIf

CompilerIf Defined(IReportReport,#PB_Interface) = #False
Interface IReportReport ; Dispatch interface for ReportReport Object
  QueryInterface(riid.l,ppvObj.l)
  AddRef()
  Release()
  GetTypeInfoCount(pctinfo.l)
  GetTypeInfo(itinfo.l,lcid.l,pptinfo.l)
  GetIDsOfNames(riid.l,rgszNames.l,cNames.l,lcid.l,rgdispid.l)
  Invoke(dispidMember.l,riid.l,lcid.l,wFlags.l,pdispparams.l,pvarResult.l,pexcepinfo.l,puArgErr.l)
  get_Params(dispidMember.l)
  get_VCLReport(dispidMember.l)
  AddColumn(Width.l,Expression.p-bstr,ExpFormat.p-bstr,Caption.p-bstr,CaptionFormat.p-bstr,SumaryExpression.p-bstr,SumaryFormat.p-bstr)
  get_AutoResizeColumns(Width.l)
  put_AutoResizeColumns(Width.w)
EndInterface
CompilerEndIf

CompilerIf Defined(IReportParameters,#PB_Interface) = #False
Interface IReportParameters ; Dispatch interface for ReportParameters Object
  QueryInterface(riid.l,ppvObj.l)
  AddRef()
  Release()
  GetTypeInfoCount(pctinfo.l)
  GetTypeInfo(itinfo.l,lcid.l,pptinfo.l)
  GetIDsOfNames(riid.l,rgszNames.l,cNames.l,lcid.l,rgdispid.l)
  Invoke(dispidMember.l,riid.l,lcid.l,wFlags.l,pdispparams.l,pvarResult.l,pexcepinfo.l,puArgErr.l)
  get_Count(dispidMember.l)
  get_Items(Index.l)
  ParamExists(paramname.p-bstr)
EndInterface
CompilerEndIf

CompilerIf Defined(IReportParam,#PB_Interface) = #False
Interface IReportParam ; Dispatch interface for ReportParam Object
  QueryInterface(riid.l,ppvObj.l)
  AddRef()
  Release()
  GetTypeInfoCount(pctinfo.l)
  GetTypeInfo(itinfo.l,lcid.l,pptinfo.l)
  GetIDsOfNames(riid.l,rgszNames.l,cNames.l,lcid.l,rgdispid.l)
  Invoke(dispidMember.l,riid.l,lcid.l,wFlags.l,pdispparams.l,pvarResult.l,pexcepinfo.l,puArgErr.l)
  get_Name(dispidMember.l)
  put_Name(dispidMember.p-bstr)
  get_Description(dispidMember.l)
  put_Description(dispidMember.p-bstr)
  get_Visible(dispidMember.l)
  put_Visible(dispidMember.w)
  get_ParamType(dispidMember.l)
  put_ParamType(dispidMember.l)
  get_Value(dispidMember.l)
  put_Value(dispidMember.p-variant)
EndInterface
CompilerEndIf

CompilerIf Defined(IReportmanXAServer,#PB_Interface) = #False
Interface IReportmanXAServer ; Dispatch interface for ReportmanXAServer Object
  QueryInterface(riid.l,ppvObj.l)
  AddRef()
  Release()
  GetTypeInfoCount(pctinfo.l)
  GetTypeInfo(itinfo.l,lcid.l,pptinfo.l)
  GetIDsOfNames(riid.l,rgszNames.l,cNames.l,lcid.l,rgdispid.l)
  Invoke(dispidMember.l,riid.l,lcid.l,wFlags.l,pdispparams.l,pvarResult.l,pexcepinfo.l,puArgErr.l)
  GetPDF(Report.l,compressed.w)
  GetCustomText(Report.l)
  GetText(Report.l)
  GetCSV(Report.l)
  GetMetafile(Report.l)
  GetCSV2(Report.l,separator.p-bstr)
EndInterface
CompilerEndIf

CompilerIf Defined(IPreviewControl,#PB_Interface) = #False
Interface IPreviewControl ; Dispatch interface for PreviewControl Control
  QueryInterface(riid.l,ppvObj.l)
  AddRef()
  Release()
  GetTypeInfoCount(pctinfo.l)
  GetTypeInfo(itinfo.l,lcid.l,pptinfo.l)
  GetIDsOfNames(riid.l,rgszNames.l,cNames.l,lcid.l,rgdispid.l)
  Invoke(dispidMember.l,riid.l,lcid.l,wFlags.l,pdispparams.l,pvarResult.l,pexcepinfo.l,puArgErr.l)
  get_Visible(dispidMember.l)
  put_Visible(dispidMember.w)
  get_AutoScroll(dispidMember.l)
  put_AutoScroll(dispidMember.w)
  get_AutoSize(dispidMember.l)
  put_AutoSize(dispidMember.w)
  get_AxBorderStyle(dispidMember.l)
  put_AxBorderStyle(dispidMember.l)
  get_Caption(dispidMember.l)
  put_Caption(dispidMember.p-bstr)
  get_Color(dispidMember.l)
  put_Color(dispidMember.l)
  get_Font(dispidMember.l)
  put_Font(dispidMember.l)
  putref_Font(dispidMember.l)
  get_KeyPreview(dispidMember.l)
  put_KeyPreview(dispidMember.w)
  get_PixelsPerInch(dispidMember.l)
  put_PixelsPerInch(dispidMember.l)
  get_PrintScale(dispidMember.l)
  put_PrintScale(dispidMember.l)
  get_Scaled(dispidMember.l)
  put_Scaled(dispidMember.w)
  get_Active(dispidMember.l)
  get_DropTarget(dispidMember.l)
  put_DropTarget(dispidMember.w)
  get_HelpFile(dispidMember.l)
  put_HelpFile(dispidMember.p-bstr)
  get_DoubleBuffered(dispidMember.l)
  put_DoubleBuffered(dispidMember.w)
  get_VisibleDockClientCount(dispidMember.l)
  get_Enabled(dispidMember.l)
  put_Enabled(dispidMember.w)
  get_Cursor(dispidMember.l)
  put_Cursor(dispidMember.w)
  SetReport(Report.l)
  get_AutoScale(Report.l)
  put_AutoScale(Report.l)
  get_PreviewScale(Report.l)
  put_PreviewScale(Report.d)
  get_EntirePageCount(Report.l)
  put_EntirePageCount(Report.l)
  get_EntireTopDown(Report.l)
  put_EntireTopDown(Report.w)
  FirstPage()
  PriorPage()
  NextPage()
  LastPage()
  RefreshMetafile()
  get_Page(Report.l)
  put_Page(Report.l)
  DoScroll(vertical.w,increment.l)
  get_Finished(vertical.l)
  put_Finished(vertical.w)
  SaveToFile(filename.p-bstr,format.l,textdriver.p-bstr,horzres.l,vertres.l,mono.w)
  Clear()
EndInterface
CompilerEndIf

CompilerIf Defined(IPreviewControlEvents,#PB_Interface) = #False
Interface IPreviewControlEvents ; Events interface for PreviewControl Control
  QueryInterface(riid.l,ppvObj.l)
  AddRef()
  Release()
  OnActivate()
  OnClick()
  OnCreate()
  OnDblClick()
  OnDestroy()
  OnDeactivate()
  OnKeyPress(key.l)
  OnPaint()
  OnWorkProgress(records.l,pagecount.l,docancel.l)
  OnPageDrawn(PageDrawn.l,PagesDrawn.l)
EndInterface
CompilerEndIf

; ##########################################
; #               Constants                #
; ##########################################

Enumeration ; TxHelpType
  #htKeyword=0
  #htContext=1
EndEnumeration

Enumeration ; TxParamType
  #rpParamString=0
  #rpParamInteger=1
  #rpParamDouble=2
  #rpParamDate=3
  #rpParamTime=4
  #rpParamDateTime=5
  #rpParamCurrency=6
  #rpParamBool=7
  #rpParamExpreB=8
  #rpParamExpreA=9
  #rpParamSubst=10
  #rpParamList=11
  #rpParamUnknown=12
EndEnumeration

Enumeration ; TxAutoScaleType
  #AScaleReal=0
  #AScaleWide=1
  #AScaleHeight=2
  #AScaleEntirePage=3
  #AScaleCustom=4
EndEnumeration

Enumeration ; TxBorderStyle
  #bsNone=0
  #bsSingle=1
EndEnumeration

Enumeration ; TxDragMode
  #dmManual=0
  #dmAutomatic=1
EndEnumeration

Enumeration ; TxMouseButton
  #mbLeft=0
  #mbRight=1
  #mbMiddle=2
EndEnumeration

Enumeration ; TxActiveFormBorderStyle
  #afbNone=0
  #afbSingle=1
  #afbSunken=2
  #afbRaised=3
EndEnumeration

Enumeration ; TxPrintScale
  #poNone=0
  #poProportional=1
  #poPrintToFit=2
EndEnumeration

; ##########################################
; #                 GUIDs                  #
; ##########################################

DataSection
  CLSID_REPORTMAN:  ; {D4D26F6B-6564-44F4-A913-03C91CE37740}
    Data.l $D4D26F6B
    Data.w $6564,$44F4
    Data.b $A9,$13,$3,$C9,$1C,$E3,$77,$40

  IID_IReportManX:  ; {B3AE1470-158D-4855-83DB-BC3A2746C26E}
    Data.l $B3AE1470
    Data.w $158D,$4855
    Data.b $83,$DB,$BC,$3A,$27,$46,$C2,$6E

  IID_IReportReport:  ; {2FCB34BE-8DD4-4567-A771-9965C2FD3A04}
    Data.l $2FCB34BE
    Data.w $8DD4,$4567
    Data.b $A7,$71,$99,$65,$C2,$FD,$3A,$4

  IID_IReportParameters:  ; {A5F6E90E-DFE7-49DA-AA38-C1A41C995B6B}
    Data.l $A5F6E90E
    Data.w $DFE7,$49DA
    Data.b $AA,$38,$C1,$A4,$1C,$99,$5B,$6B

  IID_IReportParam:  ; {F1634F9E-DE5A-411E-9A9E-3A46707A7ABB}
    Data.l $F1634F9E
    Data.w $DE5A,$411E
    Data.b $9A,$9E,$3A,$46,$70,$7A,$7A,$BB

  IID_IReportmanXAServer:  ; {F3A6B88C-D629-402E-BC62-BAB0E2EE39AF}
    Data.l $F3A6B88C
    Data.w $D629,$402E
    Data.b $BC,$62,$BA,$B0,$E2,$EE,$39,$AF

  IID_IPreviewControl:  ; {3D8043B8-E2F6-4F5D-B055-571924F5B0DC}
    Data.l $3D8043B8
    Data.w $E2F6,$4F5D
    Data.b $B0,$55,$57,$19,$24,$F5,$B0,$DC

  IID_IPreviewControlEvents:  ; {7364E2EA-8EEC-4673-9059-3B078C388717}
    Data.l $7364E2EA
    Data.w $8EEC,$4673
    Data.b $90,$59,$3B,$7,$8C,$38,$87,$17
EndDataSection

nhokem · Post by **nhokem** » Fri Aug 03, 2007 2:05 pm

@Karbon, just curious...have you found a reporting tool for your software?

I've gone back to using a webgadget and printing through IE for my invoices and reports (even though I still have issues with the header/footer sometimes printing even though I clear it via the registry).

I couldn't get Report Manager to work correctly (for some reason it could not read some fields from my SELECT query, which when I took them out there was no problem, neither would it correctly read a "SELECT *").

I've also tried Datavision, but had some difficulty with using the RUBY scripting (actually, it's more like I'm too lazy to learn it even though it is somewhat similar to C).

thanks!

ts-soft · Post by **ts-soft** » Fri Aug 03, 2007 2:14 pm

>> Can anyone give me a hint as to how to pass a variable of type "p-bstr"?
Use a normal string. This is a pseudotype and PB passes the string as Bstring (Ole-String)

Karbon · Post by **Karbon** » Fri Aug 03, 2007 2:19 pm

I ended up going with ReportMan which I continue to have a love/hate relationship with

If you're using ODBC then it's pretty easy to hook ReportMan into your application. Unfortunately I've run into a lot of problems with PB and ODBC (especially when it comes to Unicode/Thread safety), so...

nhokem · Post by **nhokem** » Fri Aug 03, 2007 3:03 pm

Karbon wrote:I ended up going with ReportMan which I continue to have a love/hate relationship with

yeah, I know what you mean about love/hate.

It seemed like the best choice for what I needed (I was going to use the commandline way for a quick fix, then try to tackle the OCX through PB), but I still cannot get around it not being able to read some fields.

For example, in my test dataset query I had:

Code: Select all

SELECT orderid, company, ponum, description, @paypostageactual FROM orders WHERE orderid='00001'

but it wouldn't read ANY of the fields. I then took out fields one-by-one and found it didn't like "ponum" (ponum is just a tinytext field, as is company and description).

unfortunately, I need that field in the invoice...

...and don't even get me started on getting parameters to work correctly (and linking datasets)

Karbon wrote:If you're using ODBC then it's pretty easy to hook ReportMan into your application. Unfortunately I've run into a lot of problems with PB and ODBC (especially when it comes to Unicode/Thread safety), so...

I too ended up going through ODBC since I couldn't get the other connections to work.

Oh well, I guess I'll stick with the webgadget way until I can find more time to figure out ReportMan...

Karbon · Post by **Karbon** » Fri Aug 03, 2007 3:11 pm

I'll give you a hint with the OCX.. Just use OpenLibrary and CallFunction on it. An OCX is nothing more than a regular DLL with an ActiveX wrapper (and you can just not use that).

Examples of calling the function to print from PB after setting some parameters to ReportMan :

Code: Select all


report_id.l = CallFunction(#REPORTMAN,"rp_open","template.rep")

InvoiceID = current_invoice\invoice_id
*InvoiceID = @InvoiceID
Param$ = "INVOICEID"
*Param = @Param$
  CallFunction(#REPORTMAN,"rp_setparamvalue",report_id,*Param,3,*InvoiceID)

Title$ = "kBilling Invoice " + current_invoice\invoice_number
*Title = @Title$  

If you_want_to_prompt_to_print = 1
  
  CallFunction(#REPORTMAN,"rp_print",report_id,*Title,0,0)
    
Else ; if not :-)
    
  CallFunction(#REPORTMAN,"rp_print",report_id,*Title,1,1)
    
EndIf

CallFunction(#REPORTMAN,"rp_close",report_id)

Karbon · Post by **Karbon** » Fri Aug 03, 2007 3:12 pm

What I do is pass in an ID for the document I want to print and use ReportMan's internal query capability to pull data sets directly from an ODBC connection (which ReportMan supports natively).