SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MultiValue Parameter not working


MultiValue Parameter not working

Author
Message
ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 54
Hello all,

I have a table that displays different statistics about "Properties". The table only has 1 row, this single row has textboxes that reference about 10 various datasets. Almost all of the datasets have a WHERE clause of "WHERE RMPROPID = @PROPERTIES".

When there is only 1 property selected from the multivalue parameter, it runs fine. However, when you select more than 1 property, all of the datasets are thrown off.

Does anyone have suggestions on how to handle the grouping, parameters, datasets, really anything so that I can list multiple properties in this table, but each row only references the individual property currently being represented?

Thanks.
H4K
H4K
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 567
First of all, you have make this "WHERE RMPROPID = @PROPERTIES"
to this "WHERE RMPROPID in (@PROPERTIES)".

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
H4K
H4K
Mr or Mrs. 500
Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)Mr or Mrs. 500 (517 reputation)

Group: General Forum Members
Points: 517 Visits: 567
[b]Does anyone have suggestions on how to handle the grouping, parameters, datasets, really anything so that I can list multiple properties in this table, but each row only references the individual property currently being represented?


You can use LookUp() and LookUpSet() function in SSRS to join datasets.

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 54
bump
ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 54
I tried to simplify my issue, here are all the factors.

I have 1 table. 1 have 1 multi-value parameter listing all properties called PROPERTIES. I want the table to display 1 property per row at a time.

The table has roughly 20 columns, referencing different datasets throughout.

All of the datasets that reference the property have a: WHERE RMPROPID IN (@PROPERTIES) and the dataset has @PROPERTIES value of: =JOIN(Parameters!PROPERTIES.Value,",") (This creates the list of properties selected)

The table has a single row, referencing the different datasets. The tables dataset is “SelectedProperties” and the group by on the row is =Fields!RMPROPID.Value…the “SelectedProperties” dataset looks like this…

SELECT RMPROPID,propname FROM RMPROP WHERE RMPROPID IN (@PROPERTIES)

Whenever I choose more than one property it does not work. Nothing is returned. Is there a trick I am missing? I want 1 row to reference one property at a time.
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90182 Visits: 38944
DDL for the table(s), sample data for the table(s), expected results based on the sample data.

Sorry, I know I'm good, but I can't see what you see so I have no idea what to tell you without more details.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 54


This is the table...the only row pulling data is the middle row...that row has a group by rmpropid. And the table as a whole has a dataset of selected properties.

Selected properties are:

select * from rmprop where rmpropid in (@PROPERTIES)



@PROPERTIES is filled by this parameter...



Let me know if this helps...

As of now I successfully got the rows to repeat for the correct properties...but the data in the datasets is still not working properly.

Do these pictures help you at all to know what I'm trying to achieve?

I just want every row to represent just that properties data.
ahosack
ahosack
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 54
I believe the problem I am experiencing at this point is this.

Each row is referencing every single value selected from multi value parameters and I only want it to reference one for each row.

This is because all the datasets are set up with WHERE RMPROPID IN (@PARAMETERS), thus using every property that was selected...

Even with group by PropID on the detail row of the table, I just don't know how to make the datasets only reference 1 property at a time.
peterzeke
peterzeke
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1842 Visits: 1766
While I think I'm somewhat understanding what you are trying to achieve (i.e., one row of data per property), your approach is confusing me. So, I have a couple of questions for you:

1) What version of SSRS are you using?
2) It looks like your dataset is SQL based (rather than OLAP, SSAS)?
3) I'm not sure why you have 20 datasets. Wouldn't a single dataset query that joins your data together be a better approach? A single dataset query could be written so that only one row per property is returned, I would think.

--pete



David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16079 Visits: 10103
peterzeke (3/8/2013)
...A single dataset query could be written so that only one row per property is returned...


+1


Far away is close at hand in the images of elsewhere.

Anon.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search