January 2011

You are currently browsing the monthly archive for January 2011.

Now and then I encounter forum questions in the style of the following:

I have a report with a title.  When rendered through the Report Manager and when exported to PDF, I want it to render as normal.  However, when exported to Excel I do not want to get the title.  How can I hide it?

Because I don’t like re-inventing the wheel each time I decided to write a blog post about it.

As of SQL Server 2008 R2, we’ve got a built-in global field that can help us out.  This field is called Globals!RenderFormat.  It has two properties: Name and IsInteractive.  Name represents the unique name that indicates the chosen renderer, and IsInteractive indicates whether or not the chosen report format is, well, interactive.

Depending on the renderer, the values of the properties differ.  To be able to use the variable in an expression, we need to know its values for each rendering format.  Here’s the list of different possibilities:

Renderer RenderFormat.Name RenderFormat.IsInteractive
Preview in BIDS or rendered through Report Manager RPL True
XML file with report data XML False
CSV (comma delimited) CSV False
TIFF file or Print button IMAGE False
PDF PDF False
MHTML (web archive) MHTML True
Excel EXCEL False
Word WORD False

If these names for RenderFormat look familiar to you, you’re probably right.  Have a look at the rsreportserver.config file in the C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer folder.  Note that you may need to adapt the folder to your specific settings.  In my case my instance is called “SQL2008R2”.  Near the bottom of that configuration file you can find the <Render> node, located under <Extensions>.  The names that you see there are those used by the RenderFormat.Name property.

Now that we know what values to test on, let’s get started.

If we get back to the example of hiding a title, or textbox, when exporting to Excel, here’s what needs to happen.  Locate the Hidden property of the textbox that you want to hide, and give it the following expression:

=IIF(Globals!RenderFormat.Name = "EXCEL", True, False)

What we’re saying here is: if the RenderFormat is EXCEL, then the Hidden property should be set to True.  Which results in a hidden textbox whenever the report is exported to Excel!

As Erik pointed out in the comments, in this particular case you don’t need the IIF() statement.  The result of the expression results in True when the expected value should be True, and False when False is expected.

As a quick note: when building your expression through the expression builder, you’ll notice that the Intellisense doesn’t know the new RenderFormat field yet.  Do not worry about that, just continue typing and ignore any errors being indicated.  If you use the syntax as I highlighted above, it will work!  Well, unless you’re running an earlier version than SQL Server 2008 R2 of course.  In that case it won’t work.

Intellisense doesn't know RenderFormat yet

In contradiction to the Intellisense, the bottom part of the expression builder screen has been updated to show the new properties.  So if you don’t remember the syntax, you can just locate the field in the Built-in Fields category and give it a good double-click.

RenderFormat is located in the Built-in Fields category

Of course, the Excel example in this post is just one of many possibilities that this new field offers.  Is your company environment-friendly and does it want to prevent wasting paper?  Now it’s possible, just hide that 50-pages long table when the report is being rendered for print!

Have fun!

Valentino.

References

Globals!RenderFormat aka Renderer Dependent Report Layout by Robert Bruckner

Share

Tags: , , ,

Last month we had the pleasure of enjoying the third edition of our Belgian SQL Server Day.  If you were there and you were unable to attend a particular session, even though you were interested in it, here’s your answer.  The sessions have been recorded, and the videos are now available online!  How’s that for a New Year’s present?

SQL Server Day 2010: The Videos

And if those videos didn’t still your hunger, or you just have plenty of time to kill, check out this blog post on Chris Webb’s BI blog.  He has posted a link to the 152 videos of SQL Bits, a three-day SQL Server event in the UK.

Still not satisfied?  Maybe these SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos can keep you busy for a little while then. :-)

Have fun!

Valentino.

Share

Tags: , , ,

Was “get a new job” in your list of resolutions for the new year?  If it was, then check out the following website:

Ordina Airlines

Ordina is hiring talented people!  Get a new challenge and a free iPad on top of it, or a travel cheque worth 500 euro. What more do you want??  Note that the extra gift is only valid until February 15.

Ordina is hiring!

If you decide to send your resume and you end up with a new contract, don’t forget to post a comment here!  Alternatively, you can also comment as you read it now, but I don’t think that you want your current employer to know that you’re on the lookout. :-)

Have fun, and good luck!

Share

Have you ever needed to write a script that would store the Windows login of the user that executes the script in a table, without hard-coding it?  And you couldn’t find your way through the user jungle of T-SQL?  Then read on!

A normal approach would be to rely on the IntelliSense feature of SSMS and start typing something like:

select use

Which gives you a dropdown like:

image

Cool you think, there are four system functions to return something user-related, one of them would be the Windows user, right?  Well, wrong.

Let’s find out what these functions actually return, and locate a method that does return what we require.

The Different User-Related System Functions

USER

According to the BOL:

Allows a system-supplied value for the database user name of the current user to be inserted into a table when no default value is specified.

So, how many times did you read that?  And do you now know what you’ll get?  Me neither.  As far as I understand it, you’ll get the database user name of the connection context.  Not what we need.

USER_ID

According to BOL:

Returns the identification number for a database user.

The function accepts one argument that accepts a string representing a username.  When no argument is provided, it will return the user ID of the execution context.

But that’s not important because the BOL also mentions that it will be removed in a future version and that you thus shouldn’t use it anymore.  No problem if you need it though, its replacement is called DATABASE_PRINCIPAL_ID.

USER_NAME

According to BOL:

Returns a database user name from a specified identification number.

This is the inverse of USER_ID.  It accepts one argument representing the user ID.  When omitted, the user of the current execution context will be returned.  Which is actually the equivalent of the USER function.

Still no Windows user though, so let’s move on to the next one.

USER_SID

Not documented in the BOL.

It seems to represent the sid column found through following query:

select * from sys.sysusers

And the sys.sysusers view is documented.  Apparently “sid” stands for “security identifier” and it’s a varbinary.  But not only is the view documented, it is also deprecated.  You should use the following view instead:

select * from sys.database_principals

That query returns a list of principals in the current database, and one of the fields is indeed our dear sid.  However, we can now conclude that sid is definitely not what we’re looking for in this little quest, it doesn’t even closely resemble a Windows user name.  So let’s move on again!

CURRENT_USER

Hmm, “current user”, will this finally be the Windows user with which I’m running my queries?

According to BOL:

Returns the name of the current user. This function is equivalent to USER_NAME().

Equivalent to USER_NAME, so still not what we’re looking for.

SESSION_USER

According to BOL:

SESSION_USER returns the user name of the current context in the current database.

You can probably guess by now that again this is not what we’re searching for.  Next.

SYSTEM_USER

According to BOL:

Allows a system-supplied value for the current login to be inserted into a table when no default value is specified.

Ah, finally a description that contains the word “login”!  And indeed, here’s what it returns:

ORDINA\VaVr

That is indeed my Windows user.  Mission accomplished!

Please note: if you’re running this through a window connected via a SQL Server login, you will get that login as opposed to your Windows login.  And if you get ‘sa’ while you’re connected to a production server you should look into making some security changes. :-)

SUSER_NAME and SUSER_SNAME

Two more similar functions exist: SUSER_NAME and SUSER_SNAME.

Both return a login name as well, and both accept a parameter.  When executed without parameter, they return the login name of the current user.  Their only difference is the parameter that they accept.  SUSER_NAME accepts the “login identification number of the user”, which is an int, and SUSER_SNAME accepts the “login security identification number”, a varbinary(85) (remember sid from above?).

Quick Query

The following query shows how to use all the functions, with the default values for any parameters.

select USER as cUSER, USER_ID() as cUSER_ID, USER_NAME() as USER_NAME,
    USER_SID() as cUSER_SID, CURRENT_USER as cCURRENT_USER,
    SESSION_USER as cSESSION_USER, SYSTEM_USER as cSYSTEM_USER,
    SUSER_NAME() as cSUSER_NAME, SUSER_SNAME() as cSUSER_SNAME

Have fun!

Share

Tags: , ,

2011

Now that the new year has started, it’s time for the “resolutions/looking forward to” type of post.

Resolutions

Resolution #1

I will continue to write articles.  Looking at my statistics and the number of comments, I know that the articles are being appreciated.  Which is good to know because they take quite some time to write.

It’s been a while since I’ve written one, because I’ve been occupied on other tasks.  Such as the following.  I’m cooperating in the creation of a book on Master Data Services, as Technical Reviewer.  If you’re interested in that subject, keep an eye on this blog.  Once it’s published I’ll make a post about it!

To finish this resolution, here are the most popular articles in my Top 10 of 2010.  At number one, still an article that I wrote back in 2008: Calling a web service from SQL Server 2005.  I have a feeling that this will be different next year, because two other posts have pushed it to the third spot when looking at “last month” statistics.

A lot of people seem to struggle with the Windows Authentication in IIS 7.5, because that one is at number two.

At number three finally the first real article written in 2010: Retrieving Data From Excel.

Can you guess what posts are pushing the web services article from its number one position? :-)

Other posts that I have in the top 10 are my script to list all SSIS packages deployed on the Integration Server, my post about SSRS 2008 and SSL, and my article showing some Pie Chart Techniques.

Resolution #2

Erm, wasn’t the first one enough?  Well, besides writing articles and shorter blog posts I’ll also keep helping you guys out on the online forums, I will keep posting answers to every question that I get on my blog, and I will even keep answering the occasional personal email that I receive.

Resolution #3

Last but not least: as a Core Member of the Belgian SQL Server User Group, I’ll keep participating in the organization of great events, such as SQL Server Day!

Looking Forward To

SQL Server 2011

I’m definitely looking forward to the next release of SQL Server, currently known as Denali!

Integration Services has gotten some nice improvements to make the developer’s life easier.  Finally CTRL-Z actually does something!!  If you weren’t aware yet, you can already try this out by downloading the first CTP.

What I’m also quite interested in is known as Apollo, a column-based query accelerator.  I’m curious to see if the queries will perform ten times faster, as claimed.

What else?  Well, the BI Semantic Model (BISM) seems to be quite promising as well.  The SSAS team has used the Vertipaq technology, which you may already have heard of when reading about PowerPivot, and integrated it into a new engine for SSAS databases.  So in 2011 you can actually install two different instances of SSAS: one running in the old OLAP mode, and one running in BISM mode.  The OLAP engine still receives MDX queries, and the new server understands DAX!

And the last feature to which I’m looking forward to is Data Quality Services!  I’m quite sure that it will be useful in the context of loading data warehouses.

Conclusion

Happy New Year to all of you out there!!!

References

Top 9 New Features of SQL Server "Denali"

Analysis Services – Roadmap for SQL Server “Denali” and Beyond

Share

© 2008-2017 BI: Beer Intelligence? All Rights Reserved