When passing multiple values for a parameter in SQL Server Reporting Services to a stored procedure, I used to JOIN them all together into one long pipe-delimited string (JOIN(Parameters!Paramname.Value, "|")) which I'd then loop through with a cursor in the stored procedure, break all the values into a table, and join that to my dataset.
That worked, but it did involve a bit of setup, since I had to add that loop to every stored procedure I used. So I was pleased this week to stumble onto an even simpler way to handle parameters.
This method relies upon SSRS's ability to dynamically string together the values itself. SSRS can create dynamic SQL that will handle these multi-selects if you enter your dataset as a text query, but it can't get into your stored procedure to modify it to take the new string. All you have to do, however, is shape your WHERE clause like the following:
WHERE (@Param + ',' LIKE '%'+ table.value + ',%' OR @Param = '' OR @Param IS NULL)
Now your SP can handle the multi-select string just the way SSRS passes it. Nice and quick! =)
Thursday, October 14, 2010
Using Sharepoint to Get the Current User ID
This was something I'd meant to post about a while back, but didn't get around to doing so until a co-worker asked me for more infomation. Here's what I shared!
As Sharepoint is used more and more as a BI platform, a lot of different solutions get hosted on SP pages. But how can you plug into SP's user identification and use it for your own code?
Here's a quick and dirty approach I unearthed while squeezing some Silverlight onto a SharePoint page.
The basic idea:
*In SharePoint Designer, open up the page you’ll be using
*Drop a list (any list) web part onto the page; convert the list to an XSL data view
*Add a system variable for the current user (I used LOGON_USER)
To do this, find the section of the page where “ParameterBinding”s are set. Add a new row that looks like this:
Next, look for the area where parameters are defined by searching for the string “XSL:Param name”, and add this line:
*Drop that variable in a cell within the data view which is named something you can recognize later, and hide it
Now you can reference the cell “MagicalUserPerson” in your code elsewhere.
Here's a bit more of a walkthrough, including an explanation of what other user variables you might choose to work with:
Getting the Current User Name or ID in SharePoint using SharePoint Designer (no code)
As Sharepoint is used more and more as a BI platform, a lot of different solutions get hosted on SP pages. But how can you plug into SP's user identification and use it for your own code?
Here's a quick and dirty approach I unearthed while squeezing some Silverlight onto a SharePoint page.
The basic idea:
*In SharePoint Designer, open up the page you’ll be using
*Drop a list (any list) web part onto the page; convert the list to an XSL data view
*Add a system variable for the current user (I used LOGON_USER)
To do this, find the section of the page where “ParameterBinding”s are set. Add a new row that looks like this:
<ParameterBinding> Name = “LogonUser” Location = “ServerVariable(LOGON_USER)” />
Next, look for the area where parameters are defined by searching for the string “XSL:Param name”, and add this line:
<xsl:param name="LogonUser"/>
*Drop that variable in a cell within the data view which is named something you can recognize later, and hide it
<table border="0" cellspacing="0" width="100%">
<tr style="display:none">
<td id="MagicalUserPerson" width="400px" valign="top" class="ms-formbody">
<xsl:value-of select="$LogonUser"/>
</td>
</tr>
</table>
Now you can reference the cell “MagicalUserPerson” in your code elsewhere.
Here's a bit more of a walkthrough, including an explanation of what other user variables you might choose to work with:
Getting the Current User Name or ID in SharePoint using SharePoint Designer (no code)
Subscribe to:
Posts (Atom)