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

««1 2

MultiValue Parameter not working

Rate Topic

Display Mode

Topic Options

Author

Message

ahosack

Posted Friday, March 08, 2013 9:38 AM

Forum Newbie

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

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

Could how this is structured be a problem?

Post #1428657

David Burrows

Posted Friday, March 08, 2013 10:10 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 6,351, Visits: 5,361

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.

Post #1428680

peterzeke

Posted Friday, March 08, 2013 10:26 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 285, Visits: 1,201

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

Post #1428690

ahosack

Posted Friday, March 08, 2013 11:00 AM

Forum Newbie

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

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&gt;=@BEGIN and rmaction.actdate &lt;= @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?

Post #1428704

peterzeke

Posted Friday, March 08, 2013 11:22 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:15 AM
Points: 285, Visits: 1,201

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

Post #1428709

ahosack

Posted Friday, March 08, 2013 11:27 AM

Forum Newbie

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

Hey,

Yeah the Units count blew up. Initialvisit was correct though ha.

This is the reason I had different datasets, because it is too complex of information spanning many different areas, to have only 1 select statement.

Post #1428711

David Burrows

Posted Friday, March 08, 2013 11:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:58 AM
Points: 6,351, Visits: 5,361

In which case you will have to produce several sub queries to produce totals as sugggested by pete.

Simplistically put

SELECT t.ID,u.Units,v.Initialvisit
FROM [table] t
LEFT JOIN (SELECT COUNT(*) AS [Units] FROM [unittable] GROUP BY ID) u ON u.ID = t.ID
LEFT JOIN (SELECT SUM(X) AS [Initialvisit] FROM [visittable] GROUP BY ID) v ON v.ID = t.ID

and repeat the joins for each subset of count/sums

Another way would be to create a temp table containing all possible output columns and do inserts into this table for each subset of data making columns not in each subset zero. Then sum the data accordingly.

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

Anon.

Post #1428718

ahosack

Posted Friday, March 08, 2013 11:55 AM

Forum Newbie

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

David Burrows, you are a life saver.

I'm doing the joining to the dataset, acting as a table.

I'm not a beginner to SQL, but this trick was definitely not in my toolbox.

Also, thanks Pete, both of you have helped me out tremendously getting over this hurdle, it's been one of those things where I've stared at it for so long I wasn't seeing all the options out there.

I'll post again if I run into anymore issues, thanks guys.

Post #1428723

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions