MultiValue Parameter not working

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » MultiValue Parameter not working

18 posts, Page 1 of 2

1 2 »»

MultiValue Parameter not working

Rate Topic

Display Mode

Topic Options

Author

Message

ahosack

Posted Wednesday, February 20, 2013 2:52 PM

Forum 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

H4K

Posted Friday, February 22, 2013 3:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

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

H4K

Posted Friday, February 22, 2013 3:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:11 AM
Points: 127, Visits: 507

[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

ahosack

Posted Tuesday, March 05, 2013 11:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 11, 2013 1:25 PM
Points: 9, Visits: 54

bump

Post #1426967

ahosack

Posted Tuesday, March 05, 2013 3:40 PM

Forum 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

Lynn Pettis

Posted Tuesday, March 05, 2013 8:26 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 4:33 PM
Points: 21,832, Visits: 27,860

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)

Post #1427138

ahosack

Posted Wednesday, March 06, 2013 2:36 PM

Forum 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

ahosack

Posted Thursday, March 07, 2013 1:35 PM

Forum 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

peterzeke

Posted Friday, March 08, 2013 6:03 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 12:21 PM
Points: 286, Visits: 1,235

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

David Burrows

Posted Friday, March 08, 2013 6:42 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:30 AM
Points: 6,380, Visits: 5,432

peterzeke (3/8/2013)

...A single dataset query could be written so that only one row per property is returned...

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

Anon.

Post #1428555

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions