Pivot non-numeric data with max() instead of count()?

Posted by: swincg on 9 September 2017, 8:29 am EST

  • Posted 9 September 2017, 8:29 am EST

    Look here for pictures: http://stackoverflow.com/questions/13149573/how-to-dynamically-pivot-non-numerical-data

    Is it possible to display a field as "Values" either without any sort of aggregate operation, or using max instead? Perhaps this is against the nature of the beast or I'd have found it already - however it feels like a generic pivot viewer that can dynamically pivot non-numerical data using max (as I can with SQL) is very much needed.

    I've been looking into various ways to do it, and I know I can perform a pivot on the SQL side within a view, to load in LightSwitch, but the problem is this can't be done with dynamic columns without writing dynamic SQL (regardless of whether you use PIVOT or SELECT CASE), and if you use dynamic SQL that means you have to recreate the view when the data used for those columns changes. I don't want to have to recreate the view when new data (pivoted to columns) is added.

    I'm also using WCF RIA Services to denormalize data in my database for a grid/OLAP to display whatever, and it's excellent, but being unable to display a date in the manner I want with the OLAP control is a little annoying (I can of course have it as a row/column field, but I don't want that, I want to see a date for each combination of row&column, just as "Values" are). Anyone have an idea on how to do this, or when/if ComponentOne's OLAP control will add the ability to use max() in such a fashion?
  • Replied 9 September 2017, 8:29 am EST

    Hello,

    I haven't really been able to find a way to accomplish this requirement with the current implementation. I update you in case I am able to find any way to accomplish this.

    Regards,
    Abhishek
  • Replied 9 September 2017, 8:29 am EST

    This would be useful, as users don't always use a Pivot to aggregate data. It's often a way for them to arrange the fields and filter them.
    Excel will allow pivoting without anything in the Values box, I think this control should allow it too.
  • Replied 9 September 2017, 8:29 am EST

    Yes this would be fantastic, as elylv1 hinted this is quite common such as in SQL, see here: http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function where max() or min() is used. It's logical that an aggregate operator is required - this is fine and shouldn't be an obstacle at all, in the raw data of these pivot cases the other values are null, as expected, so max()/min() work perfectly fine to pull that non-null value out.

    We don't need things significantly re-engineered, just max() or min() in addition to count(). It's up to us to join our table data in such a way that there's just the one value for max()/min() to retrieve (that is, count = 1), this is no difficulty with SQL/RIA Services and exactly what would be required in SQL anyway. Here's a SQL Fiddle demo from my own example: http://sqlfiddle.com/#!6/54d12/4/0 It groups with group by, case condition sets value for just one in the group, null for the rest, and max() returns that value since it's largest/only non-null in the group.

    My boss for one would very much like to see his reports structured (pivoted) a certain way, and while I can manually update a static one with SQL views or WCF RIA Services, being able to do it on-the-fly with any data in an OLAP control within LightSwitch would be a killer feature.
  • Replied 9 September 2017, 8:29 am EST

    Hello,

    Thank you for your insights, I have forwarded an enhancement request to the concerned team to add support for Pivoting non-numeric data with max() and min() functions in C1 OLAP Lightswitch extension.

    Regards,
    Abhishek
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels