Can I Display Multiple Fields in a Table Cell?

Question

Hi,
I've been asked to modify an SSRS report to break down a summed field into two sub sums. Rather than re-writing the whole query for the report to provide this level of detail, I have added the sub sums as additional columns in my data set.

Example:

DataSet:

Total Fruit    Total Apples    Total Oranges    Column2    Column3
10             6               4                value2     value3

In terms of this example, would it be possible for my report to display Total Apples and Total Oranges in the same table cell that Total Fruit is already in like so:

Total Fruit	Column2	Column3
- 10   Apples: 6   Oranges: 4	value2	value3

The minus sign next to 10 represents a toggle button. They asked to be able to expand the total to show the sub totals. I will settle for static display though if possible.

Answer 1

Hey,

This is possible.  What is the key or group for that row of data?  Is it vendorID, storeID, or something of that nature?

If so, what you can do is create a sub-report.  A sub-report is a way of executing another report within a table cell of an existing report.  This sub-report will have to be developed separately.

Assuming you have a unique identifier for each row or group like vendorID you could create a sub-report like (ignore the colors, simply used later to make the example easier to understand):

VendorID	Fruit	Qty
1	Apple	6
1	Orange	4
	Total	10
2	Apple	2
2	Banana	4
2	Pear	6
	Total	12

You would then pass the vendorID to the sub-report as a parameter for the sub-report's query.  Therefore, if you were in the row with vendorID = 1 in the main report, then you could set it up to only show the items with a green background above (I just made it green in this example to make it easy for you to see).  You could do this by hiding the column names and vendorID column (all shown in red, once again to make it more clear).

The result would be something like this:

Vendor	Column X	Fruit		Column Y	Column Z
1	Data	Apple	6	Data	Data
		Orange	4
		Total	10
2	Data	Apple	2	Data	Data
		Banana	4
		Pear	6
		Total	12

Let me know is this helps.

---

Check out my blog!

Answer 2

Hi,
Thanks for your response. I didn't get a notification about it so I didn't know it was here. A sub report is a way to do it, but I didn't really want to have to run multiple queries when the fields they're asking for are really just additional columns (apples and oranges) in the data set. Since I already have all the data I need. Is it possible to display it the same way strictly in terms of layout?

Answer 3

Hello again, you could do this by pulling the data for each row (initially looking like this below):

Vendor	Column X	Fruit	Total	Column Y	Column Z
1	abc	Apple	6	bcd	cde
1	abc	Orange	4	bcd	cde
2	def	Apple	2	efg	fgh
2	def	Banana	4	efg	fgh
2	def	Pear	6	efg	fgh

You could then create a Group within the report which included the Vendor, Column X, Column Y, and Column Z (You can add more than one column to a group, for this example we'll call this "Table1_details_group").  Also, by doing this you could then could then still add subtotals to the report.  The last change to make would be to change the "HideDuplicates" property of the Details row to "Table1_details_group".  This would make the report display as such:

Vendor	Column X	Fruit	Total	Column Y	Column Z
1	abc	Apple	6	bcd	cde
		Orange	4
2	def	Apple	2	efg	fgh
		Banana	4
		Pear	6

Add the subtotals and you are good to go!

Vendor	Column X	Fruit	Total	Column Y	Column Z
1	abc	Apple	6	bcd	cde
		Orange	4
Fruit Total			10
2	def	Apple	2	efg	fgh
		Banana	4
		Pear	6
Fruit Total			12

Try this out and let me know if you get it to work!

---

Check out my blog!

Answer 4

let's say your SQL statement is SELECT vendorID, fruit, count FROM sales

in a table create group 1 - Vendor then detail row at the fruit type level.

HEADER     Vendor                                  Fruit                      Number
GROUP1     =Fields!vendorID.value          ="Total"                 =Sum(Fields!count.value)
DETAIL                                                  =Fields!fruit.value   =Fields!count.value

let's say the vendorID textbox is textbox4

highlight the detail row, then in the properties box set visible to 'True', and the toggleitem is textbox4.

It sounds like you've got the vendorID data in a detail row, and you need to move it into a group and set the fruit data as the detail row level.

Answer 5

Thanks to you both for your suggestions. The problem is, the point of my post was that I want to know if I can put two values in a single cell rather than changing my query. You have used a different data set than the one I posted so the solutions then only apply if I change the query. I'm asking about the reports layout capabilities, not data manipulation. Going with the idea that there is a vendor ID, here is another example of what the data returned from my query looks like:

VendorID	Total Fruit	Total Apples	Total Oranges	Column2	Column3
1	10	6	4	a	b
2	55	1	54	a	b
3	19	14	5	a	b

A few things about this data set. 1) It's not my real data. I'm not working with apples and oranges. It's just a simplified example. 2)Columns Total Apples and Total Oranges are the new columns I added. Total Fruit was an original column. Rewriting the whole query to return a row for each apple and orange would have taken too long. The conclusion I'm drawing from your responses is that the answer is no, I cannot place two values in a single cell? Thanks again for your efforts.

Answer 6

yes, you could place two values in a single cell, using the ampersand.  the cell expression would be similar to =Field!TotalApples.value & " " & Fields!TotalOranges.value  However, there's no way to align the two columns of figures except loosely through the spacing you place in between the values.

Answer 7

I was able to do this in SSRS by putting a rectangle container in the table cell, then text boxes for each value you needed inside that rectangle.

Answer 8

Perfect, exactly what I was looking for. Thank you!