SSRS 2005 - Multi value parameter with null?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SSRS 2005 - Multi value parameter with null??

Author

Topic

bigweed
Starting Member

19 Posts

Posted - 2010-11-23 : 11:53:07

I am a complete newbie to SSRS 2005 so please reply as if I have no idea! :o)

I have been asked to create a report at work. The report is based on a dataset called AllCalls. AllCalls is a list of service calls, with a column called Action ID. There is a multi value parameter for this report. The parameter uses a dataset called AllActions, which is a list of all Action ID's and Action descriptions. So far so good. The report works fine when I select a value in this parameter field when running the report. The correct data gets returned and all is well.

However, some of the service calls dont actually have a action code populated, so these service calls are not being returned if I 'Select all' on the parameter when running the report. I think the correct technical term is null values, but I am not sure. How do I show these service calls, which dont have an Action code, when I choose to 'Select all'? Ideally I would also have an option in the parameters dropdown called 'Blank' which would return all service calls which dont have a Action code populated. Can someone please help with this?

The table for Service Calls is 'Mxmservcall', the table name of the actions is 'Mxmservaction'. The column name for Action ID is 'Actionid' in both tables. The column name for Service call number in mxmservcall is JobID. The parameter name is Action.

In the parameter dataset, in the code window, I already have the following code:

SELECT ACTIONID, ACTIONDESCRIPTION, DATAAREAID
FROM MXMSERVACTION
WHERE (DATAAREAID IN (@CompanyID))
ORDER BY ACTIONID

Can anyone please help?

bigweed
Starting Member

19 Posts

Posted - 2010-11-24 : 07:03:24

Can anyone help please?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 07:12:29

when doing this sort of ting I usually have an entry called All which can be selected and allocate it an id like -1 or 0 and treat that sepatately in the sp that returns the resultset. In your situation I would have an n/a as well and allocate it an id like -2.
This is returned by the SP for the paranmeter list dataset so is easy to change and keep in step with the resultset SPs.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bigweed
Starting Member

19 Posts

Posted - 2010-11-24 : 07:27:29

I now have the following code in my AllAction parameter dataset:

SELECT ACTIONID, ACTIONDESCRIPTION, DATAAREAID
FROM MXMSERVACTION
WHERE (DATAAREAID IN (@CompanyID))
UNION
SELECT '-1' AS ACTIONID, '(Blank)' AS actiondescription, DATAAREAID
FROM MXMSERVACTION AS MXMSERVACTION_1
WHERE (DATAAREAID IN (@CompanyID))
ORDER BY ACTIONID

Using -1 without the ' ' characters resulted in an error saying that the "conversion failed when converting nvarchar to int". So I assume that because the Action ID is a mixture of letter and number is what caused the error. So I kind of guessed that maybe I should put the -1 inside ' ' characters. Dont know if thats a bad thing or not, seeing as I have never used SQL before! And the following is my ServiceCalls dataset code:

SELECT MXMSERVCALL.DATAAREAID, MXMSERVCALL.SITEID, MXMSERVCALL.JOBID,
MXMSERVCALL.LOGGEDDATE,
MXMSERVCALL.CUSTPURCHASEORDER, MXMSERVCALL.FAULTID,
MXMSERVFAULT.FAULTDESCRIPTION, MXMSERVCALL.ACTIONID,
MXMSERVACTION.ACTIONDESCRIPTION, MXMSERVCALL.DETAILS,
MXMSERVSITE.CUSTOMERACCOUNT, MXMSERVCALL.SCHEDULEDENGINEER,
MXMSERVCALL.STATUSID, MXMSERVCALL.LOGGEDTIME, MXMSERVCALL.VISITDATE,
MXMSERVCALL.EQUIPMENTPRODUCT, MXMSERVCALL.EQUIPMENTSERIAL,
MXMSERVCALL.SITENAME

FROM MXMSERVCALL INNER JOIN
MXMSERVSITE ON MXMSERVCALL.SITEID = MXMSERVSITE.SITEID AND
MXMSERVCALL.DATAAREAID = MXMSERVSITE.DATAAREAID INNER JOIN
MXMSERVFAULT ON MXMSERVCALL.FAULTID = MXMSERVFAULT.FAULTID AND
MXMSERVCALL.DATAAREAID = MXMSERVFAULT.DATAAREAID LEFT OUTER JOIN
MXMSERVACTION ON MXMSERVCALL.DATAAREAID =
MXMSERVACTION.DATAAREAID AND MXMSERVCALL.ACTIONID =
MXMSERVACTION.ACTIONID

WHERE (MXMSERVCALL.DATAAREAID IN (@CompanyID)) AND (MXMSERVCALL.SITEID IN
(@Site)) AND (MXMSERVSITE.CUSTOMERACCOUNT IN (@Customer)) AND
(MXMSERVCALL.STATUSID IN (@Status)) AND (MXMSERVCALL.LOGGEDDATE >=
@StartDate) AND (MXMSERVCALL.LOGGEDDATE <= @EndDate) AND
(MXMSERVCALL.FAULTID IN (@Fault)) AND (MXMSERVCALL.ACTIONID IN (@Action))

OR (MXMSERVCALL.ACTIONID IS NULL) AND ('-1' IN (@Action))

ORDER BY MXMSERVCALL.JOBID

When I preview the report, I get an option called '(Blank)' for the Action parameter. If I select this option, it returns nothing at all. I get a blank report. If I select all options within the Action parameter EXCEPT the '(Blank)' option, 2 records get returned, which are the only 2 records with an action code. However there are lots of service calls without an Action code against them. Am I doing something very obviously wrong? If I remove the Action parameter from the report, then 'Select all' for all the other parameters, all of the records get returned in the report. So it must be something to do with the Action parameter? Somebody please help!

bigweed
Starting Member

19 Posts

Posted - 2010-11-24 : 07:29:38

What is an SP? And how do I do what you have proposed?

quote:
Originally posted by nigelrivett

when doing this sort of ting I usually have an entry called All which can be selected and allocate it an id like -1 or 0 and treat that sepatately in the sp that returns the resultset. In your situation I would have an n/a as well and allocate it an id like -2.
This is returned by the SP for the paranmeter list dataset so is easy to change and keep in step with the resultset SPs.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 07:31:20

SP is a stored procedure.
I base all ssrs resultsets on stored procedures - means that the database structure can be changed without affecting the report and you can see what the report is doing from the database and test the resultsets independently.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bigweed
Starting Member

19 Posts

Posted - 2010-11-24 : 07:42:37

How do you create an SP?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 07:47:18

in the database run the sql

create prodedure myprocname
as
select ...
from ...
go

in your case it would be something like

create proc s_lkpActions
as
select ActionID, ActionName
from Actions
union all
select 'All', -2
union all
select 'n\a', -1
order by ActionID
go

Assuming you have a table which holds the Actions.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bigweed
Starting Member

19 Posts

Posted - 2010-11-24 : 07:54:10

Yes there is a table for Actions called mxmservaction. However, I am not sure where you mean to go to create this SP. Do I open SQL Server Management Studio, and do it in there somewhere?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 08:04:01

Yes, open a query window - make sure it is connected to that database and run the above statement.
If you then go to the database in the left pane, expand the database, programmability, stored procedures (right click and refresh) you should see the SP you have created.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bigweed
Starting Member

19 Posts

Posted - 2010-11-24 : 08:57:43

Okay so I have managed to go create a new SP as follows:

create proc SPActions
as
select ActionID, ActionDescription
from mxmservaction
union all
select 'All', -2
union all
select 'n\a', -1
order by ActionID
go

When I create a new dataset and link it to this SP, then Run the dataset, I get the following error:

An error occurred while reading data from the query result set.
Conversion failed when converting the nvarchar value 'No Fault Found' to data type int.

------------------------------
ADDITIONAL INFORMATION:

Conversion failed when converting the nvarchar value 'No Fault Found' to data type int. (Microsoft SQL Server, Error: 245)

Any ideas?

bigweed
Starting Member

19 Posts

Posted - 2010-11-24 : 12:29:08

Can anyone please help?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 12:37:43

create proc SPActions
as
select ActionID, ActionDescription
from mxmservaction
union all
select -2, 'All'
union all
select -1, 'n\a'
order by ActionID
go

Resultsets in the union'd statements need to have columns in the same order.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bigweed
Starting Member

19 Posts

Posted - 2010-11-26 : 07:30:46

I have managed to create a much simpler solution, after many attempts. @Action parameter dataset is now:

SELECT ACTIONID, ACTIONDESCRIPTION, DATAAREAID
FROM MXMSERVACTION
WHERE (DATAAREAID IN (@CompanyID))
UNION
SELECT '' AS ACTIONID, '(Blank)' AS ACTIONDESCRIPTION, @CompanyID AS DATAAREAID
ORDER BY ACTIONID

This returned a ‘Blank’ value as well as all available action codes, in the parameter dropdown when running the report. I then referenced the parameter dataset in the main dataset as normal:

WHERE (MXMSERVCALL.ACTIONID IN (@Action))

Job done!

Thanks for the help guys, much appreciated, I think I may have underestimated SQL a little… but I have progressed my SQL understanding quite a bit so its all good.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources