Post reply

MultiValue Parameter not working

  • February 20, 2013 at 2:52 pm

    #271225

    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.

  • February 22, 2013 at 3:19 am

    #1590260

    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

  • February 22, 2013 at 3:25 am

    #1590264

    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

  • March 5, 2013 at 11:51 am

    #1593593

    bump

  • March 5, 2013 at 3:40 pm

    #1593670

    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.

  • March 5, 2013 at 8:26 pm

    #1593738

    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.

  • March 6, 2013 at 2:36 pm

    #1594171

    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.

  • March 7, 2013 at 1:35 pm

    #1594683

    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.

  • March 8, 2013 at 6:03 am

    #1594935

    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

  • March 8, 2013 at 6:42 am

    #1594944

    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.

  • March 8, 2013 at 9:38 am

    #1595036

    I'm using SSRS 2005.

    SQL.

    There is no real reason for multiple datasets other than I was trying to split up "sections of columns" that belonged with each other.

    Most of the datasets are something like this...

    SELECT (SELECT XY FROM XX WHERE RMPROPID IN (@PROPERTIES)), (SELECT YY FROM XX WHERE RMPROPID IN (@PROPERTIES)),(SELECT YX FROM XX WHERE RMPROPID IN (@PROPERTIES))[/CODE]

    Could how this is structured be a problem?

  • March 8, 2013 at 10:10 am

    #1595057

    I was thinking more on line of

    SELECT XX.XY, XX.YY, XX.YX

    FROM Properties

    JOIN XX ON XX.RMPROPID = Properties.RMPROPID

    WHERE Properties.RMPROPID IN (@PROPERTIES)

    Which will give you one row per property and each column required in the report (i.e. XY = Units)

    This is only guesswork since you have not supplied any DDL or sample data as requested by others.

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

  • March 8, 2013 at 10:26 am

    #1595064

    David Burrows (3/8/2013)

    I was thinking more on line of

    SELECT XX.XY, XX.YY, XX.YX

    FROM Properties

    JOIN XX ON XX.RMPROPID = Properties.RMPROPID

    WHERE Properties.RMPROPID IN (@PROPERTIES)

    Which will give you one row per property and each column required in the report (i.e. XY = Units)

    This is only guesswork since you have not supplied any DDL or sample data as requested by others.

    +1 -- I agree -- this way the data are related by RMPROPID. Also, David Burrows's example query as an idea may not necessarily return one row per property, however, but at the very least, all data related to a specific RMPROPID will be linked together correctly and reliably, which means that aggregating values together by RMPROPID would be correct.

    Lastly, if multiple tables need to be referenced, but not all tables have data for a given RMPROPID, you'll want to use outer joins (.e.g, Left Join) from a core table (e.g., Properties) to your related tables (e.g. XX).

  • March 8, 2013 at 11:00 am

    #1595074

    I'm getting closer guys.

    Here is the SELECTEDPROPERTIES dataset, this is the dataset the table is based on.

    SELECT RMPROP.RMPROPID,propname, AVB_GMSCM.PDSPDID, AVB_PDSPD.VPID, AVB_VICEPRES.SVPID, AVB_SVICEPRES.EVPID, AVB_EVICEPRES.OWNID, COUNT(UNIT.UNITID) units

    from RMPROP INNER JOIN AVB_GMSCM ON RMPROP.GMSCMID = AVB_GMSCM.GMSCMID

    INNER JOIN AVB_PDSPD ON AVB_GMSCM.PDSPDID = AVB_PDSPD.PDSPDID

    INNER JOIN AVB_VICEPRES ON AVB_PDSPD.VPID = AVB_VICEPRES.VPID

    INNER JOIN AVB_SVICEPRES ON AVB_VICEPRES.SVPID = AVB_SVICEPRES.SVPID

    INNER JOIN AVB_EVICEPRES ON AVB_SVICEPRES.EVPID = AVB_EVICEPRES.EVPID

    INNER JOIN UNIT ON RMPROP.RMPROPID = UNIT.RMPROPID

    WHERE RMPROP.RMPROPID in (@PROPERTIES)

    GROUP BY RMPROP.RMPROPID,PROPNAME, AVB_GMSCM.PDSPDID, AVB_PDSPD.VPID, AVB_VICEPRES.SVPID, AVB_SVICEPRES.EVPID, AVB_EVICEPRES.OWNID

    I added the inner join unit, and count(unit.unitid) to this dataset, before it was its own dataset. And it's being populated correctly now! That is great. However, here is an example of the next dataset I need (the next column over)

    select COUNT(DISTINCT(NAME.NAMEID)) initialvisits froM rmaction inner join name on name.nameid=rmaction.nameid inner join PROSPECT on PROSPECT.nameid=rmaction.nameid where rmaction.actcode='WI' and name.RMPROPID IN (@PROPERTIES) and rmaction.actdate>=@BEGIN and rmaction.actdate <= @END

    I just don't see a viable way to include this query into the SELECTEDPROPERTIES dataset...

    If anyone can explain to me how to get this portion to work, then I'm sure I can get the remaining 20 columns inserted into this statement as well...

    Does this make sense?

  • March 8, 2013 at 11:22 am

    #1595076

    Without knowing the relationships of your tables (i.e. one-to-one vs. one-to-many vs. many-to-many), try the following code.

    SELECT RMPROP.RMPROPID

    ,PROPNAME

    ,AVB_GMSCM.PDSPDID

    ,AVB_PDSPD.VPID

    ,AVB_VICEPRES.SVPID

    ,AVB_SVICEPRES.EVPID

    ,AVB_EVICEPRES.OWNID

    ,UNITS = COUNT(UNIT.UNITID)

    ,INITIALVISIT = COUNT(DISTINCT NM.NAMEID)

    FROM RMPROP

    JOIN AVB_GMSCM ON RMPROP.GMSCMID = AVB_GMSCM.GMSCMID

    JOIN AVB_PDSPD ON AVB_GMSCM.PDSPDID = AVB_PDSPD.PDSPDID

    JOIN AVB_VICEPRES ON AVB_PDSPD.VPID = AVB_VICEPRES.VPID

    JOIN AVB_SVICEPRES ON AVB_VICEPRES.SVPID = AVB_SVICEPRES.SVPID

    JOIN AVB_EVICEPRES ON AVB_SVICEPRES.EVPID = AVB_EVICEPRES.EVPID

    JOIN UNIT ON RMPROP.RMPROPID = UNIT.RMPROPID

    JOIN NAME ON NAME.RMPROPID = RMPROP.RMPROPID

    JOIN RMACTION ON RMACTION.NAMEID = NAME.NAMEID

    JOIN PROSPECT ON PROSPECT.NAMEID = RMACTION.NAMEID

    WHERE RMPROP.RMPROPID IN ( @PROPERTIES )

    AND NAME.ACTCODE = 'WI'

    AND RMACTION.ACTDATE BETWEEN @BEGIN AND @END

    GROUP BY RMPROP.RMPROPID

    ,PROPNAME

    ,AVB_GMSCM.PDSPDID

    ,AVB_PDSPD.VPID

    ,AVB_VICEPRES.SVPID

    ,AVB_SVICEPRES.EVPID

    ,AVB_EVICEPRES.OWNID ;

    If this query blows out your count of "Units", then you may need to consider using either derived tables, a CTE, or temp tables, before piecing all of the data together in a final result set.

    --pete

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply