September 20, 2012 at 4:52 am
Hi
I have a Matrix table in SSRS 2008 which shows sales data for the past ten financial years. The table consists of Financial years across the top (columns), sales item descriptions as rows and the sum of sales as the data within the matrix. I added a column group because I wanteed to calculate the average sales of two of the financial years (2009/10 and 2010/11). I grouped on Financial year in the new column group and then added a filter to select the two financial years I'm interested in. However when i render the report rather than getting one column for the two financial years I'm getting two columns ie one for 2009/10 and one for 2010/11. Any suggestions as to where I might be going wrong ow what I might need to do?
Thanks in advance.
BO
September 20, 2012 at 11:52 am
ByronOne (9/20/2012)
HiI have a Matrix table in SSRS 2008 which shows sales data for the past ten financial years. The table consists of Financial years across the top (columns), sales item descriptions as rows and the sum of sales as the data within the matrix. I added a column group because I wanteed to calculate the average sales of two of the financial years (2009/10 and 2010/11). I grouped on Financial year in the new column group and then added a filter to select the two financial years I'm interested in. However when i render the report rather than getting one column for the two financial years I'm getting two columns ie one for 2009/10 and one for 2010/11. Any suggestions as to where I might be going wrong ow what I might need to do?
Thanks in advance.
BO
The problem is in bold in your question itself.
You need to create a calculated field at query level to get the average of 2 years.
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
September 20, 2012 at 3:38 pm
So in other words within the select statement of the t-SQL? But the statement brings back 10 years of data how do I create a new column In the code which averages only the two years i am interested in but still brings back all 10 years worth of data? Or have I misunderstood?
September 21, 2012 at 1:03 am
Can you share the sql query and maybe screenshot of the matrix report you have created ?
That may help to suggest the sql query.
BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply