Change background color on toggle

I have a report with a table, four columns. I added a second row to the
table and inserted a subreport. The subreport's ToggleItem is the first
column in the first detail row of the table. When I toggle a row to display
the subreport, I'd like to change the background color of the row. I can't
seem to get a reference to the ToggleState of the Textbox, or the Visibility
of the subreport. Any ideas?
--
KenHi Ken,
Thank you for your posting!
Based on my research, we could not get the reference to the Visibility of a
report item or the togglestate of a textbox.
Unfortunately, I could not found a workaround now.
To provide your feedback directly to the product groups:
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Thank you for your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||There is an example on www.msbicentral.com called
ConditionalGroupBkgrdColor.rdl. It is in the downloads section for Reporting
Services Reports. Its description is:
This report demonstrates how to change a group's background color when it is
toggled to show its children. The key areas to focus on are the rectangles
and their children. Note the use of a toggle textbox, visible textbox, and a
hidden textbox.
You'll have to register(free) to download it. This might be what you are
looking for.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"GopherGold" wrote:
> I have a report with a table, four columns. I added a second row to the
> table and inserted a subreport. The subreport's ToggleItem is the first
> column in the first detail row of the table. When I toggle a row to display
> the subreport, I'd like to change the background color of the row. I can't
> seem to get a reference to the ToggleState of the Textbox, or the Visibility
> of the subreport. Any ideas?
> --
> Ken|||Thanks, Wayne. I'm using RS2005. When I open the rdl file, I'm asked to
convert it, leading me to believe that this was done for RS2000. Is that
correct? After converting it, something must have broken, because in preview
mode the groups don't toggle.
--
Ken
"Wayne Snyder" wrote:
> There is an example on www.msbicentral.com called
> ConditionalGroupBkgrdColor.rdl. It is in the downloads section for Reporting
> Services Reports. Its description is:
> This report demonstrates how to change a group's background color when it is
> toggled to show its children. The key areas to focus on are the rectangles
> and their children. Note the use of a toggle textbox, visible textbox, and a
> hidden textbox.
> You'll have to register(free) to download it. This might be what you are
> looking for.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "GopherGold" wrote:
> > I have a report with a table, four columns. I added a second row to the
> > table and inserted a subreport. The subreport's ToggleItem is the first
> > column in the first detail row of the table. When I toggle a row to display
> > the subreport, I'd like to change the background color of the row. I can't
> > seem to get a reference to the ToggleState of the Textbox, or the Visibility
> > of the subreport. Any ideas?
> >
> > --
> > Ken|||Hi Ken,
Please try to find the ToggleTextBox and modify the width to 0.3 in. Then
you could see the (+) mark and you could toggle the group.
Thanks for Wayne's great help!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Ken,
Have you tried the suggestion? Please let me know if you need any help on
this issue. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Chain transact sql scripts

I have two Transact SQL scripts and I want to call the second script from the first –
is this possible in SQL server 2005?
I am trying to do a port from Oracle (where this is possible) but cannot find the mechanism to do so in SQL Server.

Eg if Script1.sql is
BEGIN
PRINT (‘Inside Script1’)
//Invoke Script2.sql – how do I do this?
END

where Script2.sql is
BEGIN
PRINT (‘Inside Script2’)
END

When I execute Script1.sql - I need it to print both 'Inside Script2' and 'Inside Script1'

You can do this using the :r command if you invoke the script using the new SQLCMD command-line utility. It also has other script pre-processing features. You can take a look at the Books Online topic at link below:

http://msdn2.microsoft.com/en-us/library/ms162773(SQL.90).aspx

|||We can use the :r option inside sqlcmd - but was looking for Transact SQL support (without using xp_cmdshell to spawn "sqlcmd" inside a larger script) - Inside a Oracle pl/sql script, we use "@.<scriptname> from a enclosing script" - was looking for similar functionality in Transact SQL|||

I would suggest the best way would be to place the code inside two stored procedures, and call one from the other.

HTH

|||

shuges is right

store procedure can call another sp

using the exec "spname" syntax

also sql queries can be nested so it is possible to

rewrite two sps into one.

moreover you can also make use of functions

|||The :r SQLCMD command is the equivalent of @. command in SQLPLUS. These are client-side features not part of the TSQL or PL/SQL language.

CASTing a datatype returned by CASE Statement

I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.

ddave

SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
ENDThat depends (doesn't it always?) on what you really want. If you want the other regions to show using normal INT formatting, but 0 to be a special case That is one thing, if you want all the region numbers to be zero filled, that is something different. If you want something I haven't thought of yet, then that's probably different too.

The quick and dirty would be to use:SELECT Region =
CASE
WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN ' 44'
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN '000'
ELSE '999'
END

-PatP|||Pat,

Once again, "You da Man!!". It works perfectly. I decided to use '000', ' 1', ' 78', etc. I spent over an hour on it and I knew it was something easy. I mean I don't expect a medal or anything but you can be lost w/o "the little details". Thanks again.

ddave|||If I want the format to show the Region field just once, is there a way to do that? My current report has a Region field immediately to the left of BranchNo. Branches are contained within the Regions. I got it to list Region every time I show a record but just in case the manager wants it formatted the way I mention I want to be prepared. The example I was to follow has Region just once.

This is an example of what I have now:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
000 478 112 0 0 0
000 478 113 1 0 0
001 610 119 0 0 0
001 610 120 1 0 0
----------------------

This is an example of what I wish to try:

code:--------------------
Region BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
000 478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
001 610 119 0 0 0
610 120 1 0 0
----------------------

ddave|||What reporting tool are you using? Hopefully this isn't 100% Transact-SQL based, right?

-PatP|||Well, I am looking at the data in Query Analyzer but that is a good question. I guess the real answer is that we haven't decided yet. I can use Access though I have to figure out the mechanics which I know won't be difficult. I can even stick it on an Excel spreadsheet as long as it looks good. I say Access because that is "what the others did" but it is not an issue.

ddave|||It's a presntation issue, and Access is very good at it, and can easily do what your asking...

I'd love to setup reporting services though...

Anyone seen it?

What's the installation like?

What's the interface?

Can you use the same box as sql server?

PS. If they say Crystal...run...|||Reporting Services is quite cool, but it is rather complex and it requires Visual Studio to develop reports.

MS-Access would be beauteous, and would make the formatting, grouping, etc rather simple. I'm not nearly as alergic to Crystal Reports as most folks around here seem to be, but I would STRONGLY advise using Access unless you have another tool of choice.

-PatP