asked on

SSRS - multiselect not handling blanks properly

I have a report from SSRS. I need to include blanks...so that if a user selects all any value is returned for that column.

I am listing the dataset which populates the dropdown in SSRS.
I am listing the function used to parse the string from report server and create a comma delimited list of strings.

I am listing the where clause from the report where I call the function.
This where clause should be able to select all or just items on the list if a user selects individual values.

It was my thought that moving the 'blank' to the bottom of the sorted list in the dataset would fix this. It is not working.

dataset - I also replaced the commas in the list ..just in case that was messing up...no luck

select row_number() OVER (order by t1.cTitle) as titlerow,replace(t1.cTitle,',','') as cTitle

			  from
			  (select distinct coalesce(n.cTitle,'') cTitle
			  from cadoc_crm..tContact n
			  )t1

			  order by CASE when  t1.cTitle = '' then 1 else 0 end

Open in new window

Here is my string parser:

USE [cadoc_crm]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 11/6/2017 8:58:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Open in new window

Finally my where clause to get the list of values including a blank....as I need all values evaluated.

( t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) OR @Title = '' )

Open in new window

Pawan Kumar

Hey rod,
can you pls attach your rdl?

Robb Hill

ASKER

No I cannot..but I can send anything you might need..the rdl is on a customer's server....I dont have it where I can easily attach and not risk security

Pawan Kumar

ok, Do we basically dont want to so blanks

do we need to ignore them ?

Robb Hill

ASKER

I need the blanks to count....I need the query to pull blanks so those records dont break the report...

If a user selects one item...fine..

But if a user selects all....the blanks need to be included.

Pawan Kumar

so in DB also we have blanks / NULL ?

Robb Hill

ASKER

yes

Robb Hill

ASKER

thats the whole reason I need it ..so if I do a select all..I get the true select all

Pawan Kumar

Please check with this -

YourQuery
INNER JOIN
	( SELECT ISNULL(splitdata,'-99') splitdata FROM  dbo.fnSplitString(@Title,',')) k ON k.splitdata = ISNULL(t2.Title,'-99')

Open in new window

Robb Hill

ASKER

not sure - here is my joins

Pawan Kumar

yes pls give me full code ...also can you pls provide what you are currently getting with blanks/null and what you need.. may be few rows.

Robb Hill

ASKER

very large proc: the commented out part is the params I need to get working after this one if fixed...all the same principle.

ALTER  procedure [dbo].[CRMDesignation]


@SiteCode varchar(max),
@DateClientCreatedStart datetime = '1/1/1970 12:00:000',
@DateClientCreatedEnd datetime = '1/1/9999 12:00:000',
@NativeLinked varchar(20),
@ActiveClient int,
@ActiveContact varchar(20),
@State varchar(max),
@Title varchar(max)
--@Industry varchar(max)
--@EntityType varchar(max)
--@ClientRating varchar(max),
--@PartnerDirector varchar(max),
--@ReferralSource varchar(max),
--@ReferralsourceName varchar(max)

as

begin 

select c.cType AS ClientType,
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ClientStatus, 
c.cFein as [Client ID],
c.cName as [Client Name], 
t2.[Native/Linked],
t2.Designate,
t2.[Role],
t2.ContactStatus,
t2.[First Name],
t2.[Last Name],
t2.Salutation,
t2.Title,
t2.Email,
t2.[Email 2],
t2.[Email 3],
c.cPhone1 as [Entity Main Phone],
t2.[Work Phone],
t2.[Home Phone],
t2.[Cell Phone],
t2.[Address 1],
t2.[Address 2],
t2.[Address 3],
t2.City,
t2.[State],
t2.Zip,
t2.Country,
c.cIndustry as [Industry],
c.cUDF5 AS [Entity Type], 
c.cUDF6 AS [Fiscal Year End], 
cf.ic_cClientRating AS [Client Rating], 
c.cUDF7 AS [Partner/Director],
c.cUdf4 as [Date Client Created],
cf.ic_cReferralSource as [Referral Source],
cf.ic_cReferralSourceName as [Referral Source Name],
c.cClientCommunication as [Communication Method Preference],
--services
cf.ic_lSTaxCCorp as [Tax C Corp],
cf.ic_lSTaxSCorp as [Tax S Corp],
cf.ic_lSTaxFiduciary as [Tax Fiduciary],
cf.ic_lSTaxForm as [Tax-Form], 
cf.ic_lSTaxEstate as [Tax-Estate], 
cf.ic_lSTaxNonProfit as [Tax-Non-Profit], 
cf.ic_lSTaxPartnership as [Tax Partnership], 
cf.ic_lSTaxMassBusTrusts as [Tax-Max Bus Trusts], 
cf.ic_lSTaxPensions as [Tax-Pensions], 
cf.ic_lSTaxSalesPersPropTax as [Tax-S/P Prop Tax], 
cf.ic_lSTaxPayroll1099andW2 as [Tax-Payroll 1099/W-2], 
cf.ic_lSTaxInternational as [Tax-International], 
cf.ic_lSTaxIndividual as [Tax-Individual], 
cf.ic_lSTaxScheduleC as [Tax-Schedule C], 
cf.ic_lSTaxScheduleE as [Tax-Schedule E], 
cf.ic_lSTaxEstatePlanIndiv as [Tax-Estate Plan - Indiv], 
cf.ic_lSTaxGiftTaxRelated as [Tax-Gift Tax/Related], 
cf.ic_lSTaxInternationalIndiv as [Tax-InternationalIndiv], 
cf.ic_lSBKPWriteup as [BKP-Writeup], 
cf.ic_lSBKPWriteupMonthly as [BKP-Writeup-Monthly], 
cf.ic_lSBKPWriteupQuarterly as [BKP-Writeup-Quarterly], 
cf.ic_lSBKPGrayPay as [BKP-GrayPay], 
cf.ic_lSBKPOutsourcedAccounting as [BKP-Outsourced Accounting], 
cf.ic_lSAandAAOP as [A&A-Agreed Upon Proc], 
cf.ic_lSAandAAudit as [A&A-Audit], 
cf.ic_lSAandAEBPA as [A&A-Emp Ben Plan Audit], 
cf.ic_lSAandACompilationMonthly as [A&A-Compil Monthly], 
cf.ic_lSAandACompilation as [A&A-Compilation], 
cf.ic_lSAandAOverheadAudit as [A&A-Overhead Audit], 
cf.ic_lSAandAReview as [A&A-Review], 
cf.ic_lSAandAInternalAuditing as [A&A-Internal Auditing],
cf.ic_lSMASAccountingConsultation as [MAS-Acctng Consult], 
cf.ic_lSMASFuelExchange as [MAS-FuelExchange], 
cf.ic_lSMASFuelTrack as [MAS-Fuel Track], 
cf.ic_lSMASMgmtConsultation as [MAS-Mgmt Consult], 
cf.ic_lSMASLitigationSupport as [MAS-Litig Support], 
cf.ic_lSMASPersonalFinancialPlanning as [MAS-Pers Fin Plan], 
cf.ic_lSMASMandAConsulting as [MAS-M&A Consult], 
cf.ic_cSMcDonaldsServices as [McDonald's-Compil],
cf.ic_lCSValuationAppraisals as [CS-Valuation Appraisals], 
cf.ic_cSGEMFinancialPlan as [GEM Financial Plan], 
cf.ic_cSOtherServices as [Other Services]


     from

(
select 
n1.nidClient, n1.Designate,n1.[Role],n1.[First Name],n1.[Last Name],n1.Salutation,n1.Title,
n1.Email,n1.[Email 2],n1.[Email 3],n1.[Work Phone],n1.[Home Phone],n1.[Cell Phone], 
COALESCE(ContactAddressNid,ClientAddressNid) as [nid],
COALESCE(ContactAddress1,ClientAddress1) as [Address 1],
COALESCE(ContactAddress2,ClientAddress2) as [Address 2],
COALESCE(ContactAddress3,ClientAddress3) as [Address 3],
COALESCE(ContactAddressCity,ClientAddressCity) as [City],
COALESCE(ContactAddressState,ClientAddressState) as [State],
COALESCE(ContactAddressZip,ClientAddressZip) as [Zip],
COALESCE(ContactAddressCountry,ClientAddressCountry) as [Country],
n1.[Native/Linked],n1.ContactStatus



from

--native
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
c.nidClient, c.cdesignate as Designate,'' as [Role],
c.cFirstName as [First Name], c.cLastName as [Last Name], c.cSalutation as [Salutation],c.cTitle as [Title], 
c.cEmail as [Email],c.cEmailAlt as [Email 2],c.cEmailAlt2 as [Email 3],
c.cPhone as [Work Phone], c.cHomePhone as [Home Phone], c.cCellPhone as [Cell Phone],
'Native' as 'Native/Linked',
(CASE WHEN c.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
FROM cadoc_crm.dbo.tContact c 
inner  join cadoc_system..tsitexcrmclient xc 
	on  c.nidclient = xc.nidclient
inner join cadoc_system..tsite s 
	on xc.nidsite = s.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = c.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on c.nidClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
	
	
WHERE 
  c.cLastName not in('POOL','USER','ADMINISTRATOR')  and s.cPSiteCode = 'Root' ) n1

 Union 
--linked
select 
t1.nidClient, t1.Designate,t1.[Role],t1.[First Name],t1.[Last Name],t1.Salutation,t1.Title,
t1.Email,t1.[Email 2],t1.[Email 3],t1.[Work Phone],t1.[Home Phone],t1.[Cell Phone], 
COALESCE(ClientAddressNid,ContactAddressNid) as [nid],
COALESCE(ClientAddress1,ContactAddress1) as [Address 1],
COALESCE(ClientAddress2,ContactAddress2) as [Address 2],
COALESCE(ClientAddress3,ContactAddress3) as [Address 3],
COALESCE(ClientAddressCity,ContactAddressCity) as [City],
COALESCE(ClientAddressState,ContactAddressState) as [State],
COALESCE(ClientAddressZip,ContactAddressZip) as [Zip],
COALESCE(ClientAddressCountry,ContactAddressCountry) as [Country],
t1.[Native/Linked],t1.ContactStatus



from

--linked contacts
(Select   ContactAddress.nId as ContactAddressNid, ContactAddress.cAddress1 as ContactAddress1, ContactAddress.cAddress2 as ContactAddress2,ContactAddress.cAddress3 as ContactAddress3,ContactAddress.cCity as ContactAddressCity,ContactAddress.cState as ContactAddressState,ContactAddress.cPostalCde as ContactAddressZip,ContactAddress.cCountry as ContactAddressCountry,
ClientAddress.nId as ClientAddressNid, ClientAddress.cAddress1 as ClientAddress1, ClientAddress.cAddress2 as ClientAddress2,ClientAddress.cAddress3 as ClientAddress3,ClientAddress.cCity as ClientAddressCity,ClientAddress.cState as ClientAddressState,ClientAddress.cPostalCde as ClientAddressZip,ClientAddress.cCountry as ClientAddressCountry, 
xc.nIDClient,xc.cDesLinked as Designate, xc.cLabel as [Role],
n.cFirstName as [First Name], n.cLastName as [Last Name], n.cSalutation as [Salutation],n.cTitle as [Title], n.cEmail as [Email],n.cEmailAlt as [Email 2],n.cEmailAlt2 as [Email 3],
n.cPhone as [Work Phone], n.cHomePhone as [Home Phone], n.cCellPhone as [Cell Phone],
(LTRIM(n.cLastName)) + ', ' + RTRIM(LTRIM(n.cFirstName)) AS PrimaryContact,'Linked' as 'Native/Linked',
(CASE WHEN n.lActive = 0 THEN 'Inactive' ELSE 'Active' END) AS ContactStatus
from cadoc_crm..tclient c 
inner join cadoc_crm..tclientXContact xc
	on c.nid = xc.nIDClient
inner join cadoc_crm..tContact n
	on xc.nIDContact = n.nid
left join cadoc_crm..ContactXPrimaryAddress cp 
	on cp.nIdContact = n.nid
left join cadoc_crm..[Address] ContactAddress
	on ContactAddress.nId = cp.nIdAddress
left join cadoc_crm..ClientXAddress ca
	on xc.nIDClient = ca.nIdClient
left join cadoc_crm..[Address] ClientAddress
	on ClientAddress.nId = ca.nIdAddress
Where n.nid not in (
	Select    c.nid
	FROM         
		cadoc_system..tSite s 
	INNER JOIN cadoc_system..tSiteXCrmClient  xc
		ON s.nid = xc.nidSite 
	INNER JOIN cadoc_crm.dbo.tContact c 
		ON xc.nIdClient =c.nidClient
	WHERE     s.cSiteCode = 'Root'
	 and c.cLastName not in('POOL','USER','ADMINISTRATOR') 
)
 and n.cLastName not in('POOL','USER','ADMINISTRATOR')  
)t1
)t2

Left Outer Join 

cadoc_crm..tClient c 
	on t2.nidClient = c.nid
left join cadoc_crm..tClientCustomFields cf 
	on c.nid = cf.Clientnid 
left join tTaskXClient txCli 
	on c.nid = txCli.nClientID

where 
CAST(c.cUdf4 as DateTime) between @DateClientCreatedStart and @DateClientCreatedEnd 
AND
(((@NativeLinked = 'Native' or @NativeLinked= 'Linked') and t2.[Native/Linked] = @NativeLinked) or (@NativeLinked = 'All' and t2.[Native/Linked] = t2.[Native/Linked]))
And 
(((@ActiveClient = 0 or @ActiveClient= 1) and c.lActive = @ActiveClient) or (@ActiveClient = 2 and c.lActive = c.lActive))
And 
(((@ActiveContact = 'Active' or @ActiveContact= 'Inactive') and t2.ContactStatus = @ActiveContact) or (@ActiveContact = 'All' and t2.ContactStatus = t2.ContactStatus))
AND
t2.[State] IN ( SELECT splitdata FROM dbo.fnSplitString(@State,',') )
AND
( t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) OR @Title = '' )
--AND
--(c.cIndustry IN ( SELECT splitdata FROM dbo.fnSplitString(@Industry,',') ) or @Industry = '')
--AND
--(c.cUDF5  IN ( SELECT splitdata FROM dbo.fnSplitString(@EntityType,',') ) or @EntityType = '' )
--AND
--(cf.ic_cClientRating IN ( SELECT splitdata FROM dbo.fnSplitString(@ClientRating,',') ) or @ClientRating = '')
--AND
--(c.cUdf7 IN ( SELECT splitdata FROM dbo.fnSplitString(@PartnerDirector,',') ) or @PartnerDirector = '')
--AND
--(cf.ic_cReferralSource IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralSource,',') ) or @ReferralSource = '')
--AND
--(cf.ic_cReferralSourceName IN ( SELECT splitdata FROM dbo.fnSplitString(@ReferralsourceName,',') ) or @ReferralSourceName = '')


order by c.cName asc




end

Open in new window

Robb Hill

ASKER

If I remove the param for title.....I will get two rows....that do not have title poplulated.

When I add it back I do not get these rows because it is not respected blanks.

Its fine in SQL Server..this seems to be an issue with hout report server passes this pararamter...I think...

Pawan Kumar

IN Title parameter - have you selected allow NULLs and allow Blanks

Robb Hill

ASKER

its a select all in SSRS...so cannot do allow nulls.

But yes....on that parameter I have select all , and blanks

Pawan Kumar

Okies. I shall create a rdl with sample data in sometime

Robb Hill

ASKER

ok ...I really appreciate..it....im at stumped:( was supposed to have this for a presentation tomorrow.....killing me...been on the same issue for 12 hours.

Pawan Kumar

Creating rdl for this now.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

edited my last comment.

Robb Hill

ASKER

Thanks Pawan as always!!!