11519

Excel’s ability to run pivot tables against an OLAP cube in Analysis Services is a truly wondrous beast. The ease of use; volumes of data; the filtering; all of these provide a rich environment for users to easily inspect their data and get real information out. Add to this that Microsoft seem to believe that pivot tables are a winner and keep improving it, access to cubes and slicers being some of the latest significant changes, I can see no reason for all serious Excel users not to get to grips with this technology. In addition to all of this … Continue reading Cracking The Code

One of the better additions to Excel 2007 are, in my view, the functionality added to tables http://www.jkp-ads.com/articles/Excel2007tables.asp. I was recently working on a 2007 project and I was looking to add some highlighting using conditional formatting. That will be easy thought I, but as usual, I was way off the mark. Formatting The Table For the sake of clarity, I will not use the actual formatting that I was trying, but a far simpler case. Figure 1 shows an example of a typical table in Excel 2007. Figure 1 Let’s assume that I want to show those rows where … Continue reading Who Scratched My Table?

Excel 2003 Addins In Excel 2007 Excel 2007 and 2010 is so different in concept to Excel 2003 and before that there are many new challenges in providing solutions that can be deployed in either version. One challenge that I have been faced with is creating addins that may be used in Excel 2007 or earlier versions. My addins would usually have a menu and/or a toolbar to provide access to the functionality within the addin. In previous versions of Excel, this has been an elegant solution as most people are familiar with the menus and are easily able to … Continue reading Deploy Me Simple

I recently wrote about a database tool that I have built to facilitate adding stored procedures to databases, SP Builder.   XL-Dennis made a couple of comments, one of which was with regards to the medium used for the script file. He talked about XML files, and whilst I had used an INI file when I originally built it I had thought about using XML but dismissed it at the time as my XML skills and knowledge were minimal to put it mildly (oddly, I have been aware of and using XML for over 12 years now, mainly as a … Continue reading The XML Is On The Kid

Dynamic Analysis Excel 2007 provides the capability to have a pivot table connected to an OLAP cube. Furthermore, detailed analysis can be built by using various CUBE functions. One of the great things about Excel 2007 pivots and CUBE functions is that you can use a value from the pivot as a member value within a CUBE function. For instance, a CUBEVALUE might be getting the net gas sales for a particular company and period with CUBEVALUE(CubeName,                        “[Transaction Company].[Company Drilldown].&[17]”,                        “[Activity Date].[Date Drilldown].[Month].&[1]&[1]&[2009]”),                        “[Measures].[Gas_Amt_Net]”) If you also have a pivot that is filtering the company drilldown, the … Continue reading Cycling Through The Fog

Normal 0 false false false MicrosoftInternetExplorer4 <!– /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:””; margin-top:0cm; margin-right:0cm; margin-bottom:6.0pt; margin-left:0cm; mso-pagination:widow-orphan lines-together; font-size:11.0pt; mso-bidi-font-size:12.0pt; font-family:Arial; mso-fareast-font-family:”Times New Roman”; mso-bidi-font-family:”Times New Roman”;} a:link, span.MsoHyperlink {color:blue; text-decoration:underline; text-underline:single;} a:visited, span.MsoHyperlinkFollowed {color:purple; text-decoration:underline; text-underline:single;} @page Section1 {size:595.3pt 841.9pt; margin:2.0cm 2.0cm 2.0cm 2.0cm; mso-header-margin:35.45pt; mso-footer-margin:35.45pt; mso-paper-source:0;} div.Section1 {page:Section1;} –> /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Times New Roman”; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} I have blogged previously about what a rubbish job MS have done with Conditional Formatting in Excel 2007, but it seems … Continue reading OpenOffice Is Looking Good

Normal 0 false false false MicrosoftInternetExplorer4 If you are in the habit of building or customising Excel 2007 Ribbons, there is an indispensable new tool that has just hit the market. Andy Pope has released his RibbonX Visual Designer, which can be found here.   I have blogged previously about how I find cutting XML tedious, and the inadequate tools available, so this is a more than useful addition to make ribbon design and coding simpler. It provides a GUI to facilitate the Ribbon build, simple point and click rather than the tedious line coding. The big advantage of this … Continue reading Ribbons – Gain Without The Pain

Yesterday I had an incredibly frustrating time. I was updating my RibbonX Autogen routine to handle more types of controls. This is driven from a worksheet, some columns of which are generated, some of which depend upon user input. So being a helpful sort of guy, I have decided to shade the input cells as a visual aid. But in some circumstances, an input cell may not be required (for instance, a group does not require an image, so suggesting that it does by shading is a tad misleading). Being the helpful sort that I alluded to earlier, I decided … Continue reading Conditional Formatting In Excel 2007 – Chav or Mini?

Normal 0 false false false MicrosoftInternetExplorer4 In a previous post, XML Is Such A Pain, I showed the technique that I use to simplify the creation of XML code for custom ribbons. This is a handy technique, as it means that creating the XML is very simple, it is more or less a cut-and-paste job, and I spend very little time thereafter  with the XML, which I like as the tools for XML in Excel 2007 are not great. As much as I like this technique, it still requires a lot of VBA code. Whilst VBA is a great deal … Continue reading Autogen’ed Ribbon Code

Normal 0 false false false Creating a custom ribbon tab in Excel 2007 is a well known technique by now, and relatively easy to achieve.  I will not go into the details here, there are many examples scattered across the web, I would suggest Ron de Bruin’s pages at http://www.rondebruin.nl/ribbon.htm. The biggest pain for me with custom ribbon code is the XML. As an example, to create a custom ribbon tab such as this requires the following XML code <customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui” onLoad=”rxDemoRibbonOnLoad”> <ribbon startFromScratch=”false”> <tabs> <tab id=”tabDemo” getLabel=”Ribbon Demo” insertAfterMso=”TabHome”> <group id=”grpDemoGroup1″ getLabel=”Formatting Group”> <button id=”btnDemoButton1″ label=”Currency” image=”DollarSign” imageSize=”Large” screentip=”Button … Continue reading XML Is Such A Pain