Librarians are experimenting with a huge range of Facebook Application ranging from the productive 30 boxes calendar,Meebo,Twitter & Zoho Online office to silly with Zombies ,Likeness ,My Aquarium and super poke
Here are the some of the Facebook Application design for a librarian in all of us.We all love reading books .Many of my friends on Facebooks are interested in reading books ranging from Novel to children comics Books iRead – With nearly 200,000 users, this handy app claims to be the most popular book application on Facebook. iRead lets you share a virtual bookshelf of titles you are currently reading, those you want to read, those you have read in the past, and books you won’t read. You can also rate titles, spotlight book reviews you’ve written, and list books you own as well as your all-time favorite books. By accessing your iRead dashboard you can check out your friends’ reads, tweak your display settings, and view new books added to the quickly-growing collection of 2.8 million books. Other options – Visual Bookshelf (115,881 users), Books By Martin Mroz and Larry Gadea (50,542 users), Books By Jonathan Lipps, (26,177 users).
LibGuides Librarian – For those of you using Springshare’s LibGuides subject guides maker for libraries, you’ll be pleased to see this new app which lets you display your guides in your Facebook profile and also provides a search of your library’s catalog.
Librarian –This virtual librarian service provides links to books, scholarly sources, and reference resources which the community can add to and vote on. Those who access this application can access an “Ask a Librarian” service, as well as customize and build their own widget based on the original. Over 1,000 people have installed this useful application which was created by library school student Brad Czerniak.
June 20, 2008 (PC World) Like most people visiting PCWorld.com, you most likely live on the Internet. And that means you need help — help with your home or business network for accessing the Internet, with troubleshooting, with downloading, and with e-mail, instant messaging and security.
We’ve got that help for you — in 14 great downloads. Whether you need a universal messenger or programs to keep yourself safe, downloadable software can come to the rescue.
General Networking and Internet Tools
Need to troubleshoot your network, manage your downloads or get a free alternative to the bloated Adobe Reader? Here’s where to go. (And that’s just the beginning of these nifty downloads.)
PingInfoView
The simplest of all Internet tools is the humble ping command, which lets you use the command prompt to contact a Web site and see if it is alive and responds to your ping request. It also resolves host names to IP addresses. In other words, if you issue the command ping www.pcworld.com, it will tell you the site’s associated IP address. And it also tells you the speed of the connection and response.
The free PingInfoView makes the simple ping command a lot more useful. It lets you ping multiple Web sites simultaneously, will ping the sites on any schedule you set and displays the results in a graphical interface, rather than in a command prompt.
Is this an earth-shaking tool? Certainly not. But if you use ping at all, you’ll find it a useful, worthy alternative.
Here’s another update of a venerable command-line Internet tool, tracert. This tool displays the route you take to contact a Web server or another Internet device — every hop on every server or router along the way, plus the server’s or router’s IP address and how long it takes to get to each one.
This program displays all that in a visually appealing way, not just through the command line. It shows a map of every hop and graphs it against a background that shows response times. Highlight any hop, and you’ll see important details, such as packet loss and speed.
In addition, the program displays a narrative of the route, detailing whether the route has fast, average or slow throughput and highlighting any problems.
This version of VisualRoute is free, but a variety of for-pay versions is also available, with many extra features that display the network names of devices, test domain-name services and more.
What’s the most annoying Internet-related application of all time? For many people, that question has a simple answer: Adobe Reader. Countless documents online are in the Acrobat format, so there’s no way around it: You need Adobe Reader. But it’s bloated and prone to crashes, nobody’s idea of a good time.
Foxit Reader is a far better solution. It’s small and loads much faster, so it doesn’t take up much memory when you use it. It also doesn’t seem to suffer from the same instability issues as Adobe Reader does. The program also has some nice extras, such as the ability to embed comments. (However, when you embed comments, the page you mark up will show that you’re using an evaluation version of the software. You can pay for the Pro pack to get rid of those marks and get some other extras as well.)
Note that when I installed the program, it integrated directly into Internet Explorer as the default PDF reader, but it didn’t similarly integrate into Firefox. To make it the default in Firefox, you’ll have to select Tools, Options and then click the Manage button in the File Types area. Double-click each of the files that Adobe Reader opens, and tell Firefox to use Foxit Reader instead.
If you’re a techie and have more than one PC at work or home on a network sharing a single Internet connection, you’ll welcome this freebie, which offers surprisingly powerful scanning capabilities. Use it for everything from troubleshooting Internet connections and network configuration to making sure your PCs are as safe as possible when they’re on the Internet.
When you first run the program, you may encounter an error message saying that a default configuration is being used. You can safely ignore that message. Simply click the Scan button, and it goes to work. It looks across your network, finds all PCs on it and then gives you quite a bit of detail about each. Besides the local IP address of each PC, you’ll also find which ports on each PC are open. Armed with that information, you can use a firewall to close them down.
In addition to open and closed ports, you’ll see lots more information, such as what services are running on each PC, the NetBIOS names (if any) associated with each, and even a list of users and groups on each system. If any machines have shared folders, you’ll see those as well. All this data is immensely useful to those who want to keep their PCs secure or need to troubleshoot networks or Internet connections.
Making the software all the more remarkable is the fact that it’s free.
If you live to download, this freebie is for you. It’s a great download manager that will speed up the process, keep you safe and help you organize everything that you’ve downloaded. Flashget makes downloads faster by using multithreading, and it lets you find downloads via many different protocols, including HTTP, FTP and eMule. It’s also terrific at file management, showing you all the files you’ve downloaded, including information about each. You’ll even be able to delete downloaded files from directly in the program.
Making it even more useful is that it integrates with your browser, so whenever you download, it jumps into action. It will also pause and resume downloads and works with your antivirus software to scan for viruses as it downloads.
One of the Internet’s oldest file-transfer protocols — FTP — still lives. Contrary to popular wisdom, not all files are downloaded from Web sites. Many are still downloaded from FTP servers. And many individuals and businesses continue to use FTP as a way to share files. Also, many Internet service providers block large files, so using FTP is a great way around that problem.
FileZilla is an excellent FTP client that combines simplicity with a robust feature set. It has just about every FTP feature you could need, and its interface is easy to use.
Here’s a simple, easy way to check on your current bandwidth use — both uploading and downloading — and to track your usage over time. This freebie displays that usage in a constantly changing chart so you can see the fluctuations in real time. It also shows you the current bandwidth use in a text display.
Bandwidth Monitor 2 Lite is quite customizable. For example, you can skin it and change the time span in which it measures bandwidth. Also very helpful is that it will keep logs of your bandwidth usage, so you can make comparisons over time.
The Internet is all about communications — but as we all know, it is not always as easy to communicate as we would like. These downloads will help you with e-mail, instant messaging and social networking.
Email Tracker Pro
Tired of being whacked by spam? Not sure if an e-mail message that you received is legitimate or is instead a forgery or from a scammer? Then give this program a try. It gives you a set of tools for examining any e-mail message that pops into your in-box.
The program traces an e-mail back to its true point of origin, shows you the IP address and location of the sender, and tells you whether the e-mail header was tampered with in order to fool you. In addition, it provides contact information for the domain from which the message was sent so that you can report any abuse.
In order to use Email Tracker Pro, you’ll have to copy an e-mail message’s headers into the program. These headers, however, don’t refer to the Subject or From line. Instead, they’re usually something hidden. How you find them varies according to your e-mail software. For example, in Outlook 2007, right click on a message and select Message Options; you’ll then see an Internet headers field.
One obstacle I encountered: The program says that it integrates directly into Outlook, but I was unable to integrate it with Outlook 2007.
If you’ve got multiple Web-based mail accounts, such as Gmail, Yahoo Mail and Windows Live Mail, you can easily spend far too much time checking each account for new mail. This simple freebie neatly solves the problem. It automatically logs you in to all your Web-based e-mail accounts, checks for new mail, then tells you how many new messages you have for each.
In addition, you can read the messages from right within ePrompter, so you don’t have to go to your Web-based mail account. You can also delete and compose mail. When composing or reading, you won’t get a full range of options as you would when you’re on the Web-based mail site. But for quick and dirty e-mail, it’s all you’ll need.
Don’t expect a fancy interface; this program is as bare-bones as you’ll find. Also, it may not be obvious at first how to set up a new mail account; select Menu, New Account and follow the directions.
Note that this program works fine on Windows XP, but I was unable to get it working on Windows Vista. Also, if you have a Hotmail e-mail address, select the LiveMail option in ePrompter when you’re setting up the account to check; I was unable to get it working properly as Hotmail, but it worked fine as LiveMail.
The Internet was created as a way to bring people closer, but in some ways, it drives people apart. If you’re an AOL Instant Messenger user, for example, you can’t communicate with someone who uses Yahoo Messenger. And there’s no simple way from an instant messaging program to check your presence on social networking sites such as Facebook or to check e-mail from a Web-based mail site not associated with your instant messenger.
Until Digsby came along, that is. This program is a universal instant messenger, like longtime favorites Trillian and Pidgin. But it does them one better because with it, you can also check your social networking sites and e-mail accounts.
It’s extremely simple to set up any instant messaging, social networking or mail accounts from within the program. And it lets you use just about all the features of any of those programs, such as sending files via instant messaging. Considering that it’s free as well, there’s no reason not to give this tool a try.
When you install this program, by the way, it changes your home page to a Digsby home page, so if you want to retain your current home page, make sure to go into your browser and change it.
Most of the time, sending e-mail messages goes off without a hitch. Set up your e-mail program to work with an SMTP server, send your messages, and you’re done. But sometimes SMTP — the Simple Mail Transfer Protocol — won’t work, and it’s almost impossible to figure out what’s wrong. Your e-mail program won’t give you any clue about what the error might be. All in all, it can be one of the most maddening of Internet-related problems.
SMTP Diagnostics helps. It performs a complete set of diagnostics on your SMTP connection and provides an in-depth report about any errors. You can then use the report yourself to fix the problem or send the report to your Internet service provider or network administrator, who can track down the cause of the woes.
The program is particularly useful if you’re setting up an e-mail account for the first time and can’t make a connection or if you’re on the road and can’t send mail through a hotel’s broadband connection or a wireless hot spot.
This free client is about as good as it gets when it comes to FTP. It has a simple interface that you can master in about three minutes, yet it still has plenty of powerful features, such as being able to compare local and remote directories. It also lets you have multiple simultaneous connections, for faster transfers.
You need to protect yourself when you go online. Download the following programs, and you’ll go a long way toward making sure that the bad guys and bad software won’t get you.
Comodo Firewall Pro
If you’re using the firewall that ships with Windows XP or Vista, you’re not safe enough. They don’t offer customizable outbound protection — important in a world in which spyware is everywhere. This free firewall not only stops inbound and outbound threats, but it also does things such as protecting selected files and folders so that malware can’t get at them or alter them.
When you first start this program, be prepared to spend a little time training it. Whenever a program wants Internet access, Comodo pops up and asks whether you wish to let the program use the Internet. To cut down on those pop-ups, run the program in Clean PC mode, which will scan your PC, find your existing applications, and register them as safe. You then won’t see any pop-ups associated with those programs. And to make sure that Comodo doesn’t interfere with installing software, you can use its Installation mode, which will disable certain pop-ups for 15 minutes, so you can install new software without being annoyed.
To use the Internet is to put your PC at risk from spyware, adware, Trojan horses and other malware. Your antivirus program by itself isn’t enough to protect you because it misses many of these malicious programs. This long-time favorite offers a great, no-cost layer of protection.
It does a great job of scanning your PC for threats — including Trojan horses, rootkits and other spyware — warning you about them, then deleting them. It can also put them into quarantine, disabling them but not deleting them, so you can decide what to do about them later.
Even if you already have antispyware on your PC, it’s a good idea to use Ad-Aware as well, because not all spyware scanners find all threats, so using more than one is a good idea.
Your Internet activity leaves a trail — and one far more traceable than you might imagine. This trail includes cache files, browser history, autocomplete information, cookies, typed URLs and more. This means that anyone with access to your PC can easily see what you’ve been doing online. In some instances, even Web sites can gather information about where you’ve been and what you’ve done.
Free Internet Window Washer ensures your privacy by cleaning all those traces off of your PC. Not only will it remove traces of Internet activity, but it will also clean traces of application activity, such as which files you’ve recently opened in Microsoft Office applications. And it works with multiple browsers, including Internet Explorer, Firefox, Netscape and Opera.
I recently purchased an Eee PC 4G (the model with a 7″ display and 4GB of memory) on the cheap. My intent was to look at it for a couple of reasons.
Firstly, I viewing it as a tool to help me in my study. In this guise, I’d use it for accessing the Bible (I’m studying theology) and for note taking. This means that I’d need to be able to install some extra software (there are several open source Bible study applications around that use public domain translations of the Bible) and type on it.
Secondly, I plan to use it for occasional web surfing and email access.. That’s where the WiFi connection and, hopefully, 3G and Bluetooth support would be handy.
Thirdly, I attend a lot of meetings in my day job and I prefer to not carry printed papers to meetings. My preference is to take PDF or other documents with me as they’re easy to annotate on the fly and my comments are kept with the original documents.
Finally, the built-in sound recording software was appealing as I could use it while interviewing folks and while at meetings and conferences.
After a couple of days of use I was totally frustrated - enough to use my soapbox at Hydrapinion for a rant titled “Eee PC proves why Linux blows”. You see, installing extra applications to the Eee PC is a pain and, if you manage to install one, getting it to appear on the Eee’s Easy Menu is something of a black art. Yeah, I know I can look to installing another operating system (there are several Linux distros focussed on the Eee PC and there are ways to install Mac OS X (don’t bother, I did it and it was 20 minute experiment that on the 7: screen wasn’t worth the effort) or Windows XP (you’ll need a legitimate copy of XP SP2 for it work) but why should I have to do that to do, what on the face of things, looks to be a reasonably simple task.
Here’s what I learned. Some of this will be dead obvious to those experienced in the use of Linux but given that the Eee PC isn’t pitched specifically at Linux users but at consumer electronics customers after a low-cost computer, I think they’re worth noting. Also, none of this stuff is my own work. I’ll credit, where I can find them, the original sources of what I learned.
Starting the Terminal
Like it or not, getting the most out of Linux requires some access to the Terminal (Windows users would call this a DOS box or Command Prompt). The Eee PC’s Easy Menu doesn’t have a shortcut for launching the Terminal so you’ll need to remember a keyboard shortcut.
To launch the Terminal press Ctrl+Alt+T
Enabling Desktop Mode
The Easy Mode, with it’s large icons is great for quickly finding and launching applications. However, many would prefer a more usual desktop system. That’s why you might like to enable the Full Desktop mode.
To enable Full Desktop mode
1 - Start a Terminal
2 - Enter the following commands
Note: When you run something commencing with “sudo”, you’re running it with full system access. Whatever you run as “sudo” will have access to the entire system. That means, if you don’t know what you’re doing, you can accidentally mess your system up quite badly.
Exit the Terminal (just type “exit”) and then restart your Eee PC.
Now, when you press the power button, you’ll have a button to run the Full Desktop, in addition to usual Stand By, Restart and Shut Down options.
As the Eee PC runs a version of the Linux distribution called Xandros that has a very strong resemblance to Windows XP. For many people, Full Desktop might be a more familiar experience and a preferred way of working. You can boot to the Full Desktop automatically by enabling it as a permanent option by going to Settings -> Personalisation and checking the Full Desktop option.
Source: My main source for this information was Eeeuser.org
Installing Applications
For Eee PC users, installing new applications is one of the confusing things they have to do. What makes it worse is that Easy Mode, even after a successful installation, doesn’t provide easy access to the installed application.
In order to understand application installation I think a very short history lesson is in order. If you’re a Windows or Mac user, you know that, even though new versions are released regularly, that the operating system is evolving in a single direction. Linux is different. Even though, many, many moons ago there was a single Linux, it’s evolved so that there are several different types of Linux. Each type of Linux is called a distribution. Each different distribution has its own unique features.
What you need to know is that just because something works on one distribution Linux it doesn’t necessarily mean that it’ll work on all or any of the others. Now, the Eee PC runs a version of Xandros that’s been customised for the Eee PC. Xandros is a Linux distribution that sprouted from Debian so what you find is that applications written for Xandros and Debian can usually work on the Eee PC.
I’ve had some success installing Debian applications
My operating system history starts at DOS 5 and Windows 3.0 and goes through all the flavours of Windows (except Windows ME thankfully). Also, I’ve been a Mac user for about five yearts so drag and drop installation is second nature. Essentially, that means that i’m used to running an installation package and having access to the installed application a few minutes later.
Linux is different as there are many ways to install applications. Firstly, you can download source code and compile it yourself. I have never done this and never expect to in my life. I won’t be discussing this at all.
Secondly, you can run an application called Synaptic by going to the Terminal and entering the following.
sudo synaptic
This runs a program called Synaptic that makes program installation reasonably simple. Synaptic has a list of known program sources, called repositories, that are interrogated each time Synaptic is executed. This method works most of the time but is dependent on having enough “good” repositories in your list. There’s a good article on how to do this over at eeeuser.com on how to do this along with a list of decent repositories.
If you’re a Terminal or command line fan you can use the apt-get and install commands to install software. I’m not into the command line lifestyle so I’ll point you to some clear instructions. Pop over to this page and scroll all the way to the bottom where the apt-get command is explained as well as how to use the command line to find software and to delete installed software.
My preferred method is to simply use Google. Search for the application type and find an installation that’s distributed as a .deb file. For example, I installed the Opera web browser by visiting http://www.opera.com/download, choosing Xandros as my distribution and downloading Opera for Xandros 1.0 and 1.1 (the other version doesn’t work on the Eee PC (or didn’t for me).
By default, files download to the My Documents folder (you can launch the File Manager application from the Work tab of the Easy Menu - just in case you hadn’t found that yet). Right click the downloaded .deb file and choose Install DEB file… from the menu. Then just follow the prompts and it’s done
Running Applications
Having installed an application you’ll want to actually use it. If you go to Full Desktop mode, you’ll find you application under the Launch menu and looking through the Applications list. That’s great if you’re using the Full Desktop. If you’re in Easy Mode it’s a little trickier.
Open the File Manager. Go to the View menu and enable the “Show All File Systems” option. Expand the “All File Systems” branch of the file tree and then browse to “/usr/share/applications”
Note: file and folder names in Linux are case sensitive. That means that “usr” is different to “Usr”.
From there, you can look through the list of installed programs to find your new applications.
Adding Applications to the Easy Mode Menu
The contents of the Easy Mode menu are managed, in typical Linux style, through a text file. This file is made up of structured content in an XML format. There’s a tutorial on how to modify this using the Terminal and a text editor if you’re so inclined. I’m not so I poked around, looking for a graphical tool that did this.
Go to the 3epc User Portal and download Launcher Tools. Install it (it comes in DEB package so you know how to do this) and a new icon, called “Launch Tools” appears on the Settings tab of the Easy Mode menu.
To add a new icon to the Easy Mode menu, launch the SimpleUI Editor. Choose the tab you wish to add your icon to (or add a new tab using the “Tab Management” option) and then right-click on some blank space to add a new program.
Given your new icon a name and then, in the “Command” section type the full path to the program you wish to launch from your new icon. For example, the path to Opera (on my system) is
/usr/share/applications/opera.desktop
Remember, this is case sensitive.
As you’re entering it, text saying “Invalid Command” will appear below the command box. When you’ve entered a valid path, the text will automatically change to “Valid Command!” and be coloured green.
One of the things I’ve learned over the last few days is that there’s plenty of good information on using the Eee PC. However, most of it assumes some level of Linux knowledge. I’ve approached this form the point of view of the complete Linux newbie who’s bought a consumer electronics device and wants to get a little more from it. Hence, I’ve not sought to do anything more technical than install some applications and add some icons so I can easily access the apps.
Incidentally, I’ve made considerable effort to not use anyone else’s intellectual property in writing this post. Where an idea came from somewhere else I believe that I’ve credited it and provided some link love.
PstPassword is a small utility that recover lost password of Outlook .PST (Personal Folders) file.
System Requirements
This utility can recover the PST passwords of Outlook 97, Outlook 2000, Outlook XP, Outlook 2003, and Outlook 2007. You don’t have to install MS-Outlook in order to use this utility. You only need the original PST file that you locked with a password.
Versions History
Version 1.10:
Added documentation to save command-line options.
Added new command-line option: /pstpath
Added option to save as comma-delimited (.csv) text file.
Fixed bug: The main window lost the focus when the user switched to another application and then returned back to PstPassword.
Version 1.02 - The configuration is now saved to a file instead of the Registry.
Version 1.01 - New option: Get More Passwords - Displays 100 additional passwords for the selected pst file.
Version 1.00 - First release.
Password Encryption in PST File
The password encryption in the PST file is very weak, and for each password-protected PST file, there are many passwords that can open it.
PstPassword provides 3 different passwords for each password-protected PST file. It’s possible that one of them will be the origianl password that you typed, and it’s also possible that none of these passwords will be identical to the original one. However, all 3 passwords provided by PstPassword will open the PST file without problems.
For more interesting facts about the PST password encryption, click here.
Using PstPassword
PstPassword doesn’t require any installation process or additional dll files. In order to start using it, just run the executable file, PstPassword.exe
After you run PstPassword, the main window displays the list of all PST files of the current logged on user (Under Documents and Settings\User Name\Local Settings\Application Data\Microsoft\Outlook). For each password-protected PST file, PstPassword provide 3 alternative passwords that will open the PST file. If the PST is not protected by a password, the 3 password columns remain empty, and the CRC value column is 0×00000000.
If you want to recover a password of PST file that doesn’t appear in the default list, you can press F7 and select the desired PST file, or simply drag the PST file from Explorer window into the main window of PstPassword.
If from some reason PstPassword fails to read a PST file, an error message is displayed under the error column, and the item is painted with pink color.
Command-Line Options
/stext <Filename>
Save the list of PST passwords into a regular text file.
/stab <Filename>
Save the list of PST passwords into a tab-delimited text file.
/scomma <Filename>
Save the list of PST passwords into a comma-delimited text file.
/stabular <Filename>
Save the list of PST passwords into a tabular text file.
/shtml <Filename>
Save the list of PST passwords into HTML file (Horizontal).
/sverhtml <Filename>
Save the list of PST passwords into HTML file (Vertical).
/sxml <Filename>
Save the list of PST passwords to XML file.
/pstpath <Folder>
Specify the folder that contains the PST files. If you don’t specify this parameter, the default Outlook path is used.
PstPassword allows you to easily translate all menus, dialog-boxes, and other strings to other languages.
In order to do that, follow the instructions below:
Run PstPassword with /savelangfile parameter:
PstPassword.exe /savelangfile
A file named PstPassword_lng.ini will be created in the folder of PstPassword utility.
Open the created language file in Notepad or in any other text editor.
Translate all menus, dialog-boxes, and string entries to the desired language.
After you finish the translation, Run PstPassword, and all translated strings will be loaded from the language file.
If you want to run PstPassword without the translation, simply rename the language file, or move it to another folder.
License
This utility is released as freeware. You are allowed to freely distribute this utility via floppy disk, CD-ROM, Internet, or in any other way, as long as you don’t charge anything for this. If you distribute this utility, you must include all files in the distribution package, without any modification !
Disclaimer
The software is provided “AS IS” without any warranty, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The author will not be liable for any special, incidental, consequential or indirect damages due to loss of data or any other reason.
Feedback
If you have any problem, suggestion, comment, or you found a bug in my utility, you can send a message to nirsofer@yahoo.com
PstPassword is also available in other languages. In order to change the language of PstPassword, download the appropriate language zip file, extract the ‘PstPassword_lng.ini’, and put it in the same folder that you Installed PstPassword utility.
When faced with a table of data, what kind of techniques do we employ to extract information from it? Maybe the first thing we do is create summaries. We might total up the columns or we might chart the data. Perhaps, we might even create a PivotTable and drill down into subtotals.
All in all, summarizing data is useful. It’s easy to understand and easy to do.
Let’s say that we’re running a store and we have a small dataset of the sales we made in the first quarter. Using Excel we can easily create summary reports telling us how many products we sold to different types of people; how many to men and how many to women; how many to people over 40 and how many to people under 40.
All these totals are useful. We can compare them and search for interesting differences. We might notice that we sold more products to men than we did to women or that managers bought more of our products than other professions.
But can we get more information than this?
Did managers buy an unusually high number of products compared to other professions? Is this number so high that it might indicate a bias of some kind?
The answers to these types of questions enable us to make better business decisions. Perhaps if we know that our products are significantly more attractive to managers we might change our advertising policies to target managers more than other professions.
You may think that we need data warehouses and data mining tools to answer these types of questions. But that’s not necessarily true. Data warehousing and data mining are excellent tools, but so is Excel and Excel contains some sophisticated statistical functions which we can use to help us answer these types of questions.
Understanding the Basics
First of all, let’s take a look at our data set.
Our data contains customer information which we have gathered over the last quarter of trading. Each time we made a sale we recorded certain information about the customer. For instance, we recorded their Marital Status, their Gender, their Education level and their Occupation (amongst other fields). We have 1,000 records in our data set.
Now let’s say that we are interested in analyzing the Occupation field. It would be useful to know whether or not certain professions tend to buy more of our products than other professions. This would be a key piece of business intelligence for us.
But how do we decide this?
First of all, let’s build some summaries. Let’s count how many sales we made to each profession and each profession’s percentage of the overall sales and display these numbers in a table. We’ll also insert a pie chart to give some simple, visual indication of the proportions sold to each profession.
With this done, let’s now ask ourselves the question again: Do certain professions buy more of our products than other professions?
We can see that Professional and Skilled Manual occurred more frequently in the data than the other professions and that Manual occurred the least. But is this enough information to enable us to generalize and state that Professional and Skilled Manual buy more of our products in general than the other professions?
Well, not really.
Why? Because before we can state anything about these totals we need to have some benchmark to compare them to. We need to have some way of calculating expected numbers for each profession. If a profession had more sales than the expected number then that takes us one step closer to being able to state that there is an unexpected difference between the amounts of products sold to each profession.
To determine the expected values we have to think a little about probability.
For any sale, what is the probability of the occupation being Professional? There are 5 occupations and each sale can only be assigned one occupation so the probability of it being Professional is 1 in 5 i.e., 20%. In fact the probability for each occupation is 20%, assuming there is no bias in the data.
So, for any set of unbiased data, we would expect 20% to be Professional, 20% Clerical, 20% Management, 20% Manual and 20% Skilled Manual.
Thus, for 1000 records, we would expect each occupation to have 200 sales.
But common sense tells us that we’re very unlikely to get exactly 200 sales for each profession. So what we really mean is that we expect to get approximately 200 sales for each profession.
Looking at the actual data, we see that Clerical appears in 177 of the 1000 records. That’s a difference of -23 from the expected value. Is this close enough to 200 to be considered approximately 200?
Skilled Manual has 255 records; a difference of +55. Is this difference big enough to be significantly different?
In our data there are 255 Skilled Manual records. What is the probability of this? That is, what is the probability of there being 255 Skilled Manual records out of 1000 when each record could have any one of five occupations?
Also, since we expect 200 Skilled Manual records it seems logical to also expect that the probability of having 200 records is higher than the probability of having any other number. We’d also expect that the probability of getting 205 records would be higher than the probability of getting 220 (because 205 is closer to 200 than 220 is).
Thus, as we move away from 200 we would expect the probabilities to decrease and decrease towards 0.
Finally, we would expect that there would come a point when the probabilities are so small it would be extremely unlikely for these values to occur in unbiased data. Therefore, if these values did occur then our data has a high likelihood of being biased.
But, how do we calculate these probabilities?
We calculate them using something called the binomial distribution.
Using the Binomial Distribution
Data is expected to have a binomial distribution if:
There are a finite number of records;
The value of one record is not influenced by the value of any other record;
Each record has a value of either true or false;
The probability of a record having a value of true is the same for each record;
At first, it doesn’t seem like our data has these properties. But it does - we just need to look at it slightly differently.
We’re interested in the Occupation field and we know that there are five occupations. But instead of considering all five occupations at once, let’s consider just one occupation at a time. Let’s start with Skilled Manual.
We have a finite number of records (1000); the occupation of one record is not influenced by any other record; the probability that the record has a value of Skilled Manual is always the same (20%); and each record has an occupation of either Skilled Manual or not Skilled Manual.
Thus, the Skilled Manual data exhibits the required properties and therefore we expect that the Skilled Manual data is binomially distributed.
Using the same principles we can see that the same is true for the remaining four occupations. Instead of considering all five occupations at once, we consider them one at a time. Each occupation should be binomially distributed. If we find an occupation that isn’t, then there is a likelihood of bias in our data.
Excel has a function called BINOMDIST which calculates the probability of getting X true values out of a fixed number of records where the probability of each record being true has a certain, fixed value. In other words, BINOMDIST calculates the binomial probability.
For example, we can calculate the probability of getting 150 true records out of 1000 where each record has a 20% probability of being true by doing:
=BINOMDIST(150, 1000, 0.2, FALSE)
To illustrate how the probabilities are distributed (i.e., where the high values are and where the low values are) let’s calculate the BINOMDIST for each value between 1 and 1000. In other words, we will calculate the probability of getting 1 true record out of 1000, 2 true records, 3 true records etc. We’ll then plot these values on a chart.
Consider the following chart.
Notice that the highest value (the peak of the graph) is at 200? Also notice that as we move away from 200 the probabilities decrease towards 0?
Looking at the chart we can state that, if our data is binomially distributed, we are extremely unlikely to have 150 or fewer records for any occupation. Therefore, if we indeed have fewer than 150 records for any occupation it implies that our data is unlikely to be binomially distributed.
Notice the careful use of unlikely in the previous sentence. When dealing with probabilities we can rarely be 100% certain of anything. Instead we state facts as having a certain likelihood of being true. This is called a confidence level.
The confidence level is the final tool we need in order to conclude whether our data is biased towards certain occupations or whether the differences between occupations occur purely by chance.
Before we draw conclusions about our data we need to decide what confidence level we are happy with. A 95% confidence level means that there is a 5% chance that we will draw the wrong conclusion; a 99% confidence level means that there is a 1% chance we will get it wrong.
How confident do we need to be?
The confidence level is something that we need to decide for ourselves. Typically it is 95% or 99%. For the purposes of this article, let’s stick with 95%.
Now that we have our confidence level we can calculate what’s called the critical values of the binomial distribution.
We will calculate two critical values. These are:
Lower bound: the smallest number of true records for a binomially distributed variable (at a specific confidence level);
Upper Bound: the largest number of true records for a binomially distributed variable (at a specific confidence level);
We calculate the critical values using the CRITBINOM function.
To calculate the lower bound we do:
=CRITBINOM(1000, 0.2, 0.025)
This returns a value of 176.
To calculate the upper bound we do:
=CRITBINOM(1000, 0.2, 1-0.025)
This returns a value of 225.
Notice that the critical values depend on the total number of records in our data (1000), the probability each record has of being true (20% or 0.2) and the confidence level we want to work with (0.5/2 = 0.025). If these values change, the critical values change. So, for example, if we had 2000 records instead of 1000, the critical values would be different.
Also notice that we specify the confidence level as an error level. That is, we don’t supply a 0.95 confidence level we supply a 0.05 error level.
What’s more, we are performing what’s called a two-tailed test which means that we are looking for differences that are significantly lower or significantly higher than the expected value. Therefore we have to halve our error level from 0.05 to 0.025.
So now we have the bounding values. These enable us to determine whether or not our data is binomially distributed.
We expect each occupation to occur 200 times out of 1000. These critical values tell us how close to 200 the actual numbers of occurrences have to be in order for them to be considered close enough.
Thus, if the number of records for a given occupation is between 176 and 225, that occupation has a 95% likelihood of being binomially distributed. Otherwise, there is 95% likelihood that the occupation is not binomially distributed.
If we discover that our data is not binomially distributed then something other than chance is determining the data.
Producing the Report
Armed with this knowledge, we are ready to build our report for the Occupation field. Let’s restate the question we’re asking ourselves: Do certain professions buy more of our products than other professions?
Here’s the report:
The Count column simply uses COUNTIF to count the number of occurrences of each occupation. We have added some conditionally formatted data bars to offer some visual indication of the distributions.
The Score column also uses conditional formatting. An up arrow means that that the occupation is more frequent than expected; a horizontal arrow means that the occupation occurs the expected number of times (i.e., is binomially distributed) and a down arrow means the occupation occurs less frequent than expected.
The formula in the Score column is as follows (use the color highlights to interpret the cell references):
That is, the formula compares the count to the lower and higher bounds and returns -1 (less than lower bound), 0 (between lower bound and higher bound) or 1 (greater than higher bound).
The functions in the Lower Bound and Higher Bound cells use CRITBINOM as before.
Here’s the function in the Lower Bound cell:
Interpreting the Results: What Does this Really Mean?
Let’s look again at the table of results:
According to this analysis, only Clerical is binomially distributed. All the other occupations are not. So, what conclusions can we draw from this? What does this analysis really tell us?
Let’s take Skilled Manual as an example.
Our analysis tells us we can be 95% certain that the Skilled Manual occupation occurs significantly more frequently in the data than we would expect it to.
Does this really mean that Skilled Manual workers prefer our products?
Well, the numbers alone do not tell us that. All the numbers tell us is that we can be 95% sure that an unexpectedly high number of Skilled Manual workers bought our products. But there may be various reasons for this. Maybe our shop is next to a construction site or maybe it is next to a factory.
These important real-world factors are not conveyed in the numbers and instead must come from ourselves, as information workers. We must take the numbers and apply our business knowledge to them.
The numbers don’t contain magical answers, but they do provide us with some interesting properties of our data that we should seek to explain and understand.
We can use the numbers as a tool to help us make intelligent business decisions, but the numbers alone don’t make the decisions for us.
As everyone who has followed this blog already knows, we introduced a new file format with Office 2007 (and Excel 2007) with the extension .xlsx called Office Open XML (OOXML). Microsoft submitted the new file format to Ecma International, and in December 2006, that file format was approved as ECMA-376, and Ecma submitted the standard to ISO/IEC JTC 1 for fast-track processing to become an ISO standard. Over the last 15 months, many conversations have taken place between Ecma and the National Bodies that make up ISO, and comments from those National Bodies were addressed at a Ballot Resolution Meeting during the last week of February 2008. Through the month of March, the National Bodies then had the opportunity to consider the changes made to OOXML at the meeting, and today ISO officially announced the results of the voting.
Approval required at least 2/3 (i.e. 66.66 %) of the votes cast by national bodies participating in the joint technical committee ISO/IEC JTC 1, Information technology, to be positive; and no more than 1/4 (i.e. 25 %) of the total number of ISO/IEC national body votes cast to be negative. These criteria have now been met with 75 % of the JTC 1 participating member votes cast positive and 14 % of the total of national member body votes cast negative.
The Excel team views this as a very positive development, and is committed working with both Ecma and ISO to continually evolve and develop OOXML as an international standard.
More information is available for those interested:
Today’s author, Helen Hosein, a Program Manager on the Excel team.
Certain shadows created in Excel 2003 look different Excel 2007. In particular, you might notice that your old cell shadows, as well as shadows on things like Chart Titles might look a bit different when you open your workbooks in Excel 2007. This is because Excel treats these as shapes with no fill - a bit like a wire frame. The shadow that it casts is the rectangle of its outline. Only when the frame is filled in, does it cast a whole shadow. Similarly, Excel 2007 just draws a shadow on the border of a shape with no fill, instead of on the whole shape. Since many of you, our customers, miss being able to create these types of shadows, we are assessing the possibility of bringing back this capability in the next version of Excel. For now, though, there are ways to work around this and create great-looking shadows on cells and chart elements in Excel 2007.
An Example - Shadows in Excel 2003 vs Shadows in Excel 2007
Two of my very good friends, Rita and Alfie, are thinking of moving to London soon. Man, I’ll miss them. They’re shopping around for “flats” (they’re already learning the lingo) but aren’t sure which neighborhoods to consider. Rita, being web savvy, decides to download some of the 2005 rent data from the Greater London Authority and make an Excel chart so she and Alfie can gauge the relative prices.
Rita copy/pastes the average rent for a studio in each borough into Excel 2003. She also adds the overall average rent for comparison. To make the overall average stand out, she adds a Cell Shadow to that row.
Excel draws a rectangle shape around the selection and adds a shadow to it, giving that row a great effect that draws attention.
Now Rita adds her chart of the rents. She likes gray, so she makes the whole chart background that color. Since the shadow looks so good on the summary, she adds a matching shadow to my Chart Title, and sets the Area fill to None so that the chart area color shows through.
Once she’s done, Rita sends me the workbook to get my opinion. After all, I’m her best friend, and I’m hoping to visit her soon in her new neighborhood. I open her workbook in Excel 2007.
Wow! Do you see what I see? Rent in the City of London is really high. The second thing I notice, however, is that the shadows Rita put on “All Boroughs” and on the Chart Title are not quite what Rita might have intended. The good news is that these can be fixed in no time.
Chart element shadows in Excel 2007
Remember, when Rita added the shadows to the Chart Title and the Legend, she also set the Area fill to None. Excel 2007 sees these chart elements as empty rectangle frames, since they have no fill. Because of that, the shadows on them look just like a second empty rectangle. The quick, easy fix is just to give them a fill.
Step 1: Right-click on the Chart Title and choose Format Chart Title.
Step 2: On the Fill tab of the Format Chart Title dialog, choose Solid fill.
Step 3: Choose the same color fill as the background (in my case, the same shade of gray that Rita’s always on about).
Now the Chart Title looks the same as it did in Excel 2003, and it still blends with the Chart Area because I gave it the same fill. I could also have done that by using the Shape Fill menu on the Format tab under Chart Tools on the Ribbon.
Cell shadows in Excel 2007
Now the shadows on the chart are fixed, but the cell shadow still doesn’t look the way it used to. Fortunately, one of our testers, Jon Adams, wrote a convenient add-in that helps solve this problem. Jon’s add-in gives a great example of how you can use VBA to create your own cell shadows. You can download Jon’s by clicking the attachment at the bottom of this post. Here’s how you install the add-in.
Step 1: Click on the Office button and choose Excel Options.
Step 2: On the left, choose Add-Ins, and under Manage: choose Excel Add-Ins and click Go…
Step 3: An Add-Ins dialog will appear. Click Browse… and choose CellShadows (the file you just downloaded).
Step 4: Now Cellshadows will appear in your list of Add-Ins with a check mark next to it. Click Ok.
Once you’ve installed the CellShadows add-in, you’ll notice a new chunk called Cell Shadows on the Insert tab of the Ribbon. The Shadow menu allows you to insert a new cell shadow on your selection, just like you could in Excel 2003, whereas the Convert Cell Shadows button converts your old cell shadows so that they look right in Excel 2007. I want to convert Rita’s cell shadow, so I hit the Convert Cell Shadows button.
Just like that, Rita’s cell shadow is fixed.
So how did that work? If you try this yourself, you’ll find that if you click on the shadow, you can see that it’s actually a group of rectangles. The convertShadows macro looks for all the shapes in the workbook with an offset shadow and no fill, and replaces each of them with a group of two gray rectangles at the matching offset where the shadow would have existed. The CellShadowFromRange macro does the same thing, but uses the selected range as a starting point rather than an existing shape. When creating a new cell shadow, the add-in will also add a border around the selection to make it look more like Excel 2003’s shadows. If you’re converting old cell shadows that lined up perfectly with the original range that was selected (that is, you didn’t move the cell shadow shapes after creating them), you can optionally add a border to the range after conversion in Excel 2007 to complete the look.
Step 1: Go to the Home tab on the ribbon.
Step 2: On the Font chunk, click on the Border menu.
Step 3: Choose Outside Borders.
Now the shadows all look just as great in Excel 2007 as they did in Excel 2003. I’m still not sure to which neighborhood my friends will be moving, but Rita says Alfie is rather keen on Haringey.
Today’s author, Pej Javaheri, a Product Manager on the Excel Services team, talks about one of the more common configuration questions we hear about from customers.
One of the challenges in sharing Excel workbooks is ensuring that the right people see the right data. With Excel Services, this has become a lot easier ensuring user’s only see the information they are authorized to view and not sharing information that is privileged or out of their scope. With a few simple configuration steps, workbook authors can quickly setup their reports to only display the information that is authorized by user’s security privileges.
This document describes how to setup SharePoint SharedServices for Excel Services, and how to setup your ODC connection to refresh workbooks on open.
Ensure the server has the right permissions to access data connections, and run Excel workbooks on the server. To do this, go to the Shared Administration page of your Shared Services.
First, modify the Trusted File Locations to include the locations that you will allow Excel workbooks to be accessed to display on Excel Services. In the Address field, enter the location of where the workbooks are stored. Most likely this will be in a SharePoint Document library. If you want to enable the whole SharePoint site, enter the root address of the SharePoint site, such as http://SharePoint. Under Location, ensure you select Children Trusted if you will be storing workbooks in folders from this location, especially if you are going to enable the whole site.
Under External Data, select either “Trusted data connection libraries only”, or “Trusted data connection libraries and embedded” to allow Excel Services to refresh data from workbooks that access data through ODC files.
If you will be calling User Defined Functions (UDF), then check the box under User-Defined Functions. You may also want to disable the Warn on Refresh option under External Data
Click OK to return to the Excel Services Trusted File Locations screen and apply all the changes otherwise a warning dialog box will require users to okay the action before refreshing the workbook in Excel Services.
You should have something like this, which identifies the location of the workbooks that will be accessed by Excel Services. You can add as many locations as required for each shared service environment you setup.
Next, we need to identify where the data connection libraries are stored for Excel Services to access and refresh data.
Go back to the SharedServices administration page, and click Excel Services Trusted Data Connection Libraries, and then click Add Trusted Data Connection Library.
Enter the location of the document library that the ODC files are saved in. In this example, I have added the default location under the Reports document library
Creating the ODC file in Excel
In Excel, select From Other Sources under the Data tab. Select the data source type and then enter the required credentials in the Data Connection Wizard. In the File Name field, select Browse and then type the location of the Trusted Data Connection Library (alternatively, you can save the ODC file locally, and the upload it to the Data Connection Library via SharePoint).
If the connection requires specific authentication requirements, or single-sign on select the Authentication Settings and set the appropriate item.
Now that you have the connection created, you need to modify the properties of the connection so that it is set to refresh the workbook when it is opened every time.
Still in the Data tab, select Properties and then check Refresh data when opening the file.
Create your Excel workbook like normal, and then publish to Excel Services. If data is coming from a relational source, publish it in a Pivot Table.
And there you have it. Whenever users open the workbook, the data is automatically refreshed using their credentials to access the data source.
Dan Parish, from the Microsoft Excel Services team, presented how you can extend the capabilities of Excel Services to a packed room of over 300 with tremendous feedback. Not to worry if you missed that sessions as we will be posting the content that was presented in a separate blog to showcase examples of how you can use Excel Services.
Many of you who have been using Excel for a long time have most likely seen some of the creative, interesting, and rather unusual uses of Excel that people have conjured up. I’m talking about things like writing the Pac-Man game completely in Excel, stuff you wouldn’t expect to see in a critical business application like Excel, but thanks to the ingenuity of spreadsheet hackers we get to see how the power and flexibility of spreadsheets can be put to use.
I was reminded of this today when a colleague forwarded me this Slashdot article about someone who used Excel to create a 3D rendering engine (btw, this reminded me of a similar 3D animation engine written by a tester on our team that uses the new OfficeArt rendering platform in Excel 2007), and it got me wondering what other interesting uses have people come up with that I haven’t seen before.
I’ve seen a quite a few of these in my time here on the Excel team (we’ve even posted a couple of our own - check out Conway’s Game of Life and Memory), and I’m sure many of you have too. So let’s hear it! Tell me about the interesting, crazy, weird, unusual and cool spreadsheets you’ve come across or built that you never thought you’d see in Excel.
Today’s author, Sanjay Kulkarni, a Program Manager on the Excel Services team.
We have heard from several customers about scenarios where they have workbook dependencies. So the output of one workbook is used in other workbooks for calculations. The dependencies could be a result of the business model meaning different people in the organization being responsible for the workbooks or it could simply be a way of isolating related logic in different workbooks. In this blog post we will look at a sample way of how these workbook dependencies can be handled by writing a little code using Excel Services Web Services API and SharePoint object model. Note that you will need MOSS 2007 with Excel Services configured to use what is described here.
To simplify things let us look at a scenario with two workbooks. At a high level workbook one (Rates.xlsx) has mortgage rates that are refreshed from the database. This workbook also adds the overhead and calculates the retail rate that can be offered to customers. The second workbook (Payments.xlsx) has a list of potential customers and information about them. The idea is to calculate the first workbook to get the retail rates and then use these rates to calculate the mortgage payment for the customers in the second workbook. We will use document properties to pass values from the Rates workbook to the Payment workbook.
The Rates workbook has a Pivot Table that has interest rates and a cell that stores the margin. To make the sample work independently we have removed the connection to the external database and instead we get the data from another sheet in the workbook however that can be easily changed. Here is what the Rates workbook looks like.
The Payments workbook has information about the clients, their mortgage as well as cells where the retail mortgage rates can be updated.
The following sections describe the steps involved with some code snippets. The complete code and the workbooks can be downloaded at the bottom of this post (see attachments). This is a console application and accepts the margin rate added for overhead as a parameter. We use a fixed constant in the workbook if no margin is specified. To simplify things I have omitted the error handling for the most part. You will definitely need to add robust error handling if you are planning to deploy it for a practical application.
First print the margin rate and some initialization
if (args.Length > 0) Console.WriteLine(“Margin rate is = “+ args[0] + “%”);
// Instantiate the Web service and create a status array object. ExcelService es = new ExcelService(); Status[] outStatus;
// Set credentials for requests es.Credentials = System.Net.CredentialCache.DefaultCredentials;
Now we open the workbook and set the margin rate in the Rates workbook, refresh data and calculate the retail rates.
// Open the Rates workbook, get new rates by refreshing the external data and // calculate. string sessionId = es.OpenWorkbook(ratesWorkbook, “”, “”, out outStatus);
//Set the input overhead margin rate if specified omcommand line. if (args.Length > 0) { es.SetCellA1(sessionId, “RatesSheet”, “Margin”, args[0]); }
// Refresh data and recalculate the workbook. es.Refresh(sessionId, “”); es.CalculateWorkbook(sessionId, CalculateType.CalculateFull);
Now that the retail rates are calculated; we get the retail rates and set them as document properties on the Payments workbook. The advantages of using the document properties for storing values are firstly the properties are associated with the document so if you update the workbook they will still be preserved. Secondly since they are stored by SharePoint the process of calculating rates and payments can be done at different times if needed by the business process.
//Get the calculated rates from the workbook double rate1Year, rate3Year, rate5Year, rate30Year; valObj = es.GetCellA1(sessionId, “RatesSheet”, “Rate1Year”, false, out outStatus);
//Add the different rates as document properties for the payment workbook. SPSite site = new SPSite(paymentsWorkbook); SPWeb web = site.OpenWeb(); SPFile file = web.GetFile(paymentsWorkbook); Hashtable tab = file.Properties;
//Update the SPFile object so that the properties are stores in the database. file.Update(); site.Close();
The next step is to open the Payments workbook, get the retail rates from the document properties and update the Payments workbook with the new rates. Note that since the document properties are stored by SharePoint we can do the following steps at a different time than the updates to the rates that we did above. That could be critical is some business processes since the two could be owned by different people or business units in the organization.
//Open the payment workbook, update the rates from the document properties. sessionId = es.OpenWorkbook(paymentsWorkbook, “en-US”, “en-US”,out outStatus);
//Read the rates from the document properties. SPSite site1 = new SPSite(paymentsWorkbook); SPWeb web1 = site.OpenWeb(); SPFile file1 = web.GetFile(paymentsWorkbook); Hashtable tab1 = file1.Properties;
//Set the rates in the Payment workbook. es.SetCellA1(sessionId, “Payments”, “Rate1Year”, rate1Year); es.SetCellA1(sessionId, “Payments”, “Rate3Year”, rate3Year); es.SetCellA1(sessionId, “Payments”, “Rate5Year”, rate5Year); es.SetCellA1(sessionId, “Payments”, “Rate30Year”, rate30Year);
Finally we calculate the Payments workbook and print out the monthly payments. To simplify the sample we just print the monthly payments here but this can be easily updated to perform different tasks like sending email to the clients with the information. Or better yet we could compare their existing monthly payment to the new calculated payment and send an email if the new payment is less (It is time to refinance!).
// Recalculate the workbook to get new payment infomration. es.CalculateWorkbook(sessionId, CalculateType.CalculateFull);
// Get the payments and print them. In real life you could add logic here // to save the workbook or to iterate through the table and send an emai // using the email id in the workbook. valObj = es.GetCellA1(sessionId, “Payments”, “JackPayment”, false, out outStatus); double monthlyPayment = Convert.ToDouble(valObj); Console.WriteLine(“Jack’s Payment is= {0:C}”, monthlyPayment); valObj = es.GetCellA1(sessionId, “Payments”, “JudyPayment”, false, out outStatus);
// Close the session for Payments workbook. es.CloseWorkbook(sessionId);
This logic can be extended to scenarios where the output of one workbook serves as the input for several workbooks with some modifications. In the example above we are using specific properties on the workbook. Note that if there are dependencies between multiple workbooks then properties written while updating one workbook may get overwritten by another. Also one may want to use a specific namespace for the properties by prefixing them so that they are easily identifiable.
The example implements a scenario of calculating the pricing for car insurance based on the details entered in a form. The form can be collected on paper and entered manually into Excel or collected online and fed into Excel services to perform the calculation.
Each value is connected to a numeric value. High values means less risk to insure this car to this driver. More driving experience, safer car, better neighborhood etc. Of course all data is completely fake and does not represent any real data.
Each field might have a different number of possible values, some have only Yes/No and some might have 10 different values. All list of values where extended to the last row so to avoid blank appearing as an option for input. We can’t really prevent the user from choosing multiple options or the all option so an error message is displayed when the user does that.
Today’s author, Ben Rampson, a Program Manager on the Excel team.
Customers who author charts in presentations and reports often spend a significant amount of effort modifying their charts to get them looking just the way they want. While the new Chart Style and Chart Layout features in Office 2007 provide some great one-click options to quickly alter the appearance of a chart, there are still times when one needs to modify a chart even further. A company policy may require that a chart use particular colors, a project may demand a necessary chart layout, or a complex chart may necessitate the setting of many specific element properties. While there is always a onetime cost required to set up a chart for the first time, there is an easy way avoid these costs in the future when you need to re-create a similar chart.
One frequently underutilized feature in charting is the Chart Template. A chart template allows a user to quickly save and reapply settings from a previously created chart. Chart templates are standalone .crtx files that can be applied in a similar manner as chart types. In addition to saving the chart type, template files also contain chart element setting and formatting information. This information includes settings such as line and fill properties, axis settings, and element placement.
It also should be noted that chart templates do not save customer data, preventing customer information from being copied and saved into template files (customer data includes: data point and series values, text on titles, etc.). Although data is not saved, the existence, formatting, and layout of all elements is stored in the template. Assume a template is created from a chart that contains a title. When the template is applied the resulting chart will have a title in same location and with the same formatting as the chart used to generate the template, however, the default text of “Title” will appear in the element since the title’s data has not been stored in the template.
Steps to Create a Chart Template
Insert a chart to use as a starting point for your template. Note that chart templates can be created and used in any of the three Office applications that have the shared charting engine: Excel, PowerPoint, and Word.
Format the chart the way you normally would until the chart has the desired appearance. In my example I have made the following modifications to the chart:
Applied a custom fill color to the series
Deleted the legend
Removed the horizontal axis line
Set the horizontal axis maximum to 10
Changed the line color of the gridlines and vertical axis line
Reversed the categories order on the vertical axis
Set the horizontal axis crosses at setting to maximum value
Added a chart title and entered the title text
Changed the chart titles font to be the same color as my series
The resulting chart:
Save the chart template.
With the chart selected, click on the Design tab in the Ribbon
Click on the “Save As Template” button located in the Type chunk
Enter a template name and click SaveNote: Saving chart templates as standalone .crtx files is a new feature for Office 2007. By using standalone files, chart templates can easily be shared amongst different users. The saved template location for Windows Vista users is C:\Users\<user_name>\AppData\Roaming\Microsoft\Templates\Charts (C:\Documents and Settings\<user_name>\Application Data\Microsoft\Templates\Charts for Windows XP users). This folder location can also be found using the “Manage Templates…” button in the Chart Type dialog.Once a template file is saved in this folder it can be accessed and applied in any of the three charting applications.
Steps to Use a chart template
Select the data for your chart
Click on the Insert tab in the Ribbon
Open the Insert Chart dialog
If you are using Excel then select the “All Chart Types” option from the bottom of any dropdown in the Charts chunk
If you are using PowerPoint and Word then select the “Chart” button.
Tip: if you have an existing chart you can select the “Change Chart Type” option to launch the same dialog
Select the Template tab from the top of the left navigation menu
Select a chart template from those listed on the right side of the dialog
Select “OK” to apply the templateThe resulting inserted chart created from a different data set. While not completely finished it comes very close:
Complete the chart by adding back the text elements that were not saved in the chart template
In my example all I need to do to complete my chart is to modify the chart title’s text. In other cases it may be necessary to make additional minor changes such as tweaking axis settings stored in the template that do not work for the current data set.
Creating this chart was simple and only required two steps, inserting a chart using the desired template and entering the title’s text. This is a significantly easier process when compared to the list of steps I completed to build this chart for the first time. The finished chart:
Note that the above chart has fairly complex formatting and takes great advantage of the template feature; however, even templates with limited formatting can still save significant time in the chart creation process.
In summary, when creating a complex chart that you may use again in the future, remember to take advantage of chart templates. This powerful tool in charting can help you avoid duplicating effort and allows you to easily generate consistently formatted charts.
If you have any ideas on how we can improve chart templates in the future please feel free to leave your suggestions in the comments section.
Today’s author: Dany Hoter, a product planner who works on the Excel Services team.
Excel has a feature called Data Validation that controls the possible values a user might enter into a cell or a range of cells.
As you can see there are many options for validating data entry. The most popular is probably validating against a list of values. The list can be included in the validation definition or can be a region in the sheet referenced from the dialog.
Recently I saw an example created by a customer that validates input using a PivotTable instead of data validation. The idea is to use only the report filter area of the PivotTable and to use the values selected in multiple filter fields as inputs for a model:
In the example you see multiple input fields and when clicking the filter icon the user will see a list of values and can choose one.
The advantage of this method is easier maintenance of multiple lists of values used for validating multiple fields. The range that the PivotTable is based on looks a bit odd because usually PivotTables are based on rectangular ranges and not something that looks like this:
In order to avoid the “(blank)” value appearing in the PivotTable drop-down list of values, fill the last value in each category all the way down to the last row of the region:
Use of PivotTable for validation in Excel Services
So far I explained how a PivotTable could be used as a validation method in Excel. The impact of using this technique is not significant until we apply it to Excel services. Excel services does not support data validation and any workbook containing data validation will not be loaded by Excel Calculation Services (ECS). In addition, entering inputs to a model in Excel Services is not as user-friendly as Excel since values cannot be entered directly into cells. Using PivotTable filters for validation is fully supported in Excel Services and can solve these two problems. We have a friendly way to input values into a model and also to apply a validation against a list of values. The same example might look like this in the browser:
So if you are looking to build a spreadsheet that can be consumed by Excel Services and provides user-friendly, validated, input, give the PivotTable report filter a try.
If you are passionate about business intelligence (BI), you may be interested to know that a new Microsoft blog has started that is all about BI. It is aptly named The BI Blog.
One interesting recent post talks about how Microsoft was recently placed in the Leaders Quadrant for BI platforms in Gartner’s Business Intelligence Magic Quadrant.
Today’s author: Allan Folting, a program manager who works on the Excel team.
I have gotten lots of questions from customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:
Filtering individual calculated members
Showing non-visual totals when filtering
I have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:
Adding calculated members/measures
Adding named sets
Today I’ll discuss these topics and outline current ways and workarounds to address this.
Behaviors in Excel 2007 specific to Analysis Services 2005 Service Pack 2 (SP2) cubes
Let’s start with a bit of background information.
With Analysis Services 2005 SP2+, Excel 2007 uses subselects in the MDX queries generated when filtering is applied to a PivotTable.
In the following example of MDX generated by an Excel 2007 PivotTable, the user is filtering a geography hierarchy to only show United States and Australia which is represented by the subselect part of the query (in bold font).
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM
(
SELECT ({[Customer].[Customer Geography].[Country].&[United States], [Customer].[Customer Geography].[Country].&[Australia]}) ON COLUMNS FROM [Adventure Works]
Subselect support in the OLAP server is required in order for the new Label, Date and Value filtering features to be available in Excel 2007 PivotTables. The reason is that the subselect feature is designed for making Analysis Services calculate visual totals while filtering which was also an explicit design goal for Excel 2007 PivotTables.
When multiple filters are applied to different hierarchies at the same time, the filters are applied in the order that the user added them, using subselects, which ensures an intuitive filter experience where filtering is always based on the totals currently displayed in the PivotTable.
The reason why subselects are only used with Analysis Services 2005 SP2 or newer versions is that Analysis Services 2005 introduced a provider property in SP2 so that client applications can determine whether the provider supports subselects. The provider property is MDPROP_MDX_SUBQUERIES.
By reading this provider property to determine whether subselects are supported, Excel exposes the new filtering features with any OLAP provider that implements this property (and supports other fundamental OLAP provider capabilities needed, I included a link to more information on provider properties that Excel reads in the additional resources section below).
Now, let’s discuss the specific features affected by using subselects for filtering. I’ll start with filtering individual calculated members.
Filtering individual calculated members with Excel 2007 PivotTables connected to Analysis Services 2005 SP2+ cubes
Calculated members are not supported in subselects with Analysis Services 2005 so Excel 2007 does not offer filtering on individual calculated members for Analysis Services 2005 SP2+ servers.
However, you can choose to display all calculated members, or no calculated members at all, in the PivotTable Options dialog box using the “Show calculated members from OLAP server” option.
Note that this does not apply to calculated members in the Measures hierarchy (calculated measures); those are always displayed regardless of the option.
PivotTable Options dialog with the “Show calculated members from OLAP server” option
The screenshot below illustrates the filter drop down in a PivotTable for a hierarchy with calculated members (”Budget Variance” and “Budget Variance %”).
Note that the checkboxes for the calculated members are disabled and therefore cannot be unselected.
Calculated members cannot be individually selected when subselects are used for filtering with Analysis Services 2005 SP2+
Non-visual totals with Excel 2007 PivotTables connected to Analysis Services 2005 SP2+ cubes
Another implicit behavior of subselects with Analysis Services 2005 is to always generate visual totals.
For this reason, PivotTables created using Excel 2007 do not allow the user to turn off visual totals with Analysis Services SP2+ cubes.
The first screenshot below illustrates a PivotTable with visual totals. The total for United States is the sum of the states currently in the filter (California, New York and Washington).
PivotTable displaying visual totals
The second screenshot illustrates a PivotTable with non-visual totals. The total for United States is not affected by the fact that some states are filtered out; it is still showing the total sales amount for all existing states.
PivotTable displaying non-visual totals
Workarounds
Individually filtering calculated members and generating non visual totals are important features for common scenarios. The Excel and Analysis Services teams are working together on a future solution for lifting these limitations and this section outlines current workarounds for enabling these features in Excel 2007 with Analysis Services 2005 SP2+.
The workaround for creating PivotTables that support both non visual totals and filtering individual calculated members with Analysis Services 2005 SP2+ cubes is to create PivotTables in Excel 2007 with a pre-2007 version.
PivotTable versioning was introduced in Excel 2000. PivotTables created with a certain version ensures that the PivotTable is compatible with the corresponding version of Excel in that it is generally created with the same default settings as in that version of Excel and it exhibits the same behaviors. This is also true when using the Excel PivotTable object model through code. A benefit from this is that users can expect macros created in previous versions of Excel to still work exactly the same way in newer versions of Excel.
The table below outlines the different PivotTable versions supported in Excel as well as which version is the default created in different versions of Excel.
Version of Excel
Version number
Default PivotTable version created
Excel 2000
9
PivotTable.Version = 0 (xlPivotTableVersion2000)
Excel 2002
10
PivotTable.Version = 1 (xlPivotTableVersion10)
Excel 2003
11
PivotTable.Version = 1 (xlPivotTableVersion10)
Excel 2007
12
PivotTable.Version = 3 (xlPivotTableVersion12)
There is an additional PivotTable version supported in Excel 2007, namely version 2 (xlPivotTableVersion11). However, there is no functional difference between version 1 and version 2 PivotTables. Since no version of Excel creates version 2 PivotTables by default, it isn’t included in the table above. Version 2 was added in Excel 2007 for completeness although Excel 2003 creates version 1 PivotTables.
For PivotTables with a version number less than 3, Excel will generate Excel 2003 style MDX queries (no use of subselects) and will support filtering calculated members individually and toggling visual totals.
As indicated above, the drawback of working with old version PivotTables is that the new filtering features are not available.
There are two ways in Excel 2007 to create PivotTables with a specific version:
Work with Excel 2007 in compatibility mode (this mode is on when you open xls files) since then Excel automatically creates version 1 PivotTables
You can explicitly specify the version of a PivotTable when creating it if you create it using the object model (example below)
When Excel 2007 is in Compatibility Mode, version 1 PivotTables are created
The VBA code below creates a version 1 (PivotTable.Version = xlPivotTableVersion10) PivotTable, turns on the new Excel 2007 default layout and turns off visual totals. It is using the sample Adventure Works cube that comes with Analysis Services 2005. You’ll need to change the OLAPServerName to your actual OLAP server name and make sure there is a worksheet named Sheet1 which is empty in the area where the PivotTable is placed to try out this code.
Sub CreateV1PivotTable()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion10)
The screenshot below shows the PivotTable Options dialog box for a version 1 (xlPivotTableVersion10) PivotTable in Excel 2007 with the option to toggle visual totals using the “Include filtered items in totals” checkbox. Non-visual totals are displayed when the checkbox is selected.
OLAP PivotTables with a version less than 3 in Excel 2007 allow users to turn off visual totals in OLAP PivotTables using the PivotTable Options dialog box (”Include filtered items in totals”)
Note that if you save to an Excel 2007 file format while Excel is in compatibility mode, all PivotTables with a version number less than 3 will be marked for upgrade to version 3 (xlPivotTableVersion12) and the next refresh you perform on each PivotTable will do the actual upgrade. Once a PivotTable is upgraded to version 3, you’ll lose the ability to filter on calculated members individually and toggling visual totals.
If you want to avoid the automatic upgrade, before refreshing the PivotTables, go to each one and set PivotTable.PivotCache.UpgradeOnRefresh to False using the object model. For example, in the immediate window (press ALT+F11 and then CTRL+G to display the immediate window) type and run Activecell.PivotTable.PivotCache.UpgradeOnRefresh = False with the active cell inside the PivotTable.
Turning off PivotTable version upgrade for the selected PivotTable in the Immediate window
Additional resources
Excel 2007 and SQL Server Analysis Services 2005 Service Pack 2
Creating calculated members/measures and sets in Excel 2007 OLAP PivotTables
Excel doesn’t offer a user interface for creating calculated members/measures and sets in PivotTables connected to OLAP cubes. However, in the object model there is support for creating personal calculations and sets in PivotTables with OLAP data sources.
The CalculatedMembers collection holds calculations and sets private to a PivotTable and it has an Add method for creating new calculations and sets.
Below are examples of creating a calculated measure, a calculated member and a named set using the CalculatedMembers collection of the PivotTable object. All the examples are using the sample Adventure Works database that comes with Analysis Services 2005.
Example of adding a calculated measure in an Excel PivotTable connected to an OLAP data source:
Sub AddCalculatedMeasure()
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Calculated measure “Internet Sales Amount 25%” added to an OLAP PivotTable using the CalculatedMembers collection in the object model
Example of adding a calculated member to the Adventure Works Product level of the Product Categories hierarchy in an Excel PivotTable connected to an OLAP data source:
Sub AddCalculatedMember()
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
The screenshot below shows the PivotTable with the calculated member (”Mountain-100 Silver, 38 25%”) added to the Product level of the Product Categories hierarchy.
Calculated member “Mountain-100 Silver, 38 25%” added to an OLAP PivotTable using the CalculatedMembers collection in the object model
Example of adding a named set in an Excel PivotTable connected to an OLAP data source:
Sub AddNamedSet()
Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField
The screenshot below shows the PivotTable with the named set (”Mountain Bikes”) added.
Named set “Mountain Bikes” added to an OLAP PivotTable using the CalculatedMembers collection in the object model
Note that Excel 2007 only support named sets consisting of members from a single hierarchy.
Also note that even though Excel Services 2007 doesn’t support Excel workbooks with macros/code, you can still use code as illustrated here to create calculated members/measures and named sets, remove the code after creating these and then publish the workbook to Excel Services. This way the calculations created in Excel can be exposed through Excel Services.
Do you carry a USB thumb drive with you? It makes a lot of sense to do so, now that you can get so much capacity at such low cost. I keep one on a keychain and I’m always adding useful applications to it. The ones I’m getting the most good use out of now are free synching applications, which allow me to keep entire folders on my computers synchronized with my USB thumb drive.
GoodSync is the easiest to use of the various synchronization applications I’ve found. It’s completely free, and now that I have the folders I want synchronized set up, I can keep them in synch on my computer and my USB thumb drive with one click.
GoodSync is actually capable of doing much more than I use it for. You can schedule to do automatic backups, for example, although I happen to use separate backup software for that application. Just like backing up locally, you can also use it to keep local media and FTP sites in synch.
Easy2Sync has a free version of its synchronization application, and for simple tasks it works fine. Easy2Sync’s interface relies on wizards so you can set up rules for synching and synch just the files and folders you want. I found the wizards to be a little more in the way of interface than I really needed, but you may be comforted by them.
Of course, both of these synch applications are good for matching up files and folders on many kinds of devices other than USB Flash drives. The good news is, no matter what you use them for, is that they’re free.
Scheduling meetings is a necessary chore we face in business. Managing multiple person’s calendars and finding a good time to meet involves many back and forth emails. This is especially true when the persons you’re trying to meet with are on another electronic calendaring system.
To tackle this problem, I looked at Tungle last week. Now let’s look at a similar scheduling application: TimeBridge.
TimeBridge’s approach for addressing this frustrating issue comes from research involving personal assistants. In my phone interview with TimeBridge CEO Yori Nelken, Mr. Nelken said the company spent hours interviewing personal assistants to find the insider’s tips and tricks for streamlining the scheduling process. The TimeBridge application is not just a calendar view with all attendees’ free/busy data overlaid on each other. They have built a meeting organization system with some handy add-ons to make it a powerful scheduling platform.
Currently TimeBridge supports two major calendaring platforms: Microsoft Exchange/Outlook and Google Calendar, including Google Apps for Your Domains. The TimeBridge application works best when your contacts with whom you wish to meet ‘connect’ their calendars to TimeBridge.
This is done on the Microsoft side by downloading/installing an Outlook toolbar. With Google, you enter your Google sign-on credentials or you go through a process to give TimeBridge access to your Google Calendar. Most privacy-conscious people will prefer the latter method.
The TimeBridge Process
Connecting your calendar isn’t necessary to arrange a meeting with TimeBridge, it just makes the process easier. For this post, I’ll run through the process assuming no participants have ‘connected’ their calendar to TimeBridge.
To begin scheduling a meeting, log in to TimeBridge and click “Schedule a Meeting”. Next, enter in the participant’s email addresses, your meeting topic and proposed meeting time(s). You can also add an optional conference call number at no charge. Click “Send” and the email invitations are sent out to attendees. You are also presented with an option to schedule a web conference with WebEx.
The recipients get a message with an invitation to pick which of your proposed meeting times works best for them. They mark each time slot with their preferences based on their schedule.
After the attendee marks their preferences, a message is sent back to the meeting organizer where he/she finalizes the meeting time, based on everyone’s provided availability.
Finally, all attendees receive an email that contains a meeting notice that can be placed on their respective calendars.
Upon finalization of the meeting, the originator is presented with a link to schedule a reservation (if needed) with OpenTable - an online restaurant reservation service.
About TimeBridge
Mr. Nelken said the company received funding in December of 2005 and began operation in January 2006. Their team of 24 has seen their usage double over and over about 4 times. They are targeting growth amongst users, rather than engaging CIO-types and IT shops. TimeBridge has forged a relationship with Google and are providing Google a foot-in-the-door with enterprise customers who are looking at Google Calendar as a possibility for their organizations.
To monetize the product, they have a very innovative approach. The optional conference call service is a revenue sharing agreement they have set up with the calling service providers. This gives the company the opportunity to provide customers with a no-advertisement (and potentially annoying) call service and on the back-end, TimeBridge makes money too. Additionally, the optional web conference option when arranging a meeting is an agreementw with WebEx whereby TimeBridge gets a fee for the new-users sign up.
Following the typical web 2.0 web service model, Nelken stated at some point they will have a premium package, but basic functionality will always remain free.
Conclusion
Tungle required a download and install of an IM-ish application that showed you your contact’s availabililty. I like that TimeBridge is an Outlook add-in bar, keeping me within Outlook during the scheduling process.
Another interesting feature with TimeBridge is the ability to show your free/busy data on your website and/or social network. You can register with TimeBridge and get either a widget or embed-able HTML code that you can use on any website. This might be advantageous for a busy person to show their free/busy data directly on their website for all those who want to schedule a meeting with them.
TimeBridge creates a process for meeting scheduling that is easy and conducive to how meetings are set up in the “real world”. The optional services makes scheduling meeting resources painless. Their integration with Google Calendar is a plus, however I’m waiting for them, like Tungle, to support iCal on the Macintosh. Nelken assured me an iCal application would be released in the near future.
The ink was barely dry on yesterday’s announcement of the new iPhone SDK before I started seeing announcements from developers that they’d be porting their applications to the newly-opening platform. Among applications of interest to web workers, at least Twitterific, OmniFocus, TypePad and Adium have announced their intent to produce native iPhone versions - and I haven’t made an exhaustive survey.
Of course, now that we know the SDK is out there in the wild, we can indulge in uncontrolled dreaming. If you’ve already got your iPhone, which applications are you hoping to see ported to do your work? If you don’t, which ones would tempt you to plunk down the money?
We’ve coveredRemember the Milk before to help you manage your lists and tasks. However, Remember the Milk and similar services require registration and a little bit of configuration. What if you’d like to have a reminder sent to you at a certain date/time, sans all the registration headache?
This is where a new site called YourLi.st comes into play. With YourList, you enter in a title and summary of the task, then set up whether the event needs has recurrence. In my testing, setting up a reminder generates an initial e-mail confirming the reminder.
On their front page, the site pledges to not re-use or sell your email address, but I’d recommend using a disposable email address if you are concerned with getting spammed by the new service.
Also, check out the bookmarklet if you want to quickly create reminders easily in your browser.
“Strong passwords.” That’s one of the mantras of online security, along with looking for SSL connections and using unique passwords for each site you register at. But do you know how strong is strong? If you need some help evaluating the security of a password you’re planning on using, try the Microsoft Password Checker. In addition to being an AJAX tool - so that the password you’re checking never actually leaves your own computer - it includes tips on creating good passwords.Of course, if you’re really using unique passwords for every site, you’ll also want to implement some sort of password-management tool, like the ones we’ve reviewedin the past. The best of these utilities will even generate unique, strong passwords for you. Use water to run your car!
Firstly, I viewing it as a tool to help me in my study. In this guise, I’d use it for accessing the Bible (I’m studying theology) and for note taking. This means that I’d need to be able to install some extra software (there are several open source Bible study applications around that use public domain translations of the Bible) and type on it.
Given your new icon a name and then, in the “Command” section type the full path to the program you wish to launch from your new icon. For example, the path to Opera (on my system) is
