Question #1: Why in the world would you want to do that?
Question #2: Did you count all 32000 characters?
Answer to your question: you can't.
|||The count is approximated to 32000
sql
I am working with a matrix report item and would like to display something like that :
DataGroup1
DataGroup2
StaticText11 StaticText12 Data1
StaticText21 StaticText22 Data2
StaticText31 StaticText32 Data3
But I cannot find a simple way to have 2 columns of static text on the left. The only way I've found is inserting a table report item in the matrix cells, but it cannot be exported in Excel.
The only samples I found do'nt have multiple colums in the static rowgroup.
Is it possible ?
Kind regards,
Xavier Miller.
Xavier,
This is a known issue since Ms release Reporting Services 2000, they promised to fix it on a service pack... but it seems that this has not been fixed even in 2005.
Apart from the table inside the matrix... there's another nasty trick, place a rectanlge and inside textboxes with the headers (you have to adjust the size pixel by pixel), BUT.... you will get horrible columns merged when you export into excel and you cannot use dynamic column sizes....
I'm looking as well for a better solution... but it seems that nobody cares about this.
Regards
Braulio
|||oh cool, that's what I expected...Thanks anyway for the answer ;)|||
Hi,
It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Geert Verhoeven wrote:
Hi,
It is also possible to add a second Row Group and set the group expression the same value as the first. This way you will receive an additional column. In this column, you can then set the expression to the required value.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy BelgiumMy Personal Blog
Hello,
The expression is static text. How do you proceed, please ?|||
Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?
|||Mmm... but I guess this is only valid in the data area (the right hand side of the matrix report), in the columns that identify the rows (left hand side), I guess you can not set that columns on top of the left area, isn't it ?
Yes, that's why I posted that message to confirm that it is impossible.
In fact, I can double the static rows in the RDL source. The designer shows well 2 columns, but the renderer refuses to show them.|||
Gert I'm trying to group rows, but I get them as an addition column not as a row above the left hand side of columns, could you tell us which steps must be performed ?
Thanks in advance, Regards
Braulio
|||Hi,
I'll try to give an example. I'm using the following query on the AdventureWorks db:
SELECT Production.Product.Name, Production.ProductSubcategory.Name AS ProductSubCategory
FROM Production.Product INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
If I get it right you want to have something like this:
ProductSubCategory
Product statictext
To do this, you need to follow these steps:
add a matrix to your report|||Yes, that works for dynamic data and grouping, but NOT for static data, multiple columns by row, which was my initial question...|||
Sorry, what I want to get is something like:
May 07 June 07
Project Name Location
Tiger Spain 20 40 (...)
I need the header on top ( the matrix reports that I'm using is more an horizontal report, the data columns are dynamic, but the left hand side columns are static and have the ID column and some attributes).Having the data columns dynamic does not allow me to use tabular reports :-(.
Any idea about how to do this?
Thanks a lot for your help.
Braulio
|||Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for
Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
HTH
Braulio
|||Sorry, I didn't understand well the query (my problem is putting columns on top)... you want columns in the left hand side... mmm... for horizontal reports there is an interesting post in a blog tell me if this is what you are looking for
Does Reporting Services support horizontal tables (fixed rows and dynamic columns)http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
HTH
Braulio
Hmm, and does this hack work well for Excel exporting ?|||
Mmmm...
I'm not sure I found this solution meanwhile I was looking for another, I just bookmarked it :-(.
I think it's worth to give a try, it doesn't use subtables or strange stuff, and the guy that wrote that stuff owns a lot of kudos, must be good stuff.
Good luck
Braulio
Hi All,
I wish to have a report with 2 subtotal using matrix control given by reporting service.
CD
DVD
Total Price
Total Qty
2.00
5.00
JOHN
5
25.00
5
JOLIN
5
10.00
5
Total Price
10.00
25.00
Total Qty
5
5
I only can came out total price(subtotal) which provided by matrix control.
Does the matrix control able to come out as table above with 2 subtotal?
Thanks
Kendy
The grid above has the unit price in the header and only the quantity measure in the details area. You need to create and additional Price measure and display both measures in the details area either on the rows or the columns as a static group. E.g.
Thanks for your helps and advice.
Its really give me an ideas
Thanks Adam
Below is part of a matrix report. Sooo close, but I have two problems I have not been able to solve. Please help as a I have several similar reports to do.
1. Row totals. I have been able to get a row total by adding a row group (i.e., $849.7 in the first row). However it does not display a group total (i.e., the sum of $849.7 and $371.3 for Economic Development) for that column.
I have not been able to add a static column outside of the dynamic groupings. I thought this might be a resolution by displaying the sum of the Estimated Costs in a static column and hoping it would show the group totals the same as with the dynamic column totals. Is there a way to do this?
2. Sorting. The report needs to display the groups based on the descending total values. I have specified the following sorts on the groups: sum(Fields!Estimated_Cost),"matrix1_Proj_Typ_Group" descending (this is the first column) and sum(Fields!Estimated_Cost),"matrix1_Proj_Typ" descending (this is the second column). Neither sort appears to be work.
1. Row totals: finally ran across a reference to "subtotals" and it solved this issue perfectly.
2. Can anyone help me with the sorting issue? Is this a bug or am I specifying this incorrectly?
|||Regarding sorting:
What exactly are you trying to sort? You have a matrix - so if you are trying to sort descending based on the totals for "Economic Development", it will impact the overall column group sort order (which is shared for all row groups!)
You cannot have every row group instance ("Economic Development", etc.) sort the column group differently. They share the same column groups. If just the cell data is re-sorted, the final matrix data would be correct for some row/column intersections but incorrect for other intersections.
-- Robert
I have the following report using RS2000
(1) In my page header I have a textbox(title of the report).
(2) In my report body, I have a matrix report that can extend horizontally.
My issue is that I don't know how to make sure that my page header title (1) stretches as far as the last matrix row (2).
Any help? Has anyone ever come across this issue and is there a solution to it?
Thanks,
YADGOR2000
Two options:
1. You can add an outmost status column (or a dynamic column with a constant group expression) in the matrix. Put the title in the column header and set the TextAlign to center.
2. Add a table with two header rows and one column, and drop the matrix inside the second row of the table. Put the title in the first table row, and set the TextAlign to center.
|||I am not sure I can understand your response. Can you give me step by step break down for either option 1 or 2.
Thank you
yadgor2000
I have the following report using RS2000
(1) In my page header I have a textbox(title of the report).
(2) In my report body, I have a matrix report that can extend horizontally.
My issue is that I don't know how to make sure that my page header title (1) stretches as far as the last matrix row (2).
Any help? Has anyone ever come across this issue and is there a solution to it?
Thanks,
YADGOR2000
Two options:
1. You can add an outmost status column (or a dynamic column with a constant group expression) in the matrix. Put the title in the column header and set the TextAlign to center.
2. Add a table with two header rows and one column, and drop the matrix inside the second row of the table. Put the title in the first table row, and set the TextAlign to center.
|||I am not sure I can understand your response. Can you give me step by step break down for either option 1 or 2.
Thank you
yadgor2000
Hello,
I have a fairly large report with multiple matrix tables. They grow to a fixed width horizontally and may grow to various heights vertically. I have the interactive height set to zero so that it displays on the web page on one screen. When I go to print this report, I am getting a blank page between each page with data. Here are my dimensions:
Report:
height: 15 in
width: 8.5 in
interactive height: 0 in
interactive width: 8.5 in
left margin: .5 in
right margin: .5 in
top margin: .5 in
bottom margin: .5 in
Body:
height: 13.3875 in
width: 6.9 in
Would this problem be due to the fact that my matrix tables span an area greater than a normal page height in design mode even before they grow dynamically? Any suggestions would be appreciated.
Thanks.
Problem solved - it turned out to be hidden fields on the report that were causing the issue. Thanks.sql
Have you found a solution to this? I am having the same problem.
I have subtotal at the bottom of the matrix. I am using Distinct Count in the expression field of matrix. But when I run the report, The Subtotal is giving the sum of all not the Distict counts. I can see the properties of Subtotal row. But I can not find the expression Property for that. Please help me anyone know it or had this issue.
I appreciate your help.
Thanks in advance
Rams
Hi ramsk
The subtotal expression in matrix report cannot be directley accesed.
You need to use custom code if you wish to manipulate the data in a different
way. This is because the expression used in the Matrix subtotal is the same as that
of the detail rows by default.
Custom code can be written using vb.NET or c# by going to report properties
and selecting the code tab. Write the function to accept integer values(or whichever data type you use)
and the call it from the detail cell within the matrix using the following expression:
=Code.<insert function name here without sharp bracets>(<field name here without sharp brackets>)
EX: =Code.GetFields(Fields!Net_Invoice.Value)
Now you can do a count on the values using vb/c#
Using this method even the most complex calculations can be done.
Gerhard Davids
|||
Thank you Gerhard Davids. I don't know vb.NET/C# coding. I will try to do that.
Thanks,
Ramsk
|||Actually,
You can access the subtotal for Matrix report. Click on the green triangle of your matrix totals and you can write your expression there.
Hammer
|||Hi Hammer,
I'm sorry to have to inform you that this is in fact
not possible. There has been much contravercy
about this and it has been causing many people
headaches including me.
Thus we have been using this very tedious and
redundant workaround.If you follow this link you
see in the newsgroup discussion that clearly
states: it's not possible at this time.
Also if you search around a bit you will get the
same result. Even with CTP2 this functionality
is not available.
Just thaught I'd let you know as not to get some
peoples hopes up.
Gerhard Davids
Hi,
I have a matrix report like this,
for some issues, I must add a group filter to filter out some departments that I don't want to show them on this report (ex. Field!dept.value !="A"). Additionally, My users can input the date interval to query by report parameters. When the date interval user inputed only have Dept A data , the report return some error message(I have set the norows property to show "nodata" if there is no data meet the condition). But when I remove the subtotal ,it works and return "nodata" message. I think there's some problem with the subtotal, but I have no idea where to figure it out.
Anyone would give me a help ?!
First, I recommend to install SP1 of SQL Server 2005.
Regarding the filter on the group - it will only apply to the group data but no to the subtotal because the subtotal is not in the scope of the grouping. So if you want to really remove the values for certain groups and a subtotal is present, you should filter directly in the query, at the dataset level, or at the matrix level.
-- Robert
Hello,
I have this problem that I do not now how to solve it:
I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).
If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.
Nr. Of Orders Nr. of Lines Average Delivery Time
Open Order 10 20 3.2
Closed Order 15 30 5
Total 25 50 8.2 (it should be, for example, 4.5)
Is there a way in which I can tell the system to not calculate the total for the third column?
If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.
Thanks to all!
Andrea
You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).
Please search this forum for threads related to the InScope function.
-- Robert
|||Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?
Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:
= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")
|||I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?
Basically all my columns are in the row group scope and I just need the subtotals to act differently.
Thanks again for your help!
Hello,
I have this problem that I do not now how to solve it:
I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).
If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.
Nr. Of Orders Nr. of Lines Average Delivery Time
Open Order 10 20 3.2
Closed Order 15 30 5
Total 25 50 8.2 (it should be, for example, 4.5)
Is there a way in which I can tell the system to not calculate the total for the third column?
If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.
Thanks to all!
Andrea
You would need to use the InScope RS function in the matrix cell's expression to determine if the cell is neither in the row group's nor in the column group's scope (i.e. the overall total cell).
Please search this forum for threads related to the InScope function.
-- Robert
|||Robert, as the subtotal is on the rows, and the subtotal cell appears in a column calculated in the dataset, then surely in this case he does want to be in scope of the column group?
Is there an expression that will help determine what the current column is? That could then be applied to the visibility. Something like for Visibility->Hidden:
= (Not InScope("row_group")) And (Fields!Measure_Name.Value = "Average Delivery Time")
|||I am playing a little bit with the inscope functions but it doesn't seem to work. How can I change the scope of the subtotal that is placed automatically by RS?
Basically all my columns are in the row group scope and I just need the subtotals to act differently.
Thanks again for your help!
