Quick ReportView question

[stanl]

Does reportview work with a getrows() array from an SQL query? Any examples?

[td]

If 'getrows' returns a safearray like the ADO Recordset version does then yes.  You can use safe arrays just like WIL arrays so you can load a REPORTVIEW the same way you would load it with a rank 2 WIL array.

[JTaylor]

Yes.  I think there is an example in the tech database.  If you have trouble finding one let me know.   Basically just do the getrows() and use that array as the source for the ReportView.   When they first implemented ReportView, it didn't work without a small tweak to the array that was returned but after I pointed that out they fixed it so it worked.  I think I had to Redimension the array or something like that but not anymore.

Jim

[JTaylor]

Not finding an example in the tech database so here is some code I chopped out of a program.

Jim

Code (winbatch) Select Expand

 
  SQLText = "select copy_order, copy_requestor, copy_requestor_id, copy_budget, copy_location from copy_cat where copy_profile = '%copy_profile%' order by copy_order"
  myREC.Open (SQLText, myConn, adoConst.adOpenStatic, adoConst.adLockReadOnly, adoConst.adCmdText)
  If myREC.EOF == @FALSE Then
    copy_cat_arr = myREC.GetRows()
    DialogControlSet(COP_Handle,"rv_COP_copy_cat",@dcContents, copy_cat_arr)
  Else
    copy_cat_arr = ArrDimension(1,11)
    ArrInitialize(copy_cat_arr, " ")
    DialogControlSet(COP_Handle,"rv_COP_copy_cat",@dcContents,copy_cat_arr) 
  EndIf
  myREC.Close


[stanl]

Thanks (both):  I need to put together a quick demo with a select of 75 fields from MySQL.  Don't need a dynamic dialog so it seems I can assign getrows() to my reportview var.  Any chance I can get an example of how to make a static reportview pretty as possible (allow scrolling and sorting only).

My alternative is powershell and C# - looking at about 700 lines as opposed to 50 in WB

[JTaylor]

Sure...give me a few minutes.   If you want sorting to work like I think it should it requires a dynamic dialog.  That is, if you click the header it sorts ascending, if you click again it sorts descending.

Jim

[JTaylor]

After looking at this some more I think you are going to have to go with a Dynamic Dialog, if you want it pretty.  You can populate the List but then have no way to adjust column width, formatting, etc.

Jim

[stanl]

After looking at this some more I think you are going to have to go with a Dynamic Dialog, if you want it pretty.  You can populate the List but then have no way to adjust column width, formatting, etc.

Jim

I'll look into it. Right now powershell, c# can set them all in advance of the display... more initial code but had to get the demo out this morning.

I have another great alternative with an MSHTML control and an HTML table populated with getstring... just don't thnk MSHTML recognizes CSS to freeze table headers.

[JTaylor]

Sorry for not posting something sooner.   Trying to sort the Sorting out and my wife keeps finding things that have to be done "Now" :)   Sounds like you are past your deadline at the moment but I will post what I have once I work that out, in case it helps.

Jim

[JTaylor]

For what it is worth...The sorting isn't perfect as you have to click twice sometimes but not sure it can be made better than this due to the way sorting is implemented.  I am going to post a question under dialogs about the sorting.  You may want different style attributes.

This does use the native dialog constants.   Hopefully that won't be a problem.

Jim

Code (winbatch) Select Expand



Home_Path = DirScript()
DirChange(Home_Path)
GoSub Load_Routines
IntControl(49,3,0,0,0)
My_Connection()

STANFormat=`WWWDLGED,6.2`

STANCaption=`Stan Grid`
STANX=-01
STANY=-01
STANWidth=554
STANHeight=301
STANMinWidth=298
STANMinHeight=301
STANNumControls=003
STANProcedure=`DEFAULT`
STANProcedure=`Stan_Sub`
STANTextColor=`DEFAULT`
STANBackground=`DEFAULT,6|28|85`
STANConfig=4513740

STAN001=`525,003,022,012,PUSHBUTTON,"pb_STA_Exit",DEFAULT,"E&xit",1,3,@csDefButton,DEFAULT,DEFAULT,DEFAULT`
STAN002=`005,003,022,012,PUSHBUTTON,"pb_STA_Load",DEFAULT,"Load",6,27,DEFAULT,DEFAULT,DEFAULT,DEFAULT`
STAN003=`005,017,542,278,REPORTVIEW,"rv_STA_copy_cat",copy_cat_arr,DEFAULT,DEFAULT,39,@csAsort|@csFirstHeader|@csFullSel|@csGrid|@csNoAdjust|@csSingleSel,"Courier New|6144|40|49","0|0|0",DEFAULT`

ButtonPushed=Dialog("STAN")

My_Connection_Close()

Exit

:Load_Routines


#DefineSubRoutine Stan_Sub(STA_Handle,DMsg,DCID,DEInfo,ChangeInfo)

Switch (DMsg)
    Case @deInit
    DialogProcOptions(STA_Handle, @deClose,1)                             ; Close selected (IntControl(49....) (1-On, 0-Off).
    DialogProcOptions(STA_Handle, @deTimer,0)                             ; TimerEvent (0- Off).
    DialogProcOptions(STA_Handle, @dePbPush,1)                            ; Pushbutton/PictureButton.
    DialogProcOptions(STA_Handle, @deRvrSelect,1)                         ; RV Item Select Row.
    DialogProcOptions(STA_Handle, @deRvhClick,1)                          ; RV Edit Item Text.
    copy_cat_sort_col = 1
    Get_CopyCat_List()
    Break
  Case @deTimer
    x = copy_cat_sort_col_new
    If copy_cat_sort_col_new == copy_cat_sort_col Then
      If copy_cat_sort_ord_%x% == @ASCENDING Then
        copy_cat_sort_ord_%x% = @DESCENDING
        DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsRemStyle,@csAsort)
        DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsAddStyle,@csDsort)
      Else
        copy_cat_sort_ord_%x% = @ASCENDING
        DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsRemStyle,@csDsort)
        DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsAddStyle,@csAsort)
      EndIf
    Else
      copy_cat_sort_ord_%x% = @ASCENDING
      DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsRemStyle,@csDsort)
      DialogControlState(STA_Handle,"rv_STA_copy_cat",@dcsAddStyle,@csAsort)
    EndIf
    copy_cat_sort_col = copy_cat_sort_col_new
    DialogProcOptions(STA_Handle, @deTimer,0)

    Break
  Case @deClose
    Return 9
    Break
  Case @deRvhClick
    Switch(DialogProcOptions(STA_Handle,@dpoCtlNumber,DCID))
      Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"rv_STA_copy_cat")
        copy_cat_sort_col_new = ChangeInfo
        DialogProcOptions(STA_Handle, @deTimer, 1)
       Break
    EndSwitch
    Break
  Case @deRvrSelect
    Switch(DialogProcOptions(STA_Handle,@dpoCtlNumber,DCID))
      Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"rv_STA_copy_cat")
        copy_cat_idx = ChangeInfo
        copy_cat_arr = DialogControlGet(STA_Handle,"rv_STA_copy_cat",@dcSelect)
        grid_idx_old = ""
        Break
    EndSwitch
    Break
  Case @dePbPush
    Switch(DialogProcOptions(STA_Handle,@dpoCtlNumber,DCID))
      Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"pb_STA_Exit")
        Return -1
        Break
      Case DialogProcOptions(STA_Handle,@dpoCtlNumber,"pb_STA_Load")
        Get_CopyCat_List()
        Break
    EndSwitch
    Break
EndSwitch
Return -2

#EndSubRoutine
     
#DefineSubRoutine Get_CopyCat_List()

  If !IsDefined(copy_cat) Then copy_cat = ""
  copy_profile = "Jim"
 
  SQLText = "select copy_order, copy_requestor, copy_requestor_id, copy_budget, copy_area, copy_location, copy_collection, copy_itype, copy_extra1, copy_extra2, copy_extra3, copy_extra4, copy_note, copy_copies, copy_num from copy_cat where copy_profile = '%copy_profile%' order by copy_order"
  myREC.Open (SQLText, myConn, adoConst.adOpenStatic, adoConst.adLockReadOnly, adoConst.adCmdText)
  If myREC.EOF == @FALSE Then
    copy_cat_arr = myREC.GetRows()
    DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcContents,copy_cat_arr)
    copy_cat = copy_cat_arr[0,0]
    col_cnt = myREC.Fields.Count
    header_text = ""
    col_width   = ""
    col_form    = ""
    For x = 0 to col_cnt-1
      header_text = header_text:myREC.Fields(x).Name:@TAB
      col_width   = col_width:"-2 "
      col_form    = col_width:"0 "
      y = x+1
      copy_cat_sort_ord_%y% = @ASCENDING
    Next
    header_text = ItemRemove(1,header_text,@TAB)
    col_form    = ItemReplace("2",1,col_form," ")  ;RIGHT JUSTIFY
    col_form    = ItemReplace("1",2,col_form," ")  ;CENTER
    col_form    = ItemReplace("2",3,col_form," ")  ;RIGHT JUSTIFY
    col_form    = ItemReplace("2",12,col_form," ") ;RIGHT JUSTIFY
    col_form    = ItemReplace("2",14,col_form," ") ;RIGHT JUSTIFY
  Else
    header_text = "No Rows"
    col_width   = "-2"
    col_form    = ""
    copy_cat = ""
    copy_cat_arr = ArrDimension(1,11)
    ArrInitialize(copy_cat_arr, " ")
  EndIf
  myREC.Close
  DialogControlSet(Sta_Handle,"rv_Sta_copy_cat", @dcContents, copy_cat_arr)

  DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcHeaderText,header_text)
  DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcColWidth,col_width)
  DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcColFormat,col_form)
  DialogControlSet(Sta_Handle,"rv_Sta_copy_cat",@dcSelect,copy_cat)

#EndSubRoutine
   


#DefineSubRoutine My_Connection()

  jserver   = ""
  jport     = ""
  juid      = ""
  jpwd      = ""
  jdb       = ""


;############################
; Establish the connection
;############################
  myConn = ObjectOpen('ADODB.Connection')
  myREC  = ObjectOpen('ADODB.Recordset')
  myREC.CursorLocation = 3
  myConn.ConnectionString = "Driver={MySQL ODBC 5.1 Driver}; Server=%jserver%; UID=%juid%; PWD=%jpwd%; Option=71303168; Database=%jdb%"
  myConn.CommandTimeout = 360

  adoConst = ObjectConstantsGet(myConn)

  myConn.Open

#EndSubRoutine

#DefineSubRoutine My_Connection_Close()

  myConn.Close
  ObjectClose(myREC)
  ObjectClose(myConn)

#EndSubRoutine


Return


[kdmoyers]

Appreciate this post -- I'm going to steal some ideas for sure.
Thanks!
-K

[stanl]

Jim; what do you mean "for what it's worth"... I appreciate all your responses; just hate to come off as a moocher....

To be honest... I would prefer WB over most other products, but the people who pay me want VS or PS. AS PS now has a -passthru option for their grid output, it is difficult to argue reportview.

[JTaylor]

You said you had to have your demo in this morning...I just meant I may have been to late to be of any help.

Jim

Jim; what do you mean "for what it's worth"... I appreciate all your responses; just hate to come off as a moocher....

[JTaylor]

I'm sure you both know this but for anyone else...if you don't like the header names you can change that by aliasing the column name in the select statement.  Example:

SELECT copy_order as Order, copy_requestor as Requestor, copy_requestor_id as Requestor_Id,.........


Jim

[stanl]

I'm sure you both know this but for anyone else...if you don't like the header names you can change that by aliasing the column name in the select statement.  Example:

SELECT copy_order as Order, copy_requestor as Requestor, copy_requestor_id as Requestor_Id,.........


Jim

Guess we can go off-topic a little bit.  One important point about alias with MSAccess is whenever you are  aliasing to a potential reserved word, like Date, or using spaces, the alias should be enclosed in [ ], but transferring the SQL to say MySQL, they have to be removed.

Back on topic.  ReportView did not pan out as an alternative in my situation.

[JTaylor]

Most other databases you would use double quotes in such a situation.

Jim