CODEWITHSUNDEEP
reporting-services
JD136
JD136

Reputation: 175

SSRS: Get Distinct subset of Dataset

I have two SSRS reports which reference a shared dataset that returns data in a format similar to this:

create table #example (ID1 bigint, ID2 char, ID3 char(2))

insert into #example (ID1, ID2, ID3)
values ('1592648378', 'D', 'Q') 
    ,('1234567891', 'A', 'Z')
    ,('1234567891', 'A', 'Y')
    ,('1234567891', 'A', 'X')
    ,('1234567891', 'B', 'Z')
    ,('1234567891', 'B', 'Y')
    ,('9876543219', 'A', 'W')
    ,('9876543219', 'C', 'X')
    ,('9876543219', 'A', 'Q')

select *
from #example as exm
order by exm.ID1, exm.ID2, exm.ID3

drop table #example

What I need is one report to display the full level of detail (ID1 + ID2 + ID3). I need the other report to not only display, but export in a CSV format, a select level of detail. Specifically, I need the level of detail to include only ID1 + ID2. That's easy to do in the query by selecting distinct (or grouping):

select distinct exm.ID1, exm.ID2
from #example as exm
order by exm.ID1, exm.ID2

I could just have each report reference their own version of the data set, and maintain each query separately. However, I would like to avoid that if possible. What I'd rather do is something that allows me to group the fields together in a way that sticks with a CSV export. I initially tried to group by a particular field (in this case ID1 + ID2) in a Tablix object but evidently when you export a report in a CSV format it will return all the underlying data (plus I haven't successfully formatted a Tablix how I wanted). I've also thought about the possibility of querying the shared dataset from within the report (in another dataset) but I can't find a way to do that. I've also thought there might be a way via a script that modifies the dataset. Does anyone have any ideas about how I could tackle this?

This shows what I tried with a Tablix and grouping (picture on right is what I want):

TablixReportBuilder

This is what I get:

WhatIGet

And what happens when I export that to CSV:

CSVExport

Upvotes: 1

Views: 7487

Answers (2)

papermoon88
papermoon88

Reputation: 476

You can try this method to form your second report (ID1 + ID2). I will start from the beginning because there aren't too many steps.

  1. Add a new table to your report and reference your shared dataset
  2. By default, the table will have 3 columns. Delete one of the columns
  3. Add ID1 and ID2 to the table

enter image description here

  1. While this table is selected, go to Row Groups at the bottom of your screen and right click the Details row, and select Group Properties

enter image description here

  1. On the General Tab, add 2 grouping by ID1 and ID2 and click OK enter image description here

  2. Result:

enter image description here

edit:formatting

Upvotes: 1

StevenWhite
StevenWhite

Reputation: 6024

You can reuse the same dataset for both tables. The level of detail shown is based on the row groups that you have defined. For that second table, delete the "Details" row group. Make sure that in the row group properties you are grouping on the first two columns and you don't have any that aren't grouped by anything.

This will prevent the second table from having the extra rows both in the report view and thus also in the exported output.

Upvotes: 1

Related Questions