Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MultiValue Parameter not working Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 2:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
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.
Post #1422321
Posted Friday, February 22, 2013 3:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
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
Post #1422950
Posted Friday, February 22, 2013 3:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 7, 2014 2:21 PM
Points: 132, Visits: 557
[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
Post #1422954
Posted Tuesday, March 5, 2013 11:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
Points: 9, Visits: 54
bump
Post #1426967
Posted Tuesday, March 5, 2013 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
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.
Post #1427062
Posted Tuesday, March 5, 2013 8:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:23 PM
Points: 20,860, Visits: 32,883
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.



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)
Post #1427138
Posted Wednesday, March 6, 2013 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
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.
Post #1427642
Posted Thursday, March 7, 2013 1:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
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.
Post #1428246
Posted Friday, March 8, 2013 6:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 11:56 AM
Points: 317, Visits: 1,529
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



Post #1428544
Posted Friday, March 8, 2013 6:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:30 AM
Points: 7,057, Visits: 7,287
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.

Post #1428555
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse