Subreport or multiple data sets?

deanlee17
deanlee17 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Author

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.

Author

Commented:
In the image 'sub query'  should be 'sub report'
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Author

Commented:
I will look into that. Apologies for the wrong topic

Author

Commented:
Here is how the report should look....

layout.png
Thanks
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
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

Author

Commented:
Thank you Arifhusen Ansari, excellent answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial