March 19, 2012 at 11:07 am
The following query returns twelve columns of data:
select distinct
PlanParticipantID
, ICD9AlternateCode
, PreferredCodeName
, icd9.icd9id
, DateOfService
, ServicingProviderName
, ServicingProviderStateCode
, ServicingProviderNPI
, BillingProviderName
, BillingProviderStateCode
, BillingProviderNPI
, ProviderSpecialty
from DIXIE.InjuredClaimantDescriptorMatch match
inner join HC.PlanParticipantICD9 participantICD9
on match.PlanParticipantICD9ID = participantICD9.PlanParticipantICD9ID
inner join [MASTER].ICD9 ICD9
on ICD9.ICD9ID = participantICD9.ICD9ID
--*********************************************
The requirements for the report that I need to create require that the orentation of the data be pivoted so that all of the horizontal columns become data in a single colum with their associated data in another column like this:
Column1 Column 2
ICD9 Alternate Code (data)
Preferred Code Name (data)
ICD9ID (data)
Date Of Service (data)
Servicing Provider Name (data)
Servicing Provider State Code (data)
Servicing Provider NPI (data)
Billing Provider Name (data)
Billing Provider State Code (data)
Billing Provider NPI (data)
Provider Specialty (data)
I don't know if the TSQL PIVOT option would work for this problem, but I would rather not go that route if at all possible. I could also use CASE and UNION, but would be a very long set of code.
Can this be done with the Tablix control in SSRS? Is there any other technique in SSRS that can be used for this situation?
Thank you for your help.
CSDunn
March 19, 2012 at 6:24 pm
Can this be done with the Tablix control in SSRS? Is there any other technique in SSRS that can be used for this situation?
Yes it can. Insert a Matrix control. Drag a field into the Column cell, looks like PlanParticipantID is a likely candidate. In the data cell, from the in-cell menu, select the first field you want to display, ICD9 Alternate Code. The value will probably be aggregated when you add it. Highlight the value, right click to uncheck the aggregation.
Right click again in the data cell, select Insert Row - Outside Group - Below. Update the cell value. Right click into the new cell, select Insert Row - Below, add field. Repeat as needed.
You'll want to remove the value from the Column Group header, just highlight and delete.
March 20, 2012 at 7:37 am
Burninator (3/19/2012)
Can this be done with the Tablix control in SSRS? Is there any other technique in SSRS that can be used for this situation?
Yes it can. Insert a Matrix control. Drag a field into the Column cell, looks like PlanParticipantID is a likely candidate. In the data cell, from the in-cell menu, select the first field you want to display, ICD9 Alternate Code. The value will probably be aggregated when you add it. Highlight the value, right click to uncheck the aggregation.
Right click again in the data cell, select Insert Row - Outside Group - Below. Update the cell value. Right click into the new cell, select Insert Row - Below, add field. Repeat as needed.
You'll want to remove the value from the Column Group header, just highlight and delete.
Thanks for the feedback. Where PlanParticipantID = 4, I have three associated records. I think I have followed these instructions correctly, but in the preview, there is only one record.
The other idea I've seen is to create a subform with each of the eight data elements (labels and text boxes), then add the subform to a single cell table. Then the subform would receive the PlanParticipantID as a parameter. I haven't implemented this yet, but the hope is that I'll have multiple rows (3 for the PlanParticipantID 4) of subform results.
Here is a visual of what I'm trying to do. Let's say PlanParticipantID = 4 has three asssociated records. The records in the subform would look like this:
ICD Description Code 637.61
Date Of Service 12/23/2011
Servicing Provider Name South Side Hospital
Servicing Provider State CT
NPI # Provider 4487697657
Billing Provider Acme Insurance
Billing Provider State NE
NPI # Billing 9987656786
Provider Specialty Ears Nose & Throat
ICD Description Code E887
Date Of Service 05/23/2011
Servicing Provider Name Medical Associates
Servicing Provider State NM
NPI # Provider 7764532154
Billing Provider West Insurance
Billing Provider State UT
NPI # Billing 7865498043
Provider Specialty Bone and Joint
ICD Description Code V07.8
Date Of Service 08/06/2012
Servicing Provider Name Group Care Inc
Servicing Provider State VA
NPI # Provider 7654786543
Billing Provider Hometown Insurance
Billing Provider State DE
NPI # Billing 6548231098
Provider Specialty Arthritis
There would be three 'vertical record sets' per row. One idea I've seen is to set up the labels/data fields as I have done here in a subform, then drop the subform into a one cell table. I haven't tried it yet, but I hope this better explains what I am after. Can I do this in a tablix control without the subform in a table cell arrangement? I'm open to suggestion.
Thanks again.
CSDunn
March 20, 2012 at 1:43 pm
Ahhh. Misunderstood your requirement. You should be able to accomplish your goal with a Table control. Try this:
-- insert table, select dataset
-- in Row Group menu, select add Parent Group, group on your Plan ID
-- delete the 3rd (farthest right) column
-- Click into the data cell in the 2nd column, select a field
-- the field label will go into the row header, cut and paste it into the first column, in the data cell next to / left of where you added the field
-- right click into either data cell and select add row, inside group, below
-- add the fields again, repeat as needed
-- if you want a blank row between the details, insert another row and don't add fields
-- delete the header row
-- delete the group column if you want, just not the group (right click, delete, delete columns only)
March 20, 2012 at 2:16 pm
Burninator (3/20/2012)
Ahhh. Misunderstood your requirement. You should be able to accomplish your goal with a Table control. Try this:-- insert table, select dataset
-- in Row Group menu, select add Parent Group, group on your Plan ID
-- delete the 3rd (farthest right) column
-- Click into the data cell in the 2nd column, select a field
-- the field label will go into the row header, cut and paste it into the first column, in the data cell next to / left of where you added the field
-- right click into either data cell and select add row, inside group, below
-- add the fields again, repeat as needed
-- if you want a blank row between the details, insert another row and don't add fields
-- delete the header row
-- delete the group column if you want, just not the group (right click, delete, delete columns only)
Thanks again for the feedback, I will look into this. Before I saw this reponse, I tried something different:
1. I created a RowGroup in a Tablix called ICD9AlternateCode, and grouped on that field.
2. In that row, first column, I created an expression named 'Labels'. The following is the value for the expression:
="ICD Description Code" & vbcrlf &
"Date Of Service" & vbcrlf &
"Servicing Provider Name" & vbcrlf &
"Servicing Provider State" & vbcrlf &
"NPI# for Servicing Provider" & vbcrlf &
"Billing Provider Name" & vbcrlf &
"Billing Provider State" & vbcrlf &
"NPI# for Billing Provider" & vbcrlf &
"Provider Specialty"
3. In the next column, same row, I created an expression named 'FormData'. The following is the value for the expression:
=Fields!PreferredCodeName.Value & vbcrlf &
Fields!DateOfService.Value & vbcrlf &
Fields!ServicingProviderName.Value & vbcrlf &
Fields!ServicingProviderStateCode.Value & vbcrlf &
Fields!ServicingProviderNPI.Value & vbcrlf &
Fields!BillingProviderName.Value & vbcrlf &
Fields!BillingProviderStateCode.Value & vbcrlf &
Fields!BillingProviderNPI.Value & vbcrlf &
Fields!ProviderSpecialty.Value
So now if the participant has three records associated with three different ICD9AlternateCode values, I get three rows with each row containing all nine data elements. The best I was able to do with a subform was to get three rows, but only data related to the first ICD9AlternateCode were returned.
I'll try your next suggestion. One problem with this idea is that if the text ever wraps on any line, labels and data will become missaligned.
Thank you again for your help.
March 20, 2012 at 4:34 pm
You can add as many static rows in the Tablix as you would static columns in a "regular" table set up. Pretty handy...
As a suggestion, just play around with that Tablix control as much as you can. Try all kinds of combinations of row groups and column groups and adjacent groups and extra rows and extra columns.... you see where i'm going. It's a lot of fun and you can do some pretty cool stuff with it.
If you haven't yet, look at Stairway to Reporting Services on this site to get a good idea of what the tool can do for you before you have to resort to complicated workarounds. I fought with it a lot before I realized it was much easier to submit. 😛
http://www.sqlservercentral.com/stairway/72382/
Hope your first day of Spring is as lovely as it is here! Pity I'm in this carpeted box...
March 22, 2012 at 12:34 pm
Burninator (3/20/2012)
You can add as many static rows in the Tablix as you would static columns in a "regular" table set up. Pretty handy...As a suggestion, just play around with that Tablix control as much as you can. Try all kinds of combinations of row groups and column groups and adjacent groups and extra rows and extra columns.... you see where i'm going. It's a lot of fun and you can do some pretty cool stuff with it.
If you haven't yet, look at Stairway to Reporting Services on this site to get a good idea of what the tool can do for you before you have to resort to complicated workarounds. I fought with it a lot before I realized it was much easier to submit. 😛
http://www.sqlservercentral.com/stairway/72382/
Hope your first day of Spring is as lovely as it is here! Pity I'm in this carpeted box...
Thanks for the URL, and the second suggestion. I also found a way to do this with a table nested in a list control, and it works.
cdun2
March 26, 2012 at 2:31 pm
I would recommend the List Control and then each of those vertical fields are just text boxes and you don't have to concatenate them all together.
MWise
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply