March 15, 2009 at 11:11 pm
Hi All,
I have a requirement to display summary by region
Assuming I have a table with the following info
RegionName ApplesSold OrangesSold
Asia 100 200
Africa 10 50
I want to be able to display my report as
Asia Africa
ApplesSold 100 10
OrangesSold 200 50
% 50% 20%
Can I achieve this by using matrix?
March 15, 2009 at 11:57 pm
yes, it is possible using matrix report. but if no. of column is fixed then why go with the matrix instead of table report.
March 16, 2009 at 4:43 pm
No the no of columns are not fixed so I would prefer my reporting with matrix where the regions might grow in time. Also I have examples of using one row and one column in a matrix but I do not know how to do the same using two rows. Also The third row is always a computed value. In matrix I know we can get totals but how do I get a computed value?
Your help is much appreciated!!
March 17, 2009 at 5:51 am
use the given below TSQL
select 'Asia' as 'RegionName','ApplesSold' AS ROW, '100' as SoldVALUE
union
select 'Asia' as 'RegionName','OrangesSold' AS ROW, '200' as SoldVALUE
union
select 'Africa' as 'RegionName','ApplesSold' AS ROW, '10' as SoldVALUE
union
select 'Africa' as 'RegionName','OrangesSold' AS ROW, '50' as SoldVALUE
union
select 'Asia' as 'RegionName','Percent' AS ROW, '50%' as SoldVALUE
union
select 'Africa' as 'RegionName','Percent' AS ROW, '20%' as SoldVALUE
you have to calculate the percentage inside your TSQL and return that as another row as i did. this way you can create your matrix report with percent.
March 17, 2009 at 4:59 pm
Thanks for your reply, by unions we are just assuming the no of regions to be fixed, but as i said the regions might grow in future as of now I am asked to report on Asia and Africa but i might be asked to report on Europe in future so I am making my TSQL dynamic by having the results returned in the fomat as shown below
RegionName ApplesSold OrangesSold...
Trust me this is not a straight table, just an example of what I am trying to achieve but my final results after a lot of joins is in the format as shown above and I have to report it
as
Region1 Region2 Region3...........
ApplesSold
OrangesSold
How do i accomplish this?
March 18, 2009 at 1:17 am
Don't look into the TSQL just focus on the data it's returning. I just want to explain, you have to format the TSQL which will return the record set in that manner then only you can archive the required report format.
March 19, 2009 at 11:25 pm
Is this work for you?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply