SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Highlighting a row once a parameter has been selec
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/26/2013 :  07:25:32  Show Profile  Reply with Quote
I have a report that has 3 parameters, RaceDate, RaceCourse, SilksColours but the SilksColours parameter is optional, but if the SilksColours is selected I just want to highlight the row in my report with the colour yellow but all the other rows stay white.

Is this possible?

Thanks

W

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  07:38:25  Show Profile  Reply with Quote
just put an expression as below for color after setting default value of parameter as NULL


IIF(IsNothing(Parameters!SilksColours.value),"yellow","White")



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/26/2013 :  07:50:45  Show Profile  Reply with Quote
Hi Visakh

What do you mean setting parameter value to NULL, at the moment the parameter is looking up to another dataset

Thanks

Wayne
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  07:58:25  Show Profile  Reply with Quote
then whats the default value set for parameter ?(you told before that its optional)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/26/2013 :  08:00:09  Show Profile  Reply with Quote
There isn't a default set, you have a choice in a drop down list.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/26/2013 :  08:24:43  Show Profile  Reply with Quote
nope...you've to set a default if you've to make it optional. otherwise unless you manually select a value it doesnt render the report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/29/2013 :  03:54:04  Show Profile  Reply with Quote
Good Morning visakh, I dont think I have explained myself right. I have 3 parameters:

Racedate - This must be selected

Racecourse - This Must be selected

Silkcolours - This does not need to be selected if the person doesn't want to, if not selected (or left blank) all rows will we be left white, however if a person selects a colour from the parameter drop down choice then where that colour appears on the report that row will be highlighted yellow and all other rows will be white.

I hope that this has clarified my question. Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  03:57:33  Show Profile  Reply with Quote
Still my suggestion holds good

in parameter properties inside report you need to set default value of SilkColors to <blank> and then in your color properties for desired textbox you need to write an expression like

=IIF(Len(Parameters!Skillcolours.value) >0, Parameters!Skillcolours.value,"White")

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/29/2013 :  04:11:42  Show Profile  Reply with Quote
Im getting myself confused

@Colours Parameter
General Tab:
Name: Colours
Prompt: Select a Colour
Data Type: Text
Allow Multiple Values
Parameter Visibility: Visible

Available Values Tab:
Get Values from a query

This is a list of colours black, blue, red etc

Default Values Tab:

Specify Values:
Expression: <blank>

Advanced Tab: Left as default


I know I am doing something wrong but I don't know what.

Thanks

Wayne

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  04:18:05  Show Profile  Reply with Quote
if you've to set expression as blank for default value then there should be an item with value as blank returned by the query set for your parameter dataset (query giving you list of colours black, blue, red etc). otherwise it wont put default value as blank and still prompt you to enter one until it renders the report.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/29/2013 :  04:19:40  Show Profile  Reply with Quote
Do you mean my dataset?

SELECT
SilksImages.id
,SilksImages.Silks_Skey
,SilksImages.SilksName
,SilksImages.filestream_data
FROM
SilksImages
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  04:21:20  Show Profile  Reply with Quote
quote:
Originally posted by wafw1971

Do you mean my dataset?

SELECT
SilksImages.id
,SilksImages.Silks_Skey
,SilksImages.SilksName
,SilksImages.filestream_data
FROM
SilksImages


nope not this query

but query used for dataset which gives you the list of colors for SkillColor parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/29/2013 :  04:32:33  Show Profile  Reply with Quote
Hi Visakh

This is my dataset that feeds the @Colours parameter
I just changed the name in my question to be easier SilkColours = SilksImages.SilksName

SELECT
SilksImages.id
,SilksImages.Silks_Skey
,SilksImages.SilksName
,SilksImages.filestream_data
FROM
SilksImages
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/29/2013 :  04:51:37  Show Profile  Reply with Quote
Hi Visakh

=IIf(Fields!SilksName.Value = Parameters!Colours.Value(0), "Yellow", "White")

This worked sort off, but I need to have the option of selecting None (default) or selecting many but when I do this is it only shows me one highlighted field.
First Question - How do I get the parameter to default none
Second Question - How do I get the parameter to show me multiple highlighted cells when selected?

Sorry to be a pain, but this is still all new to me.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  04:52:45  Show Profile  Reply with Quote
then this should also return a row with color as blank to set as default like

SELECT
SilksImages.id
,SilksImages.Silks_Skey
,SilksImages.SilksName
,SilksImages.filestream_data
FROM
SilksImages
UNION ALL
SELECT -1,
-1,
'',
NULL


then you'll be able to set default value as blank

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/29/2013 :  05:00:08  Show Profile  Reply with Quote
I get this error message when I add your version of the dataset

TITLE: Microsoft SQL Server Report Builder
------------------------------

An error occurred while executing the query.
Operand type clash: uniqueidentifier is incompatible with int

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

Operand type clash: uniqueidentifier is incompatible with int (Microsoft SQL Server, Error: 206)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2218&EvtSrc=MSSQLServer&EvtID=206&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/29/2013 :  05:35:19  Show Profile  Reply with Quote
which field is unique identifier? then use corresponding default value as NULL

SELECT
SilksImages.id
,SilksImages.Silks_Skey
,SilksImages.SilksName
,SilksImages.filestream_data
FROM
SilksImages
UNION ALL
SELECT NULL,
-1,
'',
NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/30/2013 :  06:40:36  Show Profile  Reply with Quote
Im sorry Visakh I don't understand what you are suggesting and I don't want to waste your time.

This is my new Dataset which provides a multivalued list to my Colour parameter

SELECT Silks.SilksName
,SilksImages.Silks_Skey
FROM Silks
INNER JOIN SilksImages ON Silks.Silks_Skey = SilksImages.Silks_Skey

The Parameter list is Black, Blue, Red, Green. I need this parameter to run even if no colours have been selected, I also need the parameter to be able to select multiple colours, once selected the rows on the report are highlighted yellow.

This is what I am using with the text box properties

=IIf(Fields!silks_skey.Value = Parameters!ColourChoice.Value(0), "#f5ff43", "White")

Does this make more sense, if not dont worry about it.

Thanks for all you help as always.

Ta

Wayne
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/30/2013 :  06:57:36  Show Profile  Reply with Quote
It makes sense but my only point was for this to work you've to always select one or more colors from the parameter unless you set any value as default which is among the ones returned by above query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

wafw1971
Yak Posting Veteran

75 Posts

Posted - 04/30/2013 :  07:02:11  Show Profile  Reply with Quote
And this is where I get confused you have said

'set any value as default which is among the ones returned by above query'

How do I do this, I haven't got a clue.

Ta

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/30/2013 :  07:21:57  Show Profile  Reply with Quote
see my earlier select statement as below

SELECT
SilksImages.id
,SilksImages.Silks_Skey
,SilksImages.SilksName
,SilksImages.filestream_data
FROM
SilksImages
UNION ALL
SELECT NULL,
-1,
'',
NULL


this will now contain an additional record with '' value for color along with your other colors.
Once this is done then you can come and set value as '' in default values for your parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000