使用 SQL Server 报告服务在单个报告中使用两个数据集
我需要显示不同条件下同一组数据的报告。
- 我需要显示按地区、国家/地区和用户类型分组注册的用户数量，我已使用向下钻取功能来显示这一点并且工作正常。报告的数据还包括两个日期之间注册的用户数量。除此之外，我还必须使用相同的深入分析显示系统中的总用户数，即按地区、国家/地区和用户类型在单独的列中列出的总用户数以及每个计数（两个日期之间的用户数），
这样我的结果将是如下所示，
Region - Country - New Reg - Total Reg - User Type 1 - UserType2
+ Region1 2 10 1 5 1 5
+ Region2 3 7 2 4 1 3
在扩展区域后，它会像有
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
没有一种方法可以像这样显示我的报告，我尝试过使用两个数据集，一个包含条件数据，另一个包含非条件数据，但它不起作用，它总是 bing 总数所有注册列的注册用户
I need to show a report of same set of data with different condition.
- 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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除非我弄错了，否则您正在尝试创建一个具有不同分组级别的可扩展表？幸运的是，如果您知道在哪里查找，可以在 SSRS 中轻松完成此操作。您的示例中的总数似乎与用户列中的不匹配，所以我可能误解了这个问题。
对于初学者，设置查询以生成如下所示的单个数据集：
现在您已经得到了该数据集，您想要设置一个包含字段“NewReg”、“TotalReg”、“UserType1”和“UserType2”的新表。然后右键单击表行，然后转到“添加组>行组>父组”。在分组依据中选择“国家/地区”，然后单击“确定”。然后，重复此过程并选择“区域”。但这一次，勾选“添加组标题”框。这将在原始行上方插入另一行。
现在，对于每个字段（“NewReg”、“TotalReg”等），单击上面的新行并再次选择该字段。这会自动将 Sum(FieldName) 值添加到单元格中。这会将所有单独的行总计加在一起，并在您运行报表时显示一个按区域分组的新行。
这应该会为您提供所需的表，并正确聚合数据，因此您所需要做的就是按需管理显示/隐藏详细信息行。
为此，请选择详细信息行（原始行）并右键单击“>行可见性”。将其设置为“隐藏”。现在，选择包含“区域”的单元格，并使用“属性”记下其 ID（现在，我们假设它称为“区域”）。单击返回详细信息行并查看属性窗口。在底部您会看到“可见性”设置。在那里，将“InitialToggleState”设置为 False，并将“ToggleItem”设置为区域组单元格的名称（即“区域”）。
现在剩下的就是进行格式化等操作并进行整理。
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:
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