conditionally execute a subreport

I have a report that contains several subreports, one of which is meant to be
displayed only when the subject of the report has a particular job title. The
data source for this subreport is a stored procedure, and in that procedure I
examine the job title and execute the code only for the specified job title,
but SQL Server goes ahead and opens all of the tables anyway, increasing
overhead on a report that already takes several hours to run.
A better solution would be to conditionally execute the subreport. Is there
a way to do that?
thanks, bethMy understanding of Reporting Services is that it is not meant to work that
way. This is not a client program; it is a server platform. There is
therefore no event-driven programming paradigm like you get in Access. It is
by design.
If you want to do the kind of thing you are talking about, you will need to
change your design. You can design two separate reports, or you can write
your own client program to conditionally load reports.
There is a sample demo of how to integrate reports in your application on
http://www.microsoft.com/sql/reporting. It shows how to use a browser control
in a Windows application.
sorry if this is not the answer you wanted.
Charles Kangai, MCT, MCDBA
"beth" wrote:
> I have a report that contains several subreports, one of which is meant to be
> displayed only when the subject of the report has a particular job title. The
> data source for this subreport is a stored procedure, and in that procedure I
> examine the job title and execute the code only for the specified job title,
> but SQL Server goes ahead and opens all of the tables anyway, increasing
> overhead on a report that already takes several hours to run.
> A better solution would be to conditionally execute the subreport. Is there
> a way to do that?
> thanks, beth|||Charles,
I will persue the alternatives you suggested - thank you.
I don't understand your point about client versus server platforms.
If I can conditionally toggle visibility of an object, why could I not toggle
say, an enable/disable property, of a subreport? If this is by design though,
I guess that's that.
I could try placing another stored procedure up front that examines the job
title, that will then conditionally execute the stored procedure that opens
all the
tables and collects the data; using this method, I could at least stop SQL
Server from opening tables it is not going to use. Not sure that will work
though.
thank you for your reply Charles...beth
"Charles Kangai" wrote:
> My understanding of Reporting Services is that it is not meant to work that
> way. This is not a client program; it is a server platform. There is
> therefore no event-driven programming paradigm like you get in Access. It is
> by design.
> If you want to do the kind of thing you are talking about, you will need to
> change your design. You can design two separate reports, or you can write
> your own client program to conditionally load reports.
> There is a sample demo of how to integrate reports in your application on
> http://www.microsoft.com/sql/reporting. It shows how to use a browser control
> in a Windows application.
> sorry if this is not the answer you wanted.
> Charles Kangai, MCT, MCDBA

conditional execution of the next job in DTS

I have a job which exports and emails the data from a table (subject to some conditions) . The data is exported to a test file. I donot want to send the email if there are no rows exported. or the filesieze is 0. Otherwsie I want to send the email with this text file as attachement.

Any ideas?

Thanks
RaguYou can turn steps on or off. See the topic titled "Using ActiveX Scripts in a DTS Workflow" in BOL.

Also the topic "Using ActiveX Scripts in DTS" may be helpful.

Phil|||activex in dts is single threaded in dts and slows all kinds of stuff down.
try using any built in task first if you can.
if you want to try something trick, try the mesage queue task.
or
you can provide if logic for rows returned in an execute sql task and if successfull have the on success run an xpcmdshell from the sql task/
or try a data driven query task ++++ place if logic in the query
if rows returned then transfer to text
if no rows returned then raiserror

Books Online {Data Driven Query Task}
Books Online {Execute SQL Task}
Books Online {Message Queue Task}
Books Online {DTS tasks, overview}|||Phil/Ruprect: Thanks for your replies. I was using logic in sql (Execute SQL Task) to initiate the next step but the table has grown to 15 M rows. I plan to use activex script to just check the size of the output file (Thanks to sqldts.com site for the script) and accordingly initiate the next job.

Thanks again for the replies.

Ragu

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

Concatenating column values for each uniqueid

I have a table like this.

OrderId

QueueId

Template

Subject

RecdDtim

SentDtim

1

1

TEM1

Sub1

11/3/2005 8:10:10

11/3/2005 9:00:15

1

2

TEM3

Sub2

11/3/2005 10:25:45

11/3/2005 10:40:35

2

3

TEM1

Sub2

11/3/2005 11:45:35

11/3/2005 12:00:00

1

4

TEM2

Sub3

11/4/2005 10:25:00

11/4/2005 10/30:00

2

5

TEM3

Sub3

11/4/2005 11:56:00

11/4/2005 12:00:00

I want to concatenate Template, Subject and SentDtim column values for each order and display results like this:

OrderId

Email

1

TEM1,Sub1,11/3/2005 | TEM3,Sub2,11/3/2005|TEM2,Sub3,11/4/2005

2

TEM1,Sub2,11/3/2005 | TEM3, Sub3, 11/4/2005

Please help me how to write the sql query to insert the results in the above format into a temporary table so that I can join that temp table with another tableon orderid column.

There was a thread few days back titled "Concatenate field based on unique id. ". But you are trying to build a report using SQL and it is not going to look pretty. You are better off sending the results as is to the client and format accordingly. It is much more easier to format the results easily.|||I want to insert the results into a temp table so that I can join that temp table with another table.|||I have to agree with UC that this sounds like a hack.

Anyway, if you are using SQL Server 2005, you can use basically the same solution as I provided fpr the question in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124926&SiteID=1:

create table why (OrderId int, QueueId int, Template nvarchar(10),
Subject nvarchar(10), RecdDtim datetime, SentDtim datetime)
go

insert into why
select 1, 1, N'TEM1', N'Sub1', '11/3/2005 8:10:10', '11/3/2005 9:00:15'
union
select 1,2, N'TEM3', N'Sub2', '11/3/2005 10:25:45', '11/3/2005 10:40:35'
union
select 2,3, N'TEM1', N'Sub2', '11/3/2005 11:45:35', '11/3/2005 12:00:00'
union
select 1, 4, N'TEM2', N'Sub3', '11/4/2005 10:25:00', '11/4/2005 10:30:00'
union
select 2, 5, N'TEM3', N'Sub3', '11/4/2005 11:56:00', '11/4/2005 12:00:00'

select o.OrderId,
substring(
(select '|', substring(
(select ',', i.Template as "text()", ',', i.Subject as "text()",
',', CONVERT(nvarchar(10), RecdDtim, 1)
from why ii
where ii.QueueId = i.QueueId and ii.OrderId = i.OrderId
for xml path('')),2, 8000)
from why i
where i.OrderId = o.OrderId
for xml path('')),2,8000)
from why o
group by o.OrderId

Best regards
Michael

|||I want to know the solution in SQL Server 2000. Thanks for the help.|||If you are joining the results with another temporary table then why do you want to denormalize the data? You could use the method that I described in the other thread. But it seems unnecessary to me. Maybe it will help if you post some sample schema, data and expected results we can suggest a different solution.|||Order details are stored in Orders table. But the emails sent to customers in respect of orders are stored in a different table (EmailQueue). For one order, multiple emails may be sent. I want to display order information in a datagrid as well as to export the data to excel format. In the last column of this grid/excel sheet, I want to display template, subject and date sent of each email sent in respect of that order. I hope I have made clear my requirement.|||What you are trying to do is perfectly reasonable with tsql. Here is code that uses a cursor to loop through each row in the table.

Joyce

CREATE TABLE #T (OrderId int not null, EmailString varchar(1000) not null)

DECLARE @.OrderId int, @.EmailString varchar(1000)

,@.OldOrderId int, @.FirstString varchar(1000)

SET @.OldOrderId = -999

SET @.OrderId = 0

SET @.EmailString = ''

SET @.FirstString = ''

DECLARE RowCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY

FOR

SELECT

OrderId

,i.Template + ','+ i.Subject + ',' + CONVERT(varchar(10), RecdDtim, 1)

FROM why i

ORDER BY OrderId, Queueid

OPEN RowCursor

FETCH NEXT FROM RowCursor INTO @.OrderId, @.FirstString

WHILE (@.@.FETCH_STATUS = 0)

BEGIN

-- select @.oldorderid, @.orderid -- some debug code

IF @.OldOrderId = -999 SET @.OldOrderId = @.OrderId -- for first time through

IF @.OldOrderId <> @.OrderId

BEGIN

INSERT INTO #t (OrderId, EmailString) VALUES (@.OldOrderId, @.EmailString)

SET @.OldOrderId = @.OrderId

SET @.EmailString= ''

END

-- for the next QueueId within the same OrderId, concatenate a vertical bar between strings

SET @.EmailString = CASE WHEN @.EmailString = ''

THEN @.FirstString

ELSE @.EmailString + '|' + @.FirstString

END

FETCH NEXT FROM RowCursor INTO @.OrderId, @.FirstString

END

-- The last row was fetched so insert the OrderId

INSERT INTO #t (OrderId, EmailString) VALUES (@.OldOrderId, @.EmailString)

CLOSE RowCursor

DEALLOCATE RowCursor

-- See what is in there

select * from #t order by orderid