Create a dataset from an existing dataset

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

  • 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

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

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

  • 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

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

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

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

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

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

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

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