question

nbussel avatar image
nbussel asked

SSRS 2005 How to suppress empty cells (not lines) in a multi columns table

I have a problem I cannot solve. I have a dataset which looks like this: Line1 CAT100 ITEMA Line2 CAT200 ITEMB Line3 CAT300 ITEMC In my table the results look like this: ITEMA _____________________________________ __________________ITEMB____________________ ______________________________________ITEMC I would like to get it to look like this: ITEMA_____________ITEMB_______________ITEMC So I want several empty cells to suppress. The IIF function on the visibility property only works on lines and not on cells :-( Here is the result of my dataset: Ordernr Itemcode ITEMA ITEMB ITEMC 845 5150-283 5150-283 845 5150-4 5150-4 845 3402-88 3402-88 845 3403-88 3403-88 845 3403-195 3403-195 845 5225-70 5225-70 846 3402-195 3402-195 846 5150-283 5150-283 846 3091-291 3091-291 846 3409-195 3409-195 846 5225-70 5225-70 846 3402-240 3402-240 846 4651-29 4651-29 846 5225-237 5225-237 846 3091-231 3091-231 846 3409-343 3409-343 846 4651-33 4651-33 846 5150-288 5150-288 846 5150-289 5150-289 846 5150-4 5150-4
tablessrs-2005
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

grrl_geek avatar image
grrl_geek answered
My guess is that you will need to manipulate your dataset results. You can't set the hidden property of just one cell in a row. That cell may be hidden for that row, but what about the next row? How many columns do you have in your table? What are the columns? Can you provide your dataset query?
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@nbussel How do you want your data to look?
2 Likes 2 ·
@grrl_geek Thanks for your quick reply. Unfortunately I will not be able to fix this in the dataset. The dataset contains sales orderlines. Every sales orderline contains an item, a price and a quantity. I have to report only the items (no quantities and no prices), but categorised! So I have to report items from alle sales orders in columns (categories). You cannot fix this in a dataset because you would have to aggregate the itemcodes per sales order on what?
0 Likes 0 ·
@nbussel If you can elaborate a bit on the shape of your data (supply some sample data, sample results as they are now and desired results, you can add this info to your question by editing it) then it would be possible to come up with the query cross-tabbing your original data so it fits into desired format. This means that you will probably have to use a diferent dataset (the one which returns data in already correct format), but the bottom line is that @grrl_geek is correct, you cannot achieve what you want with formatting.
0 Likes 0 ·
I second Oleg's question - how should the output look? I know you can add IIF statements to individual cells, I have done it. For example, rather than a blank cell, or NULL, you can force it to return 0.00 or some text value. You have values in the row, so if you have a blank cell in the row, what should be done with the empty cells? Surely you would not want to suppress the entire row...
0 Likes 0 ·
I'd ask you, or the user asking you for this, "What question are you trying to answer?" You're correct - without some common item to group on, there's no aggregation. Without some sort of aggregation, you won't be able to, essentially, "compact" all these rows. If each order (row) can have only one item, and each item is only one category, then you will have to show one row for each item.
0 Likes 0 ·
Show more comments
nbussel avatar image
nbussel answered
@grrl_geek Great Idea!!! I did not think about that, but it is a great idea! Thanks!
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.