Solved

SSRS 2008, multivalued parameter, SQL Query

Posted on 2014-11-30
6
152 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 250 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Date fields 11 62
SSRS Expression syntax with Iif Else 1 61
Need more granular date groupings 4 44
When are cursors useful? 8 59
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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