Quick question for you pro's...
Within a report matrix, I want to be able to have different aggregations for totals. One column would be a Sum of the data in that column, the next column would be an Average of the data. Is this possible, or is this not supported in SSR2005?
Thanks in advance for your help,
Clint
I got the following code to add a column in a matrix with a variance:
IIF(IsNothing(Previous(Sum(Fields!Amount.Value))) or Fields!year.Value=First(Fields!year.Value,"Category") or Previous(Sum(Fields!Amount.Value))=0,nothing,
(
(Fields!Amount.Value)
/Previous(sum(Fields!Amount.Value))
)
)
This code works fine, except that the first row of the matrix shows an #error
This happens with each matrix where I use this expression. A warning emerges:
rsruntimeerrorinexpression the value expression for the textrun Textbox43.Paragraphs[0].TextRuns[0]' contains an error.
Attempted to divide by zero.
The strange thing is that the part
Fields!year.Value=First(Fields!year.Value,"Category")
should prevent an error and I expect it to show 'nothing'
An screenshot of the table. (each color is a different category. Each row stands for 2013, 2014, 2015)
As you can see, all other 2013 rows show a blank cell, except the first row.
hi,
I'm making a cube using SSAS and the data in the cube is updated daily. however, each day I take the data with the date of migration because I want to keep the data as history snapshots. the problem is when browsing the data I want to aggregate only the data of a desired date not all the data together. can I define a custom aggregator rather than those found in the properties for the measures?
Thanks a lot.
Mina
Hey,
I am trying to add aggregations to the partitions of my cube.
For that, I am using the Aggregation Designer, In which I choose MOLAP partitions with "Performance gain reaches 80%". Next I run the calculation. But the designer found only 78 aggregations and stop at 20% (496 Kb) !
My configuration is SSAS 2005 SP2, x64. Except this problem, everything is allright. Dimension attributes are all aggregatable.
Have you any idea of what can cause such a problem ?
Thanks in advance.
Alex
We are trying to compile a difinitive procedure for our customers moving from SSAS 2000 (where we use DSO) to 2005 (where we use AMO). However, we are running into problems getting even the 2000 environment set up according to the specs.
The requirement is a domain environment where the web server and the DB server are 2 different machines. We are replicating this environment locally using 3 Virtual Machines in VMWare Player. All have Windows Server 2003 Enterprise with SP2 installed. One is our domain controller, one is the DB server (SQL Server 2000 and SSAS 2000 SP4 with the hotfix rollup installed) where almost none of our application components are installed, one is the web server with IIS and our ASP.NET application and COM components, which bundles DSO and dependencies, but no database client tools installed. We set up ASP.NET to run as a domain user, a member of Administrators group on the domain and both server machines, and also OLAP Admins on the DB server (we can limit access later once everything works).
I can successfully create the database, cube, dimensions, and partitions from the ASP.NET code calligng COM components written in VB6 which use DSO. However, creating aggregations results in a clipboard error. Here is the code:
1380
Set dsoPartition = m_dsoServer.MDStores(mvarDBName).MDStores(cubeName).MDStores(1)
dsoPartition.OlapMode = olapmodeMolapIndex
1385
' get the partition analyzer
Dim dsoPartitionAnalyzer As DSO.PartitionAnalyzer
Set dsoPartitionAnalyzer = dsoPartition.Analyzer
' initialize the analyzer
dsoPartitionAnalyzer.InitializeDesign
1390
There is more, but the error occurs on one of the three statements between 1385 and 1390
The error is: "Can't open Clipboard" number 521 Source "DSO"
Creating the aggregations via the Storage Design wizard on the DB server directly seems to succeed.
Can somebody help with this?
Thanks,
Boris Zakharin, MCAD
Fact table gets update every day with thousands of records, I been increase the aggregations manually. Is there a way I can increase aggregations (design storage) programmatically . My cub gets process ones in day with VB program. I want to increase aggregations before processing cub through VB.
It's argent
please help me
thanks
Hello,
Not sure if data minimg can help me solving this type of inquiries. Thanks in advance for any pointers you could provide me.
Problem:
We have many dimensions with many levels. Sales directors will sometimes note a drop or a spike when looking at one of the dimensions members, most of the time at a high level.
Example, For Q4 07, the aggregation of Q4 Billing plus remaining Q4 Backlog dropped by $10M in America for the Automotive business from week 4 to week 5 while it was forecasted to remain stable.
The immediate question that will be sent to the guys in charge of this business is "What happened?".
Obviously evyone struggle to answer these questions eveytine there is a drop anywhere.
Using classic tools, they will try to spot a single anomaly or determine if this is accross the board and what are the concurent factors like lower than expected shipments combined with some orders cancellations.
The drop-off can lie in a single product or customer location, in which case it is fairly easy while time consuming to spot the anomaly using classic drill-down approach.
The drop-off can also be more wide spread or caused by a combination of factors like unable to ship in time, orders cancellations and sales credits in which case it is more difficult to spot where is the anomaly and explain it.
So I wonder if there is any way that I could use data mining to make the entire process of detecting anomalies accross dimensions levels, members and measures more straight-forward for end-users.
What do you think?
Thanks,
Philippe
hello there,
how can i add column datas together or merge two columns together.
cheers
zolf
Hi,
I've a report containing a matrix. I want a column on end of each row in matrix, which shows me sum of that row. Is it possible in matrix? if yes, how can i achieve it?
hi reporters!
i m using matrix in my project and i want to calculate the
(cell_value/sum_of_column). for data cell values we have to use sum or
another aggregate function for subtotaling, so for the cell value i m
wirting sum(column) / A. how to write A that points to sum of all cells
on the column.
x_column
y_column
z_column
sum(y_column)/A
A?
I have the following fields in my report
Year
2008 2007 Net Change
Val1 10 6
Val2 7 5
Val3 15 7
The Matrix Column is grouped by Year.
I need to calculate Net Change as Year 1 - Year 2 for each row.
How do I access the contents of the first and second column of the matrix ?
I have a matrix that has the following columns:Date Shift Equipment1 Equipment2 Equipment3 etcBased on a parameter, i want to show/hide the Shift column. If the Shift column is hidden, i want to move the equipment columns over so there is no gap in the columns.I know you can do this in a table by hidding the whole column, but how do you do this in a matrix? when i try to hide the column the visibility option is not there, and when i hide the field, it leaves a gap in the report.Thanks for your help.
View 2 Replies View RelatedHi,
is there a way to add column header in a Matrix?
Thanks,
Igor
Hi,
I would like to generate following table with reporting service matrix.
But if I use the expression : percentage coulmn=sum(field!qty.value) / sum(field!qty.value, "region_group")
It will become a percentage of a row total, instead of a group total (store group). Please see the second table.
Any ideas?
Thank you!
(Correct!)
store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 10% 20 20% 70 70% 100 100% 25 50% 25 50% 50 100% EURP C2 CHINA
(Wrong) store1 store2 level1 level2 level3 sub total level1 level2 subtotal category region qty % qty % qty % qty % qty % qty % qty % C1 APAC 10 6% 20 13% 70 46% 100 25 16% 25 16% 50 EURP C2 CHINA
I am developing a matrix report in SRS. In columns group there are several values. When report runs they apper in any order based on the first record in row group. I want colums to apeear in specific order all the time. For example the column sequence in one out put is Follwup 1, Initial , Followup 2. I want to column header to be in order of Initial, Folloup 1, Followup 2.
Can someone help?
Hi,
Is there any way of having Column names in a matrix report. Actually it is a RowGroup. I want to display the name of the row group at the top, so that the user understands what is the data beneath.
regards
Josh
Hello,
I have one column group and three static columns. I'd like to control static column visibility property depending on a field value. I.e. Hidden: =Fields!GroupId<>5.
But I'm not able to find visibility property in column object. Neither in properties explorer nor RDL documentation. I'm only able to set hidden property in textbox objects.
I have RS 2005.
Thank you for any idea.
Jirka Nouza
Hi,
I have a requirement from my users to be able to drill down for a single column in a matrix. I've been able to implement drilldown for all of the measures (all columns grouped at the same time) in my matrix but they now want to be able to group different columns independently of each other. In other words they want to be able to group the data in different columns by different things.
An example might make my question clearer, the report would need to look like this:
Client Name
Sales -
Costs +
Revenue -
Client A +
Europe +
Middle East +
Asia Pacific +
$12,000,000
Products +
Investments +
$12,000,000
$8,000,000
$4,000,000
$10,000,000
$22,000,000
Client B +
$77,000,000
$16,000,000
$9,000,000
$22,000,000
$8,000,000
$32,000,000
As you an see, the Sales measure has been expanded so it's grouped by region so the sales figures can be seen for all clients and the 3 regions. The revenue column has also been expanded/grouped so that you can see revenue figures for products and investments for all clients. Costs is not expanded but it could be by clicking on the '+' which would group the data in that column by something else. They have asked for more than 1 level of drilldown, so clicking on "Europe" above would allow another level for the sales measure which might be country. There will also be regular measure columns that are not drilldown/groupable columns.
Is it possible to implement this with SSRS 2005?
Thanks,
Lachlan
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
Added a subtotal to matrix column. But really wanted subtotal below the columns. Now that subtotal column is permanent. Cannot find a way to remove it.
View 1 Replies View RelatedHi,
I am a newbie with Reporting services and I have a question about matrix using...
So I am using SSRS on an cube with MDX language. I have a matrix like that :
C1 C2 C3
---------------------------
R1 | 10 12 09
R2 | 08 10 23
So I would like to color my best values on a same row. I don't find a function in the expression editor.....
Do you have an idea ?????
Hello.
I don't know if its an sql question or an ssrs question.
I have a matrix that it column are months.
In the dataset I only have part of the manth (like: 1 , 2, 3, 5, 6, 7).
The problem is that it skip the 4th month and it doesn't look good.
Is there a way to add all month to the matrix column?
Thanks.
Hi..
I want to dynamically add column to a matrix.. this is what i have done so far...
1) Have a matrix control on my report...
2) added columns to it.. i want to may be add around 20 columns.. but at a given time there may not be more than 6 -10 columns. But if i add another column to the matrix it put it below the first column like this
Fields!NDesc1.value
Fields!NDesc2.Value
but i want to display it like this
Fields!NDesc1.value Fields!NDesc2.Value
How can i do it..
Can i have some step by step directions to do it...
any help will be appreciated.
Regards
Karen
Hi All
I have a matrigx report that groups by months in the columns. The reason for using a matrix style report is due to not knowing which months are going to exist in the database for the current year.
I do however need to have a static column appended to the matrix, using the same row groupings... I did think of placing a table next to the matrix with that column although im worried the row groupings and alignment may be off of each record.
Is it possible to have a static column inside a matrix that is not grouped by any of the columns just the rows.
What would be the best way to achieve this requirement. I also need to provide the options to hide the months columns and display only the static one and vice-versa...
Any help would be appreciated..
Regards,
Neil
I have a matrix report, which looks like the following:
Header C Header D
Column A Column B Column C Column D
Column A and B are Row Group columns in the matrix. Column C and D are the details columns. How can I add column header to column A and B. Seems I cannot do it in Reporting Services 2005.
Fan
Ill start off by saying that I am an ASP.NET developer and I am self-taught in using SSRS2005. (quite poorly I might add) That being said I am having a problem with a report im building. This report has 10-12 different metrics I am reporting on and I have each of them displayed in their own matrix. The first matrix was created by the report wizard that launches when you add a new report.
To give you an overview of how I am setting up these reports I will describe the structure I am using on nearly all of the matrices. I am reporting on classes that are managed by my company and held at 3rd party locations. My matrix has the 3rd party location in the rows and the quarter the class was held is the column. Matrix setup looks like this:
________|_______Quarter____________
|MetricA|MetricB|MetricC|etc...
LocationA|
LocationB|
LocationC|
The problem Im having is this. All matrices that weren't created by the wizard don't grow dynamically. Say I have 2 quarters of data I am reporting on, Instead of having a column for quarter1 and quarter 2, the report generates separate matrices for each quarter. This is true for each matrix that I created using a matrix control from the toolbox. The matrix that was created by the wizard however, has dynamic columns that grow to the right as expected.
I am populating my datasets using Stored Procedures and the matrix that behaves normally uses a different dataset than the 4 others I have completed thus far which share a dataset.
I have looked everywhere I can think of for a checkbox or a property I need to adjust and I cant find one. Anyone have any ideas??
I have a report using a matrix component and I wanted to hide the first column which is the rows group and I don't know how to do it. Please help. Thanks in advance.
View 10 Replies View RelatedHi,
Can someone tell me,How to create a cusotm column in Matrix report
I have Row Group as State and Column group as Status
I need to have one additional column in Matrix named Resolved Count which basically shows conunts for State which got resolved
State Status1 Status2 Resolved Counts[Custom Col]
NY 12 13 1
TX 1 5 3
I tried creating a different column group but seems like it's not working.I read somewhere that you can do it.Do someone has idea how to achieve it?
-Thanks,
Digant
Hi Everyone,
I am designing a report in SQL Server 2005 Report Designer that uses a Matrix table. The matrix table is grouped by month. How can I add a Year-To-Date total column on the report? This is driving me nuts...I cannot figure this out.
Thank You
-Sam
Hi Every One,
I have a simple matrix like below
Categories
A
Total A
B
Total B
Products
SHOES
$100
$100
$50
$50
SOCKS
$80
$80
$90
$90
How can I add a Columns Group page break for the Group Categegory in Reporting Services 2005 so the first page break takes place after Total A and rest of the data moves to the next page?
Is there any way to give Title to matrix row and column groups?
I know If i have only one row i can give title in the textbox provided at the top of the Rows group area. But i have more than one row and group and more than one column group. i want to give title to all of them.
Without the title the report looks incomplete
Is there any solution for this problem?
Thanks
Rohit
<P>Greetings,</P>
<P>I am new to reporting services and am struggling with trying to add a column to the end of matrix report that has totals. You can see a jpg of the report at http://www.catertots.com/matrix.jpg What I need to do is repeat the school code that is in the first column into another column that follows the total. </P>
<P>Any help would be much appreciated.</P>
can anyone help me about reporting services on how to sum the coloumn and put it on a textbox... but the case is like this,
Stud Name Fee Name Fee Amt. Amt Paid
John Doe Registration 1,000 1,000
Tuition Fee 5,000 3,500
1,500
Jane Bake Tuition Fee 5,000 5,000
SubTotal 16,000 11,000
when i aggregate the Fee Amt. using Sum function i get the result of 16,000 because john doe break his payment in to two(it should be 11,000 only). any help would be appreciated.
Thanks...