I think you need to change this code inside the script task:

from tablea a, tableb b

where a.familyid = b.familyid

and b.groupid = [user::GroupID]

to this:

from tablea a, tableb b

where a.familyid...

I have modified the code slightly to look for recurrencetype = 1 rather than inactiveflag

SELECT

C.Name

...

If I am understanding you correctly, all three parameters are independent, so if a user selects "blah" for @resource and "all" for @partno, you only want the report to return...

If you need to run the pivot directly from your source table without using either a cte or #temp table, you need to somehow add the row number into the...

Curse my slow typing

:blush:

I think this does what you want it to

I changed the datatype of CCAC to integer and then ran the data into a temporary table to make use of the...

Hi

A couple of questions:

If the same CCAC value is entered more than once should it only be displayed once in the output?

e.g.

For LocalID 97445 I get an output of 3...

This should work:

select [User], cast(AvgMins/60 as varchar(10)) + ':' + right('00' + cast(AvgMins%60 as varchar(10)), 2)

from

(Select owner as , sum(fix_time/60)/COUNT (*) as AvgMins

from opencall

where suppgroup = 'WEBS'and date_time between (@StartDate)...

Aha, I was thinking that local #tmp tables don't have statistics generated for them, but I was getting confused with table variables

That solves the potential issue with concurrent users then

🙂

Fair enough, I guess the overhead of having an IF to decide whether or not to build the table, and then another IF to tell the query whether to look...

Would you make the creation of the temp table conditional so you only create it if the passed in parameter contains a ',' character?

(I am assuming here that the...

Using the DelimitedSplit8K function that The Ninja provided the link to, you would need to change the where clause in the sp from

DIRECTION IN (@direction)

to

DIRECTION IN (select item from...

Oops, that'll teach me for replying without reminding myself what the original question was about!

The Ninja is right, SSRS will split the multiple parameter correctly into individual values but it...

I don't think it sounds like the cascade that is the problem, otherwise you wouldn't be able to select values for all the parameters

Have you set up the parameters to...

If you can move the date range to the parameters for either the whole report or this specific dataset you could do something like this in the sql query

select sum(qty_to_ship)

from...