SSRS 2005 - Need Matrix help...
I have more experience with 2008 than 2005 but I have to draw a matrix report in 2005. My matrix is for retirement account deposits and it should look like this: Administrator Name - aka Schwabb or John Hancock etc. Plan Name - aka McDonalds 401k or Fuddruckers Pension etc. xx/xx/xxxx Deposit Date xxxx Deposit Number Amount $$, <detail $$ columns and other info>, Participant Name xxxx Deposit Number Amount $$, <detail $$ columns and other info>, Participant Name Amount $$, <detail $$ columns and other info>, Participant Name xx/xx/xxxx Deposit Date xxxx Deposit Number Amount $$, <detail $$ columns and other info>, Participant Name Need help with 2005 matrix: 1) Is there anything akin to 2008's visual 'themes' for the matrix; aka ocean or forest colors? Or do I have to draw it out by hand? 2) How do I make an equivalent to 2008's "stepped" appearance? I could save alot of horizontal real estate if I could step down into the hierarchy as opposed to having columns for long names 3) I couldn't get more than 1 row of detail per deposit to appear until I made a row group that contained every detail level column - is that correct? Now I can see my per deposit detail rows but now I have a blank column in between deposit number and the detail stuff and I can't get rid of it even if I make it invisible 4) How do I get the matrix to sort columns? No matter what I do I cannot get sorting to work - it totally ignores me. I have multiple columns of $$ amounts and I would like to sort those columns individually at various levels of the hierarchy; aka at the deposit level and at the retirment plan level 5) I defined the paper size as 11inx8.5in but the report is rendering all on one page in html?? ..For 6000 rows??
June 15th, 2011 5:32pm

Hi James, I’m glad to help you on these issues. For the first question, the “themes” is also available in Reporting Services 2005. In order to use the theme, we need to add new report by using the Report Wizard. For the other questions, I need to confirm do you use Table or Matrix in the report. Generally, both Table and Matrix are templates of the Tablix control in Reporting Services 2008. However, they are different controls in Reporting Services 2005. So, it is also different to achieve the remaining requirement based on Matrix or Table. If possible, please also post your dataset structure with some sample data, and fill some sample data into the result you expected. And I will try to provide further assistance. Thanks, Tony Chain Tony Chain [MSFT CSG] | Microsoft Community Support Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
June 16th, 2011 4:45am

It is a MATRIX based report hosted under SQL 2005... Please see below for sample data and stored procedure text. As I said I'm more familiar with 2008 than 2005; yes I am aware of the fact that in 2005 they are 2 different controls and under 2008 they are variants of the tablix control. I have the report stood up and displaying data - all of my issues are related to formatting the report using the 2005 MATRIX control. I did not use the report wizard because I did not know syntax for passing report parameters to a stored procedure call in the 2005 query window; which prompts a 6th question: 6) How do I pass report parameters to a stored procedure call in the SQL 2005 query wizard window? Data is coming from a dataset created to encapsulate this stored procedure: if exists (select name from sysobjects where (xtype='p') and (name='usp_ReportLinkedDeposits')) drop procedure dbo.usp_ReportLinkedDeposits; go create procedure dbo.usp_ReportLinkedDeposits(@BeginDate datetime, @EndDate datetime, @TPAName varchar(max), @PlanSponsorName varchar(max), @PlanName varchar(max), @OnlyAcceptedDeposits bit, @IncludeZeroRemaining bit) as begin select tas.DepositDate, tas.ID as 'DepositID', p.TPAName, p.PlanName, case when (year(tas.DateAvailable) = 1753) then null else tas.DateAvailable end as 'DateAvailable', tas.DepositType, tas.Amount as 'DepositAmount', tas.AmountAssociated as 'TASAmountAssociated', vd.DistributionSetupAssociationAmount as 'DistributionAssociationAmount', case when (tas.Status <> 'Accepted') then 0 else tas.AmountAssociated - vd.DistributionSetupAssociationAmount end as 'Remaining', tas.Status, rhp.FirstName + ' ' + rhp.LastName as 'Participant Name' from dbo.udf_ReportTASCashReceiptsJournal(@BeginDate, @EndDate) tas left outer join dbo.udv_Plan p on (p.PlanID = tas.PlanID) left outer join dbo.udv_Distribution vd on (vd.DepositID = tas.ID) and (vd.PlanID = tas.PlanID) left outer join dbo.udv_Participant rhp on (rhp.ParticipantID = vd.ParticipantID) where (p.TPAName like '%' + @TPAName + '%') and (p.PlanSponsorName like '%' + @PlanSponsorName + '%') and (p.PlanName like '%' + @PlanName + '%') and ((tas.Status = 'Accepted') or (@OnlyAcceptedDeposits = 0)) and ((@IncludeZeroRemaining > 0) or ((tas.AmountAssociated - vd.DistributionSetupAssociationAmount) > 0)) order by p.TPAName, p.PlanName, tas.DepositDate desc end go grant execute on dbo.usp_ReportLinkedDeposits to public go Sample Test Data from this stored procedure showing 4 deposits for 2 different plans (Geodetics and Granada) from the Alpha & Omega administrator looks like this: 2011-04-27 29176 Alpha & Omega Financial Management Consultants, Inc Geodetics, Inc. Profit Sharing Plan 2011-05-02 Plan Assets 3293.76 3293.76 2072.00 1221.76 Accepted Stephan Andrew Palmer 2011-04-27 29176 Alpha & Omega Financial Management Consultants, Inc Geodetics, Inc. Profit Sharing Plan 2011-05-02 Plan Assets 3293.7 3293.76 1221.76 2072.00 Accepted Mark Hosford 2011-05-16 30071 Alpha & Omega Financial Management Consultants, Inc Granada Hills NULL Plan Assets 123748.19 123748.19 19670.00 104078.19 Accepted Stella Poulos 2011-05-16 30071 Alpha & Omega Financial Management Consultants, Inc Granada Hills NULL Plan Assets 123748.19 123748.19 6930.00 116818.19 Accepted Peter Preovolos
June 16th, 2011 12:43pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics