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
I have a have a relatively simple SSAS cube that I'm trying to display in a matrix but the order of the data is not correct. The rows of the matrix are a time hierarchy and the query designer produces the correct MDX and when I run the MDX in the data tab, the order is correct. However in the report preview tab, the order is incorrect. Specifically, the time hierarchy looks like this Year-Month-FullDate. The report is ordering the Months like this 1,10,11,12,2,3,4,5,6,7,8,9. I ran the MDX query in Sql Studio and the order is correct, and I used Excel to consume the same cube and it produces the correct order.
The report is somehow treating the Month like character data instead of numeric, how do I fix this?
It sounds like your MDX query only retrieves the Member_Caption (which is the string label representation), but not the Member_Value property (which is the value representation with the original data type).
For example:
SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Due Date].[Month Number Of Year].[Month Number Of Year].ALLMEMBERS * [Due Date].[English Month Name].[English Month Name].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, MEMBER_VALUE ON ROWS FROM [Adventure Works DW] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Note that the .Value field property of RDL expressions always maps to the Member_Caption property value for dimensions (the member caption is always a string!). The additional Member_Value intrinsic dimension property is not mapped directly to any of the predefined RDL extended field property names. Therefore, in order to access the MEMBER_VALUE property in the report, you have to use the so-called field property collection syntax in the matrix group expression and sort expression to get the numeric value for the dimension property and specify the property name exactly as defined in the MDX query above (the property name is case-sensitive in this case):
=Fields!Month_Number_Of_Year("MEMBER_VALUE")
Additional information about MDX intrinsic member properties can be found here: http://msdn2.microsoft.com/en-us/library/ms145528.aspx
-- Robert
|||Robert - that worked perfectly. Thanks a bunch!
To anyone else in the same situation, to get to the matrix group expression and sort expression right-click the matrix object in the report designer and select Properties. Goto the Groups tab and then select dimension member in question and click the Edit button. This brings up another window, goto the Sort tab and enter the expression.
Areas
The Areas are Matric Columns and are dynamically generated .
i dont want sub total of each row , but i want Grand total at the end of the all columns generated by matrix
I have no clue how do to it
Kind Regards
Just Right-click the column header and choose subtotal.
|||No this is what i dont wantthis will add total below each row which i dont want i want the grand totals after the last row|||
OK, we seem to be talking cross purposes and your explanation is not clear enough.
If you want an additional column at the end the has the total value for all areas then right-click the column group and choose subtotal.
If you want an additional row at the very bottom that has the total for all the code/description row category, then right-click on the code group i.e. outermost group, and choose subtotal.
|||
the regions name that you see are actually column of matrix report and i want total in last row for each column
i have added total as column but i also want grand total row at the end
means horizontal total of each row
and vertical total of each column
i hope i got it right this time
Fine. Then doing what I said in my previous reply should work for you. Have you tried it?
Just to be totally explicit, right-click the textbox in your matrix that has the "code" and not the one that has the "description" and select the subtotal option.
|||worked thanks a lot
I need a sales report that would display weekly amounts either sold or forecasted in matrix (pivot) form.
The data used for the report is like following (INV is sold and FC is forecasted):
week
wgt
I want the report to show data like this:
WEEK 1
WEEK 2
WEEK 3
TOTAL
INV
INV
FC
FC
INV
FC
So I put region as rows, week and rtype as columns, and wgt as data field. Everything works fine except that there will be no grand totals for INV/FC. What I get is:
How can I get my totals? I know I could tailor my data to get INV/FC values into different columns to show as data fields in matrix report. But in this case, every week would always display 2 columns, which is certainly not what I want.
I also know I could add another matrix report to create a simulated total columns, but I wasn't able to "join" these 2 reports properly, there is always some space between them and the report looks unprofessionally. So I am looking for a way to do that with a single matrix.
Second question: how can I paint entire columns into different colors, so that INV is always green and FC is always blue? I tried customizing background color, but it only paints cells with values inside, leaving empty cells white. Is it possible to have the INV columns green, entirely?
Thank you in advance,
Wapper
In regards to the first question...I had a simular question:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673719&SiteID=1
as for the second question...
search forum for conditional formatting along with Inscope to accomplish what you desire...
Hope that helps...
|||Thanks for your reply.
Yes, of course I can add my own calculated subtotals to the data list using UNION ALL and GROUP BY, so that I don't need to use totals provided by the matrix report. But is it really the only way? This looks a bit ugly because I need to query my data source twice to get basically same data. Another approach with second matrix just for totals is also ugly for the same reason. It would be much better if matrix report could do this kind of summary for me.
But of course it is still a solution.
About conditional format, I found out that this can be done without InScope function. InScope is not required because I am always within the scope of "rtype". So the expression for conditional background color should be:
=SWITCH(MAX(Fields!rtype.Value, "matrix1_rtype") = "INV", "PaleGreen", MAX(Fields!rtype.Value, "matrix1_rtype") = "FC", "LightBlue")
Wapper
|||Hello Wapper,
It seems that a lot of the features that are desired are not yet implemented yet in SSRS 2005 and Microsoft is looking to a future release to handle additional features. I too feel that many elements of aggregation is missing and needs to be upgraded so that the majority of the calculation can be done with in SSRS instead of MS SQL Data structures.
goodguy1974
Hello,
I have matrix object in reportin (SQL 2005 reporting services)
When in some field have no data it display NaN.
How can I change it ?
I try to use iif(my function = nothing.....) but didn't help
Hi there,
Here's a blog posting that may help
http://blogs.digineer.com/blogs/jasons/archive/2006/08/18/374.aspx
cheers,
Andrew
