SmartCharts, SmartPivot & Gantt Chart for #Excel -Latest features & releases @DevScope #Apps

We updated some of our apps and add-ins last week. Brief post introducing some of the new features:

SmartPivot – now with Measure Group/Table Filter in search pane, also Table Report & Filter Tools improvements

A much requested feature now available (should have been in the initial Search release honestly…): SmartSearch pane now allows for measure group/table filtering, much like the regular PivotTable fields browser in Excel.

Download the new SmartPivot release here.

image

Fixed some issues also with Table Report & Filter Tools (both Saved Filters & Filter by list)

image

 

SmartCharts-now with real-time Share feature & Free download during SharePoint Conference 2014

You can now share your charts session, opening it in a browser or tablet/mobile window (disclaimer:some safari/OS issues still to fix). You can then collaborate real time with anyone connected to that SmartCharts session.

Also, you can freely download SmartCharts Task Pane in Office Store during #SPC2014. :) feedback will be appreciated!

Download SmartCharts Task Pane in Office Store

image

 

Gantt Chart & SmartCharts for SharePoint

Soon to be published SmartCharts for SharePoint ;) stay tuned.

image

Gantt Chart for Excel on the other hand is already available in Office Store, and free! :)

Download Gantt Chart for Excel

Gantt Chart for Excel

 

Take care,

 

Rui

Advertisements

SmartPivot new build – now with Saved/Quick Pivot filters and Filter by List improvements

Fresh new build for SmartPivot users, download here as usual. This version features:

  •  Improvements on Filter by List feature (interestingly one of the features mostly mentioned by users)
  • And (finally!) a new Saved/Quick filters feature. :)

New Quick/Saved Filters feature

This feature allows saving filter selections on the pivot for later use on other pivots targeting identical hierarchies. The saved/quick filters are persisted locally in user profile configuration, so that you can always refer to them very quickly.

image

Ex: filter your pivot as usual, or through SmartPivot fast search

 

image

 

Now, pick the Quick/Saved Filters option to save your filter:

 

image

 

image

Select your Pivot field and Save Current Filters option:

image

Now just create any other connection to the same cube, and use the quick/saved filters to apply the exact same selection as before.

(as most features in SmartPivot you can fast search saved filters )

image

double click and your previous selection is now filtering the new pivot:

image

Improvements in Filter By List

Filter by list feature now works as a task pane (expect most of SmartPivot dialogs to be migrated to task panes as they allow for better usability).

Improvements in this release:

  • Available as an Excel Task Pane
  • Pick values from range
  • Filter by member names or member keys

image

Other improvements

Fixed an issue with table reports and orphaned calculated measures (undefined measure groups)

Wrapping up

Download SmartPivot through the product page here.

 

Take care,

Rui

#SmartPivot new beta release with #SSAS and #PowerPivot instant text search, now supports #Excel2013

We’ve just finished publishing a new beta release for SmartPivot, our addin for Excel OLAP cubes (and now also PowerPivot/Excel 2013 Data Models).

You can download it here. Give it a try, just remember it’s a beta release, use it for testing purposes only! :)  It would be great to get feedback on the new features, particularly the “fast full text search” over Cubes/PowerPivot.

Now, onto the new stuff!

A new instant Search *beta* feature, for both OLAP cubes and PowerPivot Models

This is probably the main new feature for this release. We hope that this can take Excel olap/ppivot pivot tables usage to a whole new usability level. But tell us what you think.

We are still working on some issues with the feature, but we hope it’s stable enough on the beta , allowing for “crowdsourcing” the final adjustments. :)

It’s rather self –explanatory, just select a pivot and click search…

image

On the first run for a cube/ppivot, wait a few moments for SmartPivot to read/cache all dimension data (at the moment it will do this automatically only for PowerPivot models or local SSAS connections).

And you’ll be able to search both data (member) and metadata (measures/hierarchies) with instant search results:

image

image

This makes exploring the data much easier for end users, as they usually know the data very well (but not always the cube concepts of measures, dimensions, attributes and others).  They can start their own model discovery.

image

Can we hope in a near future that a feature like this would  be pervasive in every frontend and handled internally by the ssas/powerpivot engines? ;) (good note to post on the ssas team equest for feedback survey) But until that’s available we hope this SmartPivot feature can help a little bit. :)

 

And it now supports Excel 2007/2010/2013 and PowerPivot

 

Excel 2013

image

Excel 2010

image

PowerPivot 2013 (my kindle book stats model)

image

PowerPivot 2010 (the “Understanding the US debt” Book excel sample )companion)

image

 

Dev Tools

Not finished yet, but you can already explore the SSAS rowsets available when working with Cubes/Ppivot models, useful for troubleshooting and advanced models discovery

image

image

image

Other small improvements in this release

  • Cell Value from Table Reports –You can now start a table report from a cell value (see a previous post here), like you would do with the Detail By option, but it allows you to build a table report for that specific context.
  • Duplicate pivot option– just an handy tiny feature  to save a few clicks
  • Auto update notification

Now, we just need you feedback! Download it and tell us what you think.

Regards,

Rui

Browsing & exploring a “big” #kindle library, part II, now with #Excel2013 #PowerView

Well just recently updated my Kindle orders & highlights data from Amazon (see my previous post here), and just remembered that would also make a very good case for some cool (and useful, imo) Excel 2013 PowerView dashboards. There’s an image available for each books, fits perfectly with PowerPivot and PowerView.

image

Valuable Data & Insights

Aside from the usual visual bells & whistles, being a kind of data “playground” and a very cool demo for friends, our team, partners & customers, there are actually some very real scenarios where this data and analysis are of great value to me:

  • remember/recap my favorite books just by checking the number of highlights I’ve made (a very reasonable indicator for that)
  • to check & control my book “budget” (unfortunately it is not, yet…, real time… )
  • check savings from free kindle books promos, just for fun (& profit ) :)
  • to choose a book to read next, very usual, based on books I haven’t read yet, ie with no highlights from me, that have good ratings (either avg stars or number of reviews, or my “calculated” overall score)
  • much better browser (PowerView or PivotViewer) from my library that the Kindle or any Kindle iPad, the native apps clearly were not developed to make it easy to browse your own kindle library when you have a few hundred books…actually one of the reason I gave up on using my Kindle device, inability to slice & dice and help me choosing what to read next (honestly, it could be awesome to have this on kindle& kindle apps… don’t get it really how it’s still missing)

Brief Excel 2013 & PowerView Walkthrough

I won’t get into much detail how to get something like these going on Excel 2013 PowerViews, there are plenty of other good resources to learn the basics on PowerPivot and PowerView, but honestly for something like this it’s  very intuitive  indeed with the new Office & PowerView.

We’ll need a main table:

image

The usual “Date” utility table…

image

Check you have both tables on the PowerPivot data model:

image

image

Create the relationship between these two tables (by my order date in this case):

image

Fine tune the default behavior of the main table in order to get a better user experience, ex: title images:

image

image

Define a Default field set:

image

Save, back to Excel and Create a new PowerView Dashboard:

image

And be creative! :) 

Ex: 2012 highlight (PowerView data “highlighting” it’s really the killer feature)image

Final thoughts

Now I have two very interesting browsers for my kindle books, both PowerView and PivotViewer , fun they’re both running SilverLight, who would figure that ?

Nevertheless, some things I am already missing in PowerView:

  • exact copy/paste/duplicate PowerView sheets… as it insists on changing display positions
  • automatic date handling, someone better to solve this soon, lets skip the needed date table shall we?  I think that would allow much wider adoption of Excel analytics. Getting really tired of date/time tables, honestly… no wonder end users don’t get them easily… they are smart
  • analyze in excel option, jumping to a native Excel Pivot Table maintaining  the specific value/context- much like SmartPivot detailby feature or Office 2012 QuickExplore , that would be killer

btw-How can you build a very reasonable Kindle library like the above just with “minimal” budget? That, I will cover soon on my personal development blog :)

Anyway…$400 in books, I really hope my wife is not reading this. :)

Take care!

Rui

 

Related Posts

Tabular Queries in Excel 2013, Good news, Bad news,DAX Queries and the missing DAX visual query builder

(update: SmartPivot for Excel version 2.3 – a much improved version- has just been published, please visit SmartPivot Product Page for the latest version Excel BI features including instant search, quick connect , a stunning pivot viewer visualization for Excel tables & many others)

image6[3]

SmartPivot Latest Version for Excel 2007, 2010, 2013 & PowerPivot

The never-ending thread… :) Anyway, I just finished some tests with Excel 2013, like the title says, good news, and bad news. (that is, imo).

So, bad news first, so that I can finish this post in a good mood!

Bad News-“Tabular” Pivot Table on OLAP cubes, the scalability/performance problem is still there

Not that I was expecting improvements here, but it’s sad to note that for now I get the same very bad experience both with 2010 & 2013.

Just load an olap pivot table with 11 columns, a tiny rowset of 244 rows and I had to wait more than a minute for the data. (In the process I also crashed SQL Man Studio and Excel due to the abnormal number of cells returned)…

image

Fortunately, I was glad to see that SmartPivot tabular report works perfectly on Excel 2013 Preview.

image

Same 244 rows in a native table, 11 columns, ~1.6 secs… No dumps.

image

Anyway, it would be great that native excel cube pivot tables could be fixed to enable this scenario. Let’s see in the RTM…

So, now for the good news…

Good News-Edit Dax Query in Excel tables shows potential, but we’ll need a friendly query editor soon!

If there’s one thing I always hoped that the tabular model could fix, (and as the tabular suggests… ), were real tabular queries, with real excel tables. A single semantic model (read *real* semantic, not bism…) to rule them all (both pivot and table queries).

Yes, there’s more to data science than pivot tables and low grain aggregated values.

For a lot of (good) reasons it’s very common for users to need that style of data layout. Why the hell can’t users use the same platform/solution to get a simple list of customers by country, or products by categories (yes, there are no metric involved)

And although I don’t feel the problem is properly solved in the Excel 2013 preview, there’s a new table menu option that immediately caught my attention: Edit DAX.

image

That will allow to edit the underlying DAX model query being used, switching from table style query to a full DAX query.

So for example, I can create a table from one of my data model existing tables,

image

and then changing the table query to a DAX query, and craft the query to return an additional calculated column (ex: score based on average rating)

image

I can only guess (hope!) that anytime soon, the old field list/pivot layout that  today supports  most of pivot table/pivot chart data analysis will be finally replaced to a full grown olap/tabular friendly query editor, that can target lots of data layouts (pivot/table style). Like the field list in a pivot table, there should be a field list for a data model based table query. That would be pretty amazing.

Because, and I’ll have to disagree with some msbi most valuable guys :), DAX (like MDX) is extremely difficult to craft  manually beyond the basic stuff.  For pivot based layouts we can live (hardly…) with the drag & drop field list. For tabular queries (particularly for DAX queries) users will need a new (very easy & very visual) query editor tool that explores the full potential of tabular backends.

That reminds me that although I can’t say that DAX is a lot more complex that MDX (I’m tempted to…:) ), or the way way around, one thing is for sure, Excel OLAP users never needed to know MDX to work with OLAP, I and hope that they’ll be able to get the full power of tabular without knowing DAX too!

Btw, regarding DAX queries, be sure to check Paul te Braak promising DAX Studio and his latest posts on DAX:

DAX Querying Part I

DAX II – Extending the use of Evaluate

DAX Studio @codeplex

 

Final note – Slicers also work with DAX query tables, but they use visual filtering in the Excel table.

It was rather intriguing to note that I could use a table slicer (new feature of Excel 2013), also when using a custom DAX query. In fact the newly added DAX column was immediately available to use as a slicer. But the reason for that become pretty obvious: the slicer uses the Excel table column filters to filter the visible rows only, it doesn’t change the underlying table query. Ex:

image

So, that’s it for now. Take care,

 

Rui

#SmartPivot for Excel–Now, for the new OLAP Features

In my last post I covered a brand new feature in this release of SmartPivot-pivot viewer visualization-, actually not olap related, as in you don’t need a cube, just a regular Excel table.

image_thumb40 image_thumb36 image_thumb39 image_thumb7image_thumb5[1]

But SmartPivot still is, and will always be, a cube exploration accelerator for Excel. It makes things easier with features and quick-wins that are not available in Excel out-of-the-box (although they should honestly… and we actually have other things to do beside “fixing” Excel Olap browser usability issues… :) )

Anyway, we have a really hard time saying no to our users :)… when they ask, “well, you know… this pivot field list is always expanding, we have a hard time seeing the cube “big picture”… could you…well collapse it? toggle it?”… And well, so we have…

 

1) a Collapse/Expand Button for the pivot list fields! (this will work only on Excel 2010, sorry)

image

Select the Toggle Fields and toggle between expanded or collapsed fields:

image      image

 

2) you can search fields (measures, attributes, hierarchies), searching as you type – Excel 2007 & Excel 2010

image

image

it will do multi term search, just use a space to split the terms you’re looking

image

3) Filter by list of items, ex: Order Numbers – Excel 2007 & Excel 2010

image

Paste your items and apply the filter:

image

image

4) Detail a cell value in a new pivot with the Detail by feature-Excel 2007 & 2010

image

Select a cell value on the pivot you want to explore further, then select Detail value by:

image

That value will be isolated in a new pivot, you can immediately select another hierarchy to drill down/detail it. (The original pivot remains intact)

image

The new pivot is drill by the selected field. If appropriate a pie chart is automatically generated:

image

Off course you can start from this values into new Detail by actions, ex: detail Calgary.

image

5) Create a quick chart dashboard almost instantaneouslyExcel 2007 & 2010

image

Just select Create Dashboard, if no pivot table is selected, you’ll be asked for an analysis services connection:

 

image

Select the fields you want on the filters zone (you can always change them later):

image

And the new dashboard is created for you, all pivot charts, pivot tables and filters properly connected. Just edit each chart for the view you want.

image

Something like:

image

6) Quickly jump to a pivot chart data values (base pivot table), Just select a pivot chart and then Show Chart Data:

image 

We can then explore the values, using the Detail By feature for example.

image

That’s it for today. Grab a copy and tell us what you think. Happy cube browsing!

 

Rui

Dashboards in Excel with SmartPivot, New Version & Some Improvements

(update: version 2.0 of SmartPivot – a much improved version- has just been published, read it here)

We finally got the time to get a new (minor) build of SmartPivot available. We tackled some of the requests we received in the last few weeks.  See below for the release notes.

 

Improvement: Connected charts can now have specific filter configuration

Some charts may require specific filters, like one chart filter to a product category, and another chart to another product category. Previously any filter not present on the main filter was removed. Now they remain and can enable new dashboard scenarios.

(example, channel filter defined only on the charts left below)

image

 

Improvement: Synchronized filters can now be on rows or columns, they don’t reset to filter zone anymore.

Chart filters can now be on rows or columns, maintaining full sync functionality with main pivot filters, making it easy using them directly for chart rows /columns visualization grouping.

image

 

Table Report Minor Performance Improvements

 

Some new tweaks on this beta feature were made, improving performance in some specific scenarios.

image

 

That’s it! We’re checking out some other tips & new features feedback. Lets see were can we take it. Feel free to post your comments!

 

Regards,

 

Rui