Adding a blank line after a group of rows

I have an rdl report that shows rows group by category. I would like to include a blank line after each set of categories. Can anyone point me to how I would do that in the rdl.

thanks

idriss

Hi,

Add a blank Group Footer and increase the height of it as per your requirements.

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Add sum column to query

What's the best way to include an amount sum in a query if I also need the individual amounts? For example, I need the following columns:

order number

order amount

total amount

I tried using "with cube ", but the total number of columns in the query exceeds the allowable limit of 10.

select a.ordernum, a.orderamt, b.totalamt
from orders a, (select ordernum, sum(orderamt) from orders group by ordernum) b
where a.ordernum = b.ordernum

This will return data like:

ORDERNUM ORDERAMT TOTALAMT
1234 12.33 56.23
1234 11.25 56.23
1234 32.65 56.23
2345 ...ETC...|||

Hello,

Can you list the schema and an example of required output briefly as I'm not 100% sure what you want to do: do you want to select an order amount and sum what amount?

Sounds as if you should be using a subquery/join.

Cheers,

Rob

|||

to complete Phil's answer, I think this should be a little more efficient to do this

SELECT a.ordernum, a.orderamt, b.totalamt, c.totalamt...
FROM orders a
inner join (select ordernum, sum(orderamt) from orders group by ordernum) b on a.ordernum= b.ordernum
inner join (select ordernum, sum(orderamt) from orders group by ordernum) c on a.ordernum= c.ordernum

|||So you've just added a duplicate total column. Why?|||

Could it look something like this?

create table #x( ordernum int not null, amt int not null )
insert #x
select 1, 20 union all
select 1, 15 union all
select 2, 35 union all
select 2, 10 union all
select 2, 70
go

select ordernum, amt, sum(amt) as total
from #x
group by ordernum, amt
with rollup
go

ordernum amt total
-- -- --
1 15 15
1 20 20
1 NULL 35
2 10 10
2 35 35
2 70 70
2 NULL 115
NULL NULL 150

(8 row(s) affected)

/Kenneth

|||I tried using With Cube and With rollup, but they have a limit of 10 on the number of columns returned. The actual report I'm writing has over 20.|||So what are you trying to achieve?

Give us an example output of what you'd like the data to look like.|||I think your first post will solve the problem. Haven't had time to try it yet.|||

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

|||

stephane - Montpellier wrote:

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

Your statement won't work there either. You didn't include ordernum in your subquery.

Never-the-less, inner join and the join method I used are identical.

Add scripttask script programmatically

I'm building packages programmatically. I need to add a ScriptTask to my package and include the script code. The script task itself is easy to add. But I can't figure out how to add the script code to the task.

I found one post in this forum saying the trick is to use the PutSourceCode method of the StriptTaskCodeProvider class, but I can't figure out how to do that.

Can anyone provide a code sample of how this is done?

Thanks.

After looking into this more deeply I found that adding a Script Task is a large job and I opted for a simpler approach to my particular issue. A lot of code needs to be added into the package. You have to create a VSA project, then add the script code it contains. To see an example of what's required, open a .dtsx file containing a Script Task and look for the tag "ScriptProject Name", and examine the ProjectItems it contains.

In case it is of use to anyone in the future, here's what I came up with. This doesn't create both the project and the script code because I didn't go that far, but it shows you the references and objects you'll need to do that.

Add references to Microsoft.SqlServer.ScriptTask and Microsoft.SqlServer.VSAHosting.

Dim exe As Executable = _Package.Executables.Add("STOCK:ScriptTask")

Dim thTask As TaskHost = CType(exe, TaskHost)

thTask.Name = "MyScriptTask"

Dim st As ScriptTask = TryCast(thTask.InnerObject, ScriptTask)

dim Moniker as String = "dts://Scripts/" & st.VsaProjectName & "/" & st.VsaProjectName & ".vsaproj"

st.ReadWriteVariables = "Var1, Var2"

Dim sb As New StringBuilder

sb.AppendLine("'Microsoft SQL Server Integration Services Script Task")

'build your code here

'this inserts the code into the script task

st.CodeProvider.PutSourceCode(Moniker, sb.ToString)