Lookup using textbox value as source showing too many rows + SSRS

Question

I did a lookup function in ssrs where the source is a reportItem reference. I want to return the value from the table that I am looking up based on the reportItem reference. The report is retrieving the correct values, but and I'm getting repeated rows and I'd like to know if there's a way to eliminate that. My parameters in the tablix is based on a ticket number.

The underlying data has 3 transactions but 9 rows are currently being returned.

In SSRS, my query is:

Select 
ticketno, name, control, value 
from ticket a 
inner join details b on a.ticketno = b.ticketno
where control like 'LS%' and ticket = 'ED08'

The return result contains 4 rows transactions ie:

Ticket	Name	Control	Value
ED08	Eng	LS1	A
ED08	Acct	LS2	B
ED08	Med	LS3	C

In SSRS, I used a table and hard coded the Name as it's possible that there will be no values. I hard coded Eng, Acct, Med, Dent for names.

I entered an expression on each individual row with an expression

=lookup(ReportItems!textbox.Value,Fields!Name.Value,Fields!Value.Value, "UDF_Det")

However, when I run the report, I get extra rows.

The transactions retrieved from the ticket in SQL only retrieved 3 rows, so I would have expected that. Is there a way to filter on row specific data?

I have looked at this post Adding values to a Report when there is no Data in query SSRS but since I am not doing any calculations I'm not sure why I am getting repeat rows.

My design looks like this:

Output looks like this:

Can you share your report design and the output from both your datasets. The reason you are getting repeated rows (I am guessing) is that you have 4 rows in your 'details' rowgroup, this details row group will be repeated for each row returned (3 rows as you said) so you see each three times. I suspect you might have to rethink the structure of the datasets. Is there a reason you cannot join the datasets into a single dataset query? — Alan Schofield
Thanks Alan. Part of the problem is that I can have no data for certain items and that's why I am hard coding all the items I need. Not sure how I would run that in the SQL query. — Bonnie
I understand,, but if you show the output from the datasets and expected output then I'm sure somebody will be able to help. — Alan Schofield
Thanks Alan, I just updated my design and output. Hope that's what's needed. I'm expecting that the output would look the same as the design. Hope this is what you're asking? — Bonnie
can you show the entire design, including the group pane below the main design window and also show the dataset output, the raw data, from both your datasets (I am assuming two?). Finally, is there a reason that you cannot join the datasets in a single query? — Alan Schofield

Alan Schofield Alan Schofield · Accepted Answer · 2021-04-13T22:36:19

Acually, now I've edited your question I understand the problem. :)

You can just get a list of Name and left join from it to your existing query.

You maybe able to get the list from an existing table (hopefully) or you could hardcode one (avoid if possible).

Assuming all the Names you need are in your ticket table you could use something like this...

SELECT DISTINCT [Name] FROM ticket

(if name comes from another table, just change the query to suit) then left join your existing query to this, something like

SELECT n.[Name], t.ticketno, t.control, t.value 
    FROM (SELECT DISTINCT [Name] FROM ticket) n
        LEFT JOIN (
                  Select ticketno, name, control, value 
                      from ticket a 
                      inner join details b on a.ticketno = b.ticketno
                      where control like 'LS%' and ticket = 'ED08'
                 ) t 
                 ON n.[Name] = t.[Name]

which should give you something like

Name	Ticket	Control	Value
Eng	ED08	LS1	A
Acct	ED08	LS2	B
Med	ED08	LS3	C
Dent	NULL	NULL	NULL

Then you can simply have one row in the detail group in your table to output the results.

If this does not help, post some sample data from your database tables and show the full report design including row groups etc

Lookup using textbox value as source showing too many rows + SSRS

1 Answers