Hello, all. A little off-topic stuff for you today. I'm starting on the reporting end of the application I'm working on at the moment. I'm looking for some best practices as it relates to developing platform-independent reporting. Here's what I'd like to see:

1. Fast
2. Ability to expose data sources to the public for end-user modification/generation
3. Portable (no stored procedures?)
4. I'd love to be able to use LLBLGen Pro as our primary data source/data access method.

Given that I'd like to be able to keep the application as platform independent as possible, I don't think sprocs are gonna work. However, without sprocs, the only way to get summary information WITH parameters is to create views and summarize the information inside the report itself - that or perform summary information on an LLBLGen typelist/view, both of which require all the detail information to traverse the wire.

Another alternative is to create a server component that lives on the database server and performs the db operations necessary for each report and somehow serializes the information back to the client for display. This perhaps is a bit too complex for the ROI.

I'm interested in anyone elses thoughts on the subject, or perhaps some direction as to where I can read up a bit more on the subject. My google groups searches haven't turned up much, althought I'm still looking.

Thanks in advance!

Jeff...

What do you mean by platform-independent? it should run on linux as well, or should it run on oracle, access and work with xml files as well?

Reporting is hard, is my experience. The hard part is that it is so custom made, that it requires so much unique code you are spending a lot of time on it while it shouldn't be that time consuming.

Which reporting tool are you going to use? Html pages? (which can be run anywhere) or are you going to use Crystal Reports (Aaaaaaarg) ?

You can of course keep your data source engine on a box as a webserver/remoting host and target that service/remoted service as the source for your report data. You then can write a thin layer for the report render code on top of that. However if that's platform independent enough I don't know. If you have to view the reports on as much devices as possible, I'd go for an ASP.NET renderer. For the BL code and db accessing code, platform independence is talk for managers but is not workable as a project demand.

Hey...yea, I'm thinking you're last statement is holding true. When I said platform independent, I meant data-source independent, i.e. Oracle. However, I can't think if a single, simple way to get what I want, so I think we're going to have to sacrifice portability and go with stored procedures. Its just the solution with the least question marks, albeit with one huge shortcoming. Thanks.

BTW, we're checking out Active Reports by Data Dynamics. Seems very simple to use, and yet powerful. Plus, it doesn't have licensing requirements for distributing a custom end-user report designer, which is nice. I hate Crystal, too. Who doesn't?

Jeff...

jeffreygg wrote:

Hey...yea, I'm thinking you're last statement is holding true. When I said platform independent, I meant data-source independent, i.e. Oracle. However, I can't think if a single, simple way to get what I want, so I think we're going to have to sacrifice portability and go with stored procedures. Its just the solution with the least question marks, albeit with one huge shortcoming. Thanks.

I don't think you should go for stored procs. Reporting is something that is to be flexible, you have to do searches for a lot of random parameters, you can't have that implemented flexibly with stored procs. You can implement it with LLBLGen Pro code. With self servicing templates you can keep the entity names the same on oracle and sqlserver and you can write software which can target sqlserver and oracle without a lot of effort. (with adapter you can do this even more easily). The thing is: you can then formulate any query in your code which can be a create time saver. You can also create views for special calculations which you filter on in queries. This makes the stuff more flexible to build. Stored procs is not that flexible at all, and you probably end up writing a lot of stored procs.

BTW, we're checking out Active Reports by Data Dynamics. Seems very simple to use, and yet powerful. Plus, it doesn't have licensing requirements for distributing a custom end-user report designer, which is nice. I hate Crystal, too. Who doesn't?

I know active reports from hte ancient vb6 days and it was a great relief that it was released so we didn't have to use the dreaded crystal crap. (Crystal is still a pain, I tried it lately, the .net version, and it had me fighting with 4 lines of code for hours ). Still I've never seen any reporting tool with the power of the MS Access report generator.

Hi frans,

I'm jumping into this thread as this is the main reason I initially purchased your product. Was wondering if you had samples of how to generate a flexible reporting architeture. In a conversation we had via email, I believe you stated that you were using llblgen Pro to create a dynamic search form where users could create a search by adding controls. In my case, I have several controls(filter by date, owner, project, etc) that need to be chained together. Could you provide a sample as to how you accomplished this? This would be really cool as using DQE rather than stored procedures is, I believe, the way to go for report generation.

Thanks,

Eric

That would be great.. In every application you want to have a flexible searchform..

For example (activex / delphi component):

More information: http://www.korzh.com/delphi/sq/

Marco

ps. just picked an example. there are more components like this, but i haven't found a .NET implementation (yet )

erichar11 wrote:

I'm jumping into this thread as this is the main reason I initially purchased your product. Was wondering if you had samples of how to generate a flexible reporting architeture. In a conversation we had via email, I believe you stated that you were using llblgen Pro to create a dynamic search form where users could create a search by adding controls. In my case, I have several controls(filter by date, owner, project, etc) that need to be chained together. Could you provide a sample as to how you accomplished this? This would be really cool as using DQE rather than stored procedures is, I believe, the way to go for report generation.

The code is for a customer so I can't post the full code here, I can however explain briefly how I did it. The key is that you see the visualization as a separate part of the search expression. Example screenshot:

So you build up the search expression and then render that expression. In other words: your elements inside the expression have representing controls which alter these elements. Your renderer picks up these elements and based on the info inside these elements, renders the expression. The form itself anticipates on what the user does: so if you select a textfield, the operator list possible is different than when you chose a datetime field. A search criterium class is the element every row in the search form is build with: (I altered the entity type )

private class SearchCriterium
{
    public ComboBox FieldsComboBox;
    public ComboBox OperatorsComboBox;
    public Control ValueControl;
    public FieldValueType TypeOfValue;
    public ComboBox CriteriumOperatorsComboBox;
    public EntityTypeToSearchForFieldIndex EntityFieldIndex;
}

After the expression is build with the editor, it is converted into a set of predicate expressions and used to retrieve the data. I altered hte entity name.

/// <summary>
/// Builds a predicate expression and retrieves the entities using that filter and shows them in the grid.
/// </summary>
private void DoSearch()
{
    _resultsDataGrid.DataSource = null;
    
    // build query
    IPredicateExpression filter = new PredicateExpression();
    CriteriumOperator currentCriteriumOperator = CriteriumOperator.And;

    for (int i = 0; i < _maxAmountCriteria; i++)
    {
        if(_criteria[i]==null)
        {
            // no more criteria specified
            break;
        }

        IPredicate predicateToAdd = null;
        Operator currentOperator = (Operator) (((DictionaryEntry)_criteria[i].OperatorsComboBox.SelectedItem).Key);
        if(_criteria[i].TypeOfValue==FieldValueType.DateTime)
        {
            
            switch(currentOperator)
            {
                case Operator.Equal:
                    predicateToAdd = PredicateFactory.Between(_criteria[i].EntityFieldIndex, GetCriteriumValue(_criteria[i], true), GetCriteriumValue(_criteria[i], false));
                    break;
                case Operator.Greater:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.GreaterThan, GetCriteriumValue(_criteria[i], false));
                    break;
                case Operator.GreaterEqual:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.GreaterEqual, GetCriteriumValue(_criteria[i], true));
                    break;
                case Operator.Less:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.LesserThan, GetCriteriumValue(_criteria[i], true));
                    break;
                case Operator.LessEqual:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.LessEqual, GetCriteriumValue(_criteria[i], false));
                    break;
                case Operator.NotEqual:
                    predicateToAdd = PredicateFactory.Between(_criteria[i].EntityFieldIndex, GetCriteriumValue(_criteria[i], true), GetCriteriumValue(_criteria[i], false), true);
                    break;
            }
        }
        else
        {
            switch(currentOperator)
            {
                case Operator.Equal:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.Equal, GetCriteriumValue(_criteria[i]));
                    break;
                case Operator.Greater:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.GreaterThan, GetCriteriumValue(_criteria[i]));
                    break;
                case Operator.GreaterEqual:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.GreaterEqual, GetCriteriumValue(_criteria[i]));
                    break;
                case Operator.Less:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.LesserThan, GetCriteriumValue(_criteria[i]));
                    break;
                case Operator.LessEqual:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.LessEqual, GetCriteriumValue(_criteria[i]));
                    break;
                case Operator.Like:
                    predicateToAdd = PredicateFactory.Like(_criteria[i].EntityFieldIndex, _criteria[i].ValueControl.Text);
                    break;
                case Operator.NotEqual:
                    predicateToAdd = PredicateFactory.CompareValue(_criteria[i].EntityFieldIndex, ComparisonOperator.Equal, GetCriteriumValue(_criteria[i]), true);
                    break;
                case Operator.NotLike:
                    predicateToAdd = PredicateFactory.Like(_criteria[i].EntityFieldIndex, _criteria[i].ValueControl.Text, true);
                    break;
            }
        }

        if(i>0)
        {
            if(currentCriteriumOperator == CriteriumOperator.And)
            {
                filter.AddWithAnd(predicateToAdd);
            }
            else
            {
                filter.AddWithOr(predicateToAdd);
            }
        }
        else
        {
            filter.Add(predicateToAdd);
        }

        currentCriteriumOperator = (CriteriumOperator) (((DictionaryEntry)_criteria[i].CriteriumOperatorsComboBox.SelectedItem).Key);
    }

    MyEntityCollection searchResults = new MyEntityCollection ();
    searchResults.GetMulti(filter);

    // bind to grid
    _resultsDataGrid.DataSource = searchResults;
    _resultsDataGrid.CaptionText = String.Format("Aantal resultaten gevonden: {0}", searchResults.Count);
}

