SQL helpers for All User Data table and/or SSRS Reports

SUBSTRING(ntext3, 0, CHARINDEX(‘|’, ntext3)) – use this to get at Term Store Value

dbo.HTMLDecode(ntext5) – strips html from nText fields (must install function first)

CASE WHEN tp_ListId = ’80cde0f0-3c6c-418d-8211-f715d2b41d40′ THEN ‘Projects’ ELSE ‘WM’ END AS ListID – Compare this to a Case Statement in Access. I use to distinguish between 2 lists so I do not have to remember GUID

CASE WHEN RTrim(SUBSTRING(dbo.AllWebs.FullUrl, 16, 8)) = ” THEN ‘NA’ ELSE RTrim(SUBSTRING(dbo.AllWebs.FullUrl, 16, 8)) END AS ProjectID      – This one gets the project ID from the full url, I had to manually count to get 16 and guessed at value of 8 in case I have millions of records in the list

When filtering a large set like all user data table which has 3 joins (alluserdata, lists and webs) that you begin narrowing down the result set starting in the webs table. What I had to do was filter out only the sites in fullurl field that I need. This is good from performance and for any cast functions or substring functions to ensure thy work per your rules. Reaso I ay this is the query result set looks like it is what you what but the system will check every row anyway even though you cannot see the records.

For SQL View, specify a SQL parameter right in the report SQL text by doing this      Viewexmple.ProjectID IN (@ProjectID) using the IN clause allows a multi select pick list on the report, you just need a report parameter set up called ProjectID.

The In Clause is awesome

You can do it this way without IN

USE AdventureWorks2008R2; GO SELECT p.FirstName, p.LastName, e.JobTitle FROM Person.Person p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID WHERE e.JobTitle = ‘Design Engineer’ OR e.JobTitle = ‘Tool Designer’ OR e.JobTitle = ‘Marketing Assistant’; GO

Or this way with an IN, very nice

USE AdventureWorks2008R2; GO SELECT p.FirstName, p.LastName, e.JobTitle FROM Person.Person p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID WHERE e.JobTitle IN (‘Design Engineer’, ‘Tool Designer’, ‘Marketing Assistant’); GO

Or this way with a sub query

USE AdventureWorks2008R2; GO SELECT p.FirstName, p.LastName FROM Person.Person AS p JOIN Sales.SalesPerson AS sp ON p.BusinessEntityID = sp.BusinessEntityID WHERE p.BusinessEntityID IN (SELECT BusinessEntityID FROM Sales.SalesPerson WHERE SalesQuota > 250000); GO

Or with using the NOT IN Clause like this

USE AdventureWorks2008R2 GO SELECT p.FirstName, p.LastName FROM Person.Person AS p JOIN Sales.SalesPerson AS sp ON p.BusinessEntityID = sp.BusinessEntityID WHERE p.BusinessEntityID NOT IN (SELECT BusinessEntityID FROM Sales.SalesPerson WHERE SalesQuota > 250000); GO

Be careful when using IN, NOT IN when data contains NULL entries/records. Check this link:

Stored Procedures with parameters

You can only return an INT value from a stored procedure and should be used to indicate success or failure of the stored procedure. Once you have set the value of your OUTPUT parameter you do not need to tell the stored procedure to return or select it – it will be available when you call the stored procedure with the correct syntax. To add a parameter is quite easy, modify an sp and add this

ALTER PROCEDURE [dbo].[spWorkItems_WM_Projects]
@ProjectID Int

AS
Then on the where clause add this and that’s it. You can use an = or an IN here but you cannot pass multivalue SSRS report pick list selections because SSRS does not pass the correct format to an sp, you need to use SQL view for this or follow some online work abounds.

WHERE dbo.UserDataWM.ProjectID = (@ProjectID)

Work arounds to passing multi value pick lists to stored procedure (sp)

More details of Problem: If we use stored procedure passing an input parameter which is multi-value parameter, SSRS will take all those selected multiple values as different-2 parameters (as they all are ‘,’ separated). And thus if we do multiple selection (or Select All) error would be there, though it will work fine if we select only one value.

1. Here is a easier solution with a function and sub query with temporary table in sproc.

Got it from here Then you need to write a function for this where you can insert all the multi valued parameters into a table ,and then you can extract those values in the Where clause of your SP from that table.

I can give you some example

CREATE PROCEDURE [dbo].[spprodsales]

(

,@productid  VARCHAR(2000)

,@activity_held_status VARCHAR(2000)

)

AS

BEGIN

SELECT SalesOrderID,OrderQty,UnitPrice,ProductID FROM VP_WFAREPACTYEVENT VP_WFAREPACTYEVENT

WHERE ProductID IN (@productid) IN (SELECT InListID FROM fn_IntegerInList(@productid))

END

(where fn_IntegerInList is a function you need to write in the data base)

 

2. Here is second approach which is very nice. got this here

SSRS does output the values of a multi-valued parameter as a comma separated string… Which is why it works just fine when the SQL is typed directly into the Data Source.

Unfortunatly, it’s also why it goes tits up when you try to pass it to a single parameter of a SP. It’s coming in as a single value with a bunch of commas in it. Kind of like this… ‘Value1,Value2,Value3,Value4’

So… how to fix it???, Easy, add a little extra SQL to your SP. Like this…

ALTER PROCEDURE dbo.GetDownloadDetails_Soft
(@ProgramName varchar(150))
AS

DECLARE @x XML
SET @x = '<root><x>' + REPLACE(@ProgramName, ',', '</x><x>') + '</x></root>'

SELECT 
StatusDate, 
ProgramName, 
UserFullName, 
LoginName, 
CostCenterCode, 
EmailAddress, 
Phone, 
Manager, 
ComputerName, 
SCCMSiteCode, 
Status,
FailureReason, 
PONumber
FROM Installs
WHERE ProgramName IN (
	SELECT x.value('.', 'VarChar(50)')
	FROM @x.nodes('/root/x') AS a(x))
ORDER BY StatusDate

What it’s doing… It’s taking the csv that SSRS is sending to the SP’s parameter and converting it to XML. From there is using XML nodes to split the XML into rows in a derived table.

 

 

 

The following table provides examples of filter expression that includes a single-value or multivalue parameter reference.

Parameter type

(Filter) Expression

Operator

Value

Data Type

Single value

[EmployeeID]

=

[@EmployeeID]

Integer

Multivalue

[EmployeeID]

IN

[@EmployeeID]

Integer

 

The following table shows examples of filter equations that use different data types and different operators. The scope for the comparison is determined by report item for which a filter is defined. For example, for a filter defined on a dataset, TOP % 10 is the top 10 percent of values in the dataset; for a filter defined on a group, TOP % 10 is the top 10 percent of values in the group.

Simple Expression

Data Type

Operator

Value

Description

[SUM(Quantity)]

Integer

>

7

Includes data values that are greater than 7.

[SUM(Quantity)]

Integer

TOP N

10

Includes the top 10 data values.

[SUM(Quantity)]

Integer

TOP %

20

Includes the top 20% of data values.

[Sales]

Text

>

=CDec(100)

Includes all values of type System.Decimal (SQL "money" data types) greater than $100.

[OrderDate]

DateTime

>

2088-01-01

Includes all dates from January 1, 2008 to the present date.

[OrderDate]

DateTime

BETWEEN

2008-01-01

2008-02-01

Includes dates from January 1, 2008 up to and including February 1, 2008.

[Territory]

Text

LIKE

*east

All territory names that end in "east".

[Territory]

Text

LIKE

%o%th*

All territory names that include North and South at the beginning of the name.

=LEFT(Fields!Subcat.Value,1)

Text

IN

B, C, T

All subcategory values that begin with the letters B, C, or T.