yaay. First Comment. You are an amazing teacher Andrew. I learned VBA and SQL literally watching your video. I am glad and super happy to see you after a long time.
05:29 Creating a recursive group 10:01 Cell padding 13:20 Other formatting 14:18 Background colour 16:32 Font properties 18:24 Collapsing-expandable groups
Hello, In my report, I need to display the income statement report. This means I need to go through the indicators to take the general accounts that have a result, and then I look on the balance sheet screen for the parent of the result. There are 6 levels, meaning if the result retrieved from the indicators is among the code category 6, I need to retrieve all 5 parent categories. The same applies to all the general accounts. After that, I need to display them in a hierarchical form, grouping them with the categories. so I need help with setup in SSRS to properly display children in multiple hierarchy report. Can you help me. Thank you.
This is extremely helpful, thank you! What if I want to make aggregations on the hierarchy groups? For example in the video, what if I want to show the count of students at each level such that Gryffindor has 3 students, Ravenclaw has 4, etc., and at the level of Hogwarts it was showing 30 (or however many there are). Other aggregations I'm interested in are: average and sum. Is there a way to do this? It seems like the built-in recursive functionality doesn't actually group things together, it just displays them from the root to the branches.
Hi Jared! Most aggregate functions in SSRS support a Recursive parameter. So, if you want to count the number of items at each level of the hierarchy you could insert a column into the table and add an expression like this: =CountRows("Details", Recursive) "Details" is the default name of the row group so you'll need to change this if you've assigned a different name to the row group. Note that the count includes the current level as well as the number of children so you'll need to subtract 1 from the result to see the count of only child elements. Getting a Sum is similar - imagine there was a field called ExamScore: =Sum(Fields!ExamScore.Value, "Details", Recursive) This gives the total score for the current level and all child elements. If you want to exclude the value for the current level (in this example it would mean that the houses had exam scores) you can subtract it like so: =Sum(Fields!ExamScore.Value, "Details", Recursive) - Fields!ExamScore.Value You'd probably want to use the Level() function to test whether or not to subtract the current level value (otherwise all the lowest level child elements will subtract their own value and show 0). You can also specify the Recursive parameter for the Avg function. If you want to return the average for only the child elements though, you may be better off combining the techniques described above to take the sum of all the child elements and divide this by the count of only the child elements. =(Sum(Fields!ExamScore.Value, "Details", Recursive) - Fields!ExamScore.Value) / (CountRows("Details", Recursive) - 1) Again, you'll want to use the Level() function to test which expression to apply to the lowest level child elements. I hope that helps!
Hi Jared, thought you'd like to know that I've added a video to answer this question in more detail ruclips.net/video/1oDvO0ouwfM/видео.html It's only available to channel members with the early access perk at the moment!
@@WiseOwlTutorials Can you also show this when we have column parent child groups for aggregate functions? I am summing up amount for 3 level recursive groups and Years month break up in columns.
Hi Jared, How can I sum amount in Matrix kind report with recursion. I have rows detail parent child level recursion upto 3 levels and columns parent child grouping to Month and Years level total? Any help will be much appreciated .
Another great tutorial but it would be great if you provide the link to show us where we can download the file? I can't find it on your site and also, have you looked at my other post about the server name error for reporting server?
Hi Lee! The link for the file is in the video description - it takes you to the page on the Wise Owl website where you can download the file. I've just had a look for your previous comment but I don't have any ideas why that might have happened!
@@WiseOwlTutorials Is this the link to download the file? However, it is confusing to know where to go to find the file... must I register first? www.wiseowl.co.uk/report-builder/videos/report-builder-2016/recursive-table-groups/
@@WiseOwlTutorials I know it is possible to arrange the row groups in a way that you could expand the rows upwards instead of downwards. But I doubt it would be possible to do it using recursive groups right?
![Felix "Unfair" | [Stray Kids : SKZ-PLAYER]](http://i.ytimg.com/vi/Oswujxm2Ag0/mqdefault.jpg)
yaay. First Comment. You are an amazing teacher Andrew. I learned VBA and SQL literally watching your video. I am glad and super happy to see you after a long time.
05:29 Creating a recursive group
10:01 Cell padding
13:20 Other formatting
14:18 Background colour
16:32 Font properties
18:24 Collapsing-expandable groups
Hello,
In my report, I need to display the income statement report. This means I need to go through the indicators to take the general accounts that have a result, and then I look on the balance sheet screen for the parent of the result. There are 6 levels, meaning if the result retrieved from the indicators is among the code category 6, I need to retrieve all 5 parent categories. The same applies to all the general accounts. After that, I need to display them in a hierarchical form, grouping them with the categories.
so I need help with setup in SSRS to properly display children in multiple hierarchy report.
Can you help me.
Thank you.
This is extremely helpful, thank you! What if I want to make aggregations on the hierarchy groups? For example in the video, what if I want to show the count of students at each level such that Gryffindor has 3 students, Ravenclaw has 4, etc., and at the level of Hogwarts it was showing 30 (or however many there are). Other aggregations I'm interested in are: average and sum. Is there a way to do this? It seems like the built-in recursive functionality doesn't actually group things together, it just displays them from the root to the branches.
Hi Jared! Most aggregate functions in SSRS support a Recursive parameter. So, if you want to count the number of items at each level of the hierarchy you could insert a column into the table and add an expression like this:
=CountRows("Details", Recursive)
"Details" is the default name of the row group so you'll need to change this if you've assigned a different name to the row group. Note that the count includes the current level as well as the number of children so you'll need to subtract 1 from the result to see the count of only child elements.
Getting a Sum is similar - imagine there was a field called ExamScore:
=Sum(Fields!ExamScore.Value, "Details", Recursive)
This gives the total score for the current level and all child elements. If you want to exclude the value for the current level (in this example it would mean that the houses had exam scores) you can subtract it like so:
=Sum(Fields!ExamScore.Value, "Details", Recursive) - Fields!ExamScore.Value
You'd probably want to use the Level() function to test whether or not to subtract the current level value (otherwise all the lowest level child elements will subtract their own value and show 0).
You can also specify the Recursive parameter for the Avg function. If you want to return the average for only the child elements though, you may be better off combining the techniques described above to take the sum of all the child elements and divide this by the count of only the child elements.
=(Sum(Fields!ExamScore.Value, "Details", Recursive) - Fields!ExamScore.Value) /
(CountRows("Details", Recursive) - 1)
Again, you'll want to use the Level() function to test which expression to apply to the lowest level child elements.
I hope that helps!
Hi Jared, thought you'd like to know that I've added a video to answer this question in more detail ruclips.net/video/1oDvO0ouwfM/видео.html
It's only available to channel members with the early access perk at the moment!
@@WiseOwlTutorials Can you also show this when we have column parent child groups for aggregate functions? I am summing up amount for 3 level recursive groups and Years month break up in columns.
Great lesson. Thanks Andrew. For me it only shows till 3 levels even though my data goes to 10 levels. Any idea what I could be doing wrong?
Hi Jared, How can I sum amount in Matrix kind report with recursion. I have rows detail parent child level recursion upto 3 levels and columns parent child grouping to Month and Years level total? Any help will be much appreciated
.
Another great tutorial but it would be great if you provide the link to show us where we can download the file? I can't find it on your site and also, have you looked at my other post about the server name error for reporting server?
Hi Lee! The link for the file is in the video description - it takes you to the page on the Wise Owl website where you can download the file. I've just had a look for your previous comment but I don't have any ideas why that might have happened!
@@WiseOwlTutorials It has two server names though and it says invalid class...
is there a repair option for it?
@@WiseOwlTutorials Is this the link to download the file? However, it is confusing to know where to go to find the file... must I register first? www.wiseowl.co.uk/report-builder/videos/report-builder-2016/recursive-table-groups/
I still can't find the file.. it is a bit confusing to navigate to the right page as I don' see any download contents
Would this work the same way with matrix?
Hi Enmanuel! Yes, you can create recursive row groups and column groups in a matrix.
@@WiseOwlTutorials I know it is possible to arrange the row groups in a way that you could expand the rows upwards instead of downwards. But I doubt it would be possible to do it using recursive groups right?
wow
17:41 iif
4:20 create dataset
i have a requirement like below how can i achieve
colum1 colum2 colum3 COLUM4 colum5 colum6
A 1 A1 AA1 Q11 10
A 1 A1 AA1 Q11 11
A 1 A1 AA2 Q11 12
A 1 A1 AA2 Q14 13
B 2 B1 BB1 W11 200
B 2 B1 BB1 W12 201
B 2 B1 BB2 W13 202
B 2 B1 BB2 W14 203
COLUMN1 A COLUMN2 1 COLUN3 A1
COLUM4 colum5 colum6
AA1 Q11 10
11
12
AA2 Q14 13
IN NEW PAGE
COLUMN1 B COLUMN2 2 COLUN3 B1
COLUM4 colum5 colum6
BB1 W11 10
W12 11
12
AA2 Q14 13
how can i find hogwarts database
Hi, you can find a link in the video description.
N