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