SSRS - Multiple tablix for multiple values passed in parameter

Question

So, I've posted a question few days ago here where I had a @Sem_ID parameter and I had to pass multiple IDs separated by a delimiter. I found solution but it only works when I have one dataset. The problem now is that my report has 3 tablixes and 3 datasets, one dataset for each tablix.

What I tried: I created a a tablix and inserted a rectangle element inside row. I moved a grid inside the rectangle and then tried running the report but received an SSRS error while previewing: The Value expression for the text box ‘Textbox10’ refers to the field ‘Kondition’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.

For example, each of these two queries represent a dataset, so two datasets:

SELECT Sem_ID, Sem_KW, Sem_Jahr, Sem_SemNrKata, Sem_von, Sem_bis, h.chr_name, h.chr_ort
FROM acc_seminar.t_Seminar
JOIN dbo.tbl_hotel h ON Sem_Ht_ID=h.pk_hotel
WHERE Sem_ID IN (SELECT value  
FROM fn_Split(@Sem_ID, ';')  
WHERE RTRIM(value) <> '') AND (Sem_SemTh_ID != 76 AND Sem_Sto = 0)

and second dataset:

SELECT tu.TU_Name AS Name, tu.TU_Vorname AS Vorname, ParkgebuehrBrutto AS Preis
FROM acc_seminar.t_Teiluber_Ext tux
JOIN acc_seminar.t_Teiluber tu ON tux.TeiluberId = tu.TU_ID
JOIN acc_seminar.t_Seminar s ON tu.TU_Sem_ID = s.Sem_ID WHERE s.Sem_ID IN (SELECT value  
FROM fn_Split(@Sem_ID, ';')  
WHERE RTRIM(value) <> '')  AND ParkgebuehrBrutto IS NOT NULL

So if I pass a @Sem_Id = 123;456 , how do I then show two grids for Id=123 and then for Id=456 ?

The error you're receiving has nothing to do with the datasets you're showing here. The error is related to a field called Kondition which isn't shown in either query. Where do you have a field called Kondition? — Steve-o169

AnkUser AnkUser · Accepted Answer · 2019-04-12T07:49:14

Few Observations: You cannot move another Tablix for Dataset1 to Tablix (Rectangle) Dataset2. They are different. You could use SubReport wherein Inside Tablix 1 you have SubReport which uses Dataset2 and shows it's data. Second:

Your Use Case can be very well solved with Sub-reports. How?? Pass your Sem_ID as parameter to SubReport provided your Dataset2 (subreport) does conatin Sem_Id field.

Few Links for help http://t-xie.net/2016/05/17/ssrs-pass-multiple-value-parameter-to-sub-report/ https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/dd220581(v=sql.105)

SSRS - Multiple tablix for multiple values passed in parameter

1 Answers