How do I hide a table in a report programatically?

Hi,
I have 2 tables in a report and, depending on which value a user picks in a
particular parameter, I want to display only one of the tables. Is there any
way to do something like, "IIF(parameters!param_A.value = "Y", visible = true, visible = false)" or something like that for a table?
--
Thanks,
JeffSet the table's Hidden property to an expression. For example:
=CBool(Parameters!HideTheTable.Value)
The Hidden property can be accessed by expanding Visibility... in the
Properties window.
HTH
On Oct 1, 3:39 pm, Jeff Stroope
<JeffStro...@.discussions.microsoft.com> wrote:
> Hi,
> I have 2 tables in a report and, depending on which value a user picks in a
> particular parameter, I want to display only one of the tables. Is there any
> way to do something like, "IIF(parameters!param_A.value = "Y", visible => true, visible = false)" or something like that for a table?
> --
> Thanks,
> Jeff|||On Oct 1, 5:39 pm, Jeff Stroope
<JeffStro...@.discussions.microsoft.com> wrote:
> Hi,
> I have 2 tables in a report and, depending on which value a user picks in a
> particular parameter, I want to display only one of the tables. Is there any
> way to do something like, "IIF(parameters!param_A.value = "Y", visible => true, visible = false)" or something like that for a table?
> --
> Thanks,
> Jeff
The best way to accomplish this is to control the data populating the
table controls at the stored procedure/query level that is sourcing
the report. For example, pass the parameter value back to the stored
procedure(s)/query(ies) and if it is a certain value, return an empty
resultset to the report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Chris!
--
Thanks,
Jeff
"Chris Durkin" wrote:
> Set the table's Hidden property to an expression. For example:
> =CBool(Parameters!HideTheTable.Value)
>
> The Hidden property can be accessed by expanding Visibility... in the
> Properties window.
>
> HTH
>
> On Oct 1, 3:39 pm, Jeff Stroope
> <JeffStro...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have 2 tables in a report and, depending on which value a user picks in a
> > particular parameter, I want to display only one of the tables. Is there any
> > way to do something like, "IIF(parameters!param_A.value = "Y", visible => > true, visible = false)" or something like that for a table?
> > --
> > Thanks,
> >
> > Jeff
>
>|||Thanks Enrique!
--
Thanks,
Jeff
"EMartinez" wrote:
> On Oct 1, 5:39 pm, Jeff Stroope
> <JeffStro...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have 2 tables in a report and, depending on which value a user picks in a
> > particular parameter, I want to display only one of the tables. Is there any
> > way to do something like, "IIF(parameters!param_A.value = "Y", visible => > true, visible = false)" or something like that for a table?
> > --
> > Thanks,
> >
> > Jeff
>
> The best way to accomplish this is to control the data populating the
> table controls at the stored procedure/query level that is sourcing
> the report. For example, pass the parameter value back to the stored
> procedure(s)/query(ies) and if it is a certain value, return an empty
> resultset to the report. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT – the following columns “DatabaseName,
ObjectId and ObjectName” columns were empty.
Why is this?
Please let me know what I’m missing out on?
(Note: A similar question had been posted earlier on – but there was no
satisfactory answer to it.)
Cheers!
SQLCatz
This is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set of tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT – the following columns “DatabaseName,
> ObjectId and ObjectName” columns were empty.
> Why is this?
> Please let me know what I’m missing out on?
> (Note: A similar question had been posted earlier on – but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz
|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be able
to get all the tables that were accessed.
Cheers!
SQLCatz

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT – the following columns “DatabaseName
,
ObjectId and ObjectName” columns were empty.
Why is this?
Please let me know what I’m missing out on?
(Note: A similar question had been posted earlier on – but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running t
races on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & S
QL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular ta
bles using the TextData
column. this is so far the only reliable way I found to audit access for a s
et of tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT – the following columns “DatabaseNa
me,
> ObjectId and ObjectName” columns were empty.
> Why is this?
> Please let me know what I’m missing out on?
> (Note: A similar question had been posted earlier on – but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be abl
e
to get all the tables that were accessed.
Cheers!
SQLCatz

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT â' the following columns â'DatabaseName,
ObjectId and ObjectNameâ' columns were empty.
Why is this?
Please let me know what Iâ'm missing out on?
(Note: A similar question had been posted earlier on â' but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set of tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT â' the following columns â'DatabaseName,
> ObjectId and ObjectNameâ' columns were empty.
> Why is this?
> Please let me know what Iâ'm missing out on?
> (Note: A similar question had been posted earlier on â' but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be able
to get all the tables that were accessed.
Cheers!
SQLCatz

How do I get a ReportName using the ReportID in ExecutionLog Table

Hi,
In the executionlog table of ReportServer DB, I can get the ReportID, I want
to get the ReportName for that particular ReportID.
How do i get it?
Thanks
Balaji
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1Look in the Catalog table, I think the ItemId maps to ReportId, use
Name for the report name.
--
Regards
Chris
BALAJI K via SQLMonster.com wrote:
> Hi,
> In the executionlog table of ReportServer DB, I can get the ReportID,
> I want to get the ReportName for that particular ReportID.
> How do i get it?
> Thanks
> Balaji|||If you want to do it programatically, use the GetProperties API. This is the
only API that allows you to pass the report id to get some properties back.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"BALAJI K via SQLMonster.com" <u5178@.uwe> wrote in message
news:578a4972b3b9e@.uwe...
> Hi,
> In the executionlog table of ReportServer DB, I can get the ReportID, I
> want
> to get the ReportName for that particular ReportID.
> How do i get it?
> Thanks
> Balaji
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1

How do I find the max row size..?

Hi,

How do I find the max row size for a particular table?
This was the error I recieved while execting my proc with the relevant
i/p I need to:

"cannot sort a row of size 8192, which is greater than the allowable
maximum of 8094"

I also understand that the max bytesize of a row is 8060 bytes.But
whtz this 8094?

TIA,
SeethaSeetha (seethakn@.yahoo.com) writes:
> How do I find the max row size for a particular table?
> This was the error I recieved while execting my proc with the relevant
> i/p I need to:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094"
> I also understand that the max bytesize of a row is 8060 bytes.But
> whtz this 8094?

I don't know, but I would guess this is a about a worktable that SQL
Server sets up internal, and for such a table the limit might be somewhat
higher.

Rather than scrutinizing tables, you should probably look at the query
that gives the error.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||seethakn@.yahoo.com (Seetha) wrote in message news:<edf58070.0402162306.37ab77df@.posting.google.com>...
> Hi,
> How do I find the max row size for a particular table?
> This was the error I recieved while execting my proc with the relevant
> i/p I need to:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094"
> I also understand that the max bytesize of a row is 8060 bytes.But
> whtz this 8094?
> TIA,
> Seetha

Each datapage can actually 8192 bytes. This is because each kb is 1024
bytes. The Microsoft documentation specifies that a page header
contains 96 bytes of overhead. This is for keeping track of the page
within the system, kind of like a File Allocation Table on your hard
drive. This leaves 8096 bytes for data and row offsets (pg 247, Inside
SQL Server 2000). You get 8094 because each row has a 2 byte offset.
There is a decent article that goes into more detail at
"http://www.sqlservercentral.com/columnists/sjones/pagesize_printversion.asp"
if you're interested. Also, "Inside SQL Server 2000" by Kalen Delaney
is a great resource.

--Bryan

How do I find out what tables belong to a filegroup?

Is there anyway to find out what tables are in a particular filegroup in SQL2000 apart from running sp_help {tblnm}?You could use undocumented stored procedure...

declare @.id int
set @.i = object_id('mytb')
exec sp_objectfilegroup @.i

How do I do this in SS2000?

Hi all,

I have a simple query which returns all the names of products attached to a particular order.

select a.orderid, b.product_name
from orders a, order_items b
where a.orderid = b.orderid

Say it returns this data: (sorry about the formatting!)

orderid product_name
===== =========
001234 Sweater (Black)
001234 Trousers (Large)
001234 T-Shirt (Pink)

What I want to do is, instead of getting 3 rows back, I want to roll up all matching values (of product_name) from the order_items table into a simple string seperated by a comma. So, for the data above I would get a single row with the orderid and a string containing something like this: "Sweater (Black), Trousers (Large), T-Shirt (pink)".

I'm sure there's an easy way to do this in SQL Server 2000 but I've not been able to work out how to do this and I couldn't see anytihng in SQL Books Online..

TIA for any help...

MikeAre you prepared to use Analysis Services?|||Look at this. You may want to put it into a function, but this is the general idea

declare @.s varchar(8000)
select @.s = b.product_name + ', ' + COALESCE(@.s, '')
 from orders a, order_items b
 where a.orderid = b.orderid
if @.s is not null
 set @.s = substring(@.s, 1, LEN(@.s) - 1)
else
 set @.s = ''
select @.s

|||Thanks for that... that works fine but I need to fine tune it a bit. At the moment it gives me all products for all orders whereas I need it to give me just the product names for each unique order. I tried using a "GROUP BY a.orderid" but it won't let me use the product_name column in this way.

I also want to be able to select the columns I need from the first table such as orderid, order_date etc.

I've experimented with both but can't seem to really get it to work... a little more help would be much appreciated!

Cheers,

Mike

PS: pkr - no I can't really use Analysis Services as this is part of a stored procedure for a web app that also has to run on Oracle so it needs to be fairly standard ANSI SQL.|||Assuming you don't know how many products you've got for a an order its difficult to write a single query. This is my suggestion.
1. Create a temp table with the OrderID and a "csv" text column, defaulted to ''
2. Insert the unique set of orderids into the temp table
3. Run a query that UPDATEs the csv column with itself plus the "," + product name|||Thanks pkr... in the end I wrote a function which is passed the order id and reads the values of the products into a cursor. It then builds the string of product names and returns it. It seems to work very well and I've learnt quite a lot about SQL functions that I didn't know before. I'm not at work so I can't post it but I will on Monday so possibly someone in the future can see how to do this.

Thanks for the help.

Mike|||I use that code within a function and it works fine. Forgot to suggest that. I would stay away from cursors unless you have to use them. There is a significant performance hit. I'd use the query above and avoid the cursor.|||Replacing cursors is nearly always a good idea. However, be careful with funcs, you can basically end up doing the same thing as a cursor. If you code it "incorrectly" the function will run for each row in the set, therefore the perf will be like a cursor anyway!|||Luckily the table I run the function on will not have that many rows in it at one time. I also looked at the stats for a few orders and it does only seem to be reading the ones it needs rather than processing the whole table which is good.

Will post the function when I get to work today... then people can tell me if could do it any better.

Mike.