How to author reports for Reporting Services?

I have installed and am using Reporting Services.

I can create RDL files using Visual Studio.NET, but like to offload this task to a minion who does not have VS.NET.

Is there a stripped down something-or-other to make reports that and end user or business analyst could use and then upload the reports?

Thanks.You might consider Soft Artisans'OfficeWriter. I personally don't have any experience using Reporting Services with OfficeWriter; I have only used their ExcelWriter product and was very pleased with both it and their customer service.

Terri

How to assign values to a variable from a xls sheet?

I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?

select Proyecto from [Carga$]

TIA,

I'm so sorry it's solved!!

I promise you that from now on I'll try not be so impatient..

How to assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.sql

How to assign value to a package variable in a data flow task ?

Hi Everyone,

In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ?

Regards,

Manu

You'll have to use the script component.|||

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

|||

JBlad wrote:

Hi Manu,

I haven't used it myself, yet. But i think you can use the recordset destination to bind a recordset to a variable.

Hope this helps, if so set this post to useful.

Thanks,

Johan Blad

Yes, technically, but you'd still have to work with that recordset in the control flow, as the variable type would be Object. So you'd have to "shred" the recordset to get the real value.

|||Thanks, for me this is an eye-opener. Haven't worked with it and now doubt if i will.

How to assign user variable value to the Derived Column, in Data Flow Task

Hi:

In the derived column transformation editor, I have a Derived column name called FileGroupID. I would like to pass in a value for this column from a variable that I have set earlier in the scope. Can someone let me know, how to write the expression that does that and where do I specifiy that expression. I am thinking its the expression field in the derived column transformation editor. My main question is how to actually write the expression, what is the syntax to pull the variable value? Thanks.

MA2005

@.[User::YourVariableName]

Actually from the top left side you can expand the variables folder and drag-and-dorp it on the expression field; so you avoid the typing.

How to assign an expression with a ssis variable?

Hi all of you,

That's an easy one. I've got a Send Mail task which might send a message in plain text along with a SSIS variable.

Something like that:

'La tabla "' + SUBSTRING( @.[System:ackageName], 3,20) + "' se ha cargado correctamente'

TIA for that,

Sorry, it's solved

" La tabla " + SUBSTRING( @.[System:ackageName], 3,20) + " se ha cargado correctamente "

How to approach (Trigger-Stored procedure )

Hi All

I need opinions on how to approach my task.
I currently have 3 tables: the master table, the archive and a temp
table.
MASTER: has 3 fields ProductID and ProductNo and Released
ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
TEMP: ProductID, ProductNo, SoldDate

I have a trigger on the master table upon deletion to archive. This is
triggered from a seperate routine from a vb app to delete a record
real time.

CREATE Trigger Archive_Proc On dbo.MASTER
For Delete
As
Declare @.iDate As DateTime
Set @.iDate = GetDate()

If @.@.RowCount = 0 Return
set Nocount on
Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select ProductID, ProductNo, @.iDate from deleted

My problem is that I have a temp table that gets filled from a
seperate transaction.It needs to be matched against the master table
then deleted at both master and temp. but the issue is that the temp
table contains its own SoldDate value that needs to be archived.

Q 1: if I use a stored proc. how do i pass the SoldDate value to the
trigger as Triggers dont use GVs.
Q 2: How do I set up the stored procedure to delete with multiple
tables. I can get it to UPDATE but not delete...

CREATE PROCEDURE COMPARESOLD
@.Pool Smallint
AS
Set NoCount on

Update MASTER
Set Released = 2
From TEMP, MASTER
Where TEMP.ProductNo = MASTER.ProductNo
AND TEMP.ProductID = MASTER.ProductID
AND INVENTORY.Released = 1

hopefully someone can lead me to the right direction...
ThanksHi

I have not picked up the exact relationship between temp and the Master
table. Posing correct DDL (using the scripting options in Enterprise Manager
or the QA Object browser) and example data as inserts statements go a long
way to remove this sort of ambiguity.

It seems that you should have a FK from the TEMP table to the Master that
cascades on delete.

It may also be possible to change the trigger so that it picks up the
SoldDate from TEMP such as:

Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
Select d.ProductID, d.ProductNo, ISNULL(t.SoldDate,@.iDate)
from deleted d LEFT JOIN Temp t on d.ProductID = t.ProductID

You may want something slightly different if there are multiple TEMP
records.

John

"Alvin" <josesievert@.earthlink.net> wrote in message
news:cc27243c.0311061338.45d42f52@.posting.google.c om...
> Hi All
> I need opinions on how to approach my task.
> I currently have 3 tables: the master table, the archive and a temp
> table.
> MASTER: has 3 fields ProductID and ProductNo and Released
> ARCHIVE: Has 3 ProductID, ProductNo, SoldDate
> TEMP: ProductID, ProductNo, SoldDate
> I have a trigger on the master table upon deletion to archive. This is
> triggered from a seperate routine from a vb app to delete a record
> real time.
> CREATE Trigger Archive_Proc On dbo.MASTER
> For Delete
> As
> Declare @.iDate As DateTime
> Set @.iDate = GetDate()
> If @.@.RowCount = 0 Return
> set Nocount on
> Insert Into ARCHIVE(ProductID, ProductNo, SoldDate)
> Select ProductID, ProductNo, @.iDate from deleted
>
> My problem is that I have a temp table that gets filled from a
> seperate transaction.It needs to be matched against the master table
> then deleted at both master and temp. but the issue is that the temp
> table contains its own SoldDate value that needs to be archived.
> Q 1: if I use a stored proc. how do i pass the SoldDate value to the
> trigger as Triggers dont use GVs.
> Q 2: How do I set up the stored procedure to delete with multiple
> tables. I can get it to UPDATE but not delete...
> CREATE PROCEDURE COMPARESOLD
> @.Pool Smallint
> AS
> Set NoCount on
> Update MASTER
> Set Released = 2
> From TEMP, MASTER
> Where TEMP.ProductNo = MASTER.ProductNo
> AND TEMP.ProductID = MASTER.ProductID
> AND INVENTORY.Released = 1
>
> hopefully someone can lead me to the right direction...
> Thanks

How to add reference to SSIS script task

Hi ALL

I cannot add the reference to the following:
"Imports Microsoft.AnalysisServices.AdomdClient"
in SSIS script task. All the objects that i declared become underlined because there is no reference to the above library.
When i go in SSIS script task and open the code to add the reference it does not the list the above library.
I am able to add reference in Visualstudio but not in SSIS.

Please adivse, it is very critical and urgent for my task

Thanks.

Does this help?

http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx

Ed

|||

As Ed says, you will need to copy the DLL. Note that you only need to do this at design-time so you won't need to do it on your production server.

-Jamie

|||

Hi Jamie / Ed

Thanks for the reply, it did work for me. Appreciate your help.

Thanks

How to add an "if then else" condition within an expression?

Greetings:

I am creating a complex expression in a Send Mail Task for the MessageSource. The expression contains the values of many package variables. I want to conditionally include some variables in the expression depending on what value they contain.

Is it possible to create conditional logic (if then else) within an expression? I've got to conditionally include 8 different variables.

Thanks,

BCB

Using the expression editor, you can look at the Operators. One of them is the "conditional."

This is what you want. (?

your test ? if true : else false|||

Thanks, Phil.

This is part of the code I came up with to conditionally include "lines" in the body of my email message. The 2nd and 4th lines will appear only if the value of a variable is not empty. (Those are the lines that begin with Trim.) The key thing I discovered is that you need to enclose the conditional logic in parens in order for the overall expression to evaluate properly.

"The number of ESHL rows inserted was " + (DT_STR, 10, 1252) @.[User::ESHL_RowsInserted] + "." + "\n" +
(Trim(@.[User::ESHL_Error_LongMessage]) == "" ? "" : "\n" + "ESHL Message: " + @.[User::ESHL_Error_LongMessage] + "\n\n") +
"The number of Gulf States rows inserted was " + (DT_STR, 10, 1252) @.[User::GulfStates_RowsInserted] + "." + "\n" +
(Trim(@.[User::GulfStates_Error_LongMessage]) == "" ? "" : "\n" + "Gulf States Message: " + @.[User::GulfStates_Error_LongMessage] + "\n\n")

How to accomplish creating a simple calculated column in a table

Is there a way to create a calculated column in a table like this, or is
there a better way to accomplish this task? I've tried, but SQL keeps
adjusting my precedence and it goofs up the formula:
Let's say the column name is "NetPrice". I want to deduct the discounted
amount from the total to arrive at the net price. Discount is a decimal
value, ie: 25% = .25.
[TotalPrice] - ([TotPrice] * [Discount])Oops, typo below. Should be: [TotalPrice] - ([TotalPrice] * [Discount])
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Bill
See if this helps
CREATE TABLE #Test (
TotalPrice DECIMAL, Discount DECIMAL,
NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
SELECT * FROM #Test
"Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
> Is there a way to create a calculated column in a table like this, or is
> there a better way to accomplish this task? I've tried, but SQL keeps
> adjusting my precedence and it goofs up the formula:
> Let's say the column name is "NetPrice". I want to deduct the discounted
> amount from the total to arrive at the net price. Discount is a decimal
> value, ie: 25% = .25.
> [TotalPrice] - ([TotPrice] * [Discount])
>|||Thanks Uri. I think I was entering something incorrectly when I originally
entered the formula into the table designer.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ePsmpBmnGHA.764@.TK2MSFTNGP03.phx.gbl...
> Bill
> See if this helps
> CREATE TABLE #Test (
> TotalPrice DECIMAL, Discount DECIMAL,
> NetPrice AS [TotalPrice] - ([TotalPrice] * [Discount]))
>
> INSERT INTO #Test (TotalPrice,Discount) VALUES (50,15)
> SELECT * FROM #Test
>
> "Bill Hicks" <waylien_no_spam@.yahoo.com> wrote in message
> news:uJhvc2lnGHA.4668@.TK2MSFTNGP02.phx.gbl...
>