Arctic reports (reporting system - Windows)

Developed or developing a new product in PureBasic? Tell the world about it.
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Re: Arctic reports (reporting system - Windows)

Post by srod »

You can use Pyrex to create a separate report containing a single subreport with the relevant page header.

From code you can then use the \ImportReport() report method. See the 'header file' for some details on this method; and look for the relevant flag constants beginning with : #nxReport_IMPORTPAGEPROPERTIES.

You can then make any minor adjustments to the page header as appropriate for the underlying report etc. (using code).
I may look like a mule, but I'm not a complete ass.
Neil
Enthusiast
Enthusiast
Posts: 198
Joined: Wed Feb 29, 2012 8:04 am
Location: Melbourne, AUS

Re: Arctic reports (reporting system - Windows)

Post by Neil »

Hi srod,

Sorry but I can't figure out how to do this.
I've looked at the entries in header file but I'm not sure of the logic to use.

So I will now have 2 report files:
PyrexHeader.nxr
PyrexClubs.nxr
I'm not sure how I can call 2 report files and even then, I am not sure how I can determine when a page is full such that I have to recall report "PyrexHeader"

My code at the moment is:

Code: Select all

Procedure Button_Clubs_Reports_ViewReport(EventType)
  
  If nxReport_CatchReport(?label_ClubReportStart, ?label_ClubReportEnd - ?label_ClubReportStart, @report) = #nxReport_OKAY
    report\UseSubreport("Body", 0)
    sString = "SELECT * FROM Clubs"
    
    If GetGadgetState(gOption_Clubs_Reports_Order_ClubNumber)
      sString = sString + " ORDER BY ClubNumber"
      sReportOrder = "Club Number"
    ElseIf GetGadgetState(gOption_Clubs_Reports_Order_ClubName)
      sString = sString + " ORDER BY ClubName"       
      sReportOrder = "Club Name"       
    EndIf
    sReportOrder = "Report Order: " + sReportOrder
    
    If report\GetControlByName("#Label_ReportOrder",@control) = #nxReport_OKAY
      control\SetSource(sReportOrder)
    EndIf       
  
    report\SetCurrentSubreportDataSource(#nxReport_SQLITEDATABASE, "LacStat.s3db"+Chr(10)+sString)        
    report\PreviewInNewWindow(0, 0, 800, 650, "Clubs: Reports", #PB_Window_MinimizeGadget|#PB_Window_MaximizeGadget|#PB_Window_SizeGadget|#PB_Window_ScreenCentered, 0, $47420A, #nxReport_SHOWPRINTBUTTON|#nxReport_SHOWTHUMBNAILNAVIGATION|#nxReport_MAKEMODAL, #Gray, 120)
    report\Destroy()
  Else
    Debug "dud report"
  EndIf
  
  nxReport_RemoveTemporaryFiles() ;Just in case.  
  
EndProcedure  

DataSection
  label_ClubReportStart:
    IncludeBinary "PyrexClubs.nxr"
  label_ClubReportEnd:
EndDataSection
PyrexClubs.nxr at the moment has 2 subreports, "Header" & "Body".

thanks

Neil
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Re: Arctic reports (reporting system - Windows)

Post by srod »

Sorry, but your code doesn't make it very clear what you are trying to do.

If you have a report already loaded then you can import a separate report file using something like :

Code: Select all

If reportAlreadyLoaded\ImportReport("someReportFile.nxr", flags) = #nxReport_OKAY

etc.

EndIf
Just make sure that the IDs of the subreports/fonts/images in the report to be imported do not clash with those in the host report.

When you have done this, then the original report will now have new subreports added to it corresponding to the subreports in the report being imported. You can now alter the control properties etc. in this expanded host report, such as altering the controls in the page header(s) of the subreports you have imported.

So if the PyrexHeader.nxr report has a subreport called 'header' and the PyrexClubs.nxr report has a subreport called 'body' then the following code :

Code: Select all

If nxReport_CatchReport(?label_ClubReportStart, ?label_ClubReportEnd - ?label_ClubReportStart, @report) = #nxReport_OKAY
  If report\ImportReport("PyrexHeader.nxr", #nxReport_IMPORTFONTS|#nxReport_IMPORTIMAGES) = #nxReport_OKAY

    ;ETC>


  EndIf
  report\Destroy()
Else
  Debug "dud report"
EndIf
  
nxReport_RemoveTemporaryFiles() ;Just in case.  
  

DataSection
  label_ClubReportStart:
    IncludeBinary "PyrexClubs.nxr"
  label_ClubReportEnd:
EndDataSection
Will result in the 'report' report object containing 2 subreports : 'header' and 'body' etc. From here you can alter the controls (using code) of either subreport and so on.
I may look like a mule, but I'm not a complete ass.
Neil
Enthusiast
Enthusiast
Posts: 198
Joined: Wed Feb 29, 2012 8:04 am
Location: Melbourne, AUS

Re: Arctic reports (reporting system - Windows)

Post by Neil »

Hi srod,

Thanks again for reply.

Sorry that I hadn't commented my code, but most of it was from your suggestions anyway, and your assumptions in your reply are correct.

Anyway - almost there. I can combine the reports as per your suggestion, the only stumbling block now is that the Header report "PyrexHeader" reads data from a different table to "PyrexClubs" and I am not sure how to include this table.

The data for "PyrexClubs" reads ok, but not for "PyrexHeader" (get "Control/Field Not Found" error").

Code: Select all

Procedure Button_Clubs_Reports_ViewReport(EventType)
  
  If nxReport_CatchReport(?label_ClubReportStart, ?label_ClubReportEnd - ?label_ClubReportStart, @report) = #nxReport_OKAY

;   New code to include "Header"
    If report\ImportReport("PyrexHeader.nxr", #nxReport_IMPORTFONTS|#nxReport_IMPORTIMAGES) = #nxReport_OKAY
      report\UseSubreport("Header", 0)
;     Trying to read data from Configuration table !!!
      sString = "SELECT * FROM Configuration"
      report\SetCurrentSubreportDataSource(#nxReport_SQLITEDATABASE, "LacStat.s3db"+Chr(10)+sString)        
     
      
;    Back to "Body" subreport in "PyrexClubs"     
      report\UseSubreport("Body", 0)
      sString = "SELECT * FROM Clubs"
      
      If GetGadgetState(gOption_Clubs_Reports_Order_ClubNumber)
        sString = sString + " ORDER BY ClubNumber"
        sReportOrder = "Club Number"
      ElseIf GetGadgetState(gOption_Clubs_Reports_Order_ClubName)
        sString = sString + " ORDER BY ClubName"       
        sReportOrder = "Club Name"       
      EndIf
      sReportOrder = "Report Order: " + sReportOrder
      
      If report\GetControlByName("#Label_ReportOrder",@control) = #nxReport_OKAY
        control\SetSource(sReportOrder)
      EndIf       
      
      report\SetCurrentSubreportDataSource(#nxReport_SQLITEDATABASE, "LacStat.s3db"+Chr(10)+sString)        
      report\PreviewInNewWindow(0, 0, 800, 650, "Clubs: Reports", #PB_Window_MinimizeGadget|#PB_Window_MaximizeGadget|#PB_Window_SizeGadget|#PB_Window_ScreenCentered, 0, $47420A, #nxReport_SHOWPRINTBUTTON|#nxReport_SHOWTHUMBNAILNAVIGATION|#nxReport_MAKEMODAL, #Gray, 120)
      report\Destroy()
    EndIf 
  Else
    Debug "dud report"
  EndIf
  
  nxReport_RemoveTemporaryFiles() ;Just in case.  
  
EndProcedure  

DataSection
  label_ClubReportStart:
    IncludeBinary "PyrexClubs.nxr"
  label_ClubReportEnd:
EndDataSection
Thanks

Neil
Neil
Enthusiast
Enthusiast
Posts: 198
Joined: Wed Feb 29, 2012 8:04 am
Location: Melbourne, AUS

Re: Arctic reports (reporting system - Windows)

Post by Neil »

Hi srod.

Panic over - another brainwave while thinking of something else !!

I switched the "Header" and "Body" report files and now all works - fantastic !!

Code: Select all

Procedure Button_Clubs_Reports_ViewReport(EventType)
  
  If nxReport_CatchReport(?label_ClubReportStart, ?label_ClubReportEnd - ?label_ClubReportStart, @report) = #nxReport_OKAY
    report\UseSubreport("Header", 0)
    sString = "SELECT * FROM Configuration"
    report\SetCurrentSubreportDataSource(#nxReport_SQLITEDATABASE, "LacStat.s3db"+Chr(10)+sString)        
    
    
    If report\ImportReport("PyrexClubs.nxr", #nxReport_IMPORTFONTS|#nxReport_IMPORTIMAGES) = #nxReport_OKAY
      report\UseSubreport("Body", 0)
      sString = "SELECT * FROM Clubs"
      
      If GetGadgetState(gOption_Clubs_Reports_Order_ClubNumber)
        sString = sString + " ORDER BY ClubNumber"
        sReportOrder = "Club Number"
      ElseIf GetGadgetState(gOption_Clubs_Reports_Order_ClubName)
        sString = sString + " ORDER BY ClubName"       
        sReportOrder = "Club Name"       
      EndIf
      sReportOrder = "Report Order: " + sReportOrder
      
      If report\GetControlByName("#Label_ReportOrder",@control) = #nxReport_OKAY
        control\SetSource(sReportOrder)
      EndIf       
      
      report\SetCurrentSubreportDataSource(#nxReport_SQLITEDATABASE, "LacStat.s3db"+Chr(10)+sString)        
      
      report\PreviewInNewWindow(0, 0, 800, 650, "Clubs: Reports", #PB_Window_MinimizeGadget|#PB_Window_MaximizeGadget|#PB_Window_SizeGadget|#PB_Window_ScreenCentered, 0, $47420A, #nxReport_SHOWPRINTBUTTON|#nxReport_SHOWTHUMBNAILNAVIGATION|#nxReport_MAKEMODAL, #Gray, 120)
      report\Destroy()
    EndIf 
  Else
    Debug "dud report"
  EndIf
  
  nxReport_RemoveTemporaryFiles() ;Just in case.  
  
EndProcedure  

DataSection
  label_ClubReportStart:
    IncludeBinary "PyrexHeader.nxr"
  label_ClubReportEnd:
EndDataSection
Hopefully this also means I only require one DataSection for "PyrexHeader.nxr" rather than a separate DataSection for each report - I'll check this shortly

Thanks yet again,

Neil
Neil
Enthusiast
Enthusiast
Posts: 198
Joined: Wed Feb 29, 2012 8:04 am
Location: Melbourne, AUS

Pyrex Formatting Option to show leading zeros

Post by Neil »

Hi srod,

Does Pyrex have a integer formatting option to show leading zeros?

e.g.

instead of:

1
2
3
...
102
103

Can I get:

0001
0002
0003
...
0102
0103

Or do I have to do it in PB ??

Thanks,

Neil
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Re: Arctic reports (reporting system - Windows)

Post by srod »

Hi Neil,

unfortunately not. There are a host of formatting options you can apply directly to controls or via the Format() standard expression function, but they don't include the one you are after.

You can create a user defined function (in PB) to do what you need and then use your given function in nxReport expressions. One of the demos shows how to create user defined functions (the ParameterisedReport2 one), although it doesn't show how to return a value from such a function (you have to use API to create a BSTR and return the address of that).

Of course if you try and preview such a report in Pyrex then it will fail as the designer will not have access to your user defined functions.
I may look like a mule, but I'm not a complete ass.
Neil
Enthusiast
Enthusiast
Posts: 198
Joined: Wed Feb 29, 2012 8:04 am
Location: Melbourne, AUS

Pyrex Report Designer: Feature requests

Post by Neil »

Hi srod,

Not sure if you are still developing Pyrex, but if so I have some requests:

1. Move the "Grid" and "Snap to Grid" options to "Settings".

Image

This is to enable the selection to be remembered.


2. Add an option to show a border on existing objects.

Image

This would be handy for locating new objects.


3. Add an option such that the user can set panel heights (maybe by using drag arrows) - or else maybe set constant heights.

Image

This is to avoid the situation where the height set is such that the objects are truncated.

Thanks,

Neil
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Re: Arctic reports (reporting system - Windows)

Post by srod »

Thanks for the suggestions Neil I shall keep them in mind for any future version.
I may look like a mule, but I'm not a complete ass.
Neil
Enthusiast
Enthusiast
Posts: 198
Joined: Wed Feb 29, 2012 8:04 am
Location: Melbourne, AUS

Re: Arctic reports (reporting system - Windows)

Post by Neil »

ok - thanks for considering them.

Cheers,

Neil
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Arctic reports (reporting system - Windows)

Post by captain_skank »

Hi Srod,

I've run into a problem running a report.

Code: Select all

      report.nxReport_Report
      If nxReport_LoadReport("my_report.NXR", @report) = #nxReport_OKAY
        
       report\SetParameter("$start_date", GetGadgetText(fld_main_start_date))
       report\SetParameter("$finish_date", GetGadgetText(fld_main_finish_date))
       report\InvalidateReport()
        If PrintRequester() ; display print dialogue
          report\PrintToNamedPrinter()
        EndIf
          
      Else
        Debug "error"
      EndIf
using the above code throws a 'PureBasic_Compilation0.exe - Application Error ( The application failed to initialize properly (0xc0000005))

The odd thing is I copied this small program from another directory - along with nxReport dll, inc and wrapper but the same code works fine in the original folder.

Any ideas ??

Also can u point me in thr right direction for changing/setting the ODBC details of a report through code ?

Thanks in advance
srod
PureBasic Expert
PureBasic Expert
Posts: 10589
Joined: Wed Oct 29, 2003 4:35 pm
Location: Beyond the pale...

Re: Arctic reports (reporting system - Windows)

Post by srod »

Did you copy the .lib file as well as the dll?

I notice that you are using the \PrintToNamedPrinter() after a PB PrintRequester() call. That is a bit strange because the \PrintToNamedPrinter() will, with no parameters given, print to the default printer.

Use the \PrintWithDialog() method instead.

As for changing the the datasource etc, just take a look at some of the example programs which come with the nxReport dll. You will need to use the \SetCurrentSubreportDataSource() method after first setting a 'current subreport' with the \UseSubreport() method.
I may look like a mule, but I'm not a complete ass.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Arctic reports (reporting system - Windows)

Post by captain_skank »

Thanks for the reply.

Yep i've got all the lib dll and wrapper in there.

I've just done a test stripping the code out into a standalone and it works fine ( using a preview window ) so there must me something else in my code causing and issue, which is odd cos as soon as i comment out that code it runs fine :(

I'll have a look at the ODBC stuff when i got this working :)

Cheers
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Arctic reports (reporting system - Windows)

Post by captain_skank »

OK Got there in the end - i had an editor gadget on a form within my code that had the following code which was causing the problem :

Code: Select all

; hide editorgadget border
SetWindowTheme_(GadgetID(fld_message), @null.w, @null.w)
SetWindowLongPtr_(GadgetID(fld_message),#GWL_EXSTYLE,0)
SendMessage_(GadgetID(fld_message), #EM_SETTARGETDEVICE,0, 0)
; -----------------------------------------------------------
Not sure why - but removing it has fixed the problem.

Now I'm attempting to change the datasource of a report built using Pyrex.

The report in Pyrex uses an ODBC datasource and i've tried setting it with :

Code: Select all

      report.nxReport_Report
      If nxReport_LoadReport("outstanding_sales_orders_by_date.nxr", @report) = #nxReport_OKAY
        GBL_sql = "SELECT
        GBL_sql + "  *"
        GBL_sql + "FROM"
        GBL_sql + "  tbl_some_table "
        GBL_sql + "WHERE"
        GBL_sql + "  tbl_some_table.date >= '2014-01-01'"
        GBL_sql + "  AND tbl_some_table.date <= '2014-06-01'"
        report\UseSubreport("#sub_1", 0)
        If report\SetCurrentSubreportDataSource(#nxReport_ODBCDATASOURCE, "database" + Chr(10) + GBL_sql + Chr(10) + "user" + Chr(10) + hex_decode(GBL_dbase_pass)) = #nxReport_OKAY
          report\InvalidateReport()
          report\PreviewInNewWindow(0, 0, 800, 650, "nxReport preview demo", #PB_Window_MinimizeGadget|#PB_Window_MaximizeGadget|#PB_Window_SizeGadget|#PB_Window_ScreenCentered, 0, $47420A, #nxReport_SHOWPRINTBUTTON|#nxReport_SHOWTHUMBNAILNAVIGATION|#nxReport_MAKEMODAL, #Gray, 120)
          report\Destroy()  
        Else
          Debug "setodbc failed" 
        EndIf
      Else
        Debug "error"
      EndIf
The spool file returns

Code: Select all

** ERROR initialising "#sub_1" datasource ** (The spoolfile has not been created.)
So I think i've got the syntax wrong ?

Any help appreciated.
User avatar
captain_skank
Enthusiast
Enthusiast
Posts: 636
Joined: Fri Oct 06, 2006 3:57 pm
Location: England

Re: Arctic reports (reporting system - Windows)

Post by captain_skank »

Cracked it - the sql needed "=" at the start.

cheers
Post Reply