When using SF we have the BindingField which is based on the BusinessObject property of the control, sometimes I have controls which I would like to bind to a new form's property and no business object, so I can reference this property instead of the control itself.



In this case if I use a SF control, can still use its BindingField property with not BusinessObject? or should I just add a regular property and assign the control value in the GET/SET?

Edhy Rijo

Hi Guys,



"I would want to creae datasets that defined the relationship between tables and then simply fill the tables - either directly from sprocs on the back end or using the datatable of a BO."



The "defined relationships" are simply as choose the do so in the view. For instance the following view is used for a report:



CREATE View vw_Rpt0002HPCProdSummary



AS



SELECT tblSTAStations.STAStnType, tblBTCBatch.BTCBatch,

tblCMPCompany_1.CMPRegName + '/' + tblCMPCompany_2.CMPRegName AS AbattoirSupplier, tblSZGSizeGroup.SZGSequ, tblHGRHideGrades.HGRSequ,

tblSZRSizeRanges.SZRSize, 1 AS HideCount, tblHPCHidePiece.HPCDateTime, tblHPCHidePiece.HPCHidePart, tblHPCHidePiece.HPCWght,

tblHPCHidePiece.HPCWghtSize, tblHPCHidePiece.HPCArea, tblHPCHidePiece.HPCAreaSize, tblHPCHidePiece.HPCSubst, tblHPCHidePiece.HPCSubstSize,

tblSTAStations.STAName + '/' + tblSTRStationTerm.STRName AS Station, tblSTAStations.STAID, tblSTRStationTerm.STRID, tblHGRHideGrades.HGRName,

tblBTCBatch.BTCType, tblHPCHidePiece.HPC_CMPIDOperator, tblHPCHidePiece.HPC_CMPIDAbattoir, tblHPCHidePiece.HPC_CMPIDSupplier,

tblHPCHidePiece.HPC_CMPIDGrower, tblHPCHidePiece.HPC_CMPIDTannery, tblCMPCompany.CMPRegName AS OperatorName,

tblCMPCompany_1.CMPRegName AS AbattoirName, tblCMPCompany_2.CMPRegName AS SupplierName, tblCMPCompany_3.CMPRegName AS GrowerName,

tblCMPCompany_4.CMPRegName AS TanneryName, tblSZGSizeGroup.SZGName, tblSZRSizeRanges.SZRRangeDesc, tblINIInvItems.INIID,

tblINIInvItems.INISequ, tblINIInvItems.INIProdCode + '/' + tblINIInvItems.ININame AS INIDescription, tblHPCHidePiece.HPCID AS PKID, 0 AS CtrlShowSuppliers,

0 AS CtrlShowBatches, 0 AS CtrlShowStations, tblINIInvItems.INI_SPCID, tblSZRSizeRanges.SZRMax, dbo.GetEnumNameSizeTypes(tblSTAStations.STAStnType) As StationTypeName

FROM tblHPCHidePiece INNER JOIN

tblHGRHideGrades ON tblHPCHidePiece.HPC_HGRID = tblHGRHideGrades.HGRID INNER JOIN

tblSZRSizeRanges ON tblHPCHidePiece.HPC_SZRIDWght = tblSZRSizeRanges.SZRID INNER JOIN

tblSZGSizeGroup ON tblSZRSizeRanges.SZR_SZGID = tblSZGSizeGroup.SZGID INNER JOIN

tblSTRStationTerm ON tblHPCHidePiece.HPC_STRID = tblSTRStationTerm.STRID INNER JOIN

tblSTAStations ON tblSTRStationTerm.STR_STAID = tblSTAStations.STAID INNER JOIN

tblCMPCompany ON tblHPCHidePiece.HPC_CMPIDOperator = tblCMPCompany.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_1 ON tblHPCHidePiece.HPC_CMPIDAbattoir = tblCMPCompany_1.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_2 ON tblHPCHidePiece.HPC_CMPIDSupplier = tblCMPCompany_2.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_3 ON tblHPCHidePiece.HPC_CMPIDGrower = tblCMPCompany_3.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_4 ON tblHPCHidePiece.HPC_CMPIDTannery = tblCMPCompany_4.CMPID INNER JOIN

tblBTCBatch ON tblHPCHidePiece.HPC_BTCID = tblBTCBatch.BTCID INNER JOIN

tblPUNPackingUnit ON tblHPCHidePiece.HPC_PUNID = tblPUNPackingUnit.PUNID INNER JOIN

tblINIInvItems ON tblPUNPackingUnit.PUN_INIID = tblINIInvItems.INIID



It has lots of relationships. The actual stored procedure that the report executes to gather the data is a lot more complex but presents the same data as the view:





CREATE Procedure [dbo].[stp_Rpt0002HPCProdSummary]



@CMPIDTannery As Uniqueidentifier = Null,

@CMPIDOperator As Uniqueidentifier = Null,

@CMPIDAbattoir As Uniqueidentifier= Null,

@CMPIDSupplier As Uniqueidentifier = Null,

@CMPIDGrower As Uniqueidentifier = Null,

@SPCID As Uniqueidentifier = Null,

@STRID As Uniqueidentifier = Null,

@STRType As Integer = Null,

@INIID As Uniqueidentifier = Null,

@HGRID As Uniqueidentifier = Null,

@AbLotNo As nvarChar(500) = Null,

@Batches As nvarChar(500) = Null,

@Contract As nvarChar(500) = Null,

@ToDate As DateTime,

@FromDate As DateTime,

@FromKillDate As DateTime = Null,

@ToKillDate As DateTime = Null,

@CtrlShowSuppliers As Integer = 1,

@CtrlShowBatches As Integer = 1,

@CtrlShowStations As Integer = 1





AS





Declare @StartOfBatchTO As Integer

Declare @FirstBatch As nvarChar(500)

Declare @LastBatch As nvarChar(500)

Declare @LengthOfBatch As Integer

Declare @BatchSelectType As Integer

Declare @StartOfAbLotNoTO As Integer

Declare @FirstAbLotNo As nvarChar(500)

Declare @LastAbLotNo As nvarChar(500)

Declare @LengthOfAbLotNo As Integer

Declare @AbLotNoType As Integer

Declare @myCtrlShowSuppliers As Integer

Declare @myCtrlShowBatches As Integer

Declare @myCtrlShowStations As Integer





-- Make sure the hide/unhide controls are sensible



Set @myCtrlShowSuppliers = @CtrlShowSuppliers

Set @myCtrlShowBatches = @CtrlShowBatches

Set @myCtrlShowStations = @CtrlShowStations









-- Configure selection criteria for those controls that can multiple, comma delimitered, entries

Set @BatchSelectType = 0

Set @AbLotNoType = 0





If @Batches Is Not NULL

   Begin

      Set @BatchSelectType = 1

      Set @LengthOfBatch = Len(@Batches)

      

      Set @StartOfBatchTO = CHARINDEX(' TO ',@Batches)



      -- Found TO so lets extract the From/To values

      IF @StartOfBatchTO > 0

         Begin

            Set @BatchSelectType = 2

            Set @FirstBatch = Left(@Batches, (@StartOfBatchTO - 1))

            Set @LastBatch = Right(@Batches, (@LengthOfBatch - @StartOfBatchTO) - 3)

         End

   End



If @AbLotNo Is Not NULL

   Begin

      Set @AbLotNoType = 1

      Set @LengthOfAbLotNo = Len(@AbLotNo)

      

      Set @StartOfAbLotNoTO = CHARINDEX(' TO ',@AbLotNo)



      -- Found TO so lets extract the From/To values

      IF @StartOfAbLotNoTO > 0

         Begin

            Set @AbLotNoType = 2

            Set @FirstAbLotNo = Left(@AbLotNo, (@StartOfAbLotNoTO - 1))

            Set @LastAbLotNo = Right(@AbLotNo, (@LengthOfAbLotNo - @StartOfAbLotNoTO) - 3)

         End

   End



-- Read Committed assumes the database is has READ_COMMITTED_SNAPSHOT set to ON.

Set Transaction Isolation Level Read Committed

Begin Tran



--SELECT TOP (100) PERCENT tblSTAStations.STAStnType, tblBTCBatch.BTCBatch,



SELECT TOP (100) PERCENT tblSTAStations.STAStnType,



            CASE @myCtrlShowBatches

               WHEN 1 THEN tblBTCBatch.BTCBatch

               WHEN 0 THEN '**'

               END AS BTCBatch,

               

tblCMPCompany_1.CMPRegName + '/' + tblCMPCompany_2.CMPRegName AS AbattoirSupplier, tblSZGSizeGroup.SZGSequ, tblHGRHideGrades.HGRSequ,

tblSZRSizeRanges.SZRSize, 1 AS HideCount, tblHPCHidePiece.HPCDateTime, tblHPCHidePiece.HPCHidePart, tblHPCHidePiece.HPCWght,

tblHPCHidePiece.HPCWghtSize, tblHPCHidePiece.HPCArea, tblHPCHidePiece.HPCAreaSize, tblHPCHidePiece.HPCSubst, tblHPCHidePiece.HPCSubstSize,

Case @CtrlShowStations

               WHEN 1 Then tblSTAStations.STAName + '/' + tblSTRStationTerm.STRName

               WHEN 0 Then '**'

               END AS Station,

               

                   tblSTAStations.STAID, tblSTRStationTerm.STRID, tblHGRHideGrades.HGRName,

