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
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Highlighting a row once a parameter has been selec

Author  Topic 

wafw1971
Yak Posting Veteran

75 Posts

Posted - 2013-04-26 : 07:25:32
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

52326 Posts

Posted - 2013-04-26 : 07:38:25
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 - 2013-04-26 : 07:50:45
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

52326 Posts

Posted - 2013-04-26 : 07:58:25
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 - 2013-04-26 : 08:00:09
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

52326 Posts

Posted - 2013-04-26 : 08:24:43
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 - 2013-04-29 : 03:54:04
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

52326 Posts

Posted - 2013-04-29 : 03:57:33
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 - 2013-04-29 : 04:11:42
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

52326 Posts

Posted - 2013-04-29 : 04:18:05
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 - 2013-04-29 : 04:19:40
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

52326 Posts

Posted - 2013-04-29 : 04:21:20
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 - 2013-04-29 : 04:32:33
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 - 2013-04-29 : 04:51:37
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

52326 Posts

Posted - 2013-04-29 : 04:52:45
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 - 2013-04-29 : 05:00:08
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

52326 Posts

Posted - 2013-04-29 : 05:35:19
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 - 2013-04-30 : 06:40:36
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

52326 Posts

Posted - 2013-04-30 : 06:57:36
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 - 2013-04-30 : 07:02:11
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

52326 Posts

Posted - 2013-04-30 : 07:21:57
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
  Previous Page&nsp;  Next Page

- Advertisement -