0

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")

enter image description here

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

enter image description here

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: enter image description here

Output looks like this: enter image description here

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
Bonnie
  • 7
  • 2
  • 1
    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 Apr 13 '21 at 20:13
  • 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 Apr 13 '21 at 20:54
  • 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 Apr 13 '21 at 20:56
  • 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 Apr 13 '21 at 21:08
  • 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 Apr 13 '21 at 22:26
  • Add some sample data from the two tables. It is not clear from your query which field is related to each table. Do you have a table with all names? – niktrs Apr 14 '21 at 05:11

1 Answers1

1

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

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • I agree to the approach. It has to be solved in the query part and remove lookups. For an missing value he has just to outer join with the related table. – niktrs Apr 14 '21 at 05:15
  • Thanks!!! I just tried it in SSRS and that worked! Genius! – Bonnie Apr 14 '21 at 16:24
  • Glad to help Bonnie. Could you please mark the answer as accepted (the check mark under the vote up/down buttons to the left of the question). Thanks – Alan Schofield Apr 14 '21 at 20:48