Excel, ExcelWriter, Knowledge Base, OfficeWriter, SQL Server, SSRS, WordWriter

How to open a report from SQL Server Reporting Services with the Application object

Problem

You want to modify your reports with the WordApplication or ExcelApplication object after they’ve been populated with data by SQL Server Reporting Services. The ExcelApplication or WordApplication object’s Open method can’t pull a report from a report server, so how do you open a SSRS report with OfficeWriter?

Solution

For simplicity and ease of reading the code examples and text in this article refer to the ExcelApplication object exclusively. However, the exact same techniques can be used with the WordApplication object. The only strict requirement for opening reports from SSRS is for the Open method to have an overload that takes a Stream argument, which both objects have.

SQL Server Reporting Services exposes a web service that lets programs access and work with the reports on the server. The web service has a number of functions for interacting with a report server and in fact is the same API that the Report Manager tool is built on top of. One of its functions will let us retrieve a fully rendered report that we can then pass to the ExcelApplication object. Once the ExcelApplication object has opened the report, it can be used as if it were any other file.

Getting a Reference to the Report Server

There are a number of different ways to access a report server to manage your reports. The MSDN documentation has detailed instructions for all the different techniques. The most powerful way, and the one that the Report Manager application uses, is to use the report server’s web service, or SOAP API. The easiest way to use the web service is to add the report server as a web reference in Visual Studio. This will allow us to make web service calls to the report server as though it were any other object in a .NET project. I’ll provide a brief overview of the steps to add a report server as a web reference; details can be found at MSDN. Continue reading How to open a report from SQL Server Reporting Services with the Application object →

Email, RSS Follow
Email

Excel, ExcelWriter, Knowledge Base, OfficeWriter

Workbook colors are not displayed as expected in older versions of Excel

Problem

When using ExcelWriter’s ExcelApplication object to generate a new workbook, custom colors that are assigned in code to fonts, charts, cell backgrounds, etc., display incorrectly on the client when the workbook is generated.

OR

When creating an Excel File or an Excel file to use as a template with the ExcelTemplate object using Excel 2007 and above, colors are not colors are not preserved in Excel 2003 or older.

Continue reading Workbook colors are not displayed as expected in older versions of Excel →

Email, RSS Follow
Email

Excel, ExcelWriter, Knowledge Base, OfficeWriter

Excel’s color palette explained

Problem

Excel’s color palette contains 56 colors, all of which can be accessed and most of which can be replaced using ExcelWriter (v6 or later). This post describes the layout of the palette and enumerate the default palette colors.

This content is most pertinent to the Excel 2003 color palette, which only has 56 colors. In Excel 2007 and later, workbooks can support millions of colors, but there is still an underlying workbook palette that has 56 colors. For more information about colors in multiple versions of Excel, we have enough post about workbook colors that are not displayed properly in older versions of Excel.

Solution

The palette is split up into a few different sections: Continue reading Excel’s color palette explained →

Email, RSS Follow
Email

Excel, ExcelWriter, Knowledge Base, OfficeWriter, SQL Server, SSRS

How to create hyperlinks between cells in an Excel spreadsheet

Problem

Your application requires cells to contain hyperlinks to other cells in the same spreadsheet, but the Cell.CreateHyperlink function only makes links to URLs on the internet. This article discusses a workaround using Excel’s HYPERLINK function.

Solution

Excel has a function named hyperlink() that, with some special syntax, can create links to other cells in a spreadsheet.

Normally, the hyperlink function creates a link to a URL, much like the CreateHyperlink function. Simply passing the name of a cell to the function causes it to form a bad link; Excel will interpret it as a URL.

The solution is to enclose the name of the destination cell in quotes and preface it with a pound sign. For example, a cell with formula =HYPERLINK(“#Sheet2!C3”, “Link to C3”) will contain the text “Link to C3”, and function as a hyperlink to that cell in Sheet2. The formula can be set either by editing an Excel file directly, or through setting the Cell.Formula property in ExcelWriter.

When the user clicks on the link, Excel’s focus will move to Continue reading How to create hyperlinks between cells in an Excel spreadsheet →

Email, RSS Follow
Email

Truth in Tech

Truth in Tech is now on iTunes

Our weekly Truth in Tech series, where we cover the week’s wackiest, useful, and just downright interesting tech stories, is now available for download on itunes…for free. We sift through a ton of tech news on the daily. Usually we tweet about our findings, but 140 characters doesn’t leave room for much nuance. So buckle up, tune in, and join us as we uncover acquisitions, startups, apps and the ever-evolving tech landscape.

*If the above do not load, you can access the podcasts from the SoundCloud website: Truth in Tech Ep. 14: Hacked and Truth in Tech Ep. 15: The Path Less Taken.

Summary:

Truth in Tech Ep. 15: The Path Less Taken

Your plumber called. He wants to know about those photos you have to share. The capped social network beloved by designers and San Francisco technorati alike has come under a second round of fire for its spammy adoption practices. On Monday Continue reading Truth in Tech is now on iTunes →

Email, RSS Follow
Email

Events, Meet the Team, Office Life

Bringing Watertown together for a Boston Strong BBQ

Thank you to all who attended the Boston Strong BBQ last week. We had a wonderful time getting to know our Watertown neighbors, while eating delicious food and supporting a great cause. With your help, we were able to raise $350 for the Lutheran Church Charities K-9 Comfort Dogs. We were excited by the strong turnout – from students at the Harvard Business School to our next-door businesses at Air Graphics and Sasaki to those who simply saw the sign and flyers and decided to stop by. Thank you again for coming out to support a wonderful cause and share in the strength and pride of our city. We’re looking forward to more Watertown BBQs and community events over the summer.

For the full photo album visit our Facebook page and feel free to tag yourself. To read the Watertown Tab’s write-up of the event visit their site here: Watertown Companies Team Up to Comfort Community

Email, RSS Follow
Email

Excel, Featured, News Releases, OfficeWriter, SharePoint, SQL Server, SSRS

What’s new in OfficeWriter 8.5

Spring has sprung, bringing with it our newest release of OfficeWriter: OfficeWriter 8.5!  What’s in store for this maintenance release? Scroll down to see the latest additions our development team has been working on.

WordTemplate – Embed DOCX files into templates

In OfficeWriter 8.0, we added the ability to embed RTF or HTML documents in Word files with WordTemplate.  The feature uses the document modifier to signify that a RTF or HTML document will be inserted. To learn more about using the document modifier, see our guide on inserting an embedded document under our WordTemplate Tutorials.

HTMLtoWord

We have also extended the feature to include DOCX files. Now you can embed other Word documents into your WordWriter templates. Continue reading What’s new in OfficeWriter 8.5 →

Email, RSS Follow
Email

Events, Office Life, Social Media

Conference Recap: PubCon New Orleans 2013

PubCon

From the Pubcon New Orleans (2013) Trenches

PubCon (New Orleans, LA) – So many sessions, so much to consume and share. Where to begin? My week in New Orleans flew by as I soaked in both sessions and sightseeing. This was my first time attending Pubcon as well as my first time in NOLA. The first 48 hours were filled with numerous firsts: my first beignet, first walk down Bourbon St and the French Quarter, first tornado warning, and first full day in the SEO Masters Group Training. The conference sessions mainly focused on SEO, social media, and marketing. While I attended mostly the sessions in the SEO track, I did bounce around a little. The only way to try and recap the week effectively is for me to give you the top 5 areas from which I took the most away and what those in part covered.

PubConSEO

There were endless sessions on SEO, including tips, tricks, and tools. I heard multiple times a good way to rename “Search Engine Optimization” would be to start calling it “Search Experience Optimization.” Understanding the relationship between what people are talking about and how they’re searching is very important.

Top 5 takeaways:

  1. Create partnerships with other businesses and get your own company page on their sites. Then link back to a subpage of yours rather than a main page to boost rank on lower ranking pages.
  2. Build your own dashboards for compliance and performance so everyone is accountable for their part. Utilize Google webmaster tools and analytics to aid in this.
  3. Set up your own Google alerts to track your industry, keywords, and competitors.
  4. Use various tracking tools such as 3rd party sites or Firefox add ons like disabling JavaScript to literalize the page and see exactly how Continue reading Conference Recap: PubCon New Orleans 2013 →
Email, RSS Follow
Email

OfficeWriter, SQL Server, Windows Server

How to Find Your OfficeWriter License Keys

First Things First: What do we have, and where is it?

If you’ve ever gone through a licensing review or license audit, you know that sometimes the hardest part of the whole process is information gathering. What keys do you have? Where are they installed? What is still under support, and what is not? Are we overpaying or over-provisioned? These questions and more can drive a sysadmin to insanity, especially if your records are less than perfect.

While no method can substitute for proper record keeping, I’m here to show you how to find any OfficeWriter product keys that may be installed on your servers. Basically, what I am looking to do is get a list of license keys, versions, and where they are.

Prerequisites

SoftArtisans stores all of its license keys in HKCR:\Licenses\Softartisans. We could manually open RegEdit on all our machines and find the keys, then copy them out into an Excel spreadsheet, but that would take WAY too long, even for my development environment.

Before we begin scripting, I first got a list of all the machines I wanted to check. Continue reading How to Find Your OfficeWriter License Keys →

Email, RSS Follow
Email

Events, Featured, Office Life

Boston Strong BBQ: A Benefit for the K-9 Comfort Dogs

In light of recent events, we want to bring our community of Watertown together and share in the strength and pride of our town. Next Friday, May 3rd, we will be hosting a BBQ to benefit the K-9 Comfort Dogs, who traveled from Chicago and Newtown, CT to be with us here in Boston.

The K-9 Comfort dogs are a bundle of furry, affectionate Golden Retrievers, trained to provide comfort and care to those affected by tragedy. They were stationed at First Lutheran Church on Berekeley St. after the bombings and went around to the hospitals to visit those affected. They also provided much comfort to one of our employees, who ran the Boston Marathon. As such, they are a cause very close to our hearts. So stop by, grab some food, meet your Watertown neighbors, and support a good cause. We are one Boston.

When: Friday, May 3rd, 12pm – 2pm EST

Where:
SoftArtisans / Riparian Data HQ
3 Brook St.
Watertown, MA 02472
RSVP: http://BostonStrongBBQ.eventbrite.com

Can’t make it, but still feel like donating? You can do so here: LCC Comfort Dogs

[hs_contact name=”SoftArtisans” address=”3 Brook St.” citystate=”Watertown, MA, 02472″ phone=”6176078800″ display=”both”]

Email, RSS Follow
Email

Blogged