I want to insert the file properties into a cell for printing. – ExcelBanter

I want to insert the file properties into a cell for printing. – ExcelBanter.

Alright, so we were recently faced with a challenge. Client has a bunch of templates they use locally on desktops that they want to be more efficient with as part of a conceived application and workflow process in SharePoint. Simple enough, right, create content types, add columns, insert Document Properties into the Document Content Types and Bob’s your Uncle. Well, not quite. This client gave us their ‘Templates’ and this included 13 Word Documents and 1 Excel Document.
They had presented this to another consultant as well as their in house IT team. All had come up with relatively the same set of ‘solutions’ to the problem. What’s the problem? you ask…The problem is that you cannot add document properties, natively to Excel Documents in the quick and easy manner that you can, say a Word Document. We discussed the problem, and like so many before came up with the same possible solutions:
1. Create an InfoPath form to collect data and present it to users via PDF Output (ultimately that is where it needs to go)
2. Create a Word Document version of the Excel Workbook and let users interact with that.
3. A Variety of equally non-workable solutions for the client.
You see, they like the functionality of Excel. There are cells that have formulas and they want to be able to be flexible with those formulas, dynamic even. Which is to say some of the content in the excel workbook comes from the list item in sharepoint, but some of it is manipulated on demand by the user…Word, might could do that. Word with an embedded excel workbook object might could do that, but none of them were quite right.
Which is what leads me to the VERY helpful link above. ‘But, Bill, that is for BuiltinDocumentProperties, and SharePoint Server Document Properties are not in that collection.’ That is correct, however ActiveWorkbook also allows for ContentTypeProperties which exposes the full gambit of Server Document Properties. Note in order to use this, however, you must have the document in a Document Library with properties attached.
Now that I have my modified User Function (based on the link, thank you Gord Dibben), I can now place the formula =DocProps(“xxxxxxx”) and reveal the Metadata in the Excel Document correctly.
So now, user fills out InfoPath Form on list item, on submit, workflow creates several Word Documents and 1 Excel Workbook from Content Types, saves them in a Document Library, and populates Metadata on each newly created document and workbook.
This is an issue we have faced before, and like so many others created work arounds for clients, this time, that was not an option, and so we found a solution.

Advertisements

SSRS Lagniappe

Giving SSRS that lagniappe, takes some skill, knowledge, and a whole lot of patience.
What am I on about? Let’s start with Lagniappe…what is it? Look it up on the interwebs, but basically it’s that little something extra. It might be extra debris on your roast beef po-boy, it might be a little extra shot of whiskey in your drink, it might be that stuff that makes Emeril go Bam!
In this case it’s that thing that makes the report pop…

SSRSLagniappe01

now the fun part is that I challenge anyone to make those loverly gradient rounded corned boxes in SSRS, natively…go ahead, take a few minutes, I will wait…

So, you’re back?  It’s not as easy as it oughta be.  I mean, afterall Excel does it with the flick of a mouse.  Makes you mad, right?  Made me spit fire for a while trying to replicate those loverly gradient rounded boxes.  Finally, I figured out a workaround.  With a little southern engineering and will-power, here’s what I came up with.

1) go to excel or word for that matter or powerpoint, make some boxes in the desired colors etc…

SSRSLagniappe02

repeat as necissary…

now copy it and take it to paint and crop, until you are left with your desired shape in the color you want…

I have a couple that I used…

Blue Box GoldBox LightBlueBox RedBox

As you can see they are not uniform in size…SSRS can control the size, what wew are most concerned about is the gradient and edges.

Now you are going to make a table in SSRS and place these as background images in the required cells. Go ahead, try that out…

What’s that?  The image is just repeating and I cannot get it sized exactly right, and this effing thing sucks!!!  Don’t fear, don’t fret.  So one thing I did was play around with the shapes and I worked very hard at closly matching my size of the object with the text box I was filling.  Don’t worry, Paint does a very good job of allowing you to size the object and generally maintining a very good gradient, etc int he object…

One final piece, I have multiple data elements within each cell…the number of buildings is 1 number and the number of residential units is another…and they are attained with similar groupings in SSRS from my stored proceedure and how did I get them centered vertically and horizontally within each of those.  I used a combination of visibility expressions to decide when to show and when to not show, but I also set a matrix table within each of the cells and hid some of the grouping rows and columns and set thier backgrounds to no color, and finally I set the top cell to top center aligned, and the bottom cell to bottom center aligned…

All in all this demo piece is far from perfect, in that there may be a little off aligned if you look closely, and there’s some elements that are visible that maybe ought not be, but I think you get the point…that with some southern engineering, you to can give your SSRS Reports a little lagniappe with actually a little elbow greese and a little know how…

Good luck!