3 distinct columns

Question

3 distinct columns

erics44

SSCertifiable

Points: 6311
More actions
June 28, 2010 at 9:25 am

#220549

Hi
I am new to reporting services and i want to create a report using a table in the report that selects 3 distinct columns. How do i do that?
I always have to have some detail that is not grouped. So I want to show field1, field2 and field3 just the distict values of.
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

dunne do you mean you want to show the existing DISTINCT combinations of the three values, or do you mean that you want to force three distinct lists, but put them in a single recordset?

sort of the difference between this:

--returns all existing unique combinations of these three values

SELECT DISTINCT

field1,

field2,

field3

FROM SOMETABLE

and this:

--grabs three lists of data, forces them into a single table by joining on an arbitrary row_number()

SELECT F1.RW,F1.name,F2.name,F3.name

FROM

--strategically grabbing the biggest collection of data first.

(SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY name) AS RW,name FROM (SELECT DISTINCT name FROM sys.columns)X) F1

LEFT OUTER JOIN

(SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY name) AS RW,name FROM (SELECT DISTINCT name FROM sys.tables)X)F2

ON F1.RW = F2.RW

LEFT OUTER JOIN

(SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY name) AS RW,name FROM (SELECT DISTINCT object_name(object_id) as name FROM sys.sql_modules)X)F3

ON F1.RW = F3.RW

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

erics44 SSCertifiable Points: 6311 More actions · Answer 2

Thanks for the reply

I dont want to do a query to get the distinct values, i have the values in a dataset linked to my report already. I want to show the distinct values in the report

so a bit like your select distict query above but in a table in the report.

The problem I am having is it wont let me create a table without the detail or with nothing in the detail

thansk again

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 3

Not sure I understand your question, but:

Drag your fields into the detail. The Header should automatically populate with column titles. Delete the footer if you don't want it. This will give you a simple table of results.