Subreport or multiple data sets?

Hi guys, the title says it all really. I have a main query which returns 5 Input Groups, each input group can have multiple Voting Groups, each voting group can have multiple Voting Setpoints and each Voting Setpoint can have multiple Inputs. Should I use reports inside reports inside reports, or can i achieve this with a main report and multiple datasets in some way?  Obviously I don't want blank areas where (for example) Set points do not exist So you have Input Group > Voting Group > Inputs

An example structure could be:

- Input Group 1
      - Voting Group 1
            - Voting Setpoint 1
                    - Input ID's
             - Voting Setpoint 2
      - Voting Group 2
- Input Group 2

Thanks,
Dean
deanlee17Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike McCrackenSenior ConsultantCommented:
With Crystal you should be able to create groups on each of the fields then finally use the detail section for the INPUT IDs.

What reporting tool are you using?

mlmcc
deanlee17Author Commented:
Sorry I should have stated, SSRS. I may have over complicated things before. Please see image below. At times the sub query may only return 1 Input Group, other times there may be 5. How do I achieve this? My sub reports so far have only returned one query line (so to speak)

Explanation
Thanks,
Dean.
deanlee17Author Commented:
In the image 'sub query'  should be 'sub report'
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Read Article
Mike McCrackenSenior ConsultantCommented:
I don't use SSRS so I don't know how to handle it there.

Does it have a grouping like capability (sections perhaps?)

mlmcc
deanlee17Author Commented:
I will look into that. Apologies for the wrong topic
deanlee17Author Commented:
Here is how the report should look....

layout.png
Thanks
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Hi deanlee17,

I developed the sample report to solve your concern.  As you told that if data is not available it should not occupy the space. If can be done as the child group level but not at parent. Because if you are displaying the data for parent it will take a space at least for that row.

You don't need to create a separate data set for each group. Instead you can have your data in the same query and add the groups on report side.

Set the visibility of groups and rows based on the condition you have.

I took an example from the Adventure works database. It will be like.

Customer
    SalesOrderHeader
       Sales OrderDetail
          Product Detail.

Get all the data in single query and hence the single data set only.

1 ) Add your leaf data from your hierarchy in the Tablix. My case it's Product in your case it would be "Input IDS".
2) Add the parent level group for my case it will be "Sales OrderDetailID". While adding the Group select the "Add Group header" Check box.Please refer screen shot "Group Property.ong"
3) Set the Visibility of the Row as a toggle for "Sales OrderDetailID". when user click on that row then only Data will be visible. Please refer screen shot. "Set Toggle Visibility.png"
4) Now add Parent group on "Sales OrderDetailID" my case it will be "SalesOrderID".
   Repeat the Step 2 For the same.
5) After group has been added. You have to set the Visible property as toggle for the group add on "Sales OrderDetailID" and set the toggle for "SalesOrderID". So when user click on the "SalesOrderID" then only "SalesOrderDetail" will be visible. Refer Screen shot "SalesOrderDetail Group Visibility.png"
6) Repeat same to add the parent group on "SalesOrderDetail" and add the group on "Customer ID".
And also set group visible toggle property as per step 5.

7) You can find the blank space for all those Customer who didn't order any thing. To hide this space you have to set the row visible property so if data is available then only Row will occupy the space.

You have to do for only "SalesOrderID" group rows and "SalesOrdeDetailID" Row. Not for product because for product we did it already step 3.
RIght click on the row for "SalesOrderID" Group and set the property
For that refer screen shot. "SalesOrder Row Visibilily.png". Repeat same for "SalesOrderDetail" row.

I have attached the Zip with all the details and RDL file as well.

Hope it will help you. Let me know in case any question.
Multiple-level-Group-In-Report-Group.zip

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
deanlee17Author Commented:
Thank you Arifhusen Ansari, excellent answer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.