Visual Studio 2010 Reporting Services - how to populate something when nothing is returned

Question

I'm using a query with two parameters (@campaign,@resultcode) to populate a table with 3 columns ("Campaignname","Disposition","Count"), but when either one of those parameters don't exist in the database, nothing populates in the table. Is there a way to make it populate the two parameters with a count of 0? Also I have it set so that multiple parameters can be selected. I've tried IIF(IsNothing()..., IIF(***.value = null or ""). Still doesn't do what I want it to do. Some help?

Included code from comment response:

SELECT databasename, callresultdescription, count(*) as Count 
FROM bpsql00.[histCallCenterStats].[dbo].[CallResults] 
WHERE databasename IN(@campaign) AND callresultcode IN(@resultcode) 
GROUP BY databasename, callresultdescription 

The callresultdescription is AKA disposition

Answer 1

You can accomplish this with an IF statement in the SQL query:

IF EXISTS (SELECT 1 FROM bpsql00.[histCallCenterStats].[dbo].[CallResults] WHERE databasename IN ( @campaign ) AND callresultcode IN ( @resultcode ))
    SELECT  databasename
            , callresultdescription
            , [Count] = COUNT(*)
    FROM    bpsql00.[histCallCenterStats].[dbo].[CallResults]
    WHERE   databasename IN ( @campaign )
            AND callresultcode IN ( @resultcode )
    GROUP BY databasename ,
            callresultdescription; 
ELSE
    SELECT databasename = @campaign
        , callresultdescription = @resultcode
        , [Count] = 0

Edit per question in the comment:

It gets tricky when you need to return a multi-valued parameter. If you're on SQL 2016, you can use the new TSQL STRING_SPLIT function to split out their comma-separated selections. There are also splitter functions you can find on the interwebs for prior versions of SQL. The simplest solution, though, is to let the query return nothing and set the NoRowsMessage of the tablix to inform the client. You can use an expression like

="No records found in the selected campaigns (" & _
    Parameters!campaign.Value & ") and result codes (" & _
    Parameters!resultcode.Value & ")."

That gives the user a record of what was searched and that nothing was found to match their criteria.

Answer 2

You could union them together:

  --create table [CallResults] (databasename varchar(10),callresultdescription 
varchar(10),myvalue int)

--insert into [CallResults]
--values ('a','AA',1),
--('b','BB',2),
--('c','CC',3)

--select * from [CallResults]

declare  @campaign varchar(10)='d',@resultcode varchar(10)='dd' ; 

SELECT databasename, callresultdescription, 
count(1) as [Count]
FROM [CallResults] 
WHERE databasename IN (@campaign) 
AND callresultdescription IN (@resultcode) 
GROUP BY databasename, callresultdescription 
UNION 
SELECT databasename=@campaign,
callresultdescription=@resultcode,
0 as [Count]
from [CallResults]
where  databasename not IN (@campaign) 
AND callresultdescription not IN (@resultcode) 

Answer 3

So finally figured it out. I have concluded that the program is limited what I wanted to do. So... why not let SQL do it for me and I can just call a stored procedure. BINGO. I had to create a function as well. So for anyone who needs something like this. Stored procedure I created:

alter procedure [dbo].[rs_Query]
@campaign varchar (100),
@resultcode varchar (100)
as
Begin
declare @var_campaign varchar(100)
declare @var_resultcode varchar(100)

declare @c table(ID int identity, databasename varchar(100))
declare @r table(ID int identity, callresultcode varchar(100))
insert into @c select element from dbo.func_split(@campaign, ',')
insert into @r select element from dbo.func_split(@resultcode,',')


declare @dbcnt int --count of campaigns selected
declare @crcnt int --count of dispositions selected
declare @crrow int --row id for campaigns selected
declare @dbrow int --row id for dispositions selected
declare @tempdbname varchar(50) --temp campaign name
declare @tempcr varchar(50) --temp call result name
declare @t table (databasename varchar(100), callresultdescription varchar (100), Count int)
declare @count int

select @dbcnt = count(*) from @c
select @crcnt = count(*) from @r
select @dbrow = 1
select @crrow = 1

while @dbcnt >= @dbrow
begin 
set @tempdbname = (select databasename
from bpsql00.callcenteraux.dbo.DailyReportsCampaign
where databasename = (select databasename from @c where id = @dbrow))
    set @crrow = 1
    while @crcnt >= @crrow
    begin
    set @tempcr = (select CallResultDescription
                    from CallResultCode
                    where CallResultCode = (select CallResultCode from @r where id = @crrow));

        if exists(select 1 from bpsql00.[histCallCenterStats].[dbo].[CallResults]
                    where CallResultCode = (select CallResultCode from @r where id = @crrow) and databasename = @tempdbname)
            begin
            select @count = count(*) from bpsql00.[histCallCenterStats].[dbo].[CallResults]
                    where CallResultCode = (select CallResultCode from @r where id = @crrow) and databasename = @tempdbname
            insert into @t values(@tempdbname,@tempcr,@count)
            end

        else 
            begin
            insert into @t values(@tempdbname,@tempcr,0)
            end
    set @crrow = @crrow + 1
    end

set @dbrow = @dbrow + 1
end 
select * from @t

end

And function I created:

ALTER FUNCTION [dbo].[func_Split] 
    (   
    @DelimitedString    varchar(8000),
    @Delimiter              varchar(100) 
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = LEN(@Delimiter + 'x') - 1

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(@Delimiter, @DelimitedString)

        IF @Index = 0
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END