This should get you get started. It took me a day to write this from scratch. Key is, again, to separate the expression to build from the visual representation. It requires a set of methods to get from a SearchCriterium to a row on the screen, but that's not that hard. I used a panel to lay out the controls, which are simply rendered in this routine: (I modified the name of the entity. As you can see I cache objects, so a drop down list with values read from a table is not re-instantiated again, which speeds up the designer of the query)

/// <summary>
/// Renders the complete set of criteria on the panel canvas. It will clear all controls first.
/// </summary>
private void RenderCriteria()
{
    int currentX=_controlMarginX, currentY=_controlMarginY;

    // clear canvas
    _queryPanel.SuspendLayout();
    _queryPanel.Controls.Clear();

    for (int i = 0; i < _criteria.Length; i++)
    {
        // the first null marks the end of the list.
        if(_criteria[i]==null)
        {
            break;
        }

        SearchCriterium currentCriterium = _criteria[i];

        // Render the row. First create the controls if they're not already read.
        if(currentCriterium.FieldsComboBox==null)
        {
            // create fields combo
            currentCriterium.FieldsComboBox = CreateFieldsCombo();
            currentCriterium.FieldsComboBox.Tag = i;
            // add event handler.
            currentCriterium.FieldsComboBox.SelectedIndexChanged+=new EventHandler(FieldsComboBox_SelectedIndexChanged);
        }

        if(currentCriterium.ValueControl==null)
        {
            FieldValueType typeOfField = FieldValueType.Unknown;
            MyEntityFieldIndex entityFieldIndex = MyEntityFieldIndex.AantalPerKG;
            currentCriterium.ValueControl = CreateValueControl((SearchField)currentCriterium.FieldsComboBox.SelectedIndex, ref typeOfField, ref entityFieldIndex);
            currentCriterium.TypeOfValue = typeOfField;
            currentCriterium.EntityFieldIndex = entityFieldIndex;
        }

        if(currentCriterium.OperatorsComboBox==null)
        {
            currentCriterium.OperatorsComboBox = CreateOperatorCombo(currentCriterium.TypeOfValue);
        }

        if(currentCriterium.CriteriumOperatorsComboBox==null)
        {
            if(i<(_maxAmountCriteria-1))
            {
                // not the last one
                currentCriterium.CriteriumOperatorsComboBox = CreateCriteriumOperatorCombo();
                currentCriterium.CriteriumOperatorsComboBox.Tag = i;
                currentCriterium.CriteriumOperatorsComboBox.SelectedIndexChanged+=new EventHandler(CriteriumOperatorsComboBox_SelectedIndexChanged);
            }
        }

        // fields
        _queryPanel.Controls.Add(currentCriterium.FieldsComboBox);
        currentCriterium.FieldsComboBox.Left = currentX;
        currentCriterium.FieldsComboBox.Top = currentY;
        currentX+=_controlMarginX + currentCriterium.FieldsComboBox.Width;

        // operators
        _queryPanel.Controls.Add(currentCriterium.OperatorsComboBox);
        currentCriterium.OperatorsComboBox.Left = currentX;
        currentCriterium.OperatorsComboBox.Top = currentY;
        currentX+=_controlMarginX + currentCriterium.OperatorsComboBox.Width;

        // value
        _queryPanel.Controls.Add(currentCriterium.ValueControl);
        currentCriterium.ValueControl.Left = currentX;
        currentCriterium.ValueControl.Top = currentY;
        currentX+=_controlMarginX + currentCriterium.ValueControl.Width;

        // criterium operator.
        if(i<(_maxAmountCriteria-1))
        {
            _queryPanel.Controls.Add(currentCriterium.CriteriumOperatorsComboBox);
            currentCriterium.CriteriumOperatorsComboBox.Left = currentX;
            currentCriterium.CriteriumOperatorsComboBox.Top = currentY;
            currentX+=_controlMarginX + currentCriterium.CriteriumOperatorsComboBox.Width;
        }

        // done with this line
        currentX = _controlMarginX;
        currentY+=_controlMarginY + currentCriterium.FieldsComboBox.Height;
    }

    _queryPanel.ResumeLayout(false);
}

Well, I guess one concern I have is the fact that to summarize data (sum, avg, max, min, etc), I have to first bring the whole dataset over the wire, then most likely iterate through each record manually and perform calculations. Is this correct? Or is there a way to summarize data using your framework somehow that leaves the calculations on the server? (access to SQL aggregate functions?)

Edit:

The other problems I have are that a) it makes it difficult for non-programming IT staff to gain access to your data source and thus extend/modify existing reports, and b) you have to recompile when you want to change/extend the reports.

On the plus side, it's easier to use global parameters/values, and I guess it'd be easier to ship a new dll than shipping SQL code to modify sprocs

Jeff...

jeffreygg wrote:

Well, I guess one concern I have is the fact that to summarize data (sum, avg, max, min, etc), I have to first bring the whole dataset over the wire, then most likely iterate through each record manually and perform calculations. Is this correct? Or is there a way to summarize data using your framework somehow that leaves the calculations on the server? (access to SQL aggregate functions?)

Aggregates are not supported yet, that's a good point, I totally forgot about aggregates for reporting.

You could create views with the aggregates and then filter on that, but that is not always what you want so you have to produce some stored procs for that, OR do the aggregation on the client (like summation or averages, which is pretty simple though).

The other problems I have are that a) it makes it difficult for non-programming IT staff to gain access to your data source and thus extend/modify existing reports, and b) you have to recompile when you want to change/extend the reports. On the plus side, it's easier to use global parameters/values, and I guess it'd be easier to ship a new dll than shipping SQL code to modify sprocs

I think it's hard to come up with a solution which can be handled by non-IT personell. I think these people are more served if you offer them an export function to Excel or something. Reports are more used (my experience, can be different in your situation of course) as pre-defined views on the data and are ran periodically to get a decent overview.

Hi Frans,

How about denormalizing a few tables in to one summary table and get it as a collection using llblgen and put it in cache. Every 1 hour or so synchronize collection with the table. Do you see any loopholes with this.

Thanks.

netLearner wrote:

How about denormalizing a few tables in to one summary table and get it as a collection using llblgen and put it in cache. Every 1 hour or so synchronize collection with the table. Do you see any loopholes with this.

Caching data is ok, as long as the data in the cache can be used for decisions by code even if the data in the database is changed. That's the major issue with caching data. For the rest, there is not that of a problem. What you do is especially useful for read-only data for lookup tables and the like.

However, it's even BETTER to cache the end result of the processing of that data. People often try to optimize their systems by micromanaging some operations, like caching data on a low level in the application. It's however much more efficient to cache say 3 or 4 results of processing that cached data if those processing operations occur often (like transform a set of data into an asp.net combo box).

This forum for example caches in the database. When you 'post' it will parse the text and will create an XML tree. That XML tree is converted with an XSL document to HTML. That HTML is written into the database. (as a separate field). The data is still available, but when I want to view a thread, I simply read only the HTML fields and dump them into the page. That's much faster than any other solution with caching of data, because the real slowness is in the processing of the data, and I have to do that only once: when I save the posting.

Any plans on implementing SQL aggregates in code?

Jeff...

jeffreygg wrote:

Any plans on implementing SQL aggregates in code?

Jeff...

Yes, there are. The basis will be a column designer for the typed list, where you can add aggregated columns or mark a column as being aggregated with a given function. It will take some time though as supporting a common aggregate function set with multiple databases in a designer is tougher than it may seem

Before that, the DQE's will have to be updated which opens up the way to support them through code before the designer is done, however there is no date set when these arrive.

Hi Frans,

Will there be any advantages integrating MS SQL Reporting services in to LLBlgen and do you have any plans to do so?

Thanks.

You mean the reporting add-on or the Yukon feature?

Sorry, this is what i am talking about:

http://www.microsoft.com/sql/reporting

Thanks.

netLearner wrote:

Sorry, this is what i am talking about:

http://www.microsoft.com/sql/reporting

Thanks.

Ok I have to look into that tool, since I never have worked with it. Basicly it seems to me a fast way to publish data like Access has too. So I can't answer your question yet...

What about the possibility of doing it in code? We could then generate them dynamically as necessary... Even typed lists...something like:

Dim MyTypedList as New TypedList

With MyTypedList
  .SelectColumns.Add(New SelectColumn(CustomerFieldIndex.City)
  .SelectColumns.Add(New SelectColumn(CustomerFieldIndex.Name)

  .EntitiesToUse.Add(CustomerEntity)
  .EntitiesToUse.Add(OrderEntity)

  .RelationsToUse.Add(Relation1)
  .RelationsToUse.Add(Relation2)

  .AggregateColumns.Add(New AggregateColumn(AggregateColumnTypes.Sum,CustomerFieldIndex.Name)

  .GroupByColumns.Add(New GroupByColumn(CustomerFieldIndex.City)
End With