Can SSRS dynamically create columns for an unknown number of records?
-
Hello,
The issue I am experiencing is that I am showing a summary of drive (or event) and the information relevant to the drive. Multiple incentives (or none at all) can be assigned to a drive. When two incentives are assigned to a drive, it causes the drive to be displayed in two rows in the report. See screenshot below for an example:
Liberty C. HS has three rows due to three different types of incentives assigned to the drive. Can SSRS dynamically add an existing column(s) so each drive is only displayed once?
I'm hoping to find a solution that would resemble this:
I haven't had much luck either due to bad phrasing or not be able to apply what I've found to my situation so I was hoping someone might be able to guide me to a solution.
Thanks!
-
My first thought is that you need some sort of matrix, where the first three columns are fixed and everything after Owner Name (thus the Incentive columns) are just one matrix column.
Depends a bit on how the data is brought into the report.
What kind of query do you have?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Hey Koen,
The actual report has more columns, but the issue would remain the same and I hid those for posting purposes. Would you be able (or willing) to guide me through this process? I am relatively new to SSRS and I have never used a matrix.
As for my query, I'll post it here. It's pretty straight forward, taking a couple of variables and pulling back the drive (event) info that fall within the date range.
Create Procedure Hemasphere_IncentiveReport
@startDate datetime,
@endDate datetime,
@RegID varchar(8000),
@OrgID varchar(8000),
@SubID varchar(8000),
@COID varchar(8000),
@EquipmentID varchar(8000)
As
Begin
Select
DM.DriveID [DriveID],
DM.FromDateTime [FromDateTime],
Case When DM.OwnerType = 0 Then Acct.Name Else CD.DescLong End As [OwnerName],
CO.CodeID [CO_ID],
CO.Description [CO_Desc],
Org.CodeID [Org_ID],
Org.Description [Org_Desc],
Sub.CodeID [Sub_ID],
Sub.Description [Sub_Desc],
Reg.CodeID [Reg_ID],
Reg.Description [Reg_Desc],
Inc.Description [Incentive]
From
Hemasphere_Dev.[dbo].rpt_DriveMaster DM
Left Outer Join Hemasphere_Dev.[dbo].rpt_Accounts Acct on DM.AccountID=Acct.AccountID
Inner Join Hemasphere_Dev.[dbo].rpt_CenterDetail CD on DM.CenterID=CD.CenterID
Inner Join Hemasphere_Dev.[dbo].IDViewCollectionOp CO on CD.CenterID=CO.CodeID
Inner Join Hemasphere_Dev.[dbo].IDViewRegion Reg on CD.Region=Reg.CodeID
Inner Join Hemasphere_Dev.[dbo].IDViewOrgCenter Org on CD.OrgCenter=Org.CodeID
Inner Join Hemasphere_Dev.[dbo].IDViewOrgSubCenter Sub on CD.OrgSubCenter=Sub.CodeID
Left Outer Join OBAPPS.[dbo].OBI_Incentives Inc on DM.DriveID=Inc.DriveID
Where
DM.StatusID <>5
And DM.FromDateTime Between @startDate AND @endDate
And CO.CodeID In (Select Number From dbo.fn_SplitInt(@COID,','))
And Inc.EquipmentID In (Select Number from dbo.fn_SplitInt(@EquipmentID,','))
Order By [FromDateTime], [OwnerName], [CO_Desc], [Rec_Desc]
-
I would create a row group by Drive ID, and a column group by Incentives.
Add the Row Group:
On the left side of the table, right click and choose Add Group, Parent Group.
Choose the Drive ID field and check Add Group Header (you can check group footer if you need it).
Add the Column Group:
You have to click on a cell in the table to get right prompt to come up to insert the column group.
right click on a header cell, choose Add Group, choose the type of group (you will have to test them out to see which one gives the desired results). Choose the Incentives field to group on.
-
Thanks Sarah, I was able to accomplish this by doing what you suggeted.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply