HELP - I NEED EXTRA MATRIX COLUMNS
Apr 3, 2007
I need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.
I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?
This is an example of how I want my report to look (I can already achieve the bits highlighted bold)
Daily Data
Commission (GroupData cell 1)
Interest (GroupData CELL 2)
GroupData etc €¦
SUBTOTAL (DTD)
MTD TOTAL
YTD TOTAL
Book
Currency
=sum(Fields!DTD.VALUE)
........
=sum(Fields!MTD.VALUE)
=sum(Fields!YTD.VALUE)
and this is an example of the data being returned by my Sproc
Book
CCY
GroupedData
DTD
MTD
YTD
ABC
GBP
Commission
£0.01
£0.09
-£0.10
ABC
GBP
Interest
£0.02
£0.29
£0.11
ABC
GBP
Brokerage
£0.12
£0.06
£0.20
When I use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.
Daily Data
Grouped Data CELL 1
Grouped Data CELL 2
Book Name
Currency
DTD TOTAL
MTD TOTAL
YTD TOTAL
DTD TOTAL
MTD TOTAL
YTD TOTAL
=sum(Fields!DTD.VALUE)
Does anyone out there know how to do it?
View 7 Replies
ADVERTISEMENT
Jan 2, 2007
Can we do this?
Adding more columns in a matrix report that don€™t
belong to the columns drilldown dimensions€¦
That is, for example, having the following report:
Product Family
Product
Country City Number of units sold
Then I
would add some ratios, that is, Units Sold/Months (sold per month) and other that
is the average for Product Family (Units Sold/Number of Product Family), for putting an example€¦ some
columns should be precalculated prior to the report so do not get into it, the
real problem I don€™t see how to solve is adding one or two columns for showing
these calculated column that doesn€™t depend on the column groups but they do
for the rows groups€¦
Any guidance
on that?
The only
way I am seeing by now is to set it as two different reports, and that is not
what my client wants€¦
Many
thanks,
Jose
View 4 Replies
View Related
Sep 20, 2006
Hello,
I'm trying to make a report with the following layout:
Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%
I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?
Thanks and best regards.
vjn
View 5 Replies
View Related
Apr 20, 2007
We really like using the Matrix reports, but we were finding that every Matrix report that we created would spawn an extraneous blank page. We tried putting the matrix in a rectangle, which works well for positioning other items on reports, but this had no effect on the problem.
Then we tried placing the matrix in a list with the list group details set to "=Nothing". It worked great - no more extra pages. Looked and didn't see this tip mentioned elsewhere so thought it might be worth sharing.
View 1 Replies
View Related
Jan 23, 2006
Hello
Before I start just wanna say thanks for all the top advice so far - much appreciated. My current quandry is this:-
I have a table called STOCK with columns ITEM_ID, ITEM & PRICE. Most of the stock is my own produce but i do buy-in some items. What i want to do is add some extra columns to STOCK for the supplier details on those items i buy in. The problem is that a lot of the fields in the supplier details columns will be empty. Would this be a problem or would it be better to have a seperate table for supplier details?
Jill
View 9 Replies
View Related
Mar 12, 2008
Consider this table
Make Model Derivative Term Miles Maint CH PCH
AUDI A4 TDI 2 10000 1 100 110
AUDI A4 TDI 2 10000 0 200 210
AUDI A4 TDI 2 20000 1 300 310
AUDI A4 TDI 2 20000 0 400 410
AUDI A4 TDI 2 30000 1 500 510
AUDI A4 TDI 2 30000 0 600 610
AUDI A4 TDI 3 10000 1 700 710
AUDI A4 TDI 3 10000 0 800 810
AUDI A4 TDI 3 20000 1 900 910
AUDI A4 TDI 3 20000 0 950 955
AUDI A4 TDI 3 30000 1 975 980
AUDI A4 TDI 3 30000 0 985 990
What I'm hoping to do is consolodate these 12 rows into 1 row with the following fields
Make
Model
Derivative
2Year10kMaintainedCH
2Year10kNotMaintainedCH
2Year20kMaintainedCH
2Year20kNotMaintainedCH
2Year30kMaintainedCH
2Year30kNotMaintainedCH
3Year10kMaintainedCH
3Year10kNotMaintainedCH
3Year20kMaintainedCH
3Year20kNotMaintainedCH
3Year30kMaintainedCH
3Year30kNotMaintainedCH
2Year10kMaintainedPCH
2Year10kNotMaintainedPCH
2Year20kMaintainedPCH
2Year20kNotMaintainedPCH
2Year30kMaintainedPCH
2Year30kNotMaintainedPCH
3Year10kMaintainedPCH
3Year10kNotMaintainedPCH
3Year20kMaintainedPCH
3Year20kNotMaintainedPCH
3Year30kMaintainedPCH
3Year30kNotMaintainedPCH
Is there any way to do this?
Thanks
View 4 Replies
View Related
Aug 17, 2007
I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.
I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.
Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.
View 4 Replies
View Related
Jul 19, 2006
I have a view that is using UNION ALL to combine common fields of two tables, this is my statement:
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_FRAME_T
UNION ALL
SELECT ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER
FROM dbo.CT_ATM_T
This works fine, but I would also like some fields that do not match to appear in the view. It is OK if the value is null for the rows of data from the other table that doesn't have the columns.
The other columns are called DLCI from CT_FRAME_T and then VPI, VCI from CT_ATM_T.
My view would then return ID, STATUS, ACTIVE_STATUS, NS_PARENT_CHANGE_NUM, NS_REP, NS_CHANGE_NUM, NS_CHANGE_IDENTIFER, DLCI (where applicable), VPI and VCI (where applicable). Is this possible?
View 2 Replies
View Related
Dec 6, 2007
I have a report with several matrix objects. The data contained in each matrix is simple. One matrix has one column of labels and a column of data. The other has 3 columns of data. Both matrix's only show about 25 rows of data. A variable amount of data is displayed above the matrix's so that sometime the render across a page boundary. Whenever this happens the matrix doesn't render rows down to the bottom of the page. A considerable amount of empty space is left on the page, the completed matrix is displayed on the next page. I'm wondering how to get the matrix to render in this empty space.
This problem only occurs when I view the report on screen, in the Visual Studio "Preview" window, or from the Report Server web site. Tiff, pdf and printed output doesn't contain the extra space.
I've tried adding my matrix to a List and a Rectangle to see of this would fix the problem but it didn't help.
I've check the dimensions and margins or the page and I don't think I have any sizing problems, everything should fit on the page.
--Mark
View 1 Replies
View Related
May 31, 2007
Is there a way to sum columns in a matrix report as below?
Account 1 Account 2 Account 3
Group1 79
Customer 1 48
location A 7 4 5 16
location B 2 9 6 17
location C 4 8 3 15
Customer 2 31
location X 9 2 4 15
location Y 6 7 3 16
Group2 78
Customer 3 27
location I 6 3 5 14
location J 7 2 4 13
Customer 4 51
location P 4 5 8 17
location Q 3 9 7 19
location R 5 8 2 15
View 1 Replies
View Related
May 13, 2008
I have columns like week1,week2....... displayed in the matrix and i want to add both these columns and put them in a third column as Week 1-2. Is there any way I could do this through matrices???
View 6 Replies
View Related
Apr 16, 2008
Hi
Is there possibility to place columns(calculated) to the right of Matrix?
Like here (additional columns are marked with red).
View 3 Replies
View Related
Oct 17, 2007
hello there,
how can i add column datas together or merge two columns together.
cheers
zolf
View 2 Replies
View Related
May 27, 2008
Hi Guys,
I am creating a report in Reporting Services 2000. I have a stored procedure that returns 4 columns with one column having 21 weeks of entries in it. I am using matrix to group these 21 weeks as 21 week columns created dynamically but when I preview the report it is only displaying columns for first 5 weeks.
Any idea why Reporting services is not displaying the rest of the columns? Any limitation on maxiumum number of columns that can be generated dynamically in SSRS 2000?
Any help will be highly appreciated.
View 3 Replies
View Related
Dec 13, 2007
I have the following matrix working:
Measurement M1 Measurement M2 etc....
Unit U1 Unit U2 Unit U1 Unit U2 etc...
Item I1 123 456 120 450
Item I2 111 444 110 440
I'd need to add the manufacturing date of the item to the report. For this I need an extra column next to the row header column (in red) and also a field header (in purple).
Measurement M1 Measurement M2 etc....
Art. Date Unit U1 Unit U2 Unit U1 Unit U2 etc...
Item I1 18Nov2007 123 456 120 450
Item I2 19Nov2007 111 444 110 440
I've tried copying and pasting the first column of the matrix, inserting columns or rows, but I allways end up with an extra grouping level, which I don't want.
The date is the manufacturing date of the item, so it is an attribute of the item, not a separate level of detail.
Anyone knows how to add this extra column to the row header ?
Also, how do I add a column header like "Art." or "Date" in the example above ? I was thinking of placing a text box above the first cell of the matrix, as a kludge.
View 1 Replies
View Related
Jun 13, 2007
Hi,
Does anyone know what is the maximum number of columns allowed in a matrix?
Thanks.
View 9 Replies
View Related
Mar 5, 2008
Hi
I have a matrix report. It expands to the right when the user chooses the amounts of months he/she wishes to see. Then it is populated with revenues for each month and deportment. On the matrix report I have put in a couple of Subtotals. Some of the totals are empty due to a month in a department with no revenue.
I wish to see a 0 on the subtotal column but I don€™t want to see a 0 on the month column. If I use
=IIF( Fields!Revenue.Value is Nothing, "0", Fields!Revenue.Value)
on the month columns I will see 0 all over the place. But I only want a 0 on the subtotal column. Can I do this?
Thanks in advanced
Kind regards
//Javier
View 5 Replies
View Related
Aug 7, 2007
Hi everybody, I've a big Matrix with 50 columns and 20 rows, I would like to hide the columns in 'real time' is it possible? for example:
-col_1 -col_2 -col_3 -col_N
row_1 a b c d
row_2 a b c d
row_3 a b c d
row_N . . . .
If we don't want to see the col_1 and col_3 , we can give a click and hiding, is it possible? maybe with the option 'visibility can be toggled by another report item' or expanded and collapsed option, some idea?
Regards,
Marcos
View 6 Replies
View Related
Jan 23, 2007
I have created the report below in SSRS to show the total by month for the calendar year.
As of my January running it runs great as there is data for all 12 months of the year and the matrix is created with the required 12 columns.
01
02
03
03
4
05
06
07
08
09
10
11
12
Total
AA
17.5
22
17.5
75
30
162
BB
15
15
15
15
15
15
15
15
15
15
15
15
15
195
CC
15
15
15
15
15
337
15
233.3
15
325.5
117
15
15
1147.8
DD
315
290
1231
1231
1231
269
953
384.8
706.8
1798.8
290
602
419
9720.25
Total
345
320
1261
1261
1261
638
983
633.1
758.8
2156.8
497
662
449
11225.1
As I go to project this report for January 2007 I am having trouble.
I want the report to appear as below with the 12 requisite columns.
01
02
03
03
4
05
06
07
08
09
10
11
12
Total
AA
0
BB
15
15
CC
15
15
DD
315
315
Total
345
345
But as only data for the first column is returned from my query I only get one column to appear.
01
Total
AA
0
BB
15
15
CC
15
15
DD
315
315
Total
345
345
How do I force the other 12 columns / months to appear in my crosstab/matrix and to populate with data as it becomes available?
View 4 Replies
View Related
Jul 11, 2006
I have a matrix report and it doesn't add titles to the different groupings. And when you try and add a text box it spans across all grouping columns. How do you add titles to these columns?
Cheers
Damien
View 3 Replies
View Related
May 21, 2008
Good morning all.
I have a report that is set up as a matrix. The repor shows data relating to review numbers (for example 1-6). Based on the date range selected from my parameters the report will show stats and the matrix grouping is by the review number.
My question is this, if the dataset returns only some of the review numbers, can I do anything to show the other review numbers? They will always be from 1-6.
Thanks
View 4 Replies
View Related
Sep 10, 2007
Hi,
I have a matrxi report with 6 Columns (Col 1, 2, ...6). Based on the parameters I select I wanted to hide 3 of the columns (Col 2, 4 and 6. But the problem is when I hide the columns, it leaves a gap. That means, the matrix report shows Col 1, 3 and 5 with gap in between the Columns.
There is no Column Visibility property in matrix report.
Please help me as I am looking to solve this issue for a long time now.
Thanks,
S Suresh
View 8 Replies
View Related
Oct 23, 2006
Hi all,
I would like a matrix that look like this:
2006/01
2006/02
2006/03
Total
%
%
%
%
Amount
Amount Class.
BU1
98,82%
78,53%
88,12%
88,49%
‚¬ 217.763.099,50
‚¬ 207.328.766,75
BU2
64,98%
32,53%
92,19%
63,23%
5.093.781.522,65 ‚¬
‚¬ 287.731.766,75
BU3
74,71%
45,78%
78,69%
66,39%
‚¬ 847.871.349,50
‚¬ 819.123.124,75
I want detailed subtotals but I don't want the "Amount" and "Amount Class." values in the details of my Matrix.
I've not found other way to show the "amount" and "amount class." in the subtotal without adding those values in the details are of the matrix. But when I select "Hide" in the properties those values are hidden in the subtotal as well. Is there any way to hide a value in the details area but not in the subtotals ?
How can I achieve this ?
View 17 Replies
View Related
Aug 18, 2007
Hello,
I am trying to format a matrix report so that columns appear in a specified order.
An example of what I mean is, I have 3 columns; New, Additional and Old.
When these columns are dynamically generated by RS they are put in alphebitic order. I want them to appear in the order in which I have them above.
The dataset returns a sequence (int) for each of the columns, so 'New' = 1, 'Additional' = 2 and 'Old' = 3. I am ordering on that sequence, but still can't get it to work. These are actually column groupings.
What am I doing wrong? I don't want the column sorted (i.e. data sorted within a row), but the columns to appear in a specific order.
Many thanks
Shawn
View 2 Replies
View Related
Mar 21, 2007
HI all !
I am having a bit of a problem trying to limit a number of columns in a matrix appearing on a page.
At the moment, I have a dataset that lists the month and the mail packages that were sent during the month
The matrix works great HOWEVER, if there were more than 8 months in the matrix columns, it does not break and would make the page look like a huge landscape page.
I am trying to limit the number of columns appearing (this is the months column) on the matrix so that the pages stay in a potrait position. IE: every 8 columns appear on one page. Is there an option or an expression I could use in the Matrix ?
Thanks!
BErnard Ong
View 1 Replies
View Related
Jan 28, 2008
Hi
I€™m having trouble again with a matrix report.
I wish to make a matrix report with 5 columns. The right part of course will be filled depending on how many moths the user has chosen. The left part will be field with country, city and then the person names and some other info.
What I want to do is to have the first 2 columns, country and city, with drilldown. That is pushing the plus sign to open the corresponding group. How do I do this?
Thanks in advanced
Kind Regards
View 4 Replies
View Related
Jan 31, 2008
Hi,
Im trying to create a calulated value in my matrix table
I have the following matrix
ADSL CABLE BROADBAND %of adsl %ofcable %ofbroadband
AWAITING 5 9 11 0.2 0.36 etc
IN PROGRESS 67 10 5 0.8 0.1 etc
CHURNED 8 1 15 0.3 etc etc
I would like to create columns called
% of ADSL
% of cable
% of broadband
which is the count for that product divided by the total off adsl+cable+broadband for that particular status
I have the following problems
1. How do I add columns to a matrix which would allow these calculated columns to display?
thanks
View 3 Replies
View Related
Jan 16, 2007
I have a matrix report that I am using with MonthName(Fields!Month.Value) across the top of the report. However, when there is no data under the column, the month is skipped. So, for example, I get this:
January March
Actual Goal Actual Goal
25 25 30 35
I have tried adding
iif(Sum(Fields!Total.Value, "DatasetName")> 0, Sum(Fields!Total.Value, "DatasetName"),"N/A")
But it still is not showing up.
Any ideas?
Thanks,
SHP
View 5 Replies
View Related
Jul 17, 2007
I am creating a report that uses the Matrix control. I need to display a fixed number of columns (5). In my query, I am returning the top 5 rows of data. However, in some cases there are less than 5 rows of data returned from the dataset. Is there a way to force the number of columns displayed in the matrix control and to populate with some text (such as "n/a") if no data is available?
Thanks!
ads
View 1 Replies
View Related
Mar 5, 2008
Hi,
I am wondering if someone has some experience with hiding columns in a Matrix report. I have got two details columns: "Yr to Yr Credits Growth€? and "Yr to Yr Credits Growth %" and those two columns return only one value "N/A" for the earliest year since there is nothing to compare to. Thus, I don't really need them for the earliest year. On top of detail columns, I have got three matrix groups: matrix1-Year, matrix1-Quarter and matrix-Date. Once those two detail columns are hidden, I would like obviously resize (shrink) those three matrix groups columns to reflect the fact that the detail columns were hidden.
Thanks!
View 3 Replies
View Related
May 25, 2007
I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.
For example a report that would run on today's MM, yy (5/2007) would look something like this:
MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08
(Col 1 - Col 12 separated by commas)
How do I do this in a matrix report column?
View 1 Replies
View Related
May 1, 2008
I have used two matrix in one of my reports. One matrix is right above other. Both matrix's columns are allocated for month name. I.e there are 12 columns for each month of the year for each matrix.
column name of the second matrix was hidden. so end user can see only first matrix column name and corresponding data in each matrix.
But the problem is now, when there is no data for perticular month in first matrix, thats month's column does not appear at all.
Lets say there is no data for November in first matrix. so Novem column is missing in first mtrix now. but still Novem column is shown on the second matrix as it has some data, although column name is not shown. I wonder how I can show all the columns of both matrix regardless of population of data.
Thanks
View 1 Replies
View Related
Jun 14, 2007
Is it possible to display only a certain number of columns in a matrix, say the first 6 and then hide the rest? That is, does the matrix allow to somehow control how many columns can be displayed from a column group and hide the remaining columns (I need this to limit the number of columns a user is able to see so that the matrix width does not get infinitely long).
In other words.....
I need to display the subtotals for all dynamically generated columns but display only first 6 columns. This way I can avoid having to display 50 columns and not have user scroll to so far right and keep the page width within reasonable limits. Hope I have made it clear.
Thanks.
View 3 Replies
View Related
