July 10, 2017 at 11:10 am
I need some expression to calculate the YTD highlighted in yellow.
its calculating the difference between dec of previous year and current month.
If i am at March 2015 then it should do- (march 2015-dec 2014)/dec 2014.
i have 2 matrix .first matrix has Country,Type as row group. Date as column group. Names- BB1,BB2,Ac1,Ac2 are the values in the column.
2nd matrix is calculating the Totals. This matrix has Type as row group and Date as column group.I am calculating the Totals 1 and Totals 2 at the run time only.
Can someone please help with the YTD expression.Thanks.
July 10, 2017 at 2:05 pm
Papil - Monday, July 10, 2017 11:10 AMI need some expression to calculate the YTD highlighted in yellow.
its calculating the difference between dec of previous year and current month.
If i am at March 2015 then it should do- (march 2015-dec 2014)/dec 2014.i have 2 matrix .first matrix has Country,Type as row group. Date as column group. Names- BB1,BB2,Ac1,Ac2 are the values in the column.
2nd matrix is calculating the Totals. This matrix has Type as row group and Date as column group.I am calculating the Totals 1 and Totals 2 at the run time only.
Can someone please help with the YTD expression.Thanks.
I'm not sure you can consistently compute those values in the Matrix. It would require knowing what date is in all the previous columns, and it's just not realistic to expect SSRS to do that kind of thing. I'd be happy to be proven wrong, but I'm doubtful it can be done. This is one of those scenarios that you may need to use Excel to pull the data from an ODBC data source, and then compute it within Excel. You'll likely end up creating a template spreadsheet and then either copy the data out of SSMS and compute with formulas in Excel, or use an ODBC Data Source within Excel to pull down the data.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2017 at 2:31 pm
What if you did it using PowerPivot and DAX. There are functions for that. CLOSINGBALANCE stuff, etc. Is that an option? What did you intend to do with the data. PowerPivot and PowerBI are good for exploring and visualizing the data.
July 10, 2017 at 3:07 pm
pietlinden - Monday, July 10, 2017 2:31 PMWhat if you did it using PowerPivot and DAX. There are functions for that. CLOSINGBALANCE stuff, etc. Is that an option? What did you intend to do with the data. PowerPivot and PowerBI are good for exploring and visualizing the data.
I need to do this in SSRS itself . I have created whole of that report in SSRS and its just YTD left to be done for now. Cant switch to Power BI/Powerpivot. Is it not possible to get this built in SSRS itself?
July 12, 2017 at 10:14 am
Papil - Monday, July 10, 2017 3:07 PMpietlinden - Monday, July 10, 2017 2:31 PMWhat if you did it using PowerPivot and DAX. There are functions for that. CLOSINGBALANCE stuff, etc. Is that an option? What did you intend to do with the data. PowerPivot and PowerBI are good for exploring and visualizing the data.I need to do this in SSRS itself . I have created whole of that report in SSRS and its just YTD left to be done for now. Cant switch to Power BI/Powerpivot. Is it not possible to get this built in SSRS itself?
Any suggestions on how it can be done in ssrs?
July 12, 2017 at 11:38 am
Only way I can think of getting this to work would be to add a running total in your query.... if that's not an option, then this could get ugly.
July 12, 2017 at 12:11 pm
pietlinden - Wednesday, July 12, 2017 11:38 AMOnly way I can think of getting this to work would be to add a running total in your query.... if that's not an option, then this could get ugly.
I am doing the running total in the SSRS itself not in the query using the running value function.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply