Highlighting a row once a parameter has been selec

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

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

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

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.

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

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

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

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

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

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

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

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

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.

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

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
------------------------------

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

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

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

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

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources