Create a dataset from an existing dataset

Question

Create a dataset from an existing dataset

NineIron

SSChampion

Points: 12529
More actions
May 23, 2017 at 11:46 am

#342941

Is it possible to create a dataset, write a query, based on the main dataset for the report? or should I just create another stored procedure and use that?

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

That all depends on what your planning to do. If they're derived columns, then you could add these into the dataset or in the query. If it's filtering that's possible too. Aggragation, yes as well.

Your question is too vague to give a specific answer for your needs. If you could explain what you want/need to do, you'll get a better answer.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NineIron SSChampion Points: 12529 More actions · Answer 2

My bad..........Dataset1 is a list of patients with the top 15 diagnosis codes. So, several patients may have the same codes. Ideally, I would do something like, SELECT DIAG, count(*) FROM Dataset1 GROUP BY DIAG ORDER BY count(*) DESC.

pietlinden SSC Guru Points: 63428 More actions · Answer 3

Don't think you need a new dataset. What you're describing sounds like you need a Matrix on your report.
Group on [Diagnosis Code], then at the intersection, do COUNT().
You can set the ordering in the report.

Thom A SSC Guru Points: 99126 More actions · Answer 4

pietlinden - Tuesday, May 23, 2017 12:28 PM
Don't think you need a new dataset. What you're describing sounds like you need a Matrix on your report.
Group on [Diagnosis Code], then at the intersection, do COUNT().
You can set the ordering in the report.

I agree, If you're aggregating an existing dataset, then there's no need for a new dataset. A matrix or tablix, with groupings, will work fine.

The only time you need a new dataset is if you need data that isn't captured in another dataset. Filtering, aggregating, or deriving data from dataset can all be done on the same dataset; either by filtering/grouping on the tablix or matrix, or adding an expression to create a new column in the dataset or putting the expression in a cell in your tablix.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NineIron SSChampion Points: 12529 More actions · Answer 5

I inserted a matrix into the report with no problem. The rows are the diagnoses and the data is an expression, CountRows(). How can I sort by the expression? Also, can I put this into the page header as a summary for the report?

NineIron SSChampion Points: 12529 More actions · Answer 6

I've got everything I need except, how can I sort the matrix based off an expression?

pietlinden SSC Guru Points: 63428 More actions · Answer 7

Okay, just tried it with my own dataset. Turns out, I can't find a way to sort on an aggregated column in a matrix.

So if you base your report on the summary query (where you do the counts in T-SQL), then you can drop that into a tablix and sort it. would that work?

This is absolutely possible in DAX/PowerBI Desktop... but dont know how to use that with normal SSRS....

NineIron SSChampion Points: 12529 More actions · Answer 8

I'm going to create a separate procedure and use that as the dataset. Thanx for the help.

David Burrows SSC Guru Points: 65138 More actions · Answer 9

NineIron - Wednesday, May 24, 2017 6:55 AM
I inserted a matrix into the report with no problem. The rows are the diagnoses and the data is an expression, CountRows(). How can I sort by the expression? Also, can I put this into the page header as a summary for the report?

Set the Sort expression on the row group to
=CountRows()

Far away is close at hand in the images of elsewhere.
Anon.