Using two datasets in a single report using SQL server reporting service

I need to show a report of same set of data with different condition.

1. I need to show count of users registered by grouping region, country and userType, I have used drill down feature for showing this and is working fine. Also the reported data is the count of users registered between two dates. Along with that I have to show the total users in the system using the same drill down that is total users by region, country and usertype in a separate column along with each count (count of users between two date)

so that my result will be as follwsinitialy it will be like

   Region  - Country - New Reg - Total Reg - User Type 1 - UserType2

 + Region1              2          10         1    5        1    5

 + Region2              3           7         2    4        1    3

and upon expanding the region it will be like

  Region  - Country - New Reg - Total Reg - User Type 1 - UserType2

 + Region1              2          10         1    5        1    5
            country1    1           2         1    2        -    -

            country2    1           8         1    8        -    -


 + Region2              3           7         2    4        开发者_运维百科1    3

Is there a way I can show my report like this, I have tried with two data sets one with conditional datas and other with non conditional but it didn't work, its always bing total number of regiostered users for all the total reg columns

---

Unless I'm mistaken, you're trying to create an expandable table, with different grouping levels? Fortunately, this can be easily done in SSRS if you know where to look. The totals on your example don't seem to match up in the user columns, so I may have misunderstood the problem.

For starters, set up your query to produce a single dataset like this:

Region  Country      New Reg - Total Reg -  User Type 1 - User Type 2
Region1 country1    1              2            1   
Region1 country2    1              8            1   
Region2 country3    2              4            1              1
Region2 country4    1              3            1   

Now that you've got that, you want to set up a new table with the fields "NewReg", "TotalReg", "UserType1" and "UserType2". Then right-click the table row, and go to "Add Group > Row Group > Parent Group". Select "Country" in the Group by and click okay. Then, repeat this process and select "Region". This time however, tick the "Add group header" box. This will insert another row above the original.

Now, for each of your fields ("NewReg", "TotalReg" etc), click in the new row above and select the field again. this will automaticaly add a Sum(FieldName) value into the cell. This will add together all the individual row totals and present a new, grouped by region row when you run the report.

That should give you the table you require with the data aggregated correctly, so all you need to do is manage the show/hide the detail rows on demand.

To do this, select your detail row (the original row) and right-click "> Row visibility". Set this to "Hide". Now, select the cell that contains the "Region" and take note of its ID using Properties (for now, let's assume it's called "Region"). Click back onto your detail row and look at the properties window. At the bottom you'll see a "Visibility" setting. In there, set "InitialToggleState" to False and "ToggleItem" to the name of your region group's cell (i.e. "Region").

Now all that should be left is to do the formatting etc and tidy up.

---

I have solved this problem by taking all the records from DB and filtering the records to collect new reg count by using an expression as following

=Sum(IIF(Fields!RegisteredOn.Value  >Parameters!FromDate.Value and  Fields!RegisteredOn.Value  < Parameters!EndDate.Value , 1,0))