We're moving!

Hey readers! Thanks so much for dropping by. As of March 2016, the blog has MOVED to my personal site. Please visit http://www.elainekellner.com/sql for more bad jokes and great BI tips.

A More Efficient 12 Days of Christmas

In a fit of nerdiness, I decided to re-write the classic Christmas carol "The Twelve Days of Christmas" in SQL:

SELECT (ROW_NUMBER() OVER (ORDER BY Xmas1.DayNum DESC)) * Xmas2.DayNum AS GiftQuantity,
Xmas2.DayNum,
Xmas2.GiftTitle
FROM TwelveDaysXmas Xmas1
INNER JOIN TwelveDaysXmas Xmas2
ON Xmas1.DayNum = Xmas2.DayNum

Results:

GiftQuantity DayNum   GiftTitle
12           12       Drummers Drumming
22           11       Pipers Piping
30           10       Lords a-leaping
36           9        Ladies Dancing
40           8        Maids a-milking
42           7        Swans a-swimming
42           6        Geese a-laying
40           5        Gold Rings
36           4        Calling Birds
30           3        French Hens
22           2        Turtle Doves
12           1        Partridge In Pear Tree

See? Much easier than singing 12 choruses!

Power BI - Rotation Fix

Working with the still-new PowerBI desktop release, I ran into a small but frustrating issue. When adding a shape to a report, there is an option to rotate the object. By default, adding a "line" shape gives you a vertical bar. But what if you want a horizontal bar instead?
There is no option to type in a rotation value, so you can't directly enter 90 degrees. No problem, you say, you'll just move the slider!

Unfortunately, the slider stops at 87 and 93. There's no way to slide it to 90.

After some considerable cursing at the screen and sharing my frustration with others, my brilliant husband suggested I try the left and right arrows. Sure enough, that lets you step up and down by a single degree each time. AHA.

So if you're stuck trying to rotate something just right, use the arrow keys! This also works for increasing a fill or background transparency value, or anything else that uses a slider in PowerBI.

Microsoft confirmed that a fix is coming out in the next patch. No date available yet, but we will eventually be able to type in a value. Hurrah!

SharePoint 2013 Workflows and Lookup Columns

I may have run into a bug with SharePoint 2013 workflows and lists with lookup fields, although I've not seen it reported elsewhere.

THE STORY
I was building a workflow that ran on a list with a lookup field ("Workflow List"). That value came from a second list ("Lookup List A"). I needed to use the value set for that lookup field to pull additional information from a third list ("Lookup List B").

Workflow List	Lookup List A	Lookup List B
Title	Department	Department
Body	Sub-department	Department ID
Department	Additional info	Department email

THE ISSUE

In SharePoint 2010, it was possible to perform this sort of data relationship, as long as you specified that you were passing the value as a "lookup value". In 2013, I found that the field was set to that by default (and the option to change the datatype disabled), and the workflow would throw odd errors indicating that it couldn't find the lookup value requested. In fact, it was often reporting that it was looking for a list with a GUID made of nothing but zeros, which obviously didn't work.

I tried all kinds of combinations of lookup fields, created sample lists and added fields with different types and values to ensure it wasn't just a corrupt list, and nothing worked. It seemed my workflow just couldn't figure out lookup columns.

THE SOLUTION

Since the workflow just doesn't get lookup fields, let's give it something static to work with instead. If we can capture the ID of the lookup field and store that as a static value in our list, the workflow can happily use that to look up our related values.

THE CODE

First, edit your list to include an additional field that you can store your lookup ID in. In my case, I created DepartmentIDCopy, and made it a single-line text field (an integer would also work, and probably be more precise).

Then, edit your NewForm, EditForm, and/or any custom forms that a user could visit to change your lookup value. Just after the PlaceHolderMain tag, add the following JavaScript:

<script type="text/javascript"> 
  var sourceField;
  var targetField;

  function doFieldCopy() {
    targetField.val(sourceField.val());
    //alert('hi!');
  }
    
  //Copy selected department to text field for workflow manipulation
  $(document).ready(function() {
    sourceField = $("select[title='Question Department']");
    targetField = $("input[title='QuestionDeptCopy']");

    sourceField.change(function() {
      doFieldCopy();
    });

    doFieldCopy();
  });
</script>

This script will fire whenever the value in your lookup list drop-down is changed, so whatever the user selects should be recorded. It also fires when the page is loaded, to ensure that the value isn't accidentally dropped.

Thirdly, add an input field on your page for your new lookup ID storage field (I just created a new item form, copied the row from there, and pasted it into my existing forms). Set the display:none to ensure users don't accidentally manipulate the field.

Finally, update your workflow to use the new ID field, and voila - happy lookups!

Hiding "Home" Link in SharePoint 2013 Top Navigation

By default, the SharePoint top navigation includes a link to the landing page. This "Home" link takes up space and isn't always useful, especially if you use the site logo for the same purpose!

Sometimes the best solution can be to make changes in the master page or generate the nav from a list, but from time to time, the easiest approach was just to hide that first node with CSS. In SharePoint 2010, you'd end up using something like:

.s4-tn li.static > a{
display: none !important;
}
.s4-tn li.static > ul a{
display: block !important;
}

In SharePoint 2013, styles have changed. Here's the new class:

.ms-core-listMenu-horizontalBox li.static > a{
display: none !important;
}
.ms-core-listMenu-horizontalBox li.static > ul a{
display: block !important;
}

"Crescent" in SQL Denali - Overview and Impressions

Great official overview here

I've been reading up on SQL Denali features over the last week, and got to spend a little time getting my hands on Crescent, the new reporting solution.

By now, I have seen Crescent described as all kinds of different things - 'the successor to ReportBuilder', 'Excel meets PowerPivot' (isn't that redundant?), 'the new SSRS'. I honestly would not call it any of those things!

I'm beginning to think of Crescent as the new visualization layer for PowerPivot. Crescent consumes only Business Intelligence Semantic Model (BISM) datasources, either in the format of a stand-alone connection file (.BISM or .RSDS) or a PowerPivot workbook.

To run, Crescent needs SQL Denali (of course), SharePoint 2010, SSRS (Denali), and a BISM datasource. Note that on the client side, you need to have Silverlight 4 and a 32-bit browser (IE8 or Firefox 4 or better; I don't have any word on compatability with Chrome, Opera, etc.). In IE, in-private browsing must be turned off.

Crescent reports are generated entirely in-browser, rather than in BIDS or any other design environment. Once everything's configured and ready on your SharePoint site, buttons magically appear on BISM datasources to launch the Crescent designer. When the report is ready, it's saved to a folder/doc library within the SharePoint site as an .rdlx file.

In terms of functionality, I only have a few initial impressions. Crescent offers a nice-albeit-limited range of reporting options, and what it does do is done very well and with polish. It's a great tool for quickly tossing together a little animation - something spiffy for a last-minute sales call, or to use for playing with data to find new insights, or even for users who wouldn't usually tackle SSRS to use when they want to play around.

It's also a lovely little something to show off against some of the new report-generation competitor tools, many of which have slick user-friendly interfaces and abundant animations.

However, since these BISM models have to be put together by folks who know the data at least a little bit, and since any real report customization will still require SSRS, custom Silverlight, or other more complex solutions, we'll be breaking out BIDS before too long.

And there's my big hope for the future - Crescent does re-emphasize how dated statis SSRS reports can look (although I firmly believe SSRS still has legs). I'm very much looking forward to, someday, having animations and additional UI customizations added to Reporting Services. Next time, perhaps?

PivotViewer and Reporting Services

Microsoft has released a cool new extension for Pivot that integrates SSRS reports: PivotViewer Extension for Reporting Services.

This extension, which does require SSRS 2008 R2, allows you to use SSRS to generate the collection of cards that the PivotViewer will display. There’s a fair amount of information out there to walk through the installation of the extension, but I found that there wasn’t quite as much robust information on actually using the extension for end-to-end creation of a collection. So, here’s an overview of what you’ll be doing!

Once the extension has been installed and the pivot site set up, the basic process is:

• Create a parameterized SSRS report
• Load the RDL for that report up to a SharePoint pivot gallery
• Set up a BI Collection definition
• Create a SharePoint page to host the BI Report Crawler
• Run the BI Report Crawler for your collection definition
• Create a SharePoint page to host the PivotViewer, add the PivotViewer web part and point it at your collection


Got all that? OK, let’s take a look at the details for each step:

• Create a parameterized SSRS report
o The SSRS report is used to create the tiles in the Pivot collection. It needs
to take a parameter (or parameters) that will create one unique card for every
parameter value – something like an employee ID, for example.
• Load the RDL for that report up to a SharePoint pivot gallery
o Since I was using a PowerPivot datasource, I put the RDL file and the datasource in the same folder for convenience, but you can put it wherever makes sense for you.
• Set up a collection definition
o The collection definition instructs Pivot to grab your report (specified in the Report Template field), run it with a specific list of parameters (Report Parameter Query), and associate a certain set of metadata (Dataset Query) for each instance of the report.
 Report Template: The URL for the Report Template should specify an image format, and include a placeholder for the parameter value. Use the following format:
• http://[servername]/reportserver?http://[servername]/[folder]/[reportname].rdl&rs:Command=Render&rs:Format=image&RC:outputformat=PNG&[ParameterName]={0}
o Remember that the parameter name must be the INTERNAL name, not the friendly one displayed to users
 Dataset Query needs to return data in a particular order.
• The very first field MUST be a unique ID; in general, this will be the same value used in the Report Parameter Query.
• The second field is the name; this is the value that will appear on the top-left of the pivot viewer when an individual card is selected. Consider using the field users are most likely to do a text search on, such as employee name.
• All subsequent records will be used as slicers on the left side of the pivot viewer.
 To change the way data is displayed in slicers, you must change the Dataset Query. To get a slider bar that will let you select a range of numeric values, include a ‘dimension properties’ clause in the MDX of your Dataset Query, and pull the .key for that dimension. The crawler will automatically format the results so the slider can be created.
 Report Parameter Query is a short query that will return the list of parameter values used to run each copy the report. It can include a WHERE clause to restrict the dataset.
• Create a SharePoint page to host the BI Report Crawlero The heart of this extension is a tool that will take snapshots of your SSRS report. It cycles through each parameter from the query you specified in the collection definition, runs the SSRS report for that parameter value, and stores the output of the report.
o The tool is packaged as a SharePoint web part, which means it needs a page to live on before it can be run. Create a page wherever you’d like on your sub-site, and drop the BI Report Crawler on the page. You will likely want to expand the height to view all the information returned by the tool.
• Run the BI Report Crawler for your collection definitiono Once the web part is all loaded up on the page, select the name of your collection definition from the Inventory drop-down, then click the ‘Crawl’ button on the right hand side.
o The bottom of the web part will now begin recording the results of each execution of the report.
• Create a SharePoint page to host the PivotViewer, insert the PivotViewer web part and point it at your collection
o The PivotViewer takes the form of a SharePoint web part, so we will again need some kind of page to host it on. Create a page in a suitable location, and add the PivotViewer BI DataBrowser (under the “Custom” folder). Edit the web part, and in the configuration section, enter the name of your collection definition under “Inventory”. Save the page and check in if needed.


Tips and tricks
o The dimensions of the SSRS report directly impact the shape and whitespace seen on the Pivot card. Whatever appears on page 1 of the report as it is printed will determine the Pivot card’s appearance. Use print preview to see where your report is at!
o I found very little in the way of troubleshooting help out there, so it was a struggle to figure out why my collection wasn’t rendering on the page. I finally broke out Fiddler to see where things were breaking, and spotted an error highlighted in red. Visiting that URL (http://[Server]/_vti_bin/BICollection/PivotCollectionService.svc/[Collection Name].cxml?q=
), I discovered that the Pivot service was telling me that I’d typed my MDX statement wrong. When in doubt, check the service for error messages!
o Keys MUST be unique within the first 20-25 characters! I could not find any documentation to support this, but my collection crawl kept failing with a unique key violation. I triple-checked and confirmed that the keys WERE unique. However, the original keys (concatenated from a series of columns) weren’t always unique until as late as the 28th character. We switched to a new key that was unique within the first 6-8 characters, and that worked like a charm.
o If the crawler does error out, results are cleared from the screen and only the error message is displayed. It’s often best to keep a close watch on the crawl so you can note which record prompted the error.


Troubleshooting
• If the Report Crawler crawls but nothing displays on the page...
--Close the browser and open the page in a new window
--Ensure the SQL Server Analysis Services service is running on the host machine
• If there is any kind of error…
--Check the strings for both the report (including parameter name) and the datasource
--Check the service at
http://[Server]/_vti_bin/BICollection/PivotCollectionService.svc/[Collection Name].cxml?q=
--Check the definition query; are you sure results are being returned?
--Run Fiddler on the crawler page to check for any errors