October 9, 2012 at 11:43 am
I have a matrix in SSRS (SQL server 2008 R2)
Country 2001 2002 2003
ABC 90 10 100
BCA 110 20 130
I need the following output:
Country 2001 2002 % change(2001-2002) 2003 % change(2001-2003)
ABC 50 100 50% 50 0%
BCA 25 25 0% 100 3.5%
Please Help me...
October 9, 2012 at 12:13 pm
Looks to me like you need to rewrite the query to provide the data you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 9, 2012 at 12:15 pm
raghavender.2369 (10/9/2012)
I have a matrix in SSRS (SQL server 2008 R2)Country 2001 2002 2003
ABC 90 10 100
BCA 110 20 130
I need the following output:
Country 2001 2002 % change(2001-2002) 2003 % change(2001-2003)
ABC 50 100 50% 50 0%
BCA 25 25 0% 100 3.5%
Please Help me...
Are these just random numbers? Your output does not seem to match your input.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 12:19 pm
If it's relatively simple math, you could just create an expression for the percent change fields. Something like:
=FormatPercent((Fields!2002.Value) / (Fields!2001.Value), 2)
Note: the math above is probably not what you want, but you get the idea.
Or like G2 said, modify your query.
HTH,
Rob
October 9, 2012 at 12:33 pm
No, NO I dont want to handle the report in a query.. I would like to handle in a report, using some custom code,
October 9, 2012 at 12:36 pm
raghavender.2369 (10/9/2012)
No, NO I dont want to handle the report in a query.. I would like to handle in a report, using some custom code,
I'm not sure why you wouldn't do the calculation on the db engine side; but if you want the calcs done in the presentation layer, use an expression as I suggested.
Yes, YES -- give it a try. :laugh:
Rob
October 9, 2012 at 12:37 pm
In the above example I have taken years(2001,2002,2003) to make you understand, dont hardcode the values.
and how can i get the previous column value?
formula is curyear-prvyear/prvyear*100
October 9, 2012 at 12:40 pm
yes.... by using expression we can do that... but i dont have idea to write expression to get that result... plz help me
October 9, 2012 at 12:42 pm
raghavender.2369 (10/9/2012)
yes.... by using expression we can do that... but i dont have idea to write expression to get that result... plz help me
You posted the formula and the example was presented above. What are you still not sure of?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2012 at 12:52 pm
how to get the previous year value to do the calculation.
October 9, 2012 at 12:55 pm
raghavender.2369 (10/9/2012)
how to get the previous year value to do the calculation.
=FormatPercent((Fields!2002.Value) / (Fields!2001.Value), 2)
We don't know what the names of your columns are in your query and we can't see what you see.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply