I'll go view those and see if they answer my question. Thanks.

Hi Peter -



This answers a lot of my questions. I like the idea of using a dataset and creating the tables from SF BOs. Are you setting your relationships for child table in the dataset itself and if so do you need to turn off any parent/child metadata in the BOs so they don't interfere?



In any case, I am convinced now that Xtrareports is the direction I'm going in in the future. I finally decided the RSS interface was more counter-intuitive than I have time for

I agree. I just don't like their interface either.

Hi Guys,



"What I mean is how are you able to drag and drop fields on your report?"

Yep. You end up with a tool bar item called XtraReports and in there is a field from which you drag and drop. BTW - this field list can extended via creating calculated fields within XtraReports. I've had no need (yet) to create custom fields in the BO.





"Are you setting your relationships for child table in the dataset itself"

Yes. The proc contains all the data that the report needs





"and if so do you need to turn off any parent/child metadata in the BOs so they don't interfere?"

Charles, I'm not sure what you mean by this. Perhaps you are referring to the DDT which we don't use.





The one thing that I really like about XtraReports is that all custom code (e.g. special sub totals, special formatting) can be written in VB.Net (our language) or C#. Very easy to leaverage those skills for reporting whereas in Crystal Reports we had to use a rather clunky scripting language. Plus form controls (such as Charts) can simply be dropped on a report.



On the down side because we use VB.Net we can't ship report updates by loading a contol file, i.e. in Crystal Reports we could change a report and simple copy the .rpt file to a customer. Now we have compile our reporting project and copy the entire exe to the customer. We thought this would be a real pain but it has proven to be a problem. If we had gone down the XtraReports scripting path, rather than VB.Net for custom code, then we could have copied a designer file in the same manner as we did with Crystal Reports.



And finally - we all know that the support from the SF guys is great - they always seem to go the extra mile. Well - DevExpress support is also excellent.



Cheers, Peter

Thanks Peter



Just to clarify my question about related tables :



In the SF example and in using RSS, we would create a business object with a BBS, and have a child table's BO as a custom property of the parent.



I seems to me that using XtraReports, 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.



My thought was defining parent/child relationships between BOs, as we would when we normally use BOs, might get in the way when the BO was used simply as a data source for a data table in a dataset that itself defined a relationship..



Have you found any reason to use SF BOs in the reporting module or do you just pull data from the backend into your datasets?

Peter, if I have a View in my database that selects the records from two tables exactly like I want them to print. What would be the steps I would use to print them using XtraReport? I'm just looking for an outline of the steps used to create the report and say put a button on a form to print the data. I use Visual Basic.



If this requires a huge reply then just tell me so and I will understand that this is too lengthy to attack all at once.

TIA.

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.

Peter thanks so much.



I think I asked my question in-artfully ...



I meant in creating a parent-child report or parent-child-child-grandchild etc rather than the denormalization of a view. So for multiple companies, to show both their employees and their orders and the details of those orders :



Company ( detail band ) has two children - employees and orders

employees ( first child detail )

orders ( second child detail )

lineitems ( child detail of orders )

etc.



Do you use 4 BO s in this case to load your datasets? And assuming that there are parent child relationships defined in your BOs, does that get in the way or is it not a problem as long as the instance BOs do not have a parent BO set?



My assumption was in the case above, no matter how I got the tables in the dataset filled, the relationships in order to create a parent child report would be set in the dataset, no?

Hi Charles,



In that simple example I would have a single, denormalised, view:



Company   Employee   Order   OrderLineItem

C1   E1   O1   OL1

C1   E1   O1   OL2

C1   E1   O1   OL3

C1   E1   O2   OL1

C1   E2   O3   OL1

C1   E2   O4   OL1

C1   E2   O4   OL2

C2   E1   O5   OL1

C2   E1   O5   OL2





The report group levels would be:



Company (GroupHeader1)

Employee (GroupHeader2)

Order (GroupHeader3)

Order Line Items (Detail Band)

Order (GroupFooter3)

Employee (GroupFooter2)

Company (GroupFooter1)



So, just a single BO based on a denormalised view



A more complex approach would to use SubReports, e.g. your Order may have a range of other attributes in addition to Order Line Items that you may want to show. In this case you may have:



Company (GroupHeader1)

Employee (GroupHeader2)

Order (GroupHeader3)

Order Line Items (Detail Band)

Order (GroupFooter3)

Order Sub Report1

Employee (GroupFooter2)

Company (GroupFooter1)



In this case Order Sub Report1 would have its own BO.



"I meant in creating a parent-child report or parent-child-child-grandchild etc rather than the denormalization of a view."

Denormalised view (see above)





"Do you use 4 BO s in this case to load your datasets?"

No. See above



"And assuming that there are parent child relationships defined in your BOs, does that get in the way or is it not a problem as long as the instance BOs do not have a parent BO set?"

No relationships. See above.



"My assumption was in the case above, no matter how I got the tables in the dataset filled, the relationships in order to create a parent child report would be set in the dataset, no?"

No. There is no need for any relationships to be defined. Just get the data into a flat file format and sort and group as needed within the report. For instance, you will see that Employee E1 took Orders for 2 different Companies therefore the same view could also be used to generate a report along the lines of:



Employee(GroupHeader1)

Company (GroupHeader2)

Order (GroupHeader3)

Order Line Items (Detail Band)

Order (GroupFooter3)

Company (GroupFooter2)

Employee (GroupFooter1)



All sorting and grouping is performed in the XtraReport.



I think the essential point is that Parent > Child > Grand Child relationships fall into place using the report Grouping hierarchy.



Cheers, Peter