How to show two datasets inside the same grouped tablix

Question

I have a dataset (list) and I grouped this data with SSRS. Now I also have a second dataset.

How can I show both datasets in the same grouped tablix.

I not sure I can use the Vlookup inside SSRS, because its a n to m connection.

Here is a similiar problem:

Here the data:

USE [test]
GO
/****** Object:  Table [dbo].[Tabelle1]    Script Date: 14.09.2021 21:03:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tabelle1](
    [Region] [nvarchar](50) NULL,
    [City] [nvarchar](50) NULL,
    [Sales] [int] NULL,
    [Produced] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Tabelle2]    Script Date: 14.09.2021 21:03:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tabelle2](
    [Region] [nvarchar](50) NULL,
    [Requested] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Tabelle1] ([Region], [City], [Sales], [Produced]) VALUES (N'West', N'Boston', 5000, 400)
GO
INSERT [dbo].[Tabelle1] ([Region], [City], [Sales], [Produced]) VALUES (N'West', N'Fransico', 100, 844)
GO
INSERT [dbo].[Tabelle1] ([Region], [City], [Sales], [Produced]) VALUES (N'Nord', N'New York', 1054, 5844)
GO
INSERT [dbo].[Tabelle1] ([Region], [City], [Sales], [Produced]) VALUES (N'Nord', N'Dallas', 15474, 11841)
GO
INSERT [dbo].[Tabelle1] ([Region], [City], [Sales], [Produced]) VALUES (N'Nord', N'Berlin', 1544, 44)
GO
INSERT [dbo].[Tabelle1] ([Region], [City], [Sales], [Produced]) VALUES (N'South', N'Austin', 154, 5481)
GO
INSERT [dbo].[Tabelle1] ([Region], [City], [Sales], [Produced]) VALUES (N'South', N'Birn', 1544, 4544)
GO
INSERT [dbo].[Tabelle2] ([Region], [Requested]) VALUES (N'West', 8411)
GO
INSERT [dbo].[Tabelle2] ([Region], [Requested]) VALUES (N'Nord', 1541)
GO
INSERT [dbo].[Tabelle2] ([Region], [Requested]) VALUES (N'South', 151)
GO
INSERT [dbo].[Tabelle2] ([Region], [Requested]) VALUES (N'Nord', 15444)
GO

score 1 · Accepted Answer · answered Sep 14 '21 at 16:30

If you are unable to combine your data in the query, there is a LookupSet function that will let you see the results from another dataset based on criteria. For your issue, the expression would be something like

=LookupSet(Fields!SCIENCE_FIELD.Value, Fields!SCIENCE_FIELD.Value, Fields!APPROVED.Value, "DATASET2")

This would get Science Field from your current table and lookup records in Dataset2 that have a matching Science Field and returns the values from the Approved field.

Unfortunately, SSRS doesn't let you SUM a LookUpset. There is some VB code for a SumLookup function that can be added in a report to SUM the results.

Need help in calculation using two Datasets using Expression SSRS

You would use the Lookupset above with the code in a expression like:

=Code.SumLookup(LookupSet(Fields!SCIENCE_FIELD.Value, Fields!SCIENCE_FIELD.Value, Fields!APPROVED.Value, "DATASET2"))

@MisterXAGE_ - you'll just need to get the code from the linked page, add it to your report, and add the expression to the text box. Done. — Hannover Fist, Sep 14 '21 at 18:52

How to show two datasets inside the same grouped tablix

1 Answers1