Copy/Pasting elements in SSRS…argh!

Report-builder-was-unable-to-paste-successfully

Report Builder was unable to paste successfully.

Hmmm….that one buggers me.
Turns out that it doesn’t like have custom code in elements which you would like to copy/paste.
It’s a known issue for years and has been reported, but they decided not to fix it…
https://connect.microsoft.com/SQLServer/feedback/details/767968/visual-studio-2008-unable-to-copy-and-paste-textboxes-in-ssrs-reports-with-custom-code-after-2008-r2-sp2-upgrade

So what’s the work-around you might ask? Well…you’ll need to comment out all the ‘Code.’-sections in the XML code of the report.

Workaround:

Step 1) Open up the XML view for the report, either in Visual Studio by right clicking the report and selecting View Code, or by editing in any text editor.
Comment out Code. blocks. Ctrl + F your way through the document looking for Code.. The goal is to preserve the code in some way, while temporarily commenting out the line. Depending on your actual code, this might be different, but I here’s what I like to do:

Step 2)
Find: =Code.
Replace With: =’Code.

Step 3) Go back to the Designer View and Copy and Paste the Tablix. You can do this by right clicking the top left corner of the tablix control: Copy Tablix

Step 4) Go back to the XML View and reverse the find and replace
Find: =’Code.
Replace With: =Code.



Visual Basic code
Another things it that is has a habit of putting Visual Basic code in front of your SSRS expression, like for example:

Microsoft.VisualBasic.Information.
Microsoft.VisualBasic.Interaction.
Microsoft.VisualBasic.Strings.
Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions.

When you copy/paste anything, afterwards view the XML code and just replace remove these strings with Find&Replace.



ReportItems!Textbox.Value
In case your using ReportItems!Textbox.Value, make sure to check a few that Visual Studio ‘changed’ the number accordingly. Don’t know why, but most of the time it does understand the logic and also adjusts the textbox-numbers accordingly. But sometimes it doesn’t 🙂








Advertisements

Power BI Dashboard (Designer)

power-bi-dashboard


Microsoft released a new version of Power BI in preview mode, including many new visualizations that are immediately available to all existing subscribers also in production, such as the long waited treemap, combo charts (combining line chart and column chart), and more. These features are available only in HTML5 visualizations, so you can only use the new features online. Microsoft shown these visualizations several times this year (PASS BA Conference in San Jose, and PASS Summit in Seattle), so now this is finally available to anyone. But there is much more!

Power BI Dashboard is a new service, now in public preview (unfortunately only in United States, not sure about which other countries are supported by now, certainly not Europe), that does not require an Office 365 subscription and, more important, provide a design experience on desktop also without having Excel or Office at all. In other words, there is a separate Microsoft Power BI Designer that enables you to:

– Import data with Power Query
– Create relationships between tables
– Create data visualizations with Power View (running the latest HTML5 version locally in a desktop application)

This very first release does not include the full data modeling experience we are used to in Power Pivot, so you cannot create calculated columns or measures, but hopefully this will come in the next updates. In this way, you can use Power BI with a separate “data model” environment that is not tied to Excel. You can have an older version of Excel, or no Excel at all, and still design your data model with the Designer.


What’s important:
– It doesn’t require you to have Office 2013
– It doesn’t require you to have Office 365

Just download it and start playing! Way better then all those Excel 2010 graphs 🙂

power-bi-designer

Many video tutorials are already available at:
http://support.powerbi.com/knowledgebase/articles/424868-dashboards-in-power-bi

And download your local copy Power BI Designer here:
http://www.powerbi.com/dashboards/downloads/








Office Sway




Microsoft launched Sway, a new app that’s part of the company’s Office family of products. It will sit alongside the regular Word, Excel, PowerPoint, and OneNote apps as a rich content creation tool. In its most basic form, Sway allows anyone to create a beautiful website from just images and text without any effort, and it’s all what you see is what you get (WYSIWYG) — a modern and simple version of FrontPage. Despite that, Microsoft is taking an interesting approach with Sway, using the company’s powerful Azure cloud servers to suggest page layouts and quickly render content on the fly.

Sway will format pictures and text in a way that its algorithm feels is appropriate, even picking colors from photos to apply to the site. Although the end result is on the web as a site, it’s actually a complex new format that’s stored on Microsoft’s cloud servers. Sways will render differently based on device type, but they’re not traditional responsive design as Microsoft simply creates separate views on the backend.

You can create a Sway from the web, and soon Microsoft will have iOS and Android apps to make it easy to build one on the go. “Our intention is to go native where we can,” explains Microsoft’s head of Office marketing Michael Atalla. “We totally want native experiences.” In time that will include a Windows Phone app, and possibly a Windows app. Sway also features the ability to bring in content from OneDrive, YouTube, Facebook, and Twitter, so you could create an interactive Sway that includes a Twitter hashtag or timeline. Sways can then be shared on social networks and embedded on sites.

Source

Here are a few ‘live’ examples:

Northwest Aquarium 2014 Annual Report:
https://sway.com/s/northwest_aquarium/embed


The Red Panda:
https://sway.com/s/red_panda/embed


Pyrography How-To: Celtic Knot Table:
https://sway.com/s/wood_burning/embed


Want to know more?







SSRS: Hide a Tablix is case no rows are displayed (row count after applying a filter)

Here’s the thing. In case you don’t use filters in your Tablix, you can easily run a count on the dataset. Like for example:
=IIF(CountRows("Dataset1") > 0, False, True)


But, in my case my Tablix has a couple of filters and this changes things.
In order to count the number of rows which the Tablix is actually using/displaying, you may want to use this expression for the Hide-property:
=IIF(CountRows() > 0, False, True)

ssrs-hide-tablix-when-no-records-returned








How To: Build a (SQL) Server Inventory

Thanks to Brent Ozar for this one.

He describes how you could easily populate a list of (SQL) Servers by using one or a combination of these tools:

How to Survey Your Network for Servers
Put a row in the spreadsheet for every server you have – whether you’re in charge of it or not. We want to start with a good inventory of what we have, and there’s two good free tools to do it.

Microsoft Assessment and Planning Toolkit – it’s actually designed for licensing compliance, but it works great for building server inventories. It scans your network looking for whatever programs you pick, but just confine it to SQL Servers only.

Dell Discovery Wizard for SQL Server – it’s a GUI tool that pings all the servers in your network and tries to figure out if they’ve got SQL Server installed. If you’re in a small shop where your account has admin privileges in the domain, you might find a lot more servers than you expected.

SQL Power Doc – open source PowerShell scripts to discover, document, and diagnose your SQL Servers. I’m not gonna lie: this one is not for the faint of heart, but if you’re willing to get your hands dirty and deal with some manual work, the end result is a nice set of Excel spreadsheets.








Get an easy overview of the latest changes (check-in) within your TFS project

In Team Foundation Sidekicks you also have the ability to get an easy overview of all the previous changes which have been checked-in for your TFS project:

team-foundation-sidekick-history-overview-changes-changeset-checkin

Download Team Foundation Sidekicks here:
http://www.attrice.info/cm/tfs/

More info about Team Foundation Sidekicks? Read my previous blog post:
How to find out who has checked out files in TFS?