StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » BBS DataRelation <> Report Sharp Shooter

BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Tue, 16 Dec 2008 09:10:00 GMT

Hi,

i am using the express version of report sharpshooter. i can create simple reports with no parent/child relations by just dropping a BBS onto the form and setting it up. Tho i dont see a possibility to set up a parent-child relationship between two bbs objects in a way that report sharpshooter understands.

any ideas ?

thanks.

RE: BBS DataRelation <> Report Sharp Shooter

Trent L. Taylor — Tue, 16 Dec 2008 09:10:00 GMT

And my previous post covers that. You can perform a query at the point of the child BO being referenced if you want to stream load instead of front load.

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Tue, 16 Dec 2008 09:07:27 GMT

Hello Trent,

i did download and look at the strataflix sample. As from my posts above you may see, that i actually implemented it just like in the strataflix sample. the question in regards to fillbyparentprimarykey was in reply to a suggestion from Ivan as how to approach this with the childbo only having the records relevant to the current parentbo record that is being rendered at that time.

RE: BBS DataRelation <> Report Sharp Shooter

Trent L. Taylor — Tue, 16 Dec 2008 08:54:41 GMT

Phillip,

You can do this and is just a design that you need to approach on your side. I have mentioned out here on forum posts, especially when dealing with reporting type of scenarios, that you can really move time to the left if you will download and look at the StrataFlix sample. It shows a great structure and format to creating reporting data sources.

Now in the StrataFlix sample, we populate all of the records for each BO at the same time and then filter out the child records through the exposed child BO property. However, you can perform a query at this point instead of applying a filter. So let's assume that you have something like this:

[codesnippet]Public Class MyParentBO

    Public Readonly Property MyChildBO() As MyChildBOType
        Get
              '-- Populate the internal MyChildBO at this point
        End Get
    End Property

End Class[/codesnippet]

If you apply this logic using the layout and format of the StrataFlix reporting samples, then you should be right on track.

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 15 Dec 2008 17:21:59 GMT

[quote][b]Ivan George Borges (12/15/2008)[/b][hr]Well, I think I haven't understood what you mean. When you det your BOs ready for printing, I assume the next step would be to run the Report. How would anything change at this time?[/quote]

My report looks like this:

1stInvoiceTheUserChecked------------------------

INVOICE# and stuff...

INVOICE POSITION 1,

INVOICE POSITION 2...

2ndInvoiceTheUserChecked------------------------

INVOICE# and stuff...

INVOICE POSITION 1,

INVOICE POSITION 2,

INVOICE POSITION 3...

and so on.

So in my ParentBO i have all the invoices the user checked for appearance on the report. In the ChildBO -currently- i have all positions of thoose checked invoices.

Instead, what i would like is that when the repot engine renders the 2nd invoice, it would trigger something on the bo, so that the child bo only ever carries the thoose child records relative to the currently rendering invoice.

I got the impression that by using FillByParentPrimaryKey as you suggested instead of using FillMultipleDataTables and then set a filter, this would be possible. But for that to work, the childbo would need to update its records once the currently-being-rendered-invoice changes.

I hope that was not too terribly explained :D

RE: BBS DataRelation <> Report Sharp Shooter

Ivan George Borges — Mon, 15 Dec 2008 15:38:45 GMT

Well, I think I haven't understood what you mean. When you det your BOs ready for printing, I assume the next step would be to run the Report. How would anything change at this time?

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 15 Dec 2008 10:40:32 GMT

on a side note, does the bo expose all properties to the report, or only thoose where i add a ReflectionPropertyDescriptor for ?

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 15 Dec 2008 10:14:14 GMT

[quote][b]Ivan George Borges (12/15/2008)[/b][hr]Hi Philipp.

Glad you got it working.

[quote]is there a possible approach to have the childbo only hold the records relative to the parent bo at any time ?[/quote]

Have you tried filling your child BO by the Parent pk, for example:

[codesnippet]

_CustomerNotes.SourceBO.FillByParentPrimaryKey(cust_pk)

[/codesnippet]

instead of filtering by the current parent?[/quote]

hi ivan, if i use fillbyparentprimarykey as you suggest, will that automaticly update ? or would i have to put some code in that once the parentbo pk changes, it would call fillbyparentprimarykey on the childbo again to update ?

thanks

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 15 Dec 2008 10:10:24 GMT

Hi, no i am not loading all child records. i am loading all child records from the parent records that are in the parentbo. in the application, a user can checkmark certain invoices. thoose checked invoices are loaded into the parentbo, and then the childbo loads all child records from thoose records in the parentbo.

RE: BBS DataRelation <> Report Sharp Shooter

Edhy Rijo — Mon, 15 Dec 2008 09:33:44 GMT

[quote][b]Dustin Taylor (12/15/2008)[/b][hr]...we go so far as to only pull back the columns that we know we are going to print, to reduce the footprint of the recordset coming back.[/quote]

Good point Dustin, I am not doing that yet, but will add it to my list to review, as well as convert those to use a Store Procedure instead.

RE: BBS DataRelation <> Report Sharp Shooter

Dustin Taylor — Mon, 15 Dec 2008 09:17:54 GMT

After reading Edhy and Ivan's reply, I realise I missed the point somewhat. I didn't realise you were pulling ALL child records, not just those applying to all included parents. Yes, by all means, attack that first to reduce the size of your record set. You should only be pulling back data that you are actually going to print. In our medical app, we go so far as to only pull back the columns that we know we are going to print, to reduce the footprint of the recordset coming back.

So ignore the above, and do what Edhy and Ivan said :D

RE: BBS DataRelation <> Report Sharp Shooter

Dustin Taylor — Mon, 15 Dec 2008 09:14:52 GMT

Yep, report sharp shooter references the BO whenever it access a property, so rather than loading all child records up front, you can put a query in your reporting BO to pull only the currently relevant child records, effectively streaming the report. However, this doesn't really save you any time since it makes a bunch of small trips that add up to more load on the server as a single large query would.

We find that the bottleneck when it comes responsiveness on large reports is in the report rendering engine rather than data retrieval. We can print reports with hundreds of pages of data, and the query will typically spit the full result sets back in less than a second. On exteremely long reports (several hundred pages or more) with complex queries (a lot of joins and conditions), it might take 5-10 seconds to recieve the SQL results at the most. It is the nature of the beast in reporting, which is why you typically use criteria with logical boundries to avoid exteremly large record sets. Large record sets tend bloat your report, stretch out the time to generate that report, and make the final product more unweildy for the end user.

RE: BBS DataRelation <> Report Sharp Shooter

Edhy Rijo — Mon, 15 Dec 2008 09:14:20 GMT

[quote][b]Philipp Guntermann (12/15/2008)[/b][hr]is there a possible approach to have the childbo only hold the records relative to the parent bo at any time ?[/quote]

Hi Philipp,

I handle that scenario by creating an SQL statement that will allow me to get the child records for the selected parent PK. Here is one of my method for a Service Call Profile report:

[codesnippet][code]

''' <summary>

''' Retrieves all of the recods that will be used within the Service Call Profile Report (rptServiceCallProfile.rst)

''' </summary>

''' <param name="PK_ServiceCalls"></param>

''' <param name="includeServiceCallRecord"></param>

''' <returns></returns>

''' <remarks></remarks>

Public Shared Function ServiceCallProfileReport_AllData(ByVal PK_ServiceCalls As Integer, ByVal includeServiceCallRecord As Boolean) As SqlCommand

'-- Establish Locals

Dim cmd As New SqlCommand()

'-- Determine if the Service Call record will be included

If includeServiceCallRecord Then

cmd.CommandText = "SELECT dbo.ServiceCalls.* FROM dbo.ServiceCalls WHERE dbo.ServiceCalls.PK_ServiceCalls = @PK_ServiceCalls;"

End If

'-- Add the Service Call Appliances query for the selected Service Call PK, ordered by the Appliance Service Date.

cmd.CommandText &= "SELECT dbo.SC_Appliances.* " & _

"FROM dbo.SC_Appliances " & _

"LEFT JOIN dbo.ServiceCalls ON (dbo.ServiceCalls.PK_ServiceCalls = dbo.SC_Appliances.FK_ServiceCalls) " & _

"WHERE (dbo.ServiceCalls.PK_ServiceCalls = @PK_ServiceCalls)" & _

"ORDER BY dbo.SC_Appliances.ServicedDate;"

'-- Add the Service Call Appliance items for the selected service all PK.

cmd.CommandText &= "SELECT dbo.SC_AppliancesItems.* FROM dbo.SC_AppliancesItems " & _

"LEFT JOIN dbo.SC_Appliances ON (dbo.SC_AppliancesItems.FK_SC_Appliances = dbo.SC_Appliances.PK_SC_Appliances) " & _

"LEFT JOIN dbo.ServiceCalls ON (dbo.SC_Appliances.FK_ServiceCalls = dbo.ServiceCalls.PK_ServiceCalls) " & _

"WHERE (dbo.ServiceCalls.PK_ServiceCalls = @PK_ServiceCalls)" & _

"ORDER BY dbo.SC_AppliancesItems.ItemType, dbo.SC_AppliancesItems.ServicedDate;"

'-- Create the parms

cmd.Parameters.AddWithValue("@PK_ServiceCalls", PK_ServiceCalls).SqlDbType = SqlDbType.Int

'-- Return the results

Return cmd

End Function

[/code][/codesnippet]

Hope this help you out!

RE: BBS DataRelation <> Report Sharp Shooter

Ivan George Borges — Mon, 15 Dec 2008 09:09:09 GMT

Hi Philipp.

Glad you got it working.

[quote]is there a possible approach to have the childbo only hold the records relative to the parent bo at any time ?[/quote]

Have you tried filling your child BO by the Parent pk, for example:

[codesnippet]

_CustomerNotes.SourceBO.FillByParentPrimaryKey(cust_pk)

[/codesnippet]

instead of filtering by the current parent?

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 15 Dec 2008 08:30:32 GMT

got it working now :hehe:

tho what i dont like is, that the child bo holds all records from all parentbo datasets. could become an issue when working with

alot of data. is there a possible approach to have the childbo only hold the records relative to the parent bo at any time ?

i am not sure wether report sharp shooter acutally communicates back to the bo, so that one could use the event when the current row changes to 'dynamically' fill the childbo with only the relevant data ?

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Fri, 12 Dec 2008 02:03:28 GMT

Sorry, but i need to warm up this topic again. Kinda saw it comming tho :)

Since the structural part is now in place, i need to actually fill the BO's. From the StrataFlix Sample i got the impression that i need to use FillMultipleDataTables for this purpose.

In my application i have a list of double which are the primary keys of the ParentBO datasets.

So i could do something like this:

[codesnippet]

string SQLRechkopf, SQLRechpos;

SQLRechkopf = "SELECT * FROM Rechkopf WHERE 1=1";

foreach (double _ix in _indexes)

{

SQLRechkopf += " AND (ID = " + _ix.ToString() + ")";

}

SQLRechpos = "SELECT * FROM Rechpositionen WHERE 1=1";

foreach (double _ix in _indexes)

{

SQLRechpos += " AND (RechkopfID = " + _ix.ToString() + ")";

}

System.Data.Common.DbCommand dbc = new System.Data.SqlClient.SqlCommand();

dbc.CommandText = SQLRechkopf + ";" + SQLRechpos;

[/codesnippet]

and then

[codesnippet]

MicroFour.StrataFrame.Business.BusinessLayer.FillMultipleDataTables(dbc,ParentBO,ChildBO);

[/codesnippet]

i have no idea where to put this code tho (in the ParentReportBO, in the BuisnessObjects.ParentBO, in any of the BBS ???). Also putting ParentBO and ChildBO in FillMultipleDataTables doesnt seem to work either.

RE: BBS DataRelation <> Report Sharp Shooter

Trent L. Taylor — Tue, 09 Dec 2008 09:36:00 GMT

LOL....you guys need to tell my WIFE that :D When I am sitting on the couch watching football I don't get the same kudos :w00t:

Let me know if are still stuck, Phillip. :)

RE: BBS DataRelation <> Report Sharp Shooter

Ivan George Borges — Mon, 08 Dec 2008 14:09:29 GMT

Hey, don't give up his secret identity!!! :smooooth:

RE: BBS DataRelation <> Report Sharp Shooter

Edhy Rijo — Mon, 08 Dec 2008 13:35:08 GMT

[quote][b]Ivan George Borges (12/08/2008)[/b][hr]If I mixed all up, I'm sure SuperMan will jump in and make things right. :D[/quote]

Hummm, guess you meant SuperTrent :)

RE: BBS DataRelation <> Report Sharp Shooter

Ivan George Borges — Mon, 08 Dec 2008 10:09:44 GMT

You're welcome Philipp, great you got it working! :cool:

Your steps sound good. Creating an XYZReportBO is just for convinience, so if you need to add properties or make any customization exclusive for the report's sake, you won't be messing with your original BO.

As for the Reflection Property Descriptor, you are adding a property descriptor to your parent BO, so that is why you will need to make it a typeof(YourCurrentParentBO).

You might be confusing it because the property in question is of the type of your ChildBO, but it could be an Integer, a Boolean or whatever, and when you added the custom descriptor, you would still add the descriptor as the type of YourCurrentParentBO.

If I mixed all up, I'm sure SuperMan will jump in and make things right. :D

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 08 Dec 2008 09:17:13 GMT

ok, i got it working now by building it from scratch again and comparing to your corrected code.

Not sure tho, if i fully understand the procedure. And since i'll have to explain that to someone in future, i'am trying to summarize:

1) Creation of a "ParentBO" and a "ChildBO". For convenience put in a Namespace called "BuisnessObjects".

2) Mapping of the BO's in the BOM.

3) Creation of a BuissnessBindingSource for each BO using the CustomBuisnessBindingSource, for convenience put in a Namespace called "ReportBinding" or something like that.

4) Creation of a BuisnessObject based upon ParentBO but with a different Name (for example: "ParentReportBO" using the CustomBuisnessBindingSource put in the "ReportBinding" Namespace.

5) Correcting the Name in "ParentReportBO", because the CustomBuisnessBindingSource created code by default puts the name of "ParentBO".

6) Changing of ParentBBS so it inherits from "ParentReportBO" instead of "ParentBO". Also adding

[System.ComponentModel.ToolboxItem(true)], so it appears within the toolbox.

7) Adding the Custom Property to expose "ChildBBS" to "ParentReportBO" and adding a 'Reflection Property" for "ChildBBS", for some reason (that i dont really get) of type "ParentReportBO" instead of "ChildBO".

8) Making sure "ChildBBS" is public.

9) Adding of the "ParentBBS" from the Toolbox to the form and using that as source for the ReportManager.

RE: BBS DataRelation <> Report Sharp Shooter

Trent L. Taylor — Mon, 08 Dec 2008 09:12:10 GMT

WOW...long post. I will let Ivan respond. Let me know if you have any other issues. ;)

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 08 Dec 2008 08:29:11 GMT

Hi Ivan,

first off, thanks alot, this looks as intended. Can u provide some details of what you actually changed ? For example how you were able to rename RechnungenBO.cs to RechnungenReportBO.cs without breaking everything ?

I will try to create it from scratch again. I really need to know the way of getting there.

I used CustomBuisnessBindingSource to create the BBS and BO('s) within the ReportBinding namespace.

Thanks alot for your help.

RE: BBS DataRelation <> Report Sharp Shooter

Ivan George Borges — Mon, 08 Dec 2008 08:10:23 GMT

Hi there.

I'm facing some difficulties over here, since I don't speak neither German nor C#. :hehe:

But I got to make your project work. Had to delete the instance of your RechungenBBS from the form, as there was some error there, I guess with namespaces or such. Also repaired some code and inheritance of BO, and it looks like working now:

RE: BBS DataRelation <> Report Sharp Shooter

Philipp Guntermann — Mon, 08 Dec 2008 07:13:15 GMT

[quote][b]Ivan George Borges (12/08/2008)[/b][hr]It looks like you forgot to override the AreCustomerDescriptorsEvaluated property:

[codesnippet]

Private Shared _AreCustomDescriptorsEvaluated As Boolean = False

[/codesnippet]

[codesnippet]

/// <summary>
/// Determine if the custom descriptors are going to be evaluated
/// </summary>
/// <remarks></remarks>
    protected override bool AreCustomDescriptorsEvaluated {
    get {
        //-- Establish Locals
        bool r = _AreCustomDescriptorsEvaluated;

        //-- Do not eval the desciptors again
        _AreCustomDescriptorsEvaluated = true;

        //-- Return results
        return r;
    }
}

[/codesnippet][/quote]

Actually i allready have that:

[codesnippet]

namespace ReportSS2.ReportBinding

{

public class RechnungenBO : ReportSS2.RechnungenBO

{

#region Private Fields

private static bool _AreCustomDescriptorsEvaluated = false;

private static RechpositionenBBS _Rechpositionen = new RechpositionenBBS();

#endregion

#region Protected Methods

/// <summary>

/// Determine if the custom descriptors are going to be evaluated

/// </summary>

/// <remarks></remarks>

protected override bool AreCustomDescriptorsEvaluated

{

get

{

//-- Establish Locals

bool r = _AreCustomDescriptorsEvaluated;

//-- Do not eval the desciptors again

_AreCustomDescriptorsEvaluated = true;

//-- Return results

return r;

}

[/codesnippet]