Hello,
I cannot seem to get my Report Setup Parameters for this SQL Query to work when I run the actual Report in Tessitura (this is a BIDS Custom Report). Here is the query:
(
@list_no int = null,
@season_no varchar(100) = null
)
AS
BEGIN
SET NOCOUNT ON;
Declare @season Table (season_no int null, description varchar(100)null)
Insert into @season (season_no, description)
Select id, description
From [dbo].VRS_SEASON
Where charindex(',' + convert(varchar, id) + ',' , ',' + @season_no + ',') > 0
SELECT Distinct
a.customer_no,
a.order_no,
e.fname,
e.lname,
s.description AS Semester,
j.description AS Department,
i.description AS Class,
b.recipient_no AS Student_ID,
a.event_date AS Payment_Date,
d.tot_pur_amt AS Purchase_Amount,
c.fee_amt_paid AS Fee_Amount_Paid,
a.sli_paid_amt AS Transaction_Amount
FROM T_ORDER_SEAT_HIST a
JOIN T_SUB_LINEITEM b on a.sli_no = b.sli_no
JOIN T_SLI_FEE c on a.customer_no = c.customer_no
JOIN T_SLI_DETAIL g on a.sli_no = g.sli_no
JOIN T_LINEITEM d on b.li_seq_no = d.li_seq_no
JOIN T_CUSTOMER e on a.customer_no = e.customer_no
JOIN T_PERF p on a.perf_no = p.perf_no
JOIN VS_PROD_SEASON_LIST x on p.prod_season_no = x.prod_season_no
JOIN T_INVENTORY i on p.perf_no = i.inv_no
JOIN T_INVENTORY j on x.prod_season_no = j.inv_no
JOIN VRS_SEASON s on p.season = s.id
WHERE a.price_type IN (225,219,220,171,172,175,115,173,174,114,226,276)
and c.fee_no IN (2,5,7,9)
and a.event_code IN (3,11,25,26)
END
And in Report Setup all I need to do is to be able to select a Season and have the Custom Report then be filtered by that Season when I run it in Tessitura. But I've tried this 80 different ways with no luck. I've tried changing it to a season_str, season_no, id, etc. And no matter what I do while the report will run it will NOT filter by the Season selected. What do I need to do in order to get this to correctly work?
To anyone who is able to offer some insight/help - thank you!
Hi Jolene,
Good day.
There are two places need parameters.
Inside SSRS report, you need to put filter for @season_no,
also in report setup for Tessitura, you need put filter for @season_no.
1, in SSRS, you need to create a Dataset for @season_no
something like this:
SELECT id, descriptionFROM tr_seasonWHERE (id in (select id from tr_season where fyear>2015))ORDER BY id DESC
then set season_no parameter based on this dataset.
2, In Tessitura report setup,
Table name: vrs_season,
display name: Description
data column: id
where clause: fyear>2015
===
if your SSRS report has multiple values parameters, then whatever you selections in Tessitura, they cannot be passed to your SSRS report.
Only single value parameters in Tessitura can pass through to SSRS report.
have fun
Ben