tblBTCBatch.BTCType, tblHPCHidePiece.HPC_CMPIDOperator, tblHPCHidePiece.HPC_CMPIDAbattoir, tblHPCHidePiece.HPC_CMPIDSupplier,

tblHPCHidePiece.HPC_CMPIDGrower, tblHPCHidePiece.HPC_CMPIDTannery, tblCMPCompany.CMPRegName AS OperatorName,

tblCMPCompany_1.CMPRegName AS AbattoirName, tblCMPCompany_2.CMPRegName AS SupplierName, tblCMPCompany_3.CMPRegName AS GrowerName,

tblCMPCompany_4.CMPRegName AS TanneryName, tblSZGSizeGroup.SZGName, tblSZRSizeRanges.SZRRangeDesc, tblINIInvItems.INIID,

tblINIInvItems.INISequ, tblINIInvItems.INIProdCode + '/' + tblINIInvItems.ININame AS INIDescription, tblHPCHidePiece.HPCID AS PKID,

@myCtrlShowSuppliers As CtrlShowSuppliers, @myCtrlShowBatches As CtrlShowBatches, @myCtrlShowStations As CtrlShowStations,

tblINIInvItems.INI_SPCID, tblSZRSizeRanges.SZRMax, dbo.GetEnumNameStationTypes(tblSTAStations.STAStnType) As StationTypeName





FROM tblHPCHidePiece INNER JOIN

tblHGRHideGrades ON tblHPCHidePiece.HPC_HGRID = tblHGRHideGrades.HGRID INNER JOIN

tblSZRSizeRanges ON tblHPCHidePiece.HPC_SZRIDWght = tblSZRSizeRanges.SZRID INNER JOIN

tblSZGSizeGroup ON tblSZRSizeRanges.SZR_SZGID = tblSZGSizeGroup.SZGID INNER JOIN

tblSTRStationTerm ON tblHPCHidePiece.HPC_STRID = tblSTRStationTerm.STRID INNER JOIN

tblSTAStations ON tblSTRStationTerm.STR_STAID = tblSTAStations.STAID INNER JOIN

tblCMPCompany ON tblHPCHidePiece.HPC_CMPIDOperator = tblCMPCompany.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_1 ON tblHPCHidePiece.HPC_CMPIDAbattoir = tblCMPCompany_1.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_2 ON tblHPCHidePiece.HPC_CMPIDSupplier = tblCMPCompany_2.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_3 ON tblHPCHidePiece.HPC_CMPIDGrower = tblCMPCompany_3.CMPID INNER JOIN

tblCMPCompany AS tblCMPCompany_4 ON tblHPCHidePiece.HPC_CMPIDTannery = tblCMPCompany_4.CMPID INNER JOIN

tblBTCBatch ON tblHPCHidePiece.HPC_BTCID = tblBTCBatch.BTCID INNER JOIN

tblPUNPackingUnit ON tblHPCHidePiece.HPC_PUNID = tblPUNPackingUnit.PUNID INNER JOIN

tblINIInvItems ON tblPUNPackingUnit.PUN_INIID = tblINIInvItems.INIID



-- NOTE - it may well be wrong to exclude Regrade Ins (ERPProdType 4)

WHERE tblHPCHidePiece.HPCDateTime BETWEEN @FromDate AND @ToDate

            AND

         tblHPCHidePiece.HPCERPProdType <> 3

            AND

         tblHPCHidePiece.HPCERPProdType <> 4

            AND

         tblHPCHidePiece.HPCERPProdType <= 6

            AND

         ((@CMPIDTannery Is Null) OR (tblHPCHidePiece.HPC_CMPIDTannery = @CMPIDTannery))

            AND

         ((@CMPIDOperator Is Null) OR (tblHPCHidePiece.HPC_CMPIDOperator = @CMPIDOperator))

            AND

         ((@CMPIDAbattoir Is Null) OR (tblHPCHidePiece.HPC_CMPIDAbattoir = @CMPIDAbattoir))

            AND

         ((@CMPIDSupplier Is Null) OR (tblHPCHidePiece.HPC_CMPIDSupplier = @CMPIDSupplier))

            AND

         ((@CMPIDGrower Is Null) OR (tblHPCHidePiece.HPC_CMPIDGrower = @CMPIDGrower))

            AND

         ((@HGRID Is Null) OR (tblHPCHidePiece.HPC_HGRID = @HGRID))

            AND

         ((@STRID Is Null) OR (tblHPCHidePiece.HPC_STRID = @STRID))

            AND

         ((@FromKillDate Is Null Or @ToKillDate Is Null) OR (tblHPCHidePiece.HPCAbKillDate Between @FromKillDate AND @ToKillDate))

            AND

         ((@SPCID Is Null) OR (tblINIInvItems.INI_SPCID = @SPCID))

            AND

         ((@INIID Is Null) OR (tblINIInvItems.INIID = @INIID))

            AND

         ((@STRType Is Null) OR (tblSTAStations.STAStnType = @STRType))

            AND

         ((@BatchSelectType = 0)

            OR

            (@BatchSelectType = 1 AND CHARINDEX(tblBTCBatch.BTCBatch + ',',@Batches) > 0)

            OR

            (@BatchSelectType = 2 AND tblBTCBatch.BTCBatch Between @FirstBatch And @LastBatch))

            AND

         ((@Contract Is Null) OR (tblBTCBatch.BTCContract = @Contract))

            AND

         ((@AbLotNoType = 0)

            OR

            (@AbLotNoType = 1 AND CHARINDEX(tblHPCHidePiece.HPCAbLotNo + ',',@AbLotNo) > 0)

            OR

            (@AbLotNoType = 2 AND tblHPCHidePiece.HPCAbLotNo Between @FirstAbLotNo And @LastAbLotNo))                  



            

Option (Recompile)



Commit Tran



Keep in mind the view is only there so we can generate a BO. In terms of defining a XtraReport it isn't needed because in its designer it is using a standard Visual Studio Data Source which can be created via many means including a stored procedure. We use a SF BO because we can use our existing DAL code to populate it, i.e. from our perspective 'no extra coding'.





"might get in the way when the BO was used simply as a data source"



Agreed.







"Have you found any reason to use SF BOs in the reporting module "



Yes. Please see above - we can leverage our existing DAL code that is why we use SF BO's.





"I want them to print. What would be the steps I would use to print them using XtraReport?"



1) Create a Data Source that uses the view.



2) Assign the Data Source to your report. DexExpress has something it calls (hmm, I forget what its called!). Anyway its a small > symbol in a box - it opens up special facilities for the control (report, form, tab, etc..). For a report one of the things it exposes is the data source for the report. Just click on the Data Source pull down list and you can see all your data souces - simply select the one you want.



3) Under XtraReport on the toolbar you will find Group and Sort. Here you enter the what groupings you want and any special sorting. As you select groups they are automatically created in the body of the report. Each group must have a header but a footer is optional. Note: all sorting must be carried out within the report itself. You cannot rely on data being processed in the correct order just because it was sorted correctly in the stored procedure. This cause me a bit of grief for a time...



4) Drop the fields you want into the group and detail section. For instance assume your view contains Order and Line Items. Just drop the Order fields into the group header and the Line Item details into the report Details.



That's basically it.





"put a button on a form to print the data. I use Visual Basic"



Ah, we don't do this. We have a specific reporting module with its own menu however doing this is pretty straight forward. I know this from asking questions of DevExpress and they regularly include a sample project with their reply. Check out http://www.devexpress.com/Support/Center/default.aspx. You can download a fully functional version of DevXpress on a 60 day trial basis to check out these samples. This is from one of the samples they sent me:



Button on a form to open a report. XtraReport1 is XtraReport in the project.



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim r As New XtraReport1

r.ShowPreviewDialog()

End Sub





The code in the report was as follows. It explained how I could custom sort pivot grid columns in the report. I only show this just to demonstrate everything in the report is VB.Net.



Public Class XtraReport1



Private Sub XrPivotGrid3_CustomFieldSort(ByVal sender As System.Object, ByVal e As DevExpress.XtraPivotGrid.PivotGridCustomFieldSortEventArgs) Handles XrPivotGrid3.CustomFieldSort

Dim val1 As String = DataSet11.Tables(0).Rows(e.ListSourceRowIndex1)("ColA").ToString()

Dim val2 As String = DataSet11.Tables(0).Rows(e.ListSourceRowIndex2)("ColA").ToString()

Dim result As Integer = String.Compare(val1, val2)

If (result > 0) Then

e.Result = 1

ElseIf (result < 0) Then

e.Result = -1

Else

e.Result = 0

End If

e.Handled = True

'e.ListSourceRowIndex1

End Sub



Private Sub XtraReport1_BeforePrint(ByVal sender As System.Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles MyBase.BeforePrint

DataSet11.Tables(0).Rows.Add(New Object() {"A", "Z", 1})

DataSet11.Tables(0).Rows.Add(New Object() {"B", "Y", 1})

DataSet11.Tables(0).Rows.Add(New Object() {"C", "X", 1})

End Sub

End Class





Hope this helps.



Cheers, Peter





Cheers, Peter

Wow. Peter that is one Great Answer. Thank you. It will take me some time to digest all of that but that will certainly get me started. Thanks again for all of the time you put into helping others.