?
Solved

SSRS 2008, multivalued parameter, SQL Query

Posted on 2014-11-30
6
Medium Priority
?
155 Views
Last Modified: 2014-12-14
Hi,

a.
 I have a drop down multivalued parameter. the items in this parameter must be hard coded and not coming from a dataabase.
 e.g.:  
All
CurrentFriday
 NextFriday

how can this be done?

b.
 I want to hide the 'Select All' which is placed by default when you select multivalued parameter. How can this be done?

c.
when I send it to stored procedure, I need to check an Enddate column.
if enddate is less than today then set columnX to 'currentFriday' otherwise to next friday.
I need to do it within the query.

Thanks in advance
0
Comment
Question by:shmz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 1000 total points
ID: 40473275
a: I usually hardcode this in a query then configure the report parameter based on the dataset, easier for maintenance:

select 'Current Friday' as Label, 'CF' as Code
union all
select 'Next Friday', 'NF'

b: you can't, it comes for free when you check the multi-value checkbox.  Why would that be an issue? (this only has effect on the user interface)

c: check this

SET DATEFIRST 7;    
declare @enddate date = '2014-01-01';
declare @currentdate date = getdate();

select case when @enddate < getdate() then dateadd(dd,6-datepart(dw,@currentdate)-7,@currentdate) 
	else dateadd(dd,6-datepart(dw,@currentdate),@currentdate) end CF_or_NF
	, dateadd(dd,6-datepart(dw,@currentdate),@currentdate) as NextFriday
	, dateadd(dd,6-datepart(dw,@currentdate)-7,@currentdate) as CurrentFriday

Open in new window

0
 

Author Comment

by:shmz
ID: 40473631
Thank but I don't need to do date calculations.
I have a column of type udt_fuzzydate(char 8)

I have to do conversion bef
0
 

Author Comment

by:shmz
ID: 40473657
Thank but I don't need to do date calculations.

I have 2 issues:

I have a column of type udt_fuzzydate(char 8)

I have to do conversion before comparing, here is actual code but it doesn't work:
(Error: conversion of varchar datatype to a datetime data type resulted in an out of range value)
Select x
, y
, z
, (Select case when
( isdate(end date)= 1 and convert(datetime, enddate) > getdate()) or
(isdate(end date)= 1 and convert(datetime, enddate) = null)
Then 'current'
Else 'next'
End) as type
,.....
From tble1,.....

Other issue is that I pass the type (current, next or all) from report parameter to query and now I need to check which record in above conversion and comparison matches the selected parameter in the report???
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 40475362
"Thank but I don't need to do date calculations."

Erm...

"if enddate is less than today"

That IS a date calculation...

I understand that the data type of enddate is udt_fuzzydate(char 8)?  What does such a value look like?

PS: I recommend you to focus on one issue per question.  In this one I see at least three...
0
 

Accepted Solution

by:
shmz earned 0 total points
ID: 40490282
Thanks for your a and b.

c. this is how I solved it:

(select case when isdate(ENDDATE)=1 then (case when convert(datetime, ENDDATE) > getdate()
                                                                              then 1
                                                                              else 2
                                                                                end)
                                                              else 1  
                                                              end) as X
0
 

Author Closing Comment

by:shmz
ID: 40498739
thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question