Nreco Pivot Table Mvc

Question

Hi I'm trying to use this component: https://www.nrecosite.com/pivot_data_library_net.aspx

I see that in this project they use sqlLite for connectionString.

Unfortunately when I change the connectionString :

new SqlGroupByCube("northwind", 
                    new PivotDataConfiguration() {
                        Dimensions = new[]{"CategoryName","OrderDate_year","OrderDate_month","ProductName","CompanyName","Country","Region","City"},
                        Aggregators = new[] {
                            new AggregatorFactoryConfiguration("Count",null),
                            new AggregatorFactoryConfiguration("Sum", new object[] { "LineTotal" }),
                            new AggregatorFactoryConfiguration("Average", new object[] { "Quantity" })
                        }
                    },
                    new System.Data.SQLite.SQLiteConnection("Data Source="+ System.Web.HttpContext.Current.Server.MapPath("~/App_Data/northwind.db") ),
                    @"
                        SELECT ProductName, CategoryName, CompanyName, Country, Region, City, OrderDate_year, OrderDate_month, COUNT(*) as cnt, SUM(LineTotal) as LineTotal_Sum, AVG(Quantity) as Quantity_Average FROM (
                            SELECT p.ProductName, c.CategoryName, CAST(strftime('%Y',o.OrderDate) as integer) as OrderDate_year, 
                                CAST(strftime('%m', o.OrderDate) as integer) as OrderDate_month, cust.CompanyName, cust.Country, 
                                cust.Region, cust.City, od.Quantity, CAST( (od.Quantity*od.UnitPrice) as REAL) as LineTotal 
                            FROM [Order Details] od 
                            LEFT JOIN [Orders] o ON (o.OrderID=od.OrderID) 
                            LEFT JOIN [Products] p ON (p.ProductID=od.ProductID) 
                            LEFT JOIN [Categories] c ON (c.CategoryID=p.CategoryID) 
                            LEFT JOIN [Customers] cust ON (cust.CustomerID=o.CustomerID)
                        ) t
                        GROUP BY ProductName, CategoryName, CompanyName, Country, Region, City 
                    "
                ) {
                    Name = "Northwind DB Orders (example of SQL data source)"
                }

to this connectionString:

string connetionString = "Data Source=" + @"192.168.192.168\SqlServer" + "; 
Initial Catalog=SomeDbName;User ID=Name;Password=SomePassword";
        pvtRepository = new PivotRepository(
            new ICube[] { 
                new SqlGroupByCube("SomeName",
                    new PivotDataConfiguration() {
                        Dimensions = new[]{ "appname", "dbname", "db_path"},
                        Aggregators = new[] {
                            new AggregatorFactoryConfiguration("Count",null),
                        }
                    },

        new SqlConnection(connetionString),

                    @"SELECT TOP 1000 [some_column],[some_column1],[some_column2],[db_path]FROM [SomeDbName].[dbo].[some_table]"
                )
                {
                    Name = "My Data Test"
                }

I can not get data and fail in SqlGroupByCube.cs on this row:

        var groupedPvtDataReader = new GroupedSourceReader(
                dbCmdSource,
                "cnt"  // column name with rows count for each entry
            );
        try
        {
            var pvtDataFromGroupBy = groupedPvtDataReader.Read(PvtCfg, PvtDataFactory);

        }
        catch(Exception ex)
        {
         //somehandler..
        }

in the Exception i get just "cnt". please help.

score 0 · Answer 1 · answered Apr 27 '17 at 10:11

Take a look to the official documentation that explains how to load pre-aggregated data with PivotData SDK: https://www.nrecosite.com/pivotdata/load-pre-aggregated-data.aspx

In short, your SQL ... GROUP BY query should return columns expected by GroupedSourceReader component. In your example you've specified the following cube configuration:

dimensions: "appname", "dbname", "db_path"
aggregators: "Count"

and your SQL query should return the following columns:

SELECT appname, dbname, db_path, COUNT(*) as cnt 
FROM [some_table] GROUP BY appname, dbname, db_path

in other words, you should specify all columns used as dimensions in GROUP BY, and calculate aggregates with SQL summary functions.

BTW if you don't want to aggregate data on database level you can just specify simple select and perform aggregation in .NET code with PivotData class (see ToolkitSqlDbSource example for that).

Nreco Pivot Table Mvc

1 Answers1