Questions and Answers for Pragmatic Work’s Presentation on the Tablix Control

ChalkBoardThursday, January 23rd I had the opportunity to present for Pragmatic Works as part of their “Training on the T’s” where they provide free one-hour training every week on Tuesdays and Thursdays. I gave my presentation entitled, Scalable SSRS Reports Achieved Through the Powerful Tablix. Below are the questions (and answers) that the attendees asked during my presentation.

View Presentation
Slide deck and demos downloads

Questions and Answers for Demo 1

Q: In the first presentation, the 1st tablix, do territory group and country region has the same datasets, meaning does country region have territory?

A: A tablix or a set of nested tablix can only use 1 dataset. My dataset joined several tables, two of which were Sales.SalesTerritory and PersonCountryRegion. (I’m using the Adventureworks2008R2 database.)
Here is how they are related:
SELECT
cr.Name AS CountryRegion
,st.Name AS Territory
FROM
sales.SalesTerritory AS st
JOIN Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode

Q: When you add a Row Group with that new column that doesn’t let you merge with the other cells to the right… what you can do is split that column, and then you’ll be able to merge.

A: Yes. This is true, but you still can’t merge across the dotted lines that are introduced into the Matrix.

Q: I always supposed that group levels had to be to the left of the dashed line. But you showed that you can delete those columns/rows but keep the grouping. Does this have any effect on the data in the columns on the left or the right of the dashed line?

A: No, it does not. If the GUI asks If you want to delete only the row/column or the row/column and the group, make sure to indicate that you only want to delete the row/column. This will keep the group, which is what we were after.

Q: Did you have to create the row groups (lower left corner), or were they available dimensions?

A: I’m not sure about this question, please email me some more information so that I can answer the question better.

 

Questions and Answers for Demo 2

Q: Could you have added the expression on the image itself and not add the extra row?

A: Not for this technique. If I would have added the expression to the image to show/hide it, then it would have left “white space” where the image would normally go when the image was hidden. By repeating the row with a different layout, we can reclaim the ‘white space” for the comment field to use.

Questions and Answers for Demo 3

Q: For creating the Emp Phone list, is there a reason why you used a table and created the group as opposed to using a matrix? Thanks!

A: This is a great question. You can use either controls and end up with the same result. I had been demonstrating the matrix control in the other demos, so I wanted my viewers to see how you could start with a table. This becomes handy if they requirements of the layout change. It’s important that you know that you don’t have to start all over, but you can change one control into the other because all three controls (table, matrix, and list) are all based on the Tablix template.

Q: For you last demo where you had dynamic columns for phone numbers, what did you have to change in the detail row?

A: In the detail row, I had to change the detail row to a group. I did this by going into the Detail group properties and adding a group on BusinessEntityID. I then added Last Name, First Name, and BusinessEntitty ID as the fields to sort by. This step is needed because the employee names are repeated once for each address type and each phone number type.

 

General Questions and Answers

Q: How did you make your color palette?

A: This is a great question. Here are the steps.

    1. Add a table to a blank report that will become your template report
    2. Create a cell for each color in your color pallet.
    3. Change the dimensions of each cell to have a height of 0.15 and a width of 0.15.
    4. Change the background property of each cell to a different color in your color pallet.

Now you can make the other changes to the report to create a template report and save it in the template folder for BIDS/SSDT.

Q: How to avoid the columns getting merged on exporting to an excel

A: This is one of those questions that is helpful to also explain to all your end users since they ask the same question. The answer is not the best answer, but it is better than what we had 10 years ago. If you save your report as a CSV file, Excel will still automatically open it and the columns will not be merged. The downside is you lose charts and any formatting. The upside is the columns are no longer merged and the end user can use more functionality of Excel. They do need to be reminded that they will have to save it as an Excel file after opening it in order to save any formatting features that they have added to the file.

Q: Is there a way to fix the tablix to a set number of rows, and an exact amount of vertical space for the tablix? It seems that the tablix ‘reserves’ some additional space below the tablix which interferes when placing report items below the tablix.

A: This can be a challenging problem and it requires a lot of testing if different results set sizes to get the layout to work out the way you want. I will be creating a blog post soon on how to mimic a “fixed row” layout. You can contact me to get the the blog post early if you want.

I haven’t noticed the tablix control “reserving” space. To best figure out what is causing this extra white space do the following. Show the lines on the outside of the tablix and the control that you want to “butt up” against the tablix control You may find that the extra space is on the inside of the control instead of the outside of the control. A couple of properties to look at as are padding and Borderwidth. By changing them, you will reduce white space.

I have noticed that it can be difficult to drag and drop another control right up against another control. In those cases I do some math. By adding the Top and Height properties of the tablix you get the location of the end of the control. Then add 1 or 2 more to that value to have the Top value for the next control.

Q: Is there a way to link one tablix to multiple Datasets?

A: Unfortunately no, but there are some workarounds. There are a couple of functions that were introduced in 2008 R2 that will allow you to reference a value in another dataset, but it won’t allow you to show multiple values. A second way is to use a subreport within the tablix. I try to minimize this, since it can add ALOT of overhead. The third way, would be to create a larger dataset with repeating data that can be grouped. This is what I did in my third demo in order to create multiple addresses and multiple phone numbers for each employee. Again, you have to weigh the benefit of the technique against how much data will be used in the report.

Q: This is a great presentation. How can I get a copy of the RDLs, and the datasets?

A: You can go to my resource page to download the presentation and the demos. Pragmatics has the recording so that you can watch the presentation again. I’ve also provided the links at the top of this post.

Q: Will a transcript or summary of this demo be available for review?

A: You can go to my resource page to download the presentation and the demos. Pragmatics has the recording so that you can watch the presentation again. I’ve also provided the links at the top of this post.

Q: Will you be sharing your ‘Knowledge Based Document’ with us?

A: Unfortunately I can’t, because I don’t have references in it for code I found on the internet or in books. I will however be spending time writing short posts with tips this year, so keep an eye on my blog and on mssqltips.com for posts.

 Q: Thanks – this was a great presentation.

A: Thank you. I’m glad everyone enjoyed it and that everyone had so many questions. 🙂

Comments are closed.

%d bloggers like this: