Problem with Data Orientation

  • 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

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

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • 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

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

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • 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.

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

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]
  • 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

  • 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