September 9, 2014 at 3:31 pm
Yeah, I'm learning SSRS. My boss knows that.
I'm asking for help with one small part of a report and not for advise on why I am bringing in activity_date. The point is that I need it to extract month and year and immaterial to the issue.
I just need help with a YOY SSRS expression for one column, two cells..... Seems like you're an application developer and not SSRS person, and you haven't helped me the most, but you keep replying.
--don't let the best be the enemy of the good!
September 9, 2014 at 4:19 pm
KoldCoffee (9/9/2014)
Yeah, I'm learning SSRS. My boss knows that.I'm asking for help with one small part of a report and not for advise on why I am bringing in activity_date. The point is that I need it to extract month and year and immaterial to the issue.
I just need help with a YOY SSRS expression for one column, two cells..... Seems like you're an application developer and not SSRS person, and you haven't helped me the most, but you keep replying.
--don't let the best be the enemy of the good!
I haven't helped you the most? Who else has helped? You've provided information in tidbits and expect someone to hand you code that will just work. And now when I'm trying to help you chastise me for asking questions? Based on the information you have provided there is no need to return activity_date as part of the result set and having it just makes getting what you want more difficult.
So attached is a report that does as close as I can get to what you want. I did have to remove activity_date from the query to get it working, but I'm sure a "real" SSRS person could find a way to get it to work with that column.
If what I've got doesn't satisfy, I do suggest again that you hire an real SSRS consultant to help you since I'm apparently the only person who is trying to help you on this forum for free.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 4:39 pm
yes, you and pietlinden are the only ones. And you are trying to help me but are under and over reaching each time. I need one specific thing...not to explain the whole history of my SSRS report. Just one expression, not obfuscated with VB method that you can't explain.
Truth is that I did not provide as much information as I should have at the start because i'd hoped the problem I was posing was well known, and well documented. But it appears not to be.
So, I have not posed the queston well from the start. I realize that, but it's better if you didn't respond if you don't have an answer you understand.
It really doesn't matter whether I used a matrix or a table, as the column is dependent on the data, not the report item.
September 9, 2014 at 6:11 pm
Jack,
Cool solution! After stepping away from what I was thinking before, I realized that I could use a CTE to do the thing... (and I saw you did that). I might have to go read the MS example of how to do CTEs again...
Will have to do a bunch of Lookup stuff just to get my head around it. (Not covered at all in Larson's book, and maybe it's hidden in Lachev's book, but that's a slow read.)
Thanks!
Pieter
September 9, 2014 at 6:15 pm
but it's not the solution to this problem! I looked at the RDL Jack left and it involves creating another column that I can't hide or use.?
What I'm going to do is create two hiddlen cells. One containing a sum of sentleads for the current year with filter to include only months less than current month.
A second for the sum of sentleads for the former year with a filter that includes only records for months that have a month number less than that of the current year month number.
In a visible column I will divide them. That will give me a single cell with the YoY for sentLeads.
Please see post on writing an expression for that:-)
September 9, 2014 at 7:08 pm
Jack Corbett (9/9/2014)
KoldCoffee (9/9/2014)
Yeah, I'm learning SSRS. My boss knows that.I'm asking for help with one small part of a report and not for advise on why I am bringing in activity_date. The point is that I need it to extract month and year and immaterial to the issue.
I just need help with a YOY SSRS expression for one column, two cells..... Seems like you're an application developer and not SSRS person, and you haven't helped me the most, but you keep replying.
--don't let the best be the enemy of the good!
I haven't helped you the most? Who else has helped? You've provided information in tidbits and expect someone to hand you code that will just work. And now when I'm trying to help you chastise me for asking questions? Based on the information you have provided there is no need to return activity_date as part of the result set and having it just makes getting what you want more difficult.
So attached is a report that does as close as I can get to what you want. I did have to remove activity_date from the query to get it working, but I'm sure a "real" SSRS person could find a way to get it to work with that column.
If what I've got doesn't satisfy, I do suggest again that you hire an real SSRS consultant to help you since I'm apparently the only person who is trying to help you on this forum for free.
I find this just rude!. What is a "SSRS person" ? You should try to find every way possible to handle calculations on the SQL side. If not order a VB book and learn it yourself. It's posters like you that give these fine gentlemen a bad taste in their mouth about us new guys.
***SQL born on date Spring 2013:-)
September 9, 2014 at 7:16 pm
I disagree. This is a calculation and not a query optimization issue. A pure division so I do not want to tax SQL Server for that.
And exactly, why should I hire an SSRS consultant aka SSRS person? What is that? Someone who has some SSRS experience but doesn't know everything? I think I am such a person visiting a forum, asking for specific help regarding SSRS.
September 9, 2014 at 7:41 pm
Otherwise, Jack probably is on to something with the LOOKUP function and I haven't looked into that enough.
I do appreciate the help and should take ownership of not posting my question thouroughly from the start. So, I apologize Jack!
September 9, 2014 at 8:33 pm
KoldCoffee (9/9/2014)
yes, you and pietlinden are the only ones. And you are trying to help me but are under and over reaching each time. I need one specific thing...not to explain the whole history of my SSRS report. Just one expression, not obfuscated with VB method that you can't explain.Truth is that I did not provide as much information as I should have at the start because i'd hoped the problem I was posing was well known, and well documented. But it appears not to be.
So, I have not posed the queston well from the start. I realize that, but it's better if you didn't respond if you don't have an answer you understand.
It really doesn't matter whether I used a matrix or a table, as the column is dependent on the data, not the report item.
I do completely understand the solutions I provided and the most recent one does exactly what you provided as an example and will work dynamically as you require and does not require any hidden cells.
Everyone who posted on this thread attempted to help you solve your problem and one of the best things about this particular site is that people don't just answer a specific question, but try to educate by providing other options that may be better. I'm sorry I couldn't provide a solution suitable to you, but I learned something interesting from the exercise so that's why I stuck with it. I still don't think doing this in the report is the best way, but now I know it can be done if I'm ever in a situation where I'm not allowed to leverage the power of T-SQL to accomplish the task.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 8:39 pm
pietlinden (9/9/2014)
Jack,Cool solution! After stepping away from what I was thinking before, I realized that I could use a CTE to do the thing... (and I saw you did that). I might have to go read the MS example of how to do CTEs again...
Will have to do a bunch of Lookup stuff just to get my head around it. (Not covered at all in Larson's book, and maybe it's hidden in Lachev's book, but that's a slow read.)
Thanks!
Pieter
Thanks Pieter. It was actually pretty fun figuring out how to make it work. I'd really like to figure out a way to do it without the iif to eliminate rows from the same year and how to get it to work in the matrix with activity_date still in the data set. But I do have a real job and family so I can't spend all my time on SSC.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 9, 2014 at 10:19 pm
what I did was to do a weird join. Say you have two CTEs, one for "Current Year" and one for "Previous Year". You can do a terrible thing and join on
CREATE PROC [dbo].[getActivityDeltas]
@Activity_Year INT
AS
WITH BaseYear_CTE (Activity_Year, Activity_Month, MonthlyLeads, MonthlyReferrals)
AS
(
SELECT YEAR(Activity_Date) AS Activity_Year
, MONTH(Activity_Date) AS Activity_Month
, SUM(SentLeads) AS MonthlyLeads
, SUM(Referrals) AS MonthlyReferrals
FROM Example1
WHERE YEAR(Activity_Date)=@Activity_Year-1
GROUP BY YEAR(Activity_Date), MONTH(Activity_Date)
),
NextYear_CTE
AS
(
SELECT YEAR(Activity_Date) AS Activity_Year
, MONTH(Activity_Date) AS Activity_Month
, SUM(SentLeads) AS MonthlyLeads
, SUM(Referrals) AS MonthlyReferrals
FROM Example1
WHERE YEAR(Activity_Date)=@Activity_Year
GROUP BY YEAR(Activity_Date), MONTH(Activity_Date)
)
SELECT B.Activity_Year AS BaseYear
, B.Activity_Month
, B.MonthlyLeads AS BaseYrLeads
, B.MonthlyReferrals AS BaseYrReferrals
, N.MonthlyLeads AS NextYrLeads
, N.MonthlyReferrals AS NextYrReferrals
, 1.0 * (N.MonthlyLeads - B.MonthlyLeads)/B.MonthlyLeads AS LeadsDelta
,1.0 * (N.MonthlyReferrals - B.MonthlyReferrals)/B.MonthlyReferrals AS ReferralsDelta
FROM BaseYear_CTE AS B INNER JOIN
NextYear_CTE AS N ON (B.Activity_Year = N.Activity_Year-1
AND B.Activity_Month = N.Activity_Month);
(I cribbed some from your post, but I was trying to remember how to CTEs anyway, so I looked it up on the MS site and built it for practice.)
September 10, 2014 at 12:41 am
KoldCoffee (9/9/2014)
Yeah, I'm learning SSRS. My boss knows that.I'm asking for help with one small part of a report and not for advise on why I am bringing in activity_date. The point is that I need it to extract month and year and immaterial to the issue.
I just need help with a YOY SSRS expression for one column, two cells..... Seems like you're an application developer and not SSRS person, and you haven't helped me the most, but you keep replying.
--don't let the best be the enemy of the good!
If you don't like the advice that was given to you, you can always get a refund of the money that you paid for it...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 10, 2014 at 3:07 am
My answer, FWIW, would be to remove the year filter from the dataset and add it to the column expression instead.
e.g.
Sum(IIf(Year = Year(Now)-1,Fields!SentLeads.Value),0) for last year
Sum(IIf(Year = Year(Now),Fields!SentLeads.Value),0) for this year
Add columns after column group to contain variance and use an expression like this
(Sum(IIf(Year = Year(Now)-1,Fields!SentLeads.Value),0) - Sum(IIf(Year = Year(Now) AND MonNo < Month(Now),Fields!SentLeads.Value),0)) / Sum(IIf(Year = Year(Now) AND MonNo < Month(Now),Fields!SentLeads.Value),0)
Far away is close at hand in the images of elsewhere.
Anon.
September 10, 2014 at 12:05 pm
David, that's sublime. Key point I took from you was
My answer, FWIW, would be to remove the year filter from the dataset and add it to the column expression instead.
When I put this in the column expression:
=sum(iif(Fields!MonNo.Value < datepart(dateinterval.Month, today), Fields!SentLeads.Value, 0))
I got a sum of sentLeads for all months of the prior year (because the table row has parent group that filters for last year) whose month is less than the current month of the current year.
THANK YOU!!!!! I now have a little 3 column table whose first two columns I will hide. They contain sentLeads grouped by year, and use the 3rrd column with a division expression to display variance. Problem solved.
I attached RDL.
September 11, 2014 at 5:08 am
I now see how to use the formula so that the YoY variance is calculated in one cell. No need to bother with the hidden tables.
It is so lucky that you told me to put the expression in the table column rather than the parent group Year. I couldn't understand why the filter wouldn't evaluate the monNo < month(now).
You really helped me so much!!! Next time I need to do a much better job explaining the problem, ridding my opening explanation of typos, etc.
Many many thanks for getting me the YoY variance expression. This was part of a much larger report and was the only thing preventing it from being complete.
Done:-)
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply