Advice and techniques regarding report creation and analytics e.g. slicers, ranking, driving from a range etc.
//In part 1 of this article, we have prepared the table for the update. In this second part, we will learn how to update the SQL table with only the rows modified by the user.
Continue reading “Updating SQL Tables from Excel: A New Approach (Part 2)”
//In a previous blog article, Ambika presented a neat method to update an SQL table from Excel. This proved to be useful for many of our customers but could somewhat be painful for those wishing to update many rows. Today, an increasing number of XLCubed customers use Excel as a planning sandbox and often want to writeback a whole set of records back to the source SQL database at once.
In this article we will describe how you can writeback data to SQL and write only the changed values to the source table in one single pass by executing a stored procedure.
Continue reading “Updating SQL Tables from Excel: A New Approach (Part 1)”
//In the previous blog, we looked at how consistency is important throughout a set of reports and dashboards. It helps users to understand the reports quickly and easily. Another important aspect to maintain is scenario handling; this forms the UNIFY part of IBCS’ SUCCESS formula.
Continue reading “Lessons from IBCS – Scenario Handling”
//In the last few months we have significantly extended the charting elements within XLCubed. We wanted to get the product formally certified by IBCS, and I’m delighted to say we achieved that in early June. We’ve been aware of IBCS for several years now through some of our customers and partners, but it was really only at the beginning of this year that we started to look in detail, and it really resonated with us.
Continue reading “XLCubed and IBCS”
//Most of our customers use XLCubed for reporting, analysis and data visualisation, but an increasing number use it for data updates, either within budgeting and forecasting solutions or in niche applications.
XL3RunSQL is a neat method for running SQL updates from Excel, and allows users to build input forms (which can ultimately be web-enabled) quickly and easily.
In this blog, we will walk through the steps to set this up.
Continue reading “Updating SQL Tables from Excel”
//Do you ever find yourself creating the same grid over and over again? Sometimes we want to add the same grid multiple times but with a few variations, or in different workbooks. This can be especially tiresome if the grid has complex member selections or if the underlying data model has a large number of dimensions and hierarchies.
Fortunately, XLCubed provides a simple and quick way to insert saved grids…
//This is a revised version of this post: Warning: Excel can get Volatile
Excel is a great tool for dashboard/report delivery and design (it’s why we created our addin in the first place!), but there is a hidden performance trap:
Offset, Now, Today, Cell, Indirect, Info and Rand
If you’ve ever used any of these formulae, you may have noticed that whenever you change a cell, or collapse/expand a data grouping, Excel recalculates. That is because these are VOLATILE formulae, as soon as you use one of these, Excel will enter a mode where everything is always recalculating, and for good reason.
Continue reading “Warning: Excel Can Get Volatile (revised)”
//We recently had a customer asking us for some assistance with a business challenge.
He had been asked to provide a report analysing their customer base by % contribution to company revenue. But that wasn’t all! He also had to produce an additional report that tracked the customers ‘lost’ by the company Year on Year by looking at sales activity.
This approach is also often used when looking at profitability, and many businesses like to classify their customers in this way:
This is where XLCubed’s capability and flexibility comes into play – we have an advanced selection mode for handling exactly this type of scenario.
Continue reading “Customer Segmentation By Revenue Contribution”
//It’s a very common requirement in financial reporting to show actual and forecast data by month across the entire year. Often, we are focusing on the expected year end position so we will want to use the actual data where it exists, and the forecast data where it doesn’t so that the year-end total becomes increasingly accurate through the year.
Excel is a flexible environment and people often build that type of report using a mixture of copy-paste and formulas. It achieves the goal but can be a cumbersome process needing quite a bit of manual amendment each month to update it.
When building this type of report the main challenge is normally that Actual and Forecast are two different measures, or members of a ‘scenario’ hierarchy. In most client reporting tools, reporting across multiple months will mean both actuals and forecast showing for every month where both exist, as shown below. In our case if completed actuals exist, that’s all we want to see, and then forecast for the remaining months.
We want our report to be dynamic so that we are not having to redesign it every time. In a previous blog from some years back, we showed you how to create the report using the “Keep – exclude from display” feature. While that approach still works, there is now a more intuitive way to achieve the same result using “Keep – Keep Members and Create Dynamic Set”
Continue reading “Rolling Actual and Forecast Reporting”
//With a wide range of reports being accessed and published daily, it is important for administrators and management to be able to keep abreast of their usage. Common questions are:
Our latest 9.2.47 release provides an updated version of the Usage Metrics Dashboard for XLCubed Web which gives visibility on the questions above and more.
Continue reading “Usage Metrics in XLCubed”