SSRS Table Display

  • TheRedneckDBA

    SSChampion

    Points: 14001

    Wanting to do some complex (at least to me) manipulation of data that looks like this:

    Data

    And massage it into an SSRS table that looks like this:

    Table

    I've made the following attempt:

    Attempt

    And it is doing something close to what I want, but not quite:

    Results

    In cases where there are more than one fees per policy, it's repeating some data I don't want repeated.  I'm sure it's a simple group setting or something, but I'm not spotting it.  Can someone point me in the right direction as to how to solve this need?  Or is it something SSRS just won't do?

    Thanks!

    Jason

     

     

     

    Attachments:
    You must be logged in to view attached files.

    The Redneck DBA

  • Site Owners

    SSC Guru

    Points: 80378

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • pietlinden

    SSC Guru

    Points: 62674

    Would be easier to help with some sample data... It can be completely fake, just representative.

  • TheRedneckDBA

    SSChampion

    Points: 14001

    The first picture in the post represents some sample data.  Or do you mean send some SQL to create a table and insert sample data?

    The Redneck DBA

  • Sue_H

    SSC Guru

    Points: 90543

    TheRedneckDBA wrote:

    The first picture in the post represents some sample data.  Or do you mean send some SQL to create a table and insert sample data?

    The latter so that people can run the scripts and test out the scenario for you. Otherwise, they would need to type out create table statements and inserts from the pictures, which most people won't have time to do.

    Sue

  • TheRedneckDBA

    SSChampion

    Points: 14001

    Makes sense.  Here you go:

     

    CREATE TABLE DataTable 
    (
    DataTableID INT IDENTITY(1,1),
    QuoteNumber BIGINT,
    ProducerName VARCHAR(50),
    InsuredName VARCHAR(50),
    CompanyName VARCHAR(50),
    GAName VARCHAR(50),
    PolicyNumber VARCHAR(25),
    EffectiveDate DATETIME,
    Coverage VARCHAR(30),
    Premium MONEY,
    Tax MONEY,
    FeeName VARCHAR(25),
    Fee MONEY,
    PolicyTotal MONEY,
    BrokerFee MONEY,
    QuoteTotal MONEY
    )
    GO

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName1', 'CompanyName1', '', '11111111', '2020-01-01', 'Coverage1', 1000, 0, 'Policy Fee', 0, 1000, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName2', 'CompanyName2', 'GAName1', '2222222', '2020-01-03', 'Coverage2', 1500, 10, 'One Fee', 100, 1610, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName3', 'CompanyName3', 'GAName2', '33333333', '2020-01-07', 'Coverage3', 2500, 100, 'Two Fee - 1', 150, 2850, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName4', 'CompanyName4', 'GAName3', '33333333', '2020-01-06', 'Coverage4', 2500, 100, 'Two Fee - 2', 100, 2850, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName5', 'CompanyName5', '', '444444444', '2020-01-02', 'Coverage5', 250, 0, 'Policy Fee', 111.14, 361.14, 0.00, 8671.14)
    GO

    The Redneck DBA

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88260

    It looks like you do not have a group defined - therefore you are getting each row in the data set as a distinct row in the tablix.  You need to add a group that encompasses the columns out to the level of detail you want.

    Your sample data will not work for this either, since you have different key values for InsuredName, CompanyName and GAName but in your picture those are the same values.

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • TheRedneckDBA

    SSChampion

    Points: 14001

    Oops.  Here's updated data.

    I've tried grouping on everything but FeeName and Fee.  And that makes it show up just one record for the Policy, but then doesn't include the 2nd "FeeName/Fee Combination" I'm hoping it to display.  Just diplays the first one.

    CREATE TABLE DataTable 
    (
    DataTableID INT IDENTITY(1,1),
    QuoteNumber BIGINT,
    ProducerName VARCHAR(50),
    InsuredName VARCHAR(50),
    CompanyName VARCHAR(50),
    GAName VARCHAR(50),
    PolicyNumber VARCHAR(25),
    EffectiveDate DATETIME,
    Coverage VARCHAR(30),
    Premium MONEY,
    Tax MONEY,
    FeeName VARCHAR(25),
    Fee MONEY,
    PolicyTotal MONEY,
    BrokerFee MONEY,
    QuoteTotal MONEY
    )
    GO

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName', 'CompanyName1', '', '11111111', '2020-01-01', 'Coverage1', 1000, 0, 'Policy Fee', 0, 1000, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName', 'CompanyName2', 'GAName1', '2222222', '2020-01-03', 'Coverage2', 1500, 10, 'One Fee', 100, 1610, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName', 'CompanyName3', 'GAName2', '33333333', '2020-01-07', 'Coverage3', 2500, 100, 'Two Fee - 1', 150, 2850, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName', 'CompanyName3', 'GAName3', '33333333', '2020-01-07', 'Coverage4', 2500, 100, 'Two Fee - 2', 100, 2850, 0.00, 8671.14)

    INSERT INTO DataTable (QuoteNumber, ProducerName, InsuredName, CompanyName, GAName, PolicyNumber, EffectiveDate, Coverage, Premium, Tax, FeeName, Fee, PolicyTotal, BrokerFee, QuoteTotal)
    VALUES (1234567, 'ProducerName', 'InsuredName5', 'CompanyName4', '', '444444444', '2020-01-02', 'Coverage5', 250, 0, 'Policy Fee', 111.14, 361.14, 0.00, 8671.14)
    GO

    The Redneck DBA

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88260

    The grouping needs to be defined in the report - not in the SQL.  By setting up a group in the report - you can display the detail rows for each 'group' of header rows.

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • TheRedneckDBA

    SSChampion

    Points: 14001

    Agreed.   I'm apparently just doing it wrong.

     

    Attempt-GroupGroup

    The Redneck DBA

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply