2sxc - Query data for statistics

Question

One thing I have not been able to do is to query data for statistical use.

In FnL, for example, you can use the full SQL server capabilities, bit I still can't figure how to access data in 2sxc to achieve the same purpose...

Is there any way I can place the data in a list into an sql view (temp table) and then query it? Or use any other method to access the data and query it?

Best regards, João

Answer 1

You can do that in Razor.

You receive in your template an object App.Data["your_pipeline_data"] for example App.Data["Default"] depending on what you filtered, sorted, extracted in your 2sxc visual query/pipeline. You can use Linq on this object to manipulate the data and if you put this data in an enumerable type object, you can have the information you want.

An example:

@{
    var termGroups = ((IEnumerable<dynamic>)AsDynamic(App.Data["Default"]))
        .OrderBy(t => t.Nom)                              // sort terms
        .GroupBy(l => l.Nom.Substring(0, 1).ToUpper())    // group by first letter
        .OrderBy(s => s.Key);                               // ensure sorting at group-level

    int groups= Enumerable.Count(termGroups);

}

is the equivalent of a GroupBy in SQL.

To manipulate all the data, do not put any Linq directive

@{
        var terms = ((IEnumerable<dynamic>)AsDynamic(App.Data["Default"]));   
        int count = Enumerable.Count(terms);
    }

After that, just use the variable in your template like that:

There are @terms items in the list.

or

There are @groups groups in the list.

Credit: the Linq code is from the 2sxc A-Z tutorial