IS it possible to insert a blank line in my report

Hello,

I my dataset is like following

sql:

select quarter, sum(amount) as amount from table1 group by quarter

then I get the dataset

field: quarter,amount

date: quarter1,100

quarter2,500

Is it possible to get my report from the dataset above.

my report:

quarter,amount

quarter1,100

quarter2,500

quarter3,0

quarter4,0

thanks!

This is possible if you have a time (or quarter) table in your database:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q

LEFT JOIN table1 t1

ON q.quarter = t1.quarter

|||

thank you for reply

I can't get the tabe quarters ...

|||

Use this SQL:

SELECT

q.quarter,

ISNULL(t1.amount,0) as amount

FROM quarters q LEFT JOIN

(SELECT 'quarter1' AS quarter UNION SELECT 'quarter2' AS quarter UNION SELECT 'quarter3' AS quarter UNION SELECT 'quarter4' AS quarter) AS qrt

ON quarters.quarter = qrt.quarter

This will work.

Please mark the post as answer.

Shyam

|||

In order to more easily manage date groupings (weeks, months, quarters, years, etc.) it is a VERY good idea to have a Calendar table in your database. Having a Calendar table makes tasks such as this one very simple.

Here is additional information about creating and using a Calendar table.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

is it possible to execute package through Stored Proc

is there a way to execute SSIS Package through stored proceedure.

Or any other method of executing the SSIS Package command line in stored proceedure

Thanks,

jas

way back then you can use xp_cmdshell dtsrun package with a stored proc.

i think you can still use that

xp_cmdshell dtexec package

pls consult the link below

for xp command shell

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

for dtexec

http://msdn2.microsoft.com/en-us/library/ms138023.aspx

regards

|||Or create an Agent Job without a schedule, and then run the package on demain using Agent's stored procedures.|||Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.|||

SMW_VA wrote:

Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.

There doesn't seem to be anything in sp_start_job that would enable you to do this:

http://msdn2.microsoft.com/en-us/library/ms186757.aspx

One option might be to use SQL Server configurations which you can update using T-SQL code as normal. When the package executes it will use those config values.

-Jamie

|||

Did you get a good response for this? I'm still looking..Thanks!

|||

XtineInWa wrote:

Did you get a good response for this? I'm still looking..Thanks!

There are three responses. How many do you want?

|||

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

|||

Dwaraka wrote:

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

Could I politely ask that you consult Books Online for the answer to this. If there are any gaps in your understanding after doing that then feel free to reply here.

-Jamie

is it possible to execute package through Stored Proc

is there a way to execute SSIS Package through stored proceedure.

Or any other method of executing the SSIS Package command line in stored proceedure

Thanks,

jas

way back then you can use xp_cmdshell dtsrun package with a stored proc.

i think you can still use that

xp_cmdshell dtexec package

pls consult the link below

for xp command shell

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

for dtexec

http://msdn2.microsoft.com/en-us/library/ms138023.aspx

regards

|||Or create an Agent Job without a schedule, and then run the package on demain using Agent's stored procedures.|||Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.|||

SMW_VA wrote:

Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.

There doesn't seem to be anything in sp_start_job that would enable you to do this:

http://msdn2.microsoft.com/en-us/library/ms186757.aspx

One option might be to use SQL Server configurations which you can update using T-SQL code as normal. When the package executes it will use those config values.

-Jamie

|||

Did you get a good response for this? I'm still looking..Thanks!

|||

XtineInWa wrote:

Did you get a good response for this? I'm still looking..Thanks!

There are three responses. How many do you want?

|||

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

|||

Dwaraka wrote:

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

Could I politely ask that you consult Books Online for the answer to this. If there are any gaps in your understanding after doing that then feel free to reply here.

-Jamie

is it possible to execute package through Stored Proc

is there a way to execute SSIS Package through stored proceedure.

Or any other method of executing the SSIS Package command line in stored proceedure

Thanks,

jas

way back then you can use xp_cmdshell dtsrun package with a stored proc.

i think you can still use that

xp_cmdshell dtexec package

pls consult the link below

for xp command shell

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

for dtexec

http://msdn2.microsoft.com/en-us/library/ms138023.aspx

regards

|||Or create an Agent Job without a schedule, and then run the package on demain using Agent's stored procedures.|||Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.|||

SMW_VA wrote:

Is there a way to pass parameters to that job? I want a trigger to run an SSIS package, but I need to pass some variables through the job. The variables are in the table that the trigger is on.

There doesn't seem to be anything in sp_start_job that would enable you to do this:

http://msdn2.microsoft.com/en-us/library/ms186757.aspx

One option might be to use SQL Server configurations which you can update using T-SQL code as normal. When the package executes it will use those config values.

-Jamie

|||

Did you get a good response for this? I'm still looking..Thanks!

|||

XtineInWa wrote:

Did you get a good response for this? I'm still looking..Thanks!

There are three responses. How many do you want?

|||

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

|||

Dwaraka wrote:

Hi Jamie

How do I set up SSIS package to use SQL server configurations. Details answer is appreciated.

Could I politely ask that you consult Books Online for the answer to this. If there are any gaps in your understanding after doing that then feel free to reply here.

-Jamie