Simons SQL Blog : Reporting

SQL Server Denali – Whats new for reporting?

simonsabin — Sun, 07 Nov 2010 09:21:01 GMT

This is a placeholder for content about what’s new in SQL Server vNext aka Denali. I will be updating this as more content is published....(read more)

Report Builder 3.0

simonsabin — Fri, 23 Jul 2010 09:11:49 GMT

Report Builder 3.0 has some minor additions that make your life a better world. On that i’ve just found is the ability to know what renderer is being used. Why is that useful? Well in all the training I do on Reporting Services I always highlight the...(read more)

RS How To : Multi value parameters and IN clause

simonsabin — Thu, 22 Nov 2007 23:32:44 GMT

Following my preview two posts about using filter and with IN in the multivalue parameters. Rob Farley has posted a bit more detail on how Reporting services cheats.

http://msmvps.com/blogs/robfarley/archive/2007/11/23/how-multi-value-query-parameters-in-ssrs-are-treated-by-sql-server.aspx

It would be good if it still used parameterised SQL but it doesn't it hard codes the values and thus causes an adhoc SQL statement and so a completely unique plan. Oh dear.

Read Rob's post for more details.

RS Howto: Use the IN operator in a filter expression

simonsabin — Tue, 20 Nov 2007 19:28:48 GMT

Filters are not always a good thing in reports as it generally indicates you are getting more data back from the database than you need. However I often find that I want to use virtually the same data in a report jst formatted differently, i.e. a chart, a table, etc. This is were filters come into their own.

You can filter on many things in a report. Generally these are anything that has a set of data, i.e. a table, a group, a series for a chart.

I recently wanted to filter using the IN operator, but couldn't find out how.

I guessed based on previous experience that a delimted lst of values probably wasn't going to cut it. So I took my delimited list and used the SPLIT function on it. This returns an array, and it works.

I can only guess that RS loads the array into a dictionary object and does a lookup against it. For that reason it needs an array that it can loop over to load into the dictionary.

So an example of an expression is as follows

="Simon,Sabin,SQL,Server".Split(,)

Go into an filters dialog, select the field (or expression) you want to filter on, select the "In" operator and then enter the expression as above.

Interestingly the Value property of a Multivalue parameter is an array object already so if you ave one of those in your report you should be able to do something like,

=Parameters!MyMultiValueParameter.Value

Performance Point RTM is now available

simonsabin — Thu, 08 Nov 2007 08:40:49 GMT

I got an email from MS today saying the Performance Point RTM is now generally available.

Performance point is something I am interested in but as with many new technologies I have yet to dedicate any time to picking it up.

If you are interested in learning Performance Point A great place to start is Nick Barclays blog http://nickbarclay.blogspot.com/ and his books. As PP is based on business score card manager the business score card manager book is still relevant to PP.

Available November 2007

Available December 2007

Now available!

"We are pleased to announce that Microsoft Office PerformancePoint Server 2007 is now generally available.

Product Information:

Available in both 32 and 64 bit editions

Evaluation editions are available are available on the Microsoft Download Center:

32 bit edition (http://www.microsoft.com/downloads/details.aspx?FamilyId=6372C24F-67DD-42DD-B034-748907B23420&displaylang=en)

64 bit edition (http://www.microsoft.com/downloads/details.aspx?FamilyId=3ADAC793-DEF0-4BA8-A9AB-228979B8DB40&displaylang=en)

Enterprise editions are available for MSDN/TechNet subscribers under Applications within the left pane of the MSDN home page.

Enterprise editions are also available on Volume License. Learn more http://www.microsoft.com/performancepoint.

PerformancePoint Server 2007 Volume Licensing (http://www.microsoft.com/business/performancepoint/howtobuy/default.aspx)

General Volume Licensing information

(http://www.microsoftvolumelicensing.com/userights/ProductPage.aspx?pid=255)

English, French, German and Spanish Languages are available now. "

SSRS Getting pounds instead of dollars

simonsabin — Thu, 27 Sep 2007 10:21:59 GMT

As much as I hate to admit it Microsoft is a US company which means everything defaults to US. So dates are in mmddyyyy not ddmmyyy format, words are missing letters such as color instead of colour and the currency is dollars and not the Great British pound.

Most of this is handled by regional settings so once you have said you are in the UK you get the correct formatting. However Reporting services is different.

A recent client was trying to display financial figures and so wanted the currency symbol to be displayed. They tried all the regional settings but couldn't get rid of the dollar sign.

The trick is to change the language of the report to English(United Kingdom). And heh presto you get a pound sign.

Reporting Services - How to rotate text on a report

simonsabin — Fri, 01 Jun 2007 09:55:09 GMT

I've been trying to do this for a long time and even looked into writing some CLR code that would take some text and produce a graphic of the text rotated.

Well all my prayers have been answered by a colleague of mine. Thanks Andrew.

To get text rotated in reporting services all you have to do is set the WritingMode to "tb-rl". You may also want to set the TextAlign and VerticalAlign to position the text correctly. Remember though the text is rotated so to get the text appearing on the left you have to set the VerticalAlign to Bottom. To get the text to align with the bottom of the text box you have to set the TextAlign to Right

ProClarity 6.3 available on MSDN downloads

simonsabin — Wed, 04 Apr 2007 22:28:43 GMT

I've just logged into MSDN downloads and was very shocked to see ProClarity available for download.

Both server and desktop versions are available.

You can read more on Proclarity here

http://www.proclarity.com/

Update to the taskpad custom report

simonsabin — Wed, 28 Mar 2007 09:21:05 GMT

Thank you to those people that informed me that my taskpad report was incorrectly reporting the backups times against the wrong type of backup.

I have now fixed the report and have also included the backup types that are supported by SQL 2005.

You can downlaod the new version of the taskpad custom report

You will need SQL Server 2005 SP2 to use the custom report.

Task pad report

simonsabin — Thu, 11 Jan 2007 22:30:26 GMT

Following feedback I have issued another version of the taskpad report. This now works with databases that have been created by users that have access to the sql server via a group membership

You can download the report from http://sqlblogcasts.com/files/10/default.aspx