Master detail with some column values in summary at top and the rest in detail in a tablix

  • I haven't worked with SSRS in a while so I'm trying to remember how to make a report that has the column values that describe the physician practice as a whole with the detail of various metrics that they're measured on in the tablix below the practice data.

    I'm stuck on the items at the top. I don't know what to use. For example, I would like to have the specialty, practice name within a specialty, the range of ranks within the specialty, the practices rank, final rank, and a line of static text., each in grid like area at the top with column names to the left and column values to the right, with two groups of col. name/col. value across. So this small grid has 3 rows and 4 columns.

    The tablix that goes below the summary part is easy and I've already done it. It has a varying number of metric detail rows (none the same as the structure I describe in the last paragraph). The report should page break by practice. I will have a separate report for each specialty because the columns(metrics) measured vary for each specialty. So assume one specialty, say, Oncology for the whole report I'm describing.

    I have one query with quite a few joins that I use as a dataset. It has all the data needed. I am open to converting the query into a stored procedure with parameters if needed. My question in summary is this: How to get the structure I need for the top box?

    I know it's heinous to post a screenshot but in the case of showing what I need in a report it can clarify I think. Here it is (I've attached it). I've been struggling with this for hours and I'd really appreciate the help.

    THanks in advance.

  • pharmkittie (3/1/2015)


    I haven't worked with SSRS in a while so I'm trying to remember how to make a report that has the column values that describe the physician practice as a whole with the detail of various metrics that they're measured on in the tablix below the practice data.

    I'm stuck on the items at the top. I don't know what to use. For example, I would like to have the specialty, practice name within a specialty, the range of ranks within the specialty, the practices rank, final rank, and a line of static text., each in grid like area at the top with column names to the left and column values to the right, with two groups of col. name/col. value across. So this small grid has 3 rows and 4 columns.

    The tablix that goes below the summary part is easy and I've already done it. It has a varying number of metric detail rows (none the same as the structure I describe in the last paragraph). The report should page break by practice. I will have a separate report for each specialty because the columns(metrics) measured vary for each specialty. So assume one specialty, say, Oncology for the whole report I'm describing.

    I have one query with quite a few joins that I use as a dataset. It has all the data needed. I am open to converting the query into a stored procedure with parameters if needed. My question in summary is this: How to get the structure I need for the top box?

    I know it's heinous to post a screenshot but in the case of showing what I need in a report it can clarify I think. Here it is (I've attached it). I've been struggling with this for hours and I'd really appreciate the help.

    THanks in advance.

    I don't think it's heinous to put a screen shot in for a report design question. I would have had a really hard time visualizing the output if you hadn't done that.

    Make a table with 2 groups - first Specialty and then Practice.

    In the Practice group header row, put the label Practice, and then in the next column the Practice field. Then in the next column, the Nominated label and the Nominated field in the next column.

    Right click on that Practice group header row and choose Insert Row - Inside Group - Below. Then put the label Specialty in the first column, the Specialty field in the second....

    Repeat for the headings that you want. This should work. You will have to test it out.

  • Thank you Sql Sarah!

    I had to get this report done by yesterday so I ended up having two separate stored procedures: One for Master that just gave me the distinct practices and related data and another for the detail. I used a parameter to pass in the practice name to the detail stored procedure. I put the detail grid into a sub report control. I needed more a freeform effect for the master so I used all textbooks in a rectangle.

    Somehow it worked but next time I will use your advice. Trouble for me is that there are big gaps (6 months, a year) between working with SSRS and I seem to forget some key details.

    When I posted my mind was blank about how to achieve master detail and googling gave me different examples than what I was trying to accomplish.

    Again, thanks very much for you advice!

    P.S. I once was flamed by Joe Cielko for posting a screenshot so I still remember that.

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

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