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

score 1 · Accepted Answer · answered Oct 12 '17 at 14:28

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

Never thought of LINQ as a query language, but that's exactly what it is... There is no "GroupBy" info on 2sxc page. Can I search any LINQ documentation available on the web? Are all LINQ Operators available to use on 2sxc views? — João Gomes, Oct 12 '17 at 16:44
I would say yes to both questions. Linq is there because SIC integrated it by default so all the Linq syntax is available. Look at the IEnumerable documentation : https://msdn.microsoft.com/en-us/library/system.linq.enumerable_methods(v=vs.110).aspx — Frédéric Laurent, Oct 12 '17 at 17:01
Just one last addon. With this: foreach(var z in items){@z.field} I get the following output: A, A, B, C, D, D. How do I group AND count them in a single line of code? I need to output this: A - 2; B - 1; C - 1; D - 2, etc — João Gomes, Oct 15 '17 at 11:42

2sxc - Query data for statistics

1 Answers1