March 7, 2011 at 7:37 pm
Hello,
I am trying to build a report in 2005 RS which contains the following columns,
Region Current Year Previous Years % Change
The attached SQL table I am using contains the following example of data(see attachment).
I understand that I will need to have parameters to choose the current year's data, how do I incorporate previous year's data into the 3rd column?
What I need is the TOTAL MINS column in the report under the Current and Previous Year columns.
How do I match them up in the report from the SQL table and how can I enter the formula for % change(current year - previous year/previous year) in Reporting Services as well?
March 8, 2011 at 2:44 am
why don't yo do it in SQL?
Fill your data set with
Select
T1.TotalMins - (T2.TotalMins/T1.TotalMins) as Change%,
|AllNeededFields|,
FROM table T1
join table T2
on T2.|PKey|= T1.|PKey|
and T2.[Year]= T1.[Year]+ 1
where T1.[Year] = @CurrentYear
March 8, 2011 at 6:27 am
Thanks for your help, but all the data is in one table.
Is there another way to script it without using joins?
March 8, 2011 at 6:40 am
If you would actually do an effort and read my SQL you'll see that it's a self join as I use 'table' twice with different aliases. I realize you might be new to SQL so please read about it.
I essentially join the table onto itself but with a skew of one year so that you get one year's data together with the previous year's data on one record.
I don't see any reason to try such things in SSRS because reports are mainly just a display of your data and they are not suitable for complex logic.
March 8, 2011 at 6:46 am
It would actually help if you would provide the DDL (CREATE TABLE statement), sample data (as a series of INSERT INTO statements) that reflects the problem you are attempting to solve, the code you have currently tried, and finally the expected results based on the sample data you provide.
If you have questions regarding this request, please read the first article I reference below in my signature block regarding asking for help. The more information you provide us, the better responses you will get, including tested code in return.
March 8, 2011 at 10:00 am
Thank you for your help, as it stands I have never tried the Self Join as of yet, much appreciated.
I apologise if I didn't make myself clearer before, but what I need to put under the Current Year and Previous Year columns is the data from the Total Mins column, thereby inserting the Total Mins for 2011 under the Current Year column and the Total Mins for 2010 under the Previous Year column. I could not get that to work with your query, I hope this better explains my issue.
March 8, 2011 at 10:17 am
What will really help is to provide the information I requested. Based on what you have given us, we can only provide very basic guidance. If you provide what I requested, we can actually provide you with a tested working solution.
So please, help us help you.
March 8, 2011 at 11:56 am
Thanks Lynn, I apologise if I am going about this all wrong, please bear with me.
Here is the Create table query:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[#mytable](
[Sector] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[Technician #] [nvarchar](255) NULL,
[TOTAL MINS] [float] NULL,
[YEAR] [nvarchar](30) NULL,
[MONTH] [nvarchar](30) NULL
) ON [PRIMARY]
Attached is the Sample Data.
Basically, I am trying to create a report that will do a monthly comparison of % change of Total Mins.
For example, I would have a Current Year column(2011), a Previous Year column(2010) and a % Changed column (with formula I have stated in previous posts).
This report would also include the Sector, Region for each Total Mins results.
It would be in a matrix format in Reporting Services, going from left to right as each additional month passes in 2011, so right now I would only have data for Jan, Feb, and March only.
I hope this explains it better and thanks again for all your help with this.
March 8, 2011 at 1:35 pm
SQLBeginner, that's a good start for your data, but you don't have any months to compare to prior year? Can you mock up some data with Feb 2010 so we know what your expected results are (and post those too)? Doesn't have to be a lot, just a few examples should get the job done.
Also, small note, the very last line in your file should not have the UNION ALL, if someone straight copy/pastes the code, it throws a syntax error. Not a big deal, easy to fix, but it makes it easier (and less frustrating) for folks like Lynn to just grab your sample and make it work.
You did a great job of reading the article Lynn suggested and following through!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 8, 2011 at 2:03 pm
Ok, I was waiting on something so just mocked up one example.
bulbanos had you on the right track, in your sample table you need to join on the month too:
--sample data that I modified, at half the minutes of the 2011 record
SELECT '416','BLOORCENT','B58123','92','2010','February' UNION ALL
--see everything for this tech
SELECT *
FROM #myTable
WHERE [Technician #]='B58123'
--now use a modified version of bulbanos' self join code to see the results
Select
T1.*,
T2.[Total Mins],
--this CASE is to avoid divide by zero errors
CASE WHEN t1.[total mins] = 0 THEN 0 ELSE (T2.[Total Mins]/T1.[Total Mins]) END as [Change%]
FROM #myTable T1
--now self join to the same table on the appropriate fields to match up the techs each month
join #myTable T2 on T2.[Sector]= T1.[Sector]
AND t2.region = t1.region
AND t2.[month]=t1.[month]
AND t2.[Technician #]=t1.[Technician #]
--make sure your year is offset so that we're looking at last year
and T2.[Year]= T1.[Year]- 1
where T1.[Year] = '2011'--this value '2011' should be a variable, like bulbanos used, which is your report parameter
hope that helps!
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 8, 2011 at 2:48 pm
Nice job there 🙂 You should indeed fill the "divide by zero" black hole.
But the change % formula is strictly seen incorrect 🙂
T2.[Total Mins]/T1.[Total Mins] - 1 should do it. Details 🙂
March 8, 2011 at 3:01 pm
Not following you on that one, my resultset might have confused things a bit, since it makes it look like the Feb 2011 value is 92. In fact, Feb 2010 = 92, Feb 2011 = 184, so that'd be 92 / 184, which divides out to 0.5, but in point of fact is a 100% increase year over year (oops).
To truly reflect that, it should really be (change)/priorYear = (184 - 92) / 92 = 1.0, in SQL:
(T2.[Total Mins] - T1.[Total Mins])/T1.[Total Mins]
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 8, 2011 at 3:04 pm
basic math tells me both our equations are the same
March 8, 2011 at 3:27 pm
bulbanos (3/8/2011)
basic math tells me both our equations are the same
Ah, well that's because I'm an idiot, and can't keep track of which table is which year (a case for aliasing tables based on what they represent instead of generic T1/T2). Should be:
Select
T1.*,
T2.[Total Mins],
CASE WHEN t1.[total mins] = 0 THEN 0 ELSE (T2.[Total Mins]/T1.[Total Mins]) END as [Change%]
,
CASE WHEN t1.[total mins] = 0 THEN 0 ELSE (T2.[Total Mins]/T1.[Total Mins])-1 END as [Change%Bulbanos],
CASE WHEN t1.[total mins] = 0 THEN 0 ELSE ((T1.[Total Mins] - T2.[Total Mins])/T2.[Total Mins]) END as [Change%corrected]
FROM #myTable T1
join #myTable T2
on T2.[Sector]= T1.[Sector]
AND t2.region = t1.region
AND t2.[month]=t1.[month]
AND t2.[Technician #]=t1.[Technician #]
and T2.[Year]= T1.[Year]- 1
where T1.[Year] = '2011'
AND T1.[Total Mins]>0
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 9, 2011 at 1:03 am
I think you've already helped abundantly 🙂
are you saying aliasing with T1 and T2 is a bad habit?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply