May 22, 2008 at 12:05 pm
Help!!!!!! I've been asked to produce a report which allows the user to either enter a range of codes or select from a multivalued list. I can create both seperately in Visual Studios and they work great in reporting services, but I can't figure out how to use them at the same time in the report. Here is a sample of the code I am using in SQL:
ALTER PROC [dbo].[lmc_sp_rs_NGDM_SplitTest]
@DIAG VARCHAR(255),@DIAG1 VARCHAR(10),@DIAG2 VARCHAR(10)
AS
SELECT DISTINCT
DIAGNOSISCODE,
PROBLEMDESCRIPTION AS PROBLEMDESCRIPTION
FROM PROBLEM_LIST_DATA_ WITH(NOLOCK)
WHEREISNUMERIC(OBSERV_VALUE) = '1' AND
(diagnosiscode IN (SELECT str FROM dbo.split(@diag,',')) or
diagnosiscode between @diag1 and @diag2)
Any thoughts??????
:crying:
June 2, 2008 at 10:23 am
Hi,
It is very simple to use Range in Reporting services. Just create two new report parameters for "FromValue" and "ToValue". Pass them to your query/stored procedure same as you are doing in your procedure.
for example: SELECT Field1, Field2 FROM Table1 WHERE Table1.Field1 Between @FromParam AND @ToParam
And for multivalue you have to create one Multivalue Report parameter, Just select the Multi-Value option (checkbox) and use comma (,) seperated values to use in your query.
But while you create multi-value parameter, be sure you selected STRING as data type.
for example: SELECT Field1, Field2 FROM Table1 WHERE Table1.Field1 IN (@MultiValueParam)
[Here @MultiValueParam is comma seperated values like 1,2,3,4,]
You can also use fetch multiple value form a query by selecting "from Query" option for Report parameter and specify the query for it.
Let me know is this what you need or not?
June 2, 2008 at 10:57 am
Thanks, it worked great!
Now that I that working, do you have any idea how I can set up the report in Visual Studios so the end user can pick either entering a value for the range parameter or picking a value from the drop down list I have for the multi-valued parameter. For example they need to either enter codes 101-150 or pick 101,102,105. When I tried putting the report together it won't run because the mv parameter cannot be null. Any help in this would be extremely appreciated!!
June 2, 2008 at 12:23 pm
You can use the query like this in your report
Query:
select Field1, Field2 from Table1 WHERE (Field1 between @FromId AND @ToId) OR Field1 in (@SelectedList)
Parameters:
Allow Null to @FromId and @ToId (single value parameters)
Allow Blank Value to @SelectedList (Multivalued parameter)
Case 1: 101-150 (Range)
@FormId = 101
@ToId = 150
@SelectedList = , (blank values)
Case 2: 101,102,105 (Selected Values)
@FromId = NULL,
@ToId = NULL,
@SelectedList = 101,102,105
Please try it and let me know if it serve you purpose.
Thank you
June 3, 2008 at 10:31 am
Almost!!
The query works great but the Visual Studio report stills needs a little tweaking. The report runs fine when I enter the multi-valued parameter and leave the parameter range null, but if I try to enter the range and leave the mutli-valued parameter blank it gives me an error that I have to select a value.
June 3, 2008 at 12:57 pm
Cant you pass "," from VS to Report?
June 3, 2008 at 1:01 pm
Yes, missed that. Works great!! Thanks a bunch!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply