hi Ella:
can you give a detailed sample to me?I am a fresher to the SSRS that used it to display the parent-child dimensions in matrix.this problem has already puzzled me for many days.thx.
from wing
Hi Wing,
I have already attached the detailed file to the blog post (look at the last paragraph “Sum Recursive Matrix”).
Hope that helps,
Ella
Hi Ella
I am having difficulties understanding this InScope function. I tried copying your code above and using it for navigation on matrix totals but I'm not winning everytime I click on the total it randomly select a cell and pass it as a parameter which then give me incorrect info.
may you please give an example of how to do this
many thanks
Hi Thato,
The formula is not meant for navigation but as an expression in a cell inside the matrix itself. Try to build an SSRS project in your BIDS and then download the RDL file I put at the end of the post, and attach it to the project.
Look at the file and that should explain it the best way.
All the best,
Exporting the report to Excel does not keep the visual hierarchy. Any ideas / suggestions to keep the + , -?
Thanks for this article.
Hi Sri,
From what I understand, this is a known problem with no workaround:
social.msdn.microsoft.com/.../5fe9d2e3-6e4d-4aac-bc2c-bf4f011e93ef
Also see:
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/29576945-a99a-482a-ba4b-a8639ceef31a
I read a few months back a post by Robert Bruckner saying that the InScope function isn't relevant
Hi Ella,
I'm having a problem with a Tablix in SSRS 2008.
I have:
On rows: 2 levels of hierarchy.
On columns: 2 levels of hierarchy.
Everything is with subtotals. It's something like this:
Year
M1 M2 M3 Totalyear
Category C1
Subcategory S1
S2
S3
C1Total
Category C2
C2Total
Overall Total
What i want to do, is to calculate a percent over the parent group, i.e:
in (S1,M1), i want ((S1,M1) / (C1Total, M1))
the same way, what i want in (C1Total, M1) is to calculate ((C1Total, M1) / (Overall Total, M1)).
So far, i've been able to calculate percent over the parent group, but the problem is that it calculates it over the total of the tablix, so it gives me back the same value for each month.
I've been reading about this and it sais that you can't calculate an aggregation function with 2 scopes (category and month, in this case).
I need to find another way to calculate what i want.
The data comes from a MOLAP cube in analysis services, so an alternative is to precalculate it there, but i haven't been able to do it.
Any help is welcome.
Thx to anyone who answers me. (i'm sorry about my english, but is not my native languaje).
Juan Ignacio
Hi Juan,
Indeed you are right and you cannot calculate an aggregation function with 2 scopes. If you’re willing to use a scope in MDX, you would need something along the lines of:
Create Member CurrentCube.[Measures].[Ratio S1M1 to C1M1]
As IIf
(
IsEmpty
[Measures].[Sales], [Category]
),
Null,
[Measures].[Sales], [Subcategory]
)
/
Format_String = "Percent" ;
Assuming your regular measure is called [Measures].[Sales Ratio] what you would need to do in MDX is also add a scope to replace the ratio calculated with the one you created yourself:
Scope
[Subcategory].Members,
[Date].[Fiscal].[Fiscal Months].Members,
[Measures].[Sales Ratio]
) ;
This = ([Measures].[Ratio S1M1 to C1M1]) ;
End Scope ;
Hope that helped,
Hi,
I am able to create "A Recursive Hierarchy Group". My problem is the data appears in this way
- A 8000
A1.1 5000
A1.2 3000
- B 17000
B1.1 8000
B1.2 9000
but I want the report to show the data in the below format
Please let me know if this is possible in Recursive hiearacial Groups ?
Thanks
Hi Ravi,
I regret to say that as far as I know that’s not possible.
Hi Ela,
Thanks for your answer,Please let me know if you come accross this.
Bye
Ravi
Hi ella, thanks for your answer, it works just fine! :)
Glad I could help :)
It's been three years that me and the blog have been together (truthfully, the date was on the 15
i have a small query in SSRS, which is quite big for me right now. Actually i hav to calculate the Progressive total in a Table. The situration is like:
Intial(Opening Bal) : 2000
S.No Cost Progressive_Cost
1 100 2100 (Opening Bal + Current Cost)
2 200 2300( Previous Progressive_Cost+cost)
3 300 2600(2300 + 300)
Kindly help me, How can i achieve that.I am using SSRS right now.I have also tried Previous() Function but thats not giving the desired Result
Hi Rohit,
First of all the opening balance should be also part of your dimension. It should look like:
S.No Cost Progressive_Cost0 2000 20001 100 21002 200 23003 300 2600
This way you can achieve progressive cost with the SSRS function of RunningValue. The function is located under Sommon Functions > Aggregate. For use of the function, please look at the following:http://msdn.microsoft.com/en-us/library/ms159136.aspxhttp://blog.stevienova.com/2007/07/02/ssrs-runningvalue-to-get-cumulative-totals/
All the best,Ella
does the sum recursive work in the sort expression in rs 2005? I get error.
Hi Djaif,
I don’t think that SSRS can compute the recursive subtotal (whose value is dependant per cell) and sort on it, all before rendering the report. If you want to sort on this expression, you’ll have to calculate all the values before hand in the dataset itself of the report.
with rs2000 I have sortexpression on the group witch contained a sum with recursive parameter
IIF((Parameters!SortDir.Value="Desc" AND Parameters!Sort.Value="Revenue"),Sum(FIELDS!RevTotal.Value, "table3_CustomerGroup", Recursive),0)
So rs did all the job sorting in recursive way every level in the hierarchy :
Parent1 1000
Parent 2 1500
Parent 1-2 700
Enfant1 300
Enfant2 400
Parent2-2 800
Enfant1 200
Enfant2 600
But in rs 2005 this expression results error!! Without the parameter recursive there is no error but the sort does not work on parents
I tried to implement it using the interactive sort new feature in rs2005. There is no error for the sort expression with recursive parameter but the sort does not work properly.
Clients families are grouped sing a key CustomerId, ParentCustomerId.
Thank you Daniele for the added information on Djaif's question!
no its not add information!!! I have exactly the same problem as Djaif. I dont know how to make it work using just features of RS2005!!!?
hi,
thanks for your solution!
i have also 1 problem about sum over colums.
column1 column2
50 30
100 60
sum col1: 150 sum col1:90
Grand Total = sum col1 + sum col2
i try to calculate this Grand total but RS 2008R2 don't want to summe the values. Please can you help me?
Ida
Daniele - as I have told Djaif before, I’m afraid I cannot assit you on the matter.
Ida – try creating another column which has an expression to sum column 1 + 2.
Sorry if it is a repeat, from the figure, What is the process or expression to add column “ “T and Column “ “S” Assuming it is matrix report and the columns are displayed based on the selection in the parameters. Say for example your param A (You selected “R,M,S,T”) every time your selection changes, but you always need to sum the last 2 columns.
Any suggestions are highly appreciated.
Thanks,
Devi
Daniele and Djaif – please look at the following link:
technet.microsoft.com/.../dd255193.aspx
Follow the steps described for "To sort values in a group, including the details group, for a Tablix". You should plant your expression in the sort expression for the group and that should work well enough.
Devi – just to make sure, are you asking how to sum the last two columns, never mind which 2 columns were chosen by the user?
I have an issue with SSRS 2008(not sure if its an issue). Our requirement is to have a recursive hierarchical report which i was able to crack through. However we also have a column grouping and when the recursive sum (rolling up sub total) is used, the values are being doubled.
Month1 Month2 Month3
Level 1 3 2 1
Level 2 2 0 0
Level 3 1 2 1
As per the above, I need to display hierarchical information month wise along with the subtotals rolling up. However recursive SUM double up the values.
Is there a way out to display accordingly. All the help is much appreciated. Thanks.
hi, thanks for the post. it really helped me a lot. just a quick question, i tried the solution below but it seems it's only showing 2 levels? I have a data that drill down upto 5 levels. please help. thanks
Hi Jennette,
The report can drill just as deep as the Parent – Child (or any recursive hierarchy for that matter) can go. But you can only define one parent and one child. If what you have isn’t a recursive hierarchy but rather a basic hierarchy with 5 levels, use the usual report grouping.
I'm new in the SSRS, and i'm having a problem creating a measure to do a apparently easy SUM with a condition!
Can someone help me?
What i have is:
[Dm Plano Contas].[Snc Cod].[1]
...
[Dm Plano Contas].[Snc Cod].[23]
[Dm Plano Contas].[Snc Cod].[231]
[Dm Plano Contas].[Snc Cod].[2311]
[Dm Plano Contas].[Snc Cod].[2312]
And what i want is to SUM all the childrens of the [Dm Plano Contas].[Snc Cod].[23] where the [Measures].[Saldo] is smaller than zero.
Hi Nuno,
From the example it looks like what you want is to check that measure for all the descendants of that member as the key is either 3 or 4 letters long (which I guess means different levels). Using MDX functions you can write something along the lines of:
Sum(YTD(), iif(([Measures].[Saldo], [Dm Plano Contas].[Snc Cod].currentmember) <0, [Measures].[Saldo], 0)
Create that sum as a new measure in your cube and use that as a measure for the recursion in SSRS I described in the post.
I would show in the report with just the relevant members as I don't see the logic in this measure for any member that isn't under [Dm Plano Contas].[Snc Cod].[32].
Please also look into the children, descendants and members MDX functions if they're at all relevant for you.
Hope that helped,Ella
how to do the average of average in a matrix .