ssrs two data sources into 1 tablix and summary row

Martinko6 21 Reputation points
2022-06-21T10:02:00.667+00:00

Hello, I have one question. I have 2 DataSources because of two databases in different servers, I uses 2 DataSets and I put data into 1 tablix. I use Lookup function and it works fine. But I need to add summary row in this tablix and there I have a trouble.
Let me explain it in one easy example:

----------------------------

  1. dataset (master) return :

Id, PrizeMoney
1, 200
2, 300

  1. dataset (child) return

Id, PrizePool
1, 500
2, 1500
4, 800
5, 600

Final Table I want to is:

Id, PrizeMoney, PrizePool
1, 200, 500
2, 300, 1500

------------

Total: 500, 2000

But instead 2000 I have 3400.

Is there any way how I can solve it? Maybe it is easy and I don´t see it....
Thank you
Monika

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
{count} votes

Accepted answer
  1. Isabellaz-1451 3,611 Reputation points
    2022-06-22T01:25:28.1+00:00

    Hi @Martinko6

    I did a local test,here is my preview result:
    213623-image.png

    My table is like this:
    213641-image.png

    the 2 expression I use :
    =Lookup(Fields!id.Value, Fields!id.Value, Fields!pricepool.Value, "DataSet2")
    =Sum(Lookup(Fields!id.Value, Fields!id.Value, Fields!pricepool.Value, "DataSet2"))

    Hope this can help you.

    Best Regards,
    Isabella


    If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

0 additional answers

Sort by: Most helpful