Hide one Table section and change location of 2 table

I have two table sections in my report. I make one table section invisible if
i dont have data for the section. I want to change the location of below
table section which has data to the location of first table section. How to
do it?
Thanks in Advance,
Parimal.If I am not mistaken, we put each item in a rectangle and hide the
rectangle... It removes most of the space, but not all(if I remember
correctly.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Parimal" <Parimal@.discussions.microsoft.com> wrote in message
news:EB291698-744F-45B5-A7BF-BAC12F16B19F@.microsoft.com...
>I have two table sections in my report. I make one table section invisible
>if
> i dont have data for the section. I want to change the location of below
> table section which has data to the location of first table section. How
> to
> do it?
> Thanks in Advance,
> Parimal.

Hide Group Header Elements when Detail Visible

This seems like an easy request but I can't figure it out.
I have a table with multiple groups. Each group header contains subtotal
fields. I want to hide the contents of the subtotal fields on each group
header when that group is expanded but I want the field that contains the
name of the group to remain visible.
Thanks, JonI did this by adding an additional row to the group header.
Put the field that contains the name of the group on row 1 of your header,
and the subtotal field on row 2. Now you can select the field with the group
name to toggle Visibility on or off for both your Details and your Subtotal
field (or row). Leave the Subtotal field visible in the report definition
(Hidden=False), but hide the Details section.
Hth,
Flip
"Jon B" wrote:
> This seems like an easy request but I can't figure it out.
> I have a table with multiple groups. Each group header contains subtotal
> fields. I want to hide the contents of the subtotal fields on each group
> header when that group is expanded but I want the field that contains the
> name of the group to remain visible.
> Thanks, Jon

Hide Duplicates - and border

If a person selects the "hide duplicates" option in a Table/Group, how would
one also hide the "border"?
I've tried using expressions to set the borderstyle to None, and this works,
but I can't figure out how to evaluate if a table cell (value) is "hidden".
Below is what I'm trying to do, hide the border, if the (duplicating) value
is hidden.
Division Product 1999 2000 2001 2002
North Wiget 100 200 100 300
Racket 100 200 100 300
Doget 100 200 100 300
South Wiget 100 200 100 300
Racket 100 200 100 300
Doget 100 200 100 300
East Wiget 100 200 100 300
Racket 100 200 100 300
Doget 100 200 100 300
West Wiget 100 200 100 300
Racket 100 200 100 300
Doget 100 200 100 300
Thx,
RussellHaven't tried it but you should be able to use a conditional border based on
the Previous() aggregate.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Hardie" <russell_hardie@.cranevalve.com> wrote in message
news:uN%23WlOexEHA.2036@.TK2MSFTNGP12.phx.gbl...
> Sorry, but my example did not come across as I sent it.
> I've attached a jpg as an example...
> Thx,
> Russell
>
> "Russell Hardie" <russell_hardie@.cranevalve.com> wrote in message
> news:eWFwWGexEHA.2036@.TK2MSFTNGP12.phx.gbl...
>> If a person selects the "hide duplicates" option in a Table/Group, how
>> would
>> one also hide the "border"?
>> I've tried using expressions to set the borderstyle to None, and this
>> works,
>> but I can't figure out how to evaluate if a table cell (value) is
>> "hidden".
>> Below is what I'm trying to do, hide the border, if the (duplicating)
>> value
>> is hidden.
>> Division Product 1999 2000 2001 2002
>> North Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>> South Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>> East Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>> West Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>>
>> Thx,
>> Russell
>>
>|||Thanks Brian,
That is my issue. I have been trying to do a conditional border, but I can't
figure out what the "expression" is that I need to use to evaluate to True.
If I test the textbox (in the table) for Value="" it returns a False. I
assume that the textbox still has a value, it's Visibility is just hidden.
So what I can't figure out is the expression to use to test a textbox's
"Visibility" (if this is the correct approach).
Thanks,
Russell
"Brian Welcker [MS]" <bwelcker@.online.microsoft.com> wrote in message
news:%23Hz3i$ixEHA.1260@.TK2MSFTNGP12.phx.gbl...
> Haven't tried it but you should be able to use a conditional border based
> on the Previous() aggregate.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Russell Hardie" <russell_hardie@.cranevalve.com> wrote in message
> news:uN%23WlOexEHA.2036@.TK2MSFTNGP12.phx.gbl...
>> Sorry, but my example did not come across as I sent it.
>> I've attached a jpg as an example...
>> Thx,
>> Russell
>>
>> "Russell Hardie" <russell_hardie@.cranevalve.com> wrote in message
>> news:eWFwWGexEHA.2036@.TK2MSFTNGP12.phx.gbl...
>> If a person selects the "hide duplicates" option in a Table/Group, how
>> would
>> one also hide the "border"?
>> I've tried using expressions to set the borderstyle to None, and this
>> works,
>> but I can't figure out how to evaluate if a table cell (value) is
>> "hidden".
>> Below is what I'm trying to do, hide the border, if the (duplicating)
>> value
>> is hidden.
>> Division Product 1999 2000 2001 2002
>> North Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>> South Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>> East Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>> West Wiget 100 200 100 300
>> Racket 100 200 100 300
>> Doget 100 200 100 300
>>
>> Thx,
>> Russell
>>
>>
>

Hide Duplicates

I have a query returning a parent table with mutliple child rows. I want the
report to show the info from the parent table and then show all the child
info and then show the next parent info. Setting the parent fields to Hide
Duplicates prints the information the right way, but leaves a blank area
where those fields would have printed. Then below it prints the child table
fields. How can I make it skip the repeated values entirely?Group the table with the Unique Parent field and display the parent info in
group header and child fields in the details part.
"MNicks" wrote:
> I have a query returning a parent table with mutliple child rows. I want the
> report to show the info from the parent table and then show all the child
> info and then show the next parent info. Setting the parent fields to Hide
> Duplicates prints the information the right way, but leaves a blank area
> where those fields would have printed. Then below it prints the child table
> fields. How can I make it skip the repeated values entirely?sql

Hide document map label using expression

When a report item is hidden, its document map still shows. I would like to
access the hidden attribute of the item (in this case they can be table or
chart, but I believe the model to be the same). I can't seem to find the
object model how to access that attribute. I tried ReportItems!table1.hidden,
ReportItems!table1.visible, ReportItems!table1.Visibility and they all say
those attributes do not belong to the member. Anyone can point me to the doc?
I'll try to dig around and find the dll for reporting services and see if
object browser might help.
Thanks.Hide document map label using expression

Hide dimension members which have no rows in fact table

When a cube is presented to the end user (thru Excel or Reporting services), I would like to limit the available dimension members to only those which have at least one row in fact table. This is to simplify what user sees as a list of choices under a dimension attribute/hierarchy and not be overwhelmed with all the dimension members for which no data may exist in reality in the fact table. Does Analysis Services 2005 have any easy way to accomplish this as part of UDM/Cube design?

Let me give you an example. Take "Locations" role playing dimension. In ocean transportation business, "Locations" are of different types such as Port of Load (sea-port), Port of Discharge (sea-port), Inland Point of origin, Inland Point of destination etc. Sea-ports are only few (5 to 10) where as inland locations are in thousands. The entire list of locations, which includes locations which may not have had any shipment booked till date, may be even bigger. To keep the ETL simple, we want to keep one central dimension table for all location roles. However, for cube query selection purposes, users want to see a simple and compact list of Port of Load locations for which bookings have been made (i.e., data exists in fact table with those Port Of Load locations) - for example.

One possible solution is... to use one view for each location role in the UDM and setup relationships from fact table column to these individual location views, where the view joins Location dimension table with the fact table to select only those locations that exist in the fact table. Is this a good approach? Will there be any performance impact in Cube processing or end user cube query performance because of using views like this (since view is run every time it is referenced on the fly to generate the result set)?

Another solution is... to have separate dimension tables for each location role with only those members that are used in fact table records. But this complicates the ETL due to lot of redundancy. Will it give any performance advantage since it uses a permanent table versus view in UDM (data source view)?

Please share your insights on the best ways of handling this problem in SQL Server 2005. Thank you!

Hello. The standard behaviour you are looking for is the standard behaviour in a TSQL inner join. So if you make a report in SSRS2005 and use TSQL with your data mart / data warehouse as a source, you will only see connected records in the dimensions and the fact table.

A cube is more like a crossjoin in TSQL. If you crossjoin two tables in TSQL you will get all combinations of members in the tables. This is standard behaviour in SSAS2005.

But within a dimension table in SSAS2005 you have a new feature, only within the dimension, called autoexist, that will only show combinations of members like a TSQL inner join of members. Within a dimension, in the previous version of SSAS (2000), you would get a TSQL-crossjoin of all members within a dimension.

If you combine sea-ports and inland destinations, in the same dimension(in SSAS2005), you will get an inner join of dimension member combinations(autoexists). If you put them as separate dimensions you will miss autoexist.

To reduce empty cells in the grid(in a SSAS2005 cube) you will have to add NonEmpty or Non Empty in MDX in order to not show empty cells. Many clients can help help you with this by having a button to hide empty rows and columns in the grid.

There are many more superior MDX experts, on this forum, that can explain this even further.

HTH

Thomas Ivarsson

|||

Hello. Auto exist does not have anything to do with data in the fact table. Auto exist is a dimensional concept only. Auto-exist only pertains to the attributes in the same dimension. If we separate the datasources (either using view or permanent table) for each location role dimension, then with in any single location role dimension, "auto exist" is still effective across the attributes of that dimension. My problem has to do with not wanting to see the dimension members if they (dimension key) are not present in the fact table data. Thanks for the reply. Appreciate it.

|||

I have tried to tell you about the behavior of Autoexist if you read my post carefully. You have also an explanation for the dimension and fact problem.

Autoexist is not new to me either.

Regards

Thomas Ivarsson

|||

Shan,

If I understand your requirement correctly, you have one Locations dimension table, but you use that single dimension within your UDM in different roles. In each of its roles, you want the dimension to have a different set of members based on whether or not the members exist within a related fact table or not. Is that correct?

If that is the case, then I believe your best option is to create different views (or named queries in the DSV) on top fo the Locations dimension table that handle the inner join to each related fact table. Then, in the UDM, set up separate dimensions on top of each view and relate each dimension to each fact table/measure group as appropriate.

There will be some performance implications to doing this. For example, you'll be running similar (or even the exact same) queries agains the Locations dimension table as you process each of the separate dimensions in the UDM. So, if you have a lot of them and/or if the joins to the fact tables within the views or named queries are expensive, you'll be adding processing time. Once processing is complete, however, I don't think query performance against any given measure group within the UDM will be any different than if you had a single dimension that was being used as a role-playing dimenision within the cube.

HTH,

Dave Fackler

|||

Dave,

That's correct summary of my requirement. We will proceed with this recommended solution. Thank you. I was not sure whether Analysis Services has some built-in support to meet this common need which does not require separating data sources (view or separate table) since that approach takes some additional work! Trying to find an easier or better way since this question applies to many dimension tables in our data warehouse (both simple and role playing dimensions)... :-) Now I understand that there is no such easy way (out of the box support).

If I am right, the added performance time you mentioned impacts the cube processing step time during ETL job run. That's okay.

sql

Hide dimension members which have no rows in fact table

When a cube is presented to the end user (thru Excel or Reporting services), I would like to limit the available dimension members to only those which have at least one row in fact table. This is to simplify what user sees as a list of choices under a dimension attribute/hierarchy and not be overwhelmed with all the dimension members for which no data may exist in reality in the fact table. Does Analysis Services 2005 have any easy way to accomplish this as part of UDM/Cube design?

Let me give you an example. Take "Locations" role playing dimension. In ocean transportation business, "Locations" are of different types such as Port of Load (sea-port), Port of Discharge (sea-port), Inland Point of origin, Inland Point of destination etc. Sea-ports are only few (5 to 10) where as inland locations are in thousands. The entire list of locations, which includes locations which may not have had any shipment booked till date, may be even bigger. To keep the ETL simple, we want to keep one central dimension table for all location roles. However, for cube query selection purposes, users want to see a simple and compact list of Port of Load locations for which bookings have been made (i.e., data exists in fact table with those Port Of Load locations) - for example.

One possible solution is... to use one view for each location role in the UDM and setup relationships from fact table column to these individual location views, where the view joins Location dimension table with the fact table to select only those locations that exist in the fact table. Is this a good approach? Will there be any performance impact in Cube processing or end user cube query performance because of using views like this (since view is run every time it is referenced on the fly to generate the result set)?

Another solution is... to have separate dimension tables for each location role with only those members that are used in fact table records. But this complicates the ETL due to lot of redundancy. Will it give any performance advantage since it uses a permanent table versus view in UDM (data source view)?

Please share your insights on the best ways of handling this problem in SQL Server 2005. Thank you!

Hello. The standard behaviour you are looking for is the standard behaviour in a TSQL inner join. So if you make a report in SSRS2005 and use TSQL with your data mart / data warehouse as a source, you will only see connected records in the dimensions and the fact table.

A cube is more like a crossjoin in TSQL. If you crossjoin two tables in TSQL you will get all combinations of members in the tables. This is standard behaviour in SSAS2005.

But within a dimension table in SSAS2005 you have a new feature, only within the dimension, called autoexist, that will only show combinations of members like a TSQL inner join of members. Within a dimension, in the previous version of SSAS (2000), you would get a TSQL-crossjoin of all members within a dimension.

If you combine sea-ports and inland destinations, in the same dimension(in SSAS2005), you will get an inner join of dimension member combinations(autoexists). If you put them as separate dimensions you will miss autoexist.

To reduce empty cells in the grid(in a SSAS2005 cube) you will have to add NonEmpty or Non Empty in MDX in order to not show empty cells. Many clients can help help you with this by having a button to hide empty rows and columns in the grid.

There are many more superior MDX experts, on this forum, that can explain this even further.

HTH

Thomas Ivarsson

|||

Hello. Auto exist does not have anything to do with data in the fact table. Auto exist is a dimensional concept only. Auto-exist only pertains to the attributes in the same dimension. If we separate the datasources (either using view or permanent table) for each location role dimension, then with in any single location role dimension, "auto exist" is still effective across the attributes of that dimension. My problem has to do with not wanting to see the dimension members if they (dimension key) are not present in the fact table data. Thanks for the reply. Appreciate it.

|||

I have tried to tell you about the behavior of Autoexist if you read my post carefully. You have also an explanation for the dimension and fact problem.

Autoexist is not new to me either.

Regards

Thomas Ivarsson

|||

Shan,

If I understand your requirement correctly, you have one Locations dimension table, but you use that single dimension within your UDM in different roles. In each of its roles, you want the dimension to have a different set of members based on whether or not the members exist within a related fact table or not. Is that correct?

If that is the case, then I believe your best option is to create different views (or named queries in the DSV) on top fo the Locations dimension table that handle the inner join to each related fact table. Then, in the UDM, set up separate dimensions on top of each view and relate each dimension to each fact table/measure group as appropriate.

There will be some performance implications to doing this. For example, you'll be running similar (or even the exact same) queries agains the Locations dimension table as you process each of the separate dimensions in the UDM. So, if you have a lot of them and/or if the joins to the fact tables within the views or named queries are expensive, you'll be adding processing time. Once processing is complete, however, I don't think query performance against any given measure group within the UDM will be any different than if you had a single dimension that was being used as a role-playing dimenision within the cube.

HTH,

Dave Fackler

|||

Dave,

That's correct summary of my requirement. We will proceed with this recommended solution. Thank you. I was not sure whether Analysis Services has some built-in support to meet this common need which does not require separating data sources (view or separate table) since that approach takes some additional work! Trying to find an easier or better way since this question applies to many dimension tables in our data warehouse (both simple and role playing dimensions)... :-) Now I understand that there is no such easy way (out of the box support).

If I am right, the added performance time you mentioned impacts the cube processing step time during ETL job run. That's okay.

hide columns with no data

I'm trying to create a table where if the column has no data then it is not
visible. e.g. Sum(values)=0 then hide=true type of thing
This is causing a problem with printing PDF as this still includes all the
blank columns, I understand the explanation as to why this happens.
I've scoured the web and have seen many suggestions but nothing seems to work.
The 'cangrow' property is only valid for height not width.
It is not possible to set an expression on width etc etc
Did anyone ever figure out a way around this issue?I would suggest you to do this hiding part or remove the column itself using
your sql query and use matrix so that depending on the column this will be
displayed.
and all this can be done, provided you dont change/show/hide the columns
very frequently.
Amarnath
"adolf garlic" wrote:
> I'm trying to create a table where if the column has no data then it is not
> visible. e.g. Sum(values)=0 then hide=true type of thing
> This is causing a problem with printing PDF as this still includes all the
> blank columns, I understand the explanation as to why this happens.
> I've scoured the web and have seen many suggestions but nothing seems to work.
> The 'cangrow' property is only valid for height not width.
> It is not possible to set an expression on width etc etc
> Did anyone ever figure out a way around this issue?

Hide Columns

Hi,
I have created a report that contains a table. The table returns data w/
multiple statuses. I have created a group based on the status and have a
page break at the end of the group (so each page will display only one status
type). I would like to dynamically show / hide columns based on the status.
Is this possible? I tried setting the visibility of the individual cells,
however, this leaves a gap where the cells were (which is not desirable)â?¦
Any thoughts?
Thanks!Try setting the visibility property on the column instead of the
individual cells.
On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a report that contains a table. The table returns data w/
> multiple statuses. I have created a group based on the status and have a
> page break at the end of the group (so each page will display only one status
> type). I would like to dynamically show / hide columns based on the status.
> Is this possible? I tried setting the visibility of the individual cells,
> however, this leaves a gap where the cells were (which is not desirable)...
> Any thoughts?
> Thanks!|||Hi Jen,
Can this be done with a Cross-Tab Report? I dont see the Visibility Property
of the column group :( .
--
With Thanks and Regards,
Raja Krishnamurthy
"Jen" wrote:
> Try setting the visibility property on the column instead of the
> individual cells.
> On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have created a report that contains a table. The table returns data w/
> > multiple statuses. I have created a group based on the status and have a
> > page break at the end of the group (so each page will display only one status
> > type). I would like to dynamically show / hide columns based on the status.
> > Is this possible? I tried setting the visibility of the individual cells,
> > however, this leaves a gap where the cells were (which is not desirable)...
> >
> > Any thoughts?
> >
> > Thanks!
>
>

Hide [+]/[-] toggle sign when group doesnot have any member

Hi,

I need help to hide [+]/[-] sign when a group doesnot have any item in it. Currently, I have a table with a group header where I put the companyName and in the detail section I put the users for that company. I want that, if a company doesnot have any users then there should not be any [+]/[-] sign next to it. Please help!!

Thanks,

-Rohit

Does any one know the answer to this one?

Thanks.

Hidden table details row and whitespace issue.

I have a table in a list box with more than one detail row..conditionally hidden based on some criteria..Is there any way to get rid of all the white space generated in the report viewer
From http://www.developmentnow.com/g/115_2004_12_0_29_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comOn Jul 24, 12:56 am, sash<she...@.hotmail.com> wrote:
> I have a table in a list box with more than one detail row..conditionally hidden based on some criteria..Is there any way to get rid of all the white space generated in the report viewer.
> Fromhttp://www.developmentnow.com/g/115_2004_12_0_29_0/sql-server-reporti...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
There are not very many options available in this scenario. If you
haven't already selected decrease to accommodate contents in the
properties of the table control, I would suggest doing that first. The
most effective way to handle this is to manage it in the stored
procedure/query that sources the report. That way you can pass the
criteria back to the stored procedure and it can decide rather to
return a particular row in the returned dataset. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Hidden records?

Hi all,
Using vb6 ado connection I can see the record
e.g. SELECT * FROM table where id=1234
I will see the record
but
Using the same query on Enterprise Manager
the same query results to nothing.
Also
SELECT COUNT(*) AS Expr1
FROM table
Result:
vb6 query:1726 records
Ent. Manager view: 18 records
My questions is why Ent. Manager cannot see all the records
I am using SQL Server 2000 standard edition
The database itselt is nearly 21gb.
Any help or advice appreciated
Regards,
AllanAllan
Are you sure you connected to the same database?
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
My guess is that these are different server/databases with the same table.
Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||Uri,
Yes. We only have one database. I connect via ip address and database name
"Uri Dimant" wrote:
> Allan
> Are you sure you connected to the same database?
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>
>|||Also, after verifying the same servername/databasename, make sure you use the same table by
owner-qualifying the name, like
SELECT COUNT(*) FROM dbo.table
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:2D08ED0C-D379-4DE6-97E1-3A97037EA0AE@.microsoft.com...
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table. Try verifying
> connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> Hi all,
>> Using vb6 ado connection I can see the record
>> e.g. SELECT * FROM table where id=1234
>> I will see the record
>> but
>> Using the same query on Enterprise Manager
>> the same query results to nothing.
>> Also
>> SELECT COUNT(*) AS Expr1
>> FROM table
>> Result:
>> vb6 query:1726 records
>> Ent. Manager view: 18 records
>> My questions is why Ent. Manager cannot see all the records
>> I am using SQL Server 2000 standard edition
>> The database itselt is nearly 21gb.
>> Any help or advice appreciated
>> Regards,
>> Allan
>|||Hi Dan
Same reply as Uri
We only have one database. I connect via ip address and database name
I wish that was the case and the problem is solved
Will the size of the database (21 gb) makes any difference to the standard
edition of SQL server?
How about data corruption?
Thank you for your interest
"Dan Guzman" wrote:
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> My guess is that these are different server/databases with the same table.
> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan" <Allan@.discussions.microsoft.com> wrote in message
> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
> > Hi all,
> >
> > Using vb6 ado connection I can see the record
> > e.g. SELECT * FROM table where id=1234
> > I will see the record
> >
> > but
> > Using the same query on Enterprise Manager
> > the same query results to nothing.
> >
> > Also
> > SELECT COUNT(*) AS Expr1
> > FROM table
> >
> > Result:
> > vb6 query:1726 records
> > Ent. Manager view: 18 records
> >
> > My questions is why Ent. Manager cannot see all the records
> >
> > I am using SQL Server 2000 standard edition
> > The database itselt is nearly 21gb.
> >
> > Any help or advice appreciated
> >
> > Regards,
> > Allan
> >
>|||"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
Not at all.
> How about data corruption?
Highly unlikely that a query one place would return one set of data and the
same query from another place would return different data.
So sounds like something is missing in your description (like a table with
the same name was accidentally created in the MASTER DB and some records
inserted and when you connect the 2nd way you're hitting master, not your
database and seeing the wrong table.
As other have said, to be 100% sure, do a select * from
<dbname>.<owner>.tablename.
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I think Tibor's idea that you have different tables in different schema is
likely. The Best Practice is to always schema-qualify table names. Not
only does this avoid ambiguous references, it helps improve performance too.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan" <Allan@.discussions.microsoft.com> wrote in message
news:FE6482BA-6070-40EE-AC99-ABDA230A42BD@.microsoft.com...
> Hi Dan
> Same reply as Uri
> We only have one database. I connect via ip address and database name
> I wish that was the case and the problem is solved
> Will the size of the database (21 gb) makes any difference to the standard
> edition of SQL server?
> How about data corruption?
> Thank you for your interest
> "Dan Guzman" wrote:
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> My guess is that these are different server/databases with the same
>> table.
>> Try verifying connection context with SELECT @.@.SERVERNAME, DB_NAME().
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Allan" <Allan@.discussions.microsoft.com> wrote in message
>> news:A58D0D12-4BD4-4125-9542-8A45D53BA2E3@.microsoft.com...
>> > Hi all,
>> >
>> > Using vb6 ado connection I can see the record
>> > e.g. SELECT * FROM table where id=1234
>> > I will see the record
>> >
>> > but
>> > Using the same query on Enterprise Manager
>> > the same query results to nothing.
>> >
>> > Also
>> > SELECT COUNT(*) AS Expr1
>> > FROM table
>> >
>> > Result:
>> > vb6 query:1726 records
>> > Ent. Manager view: 18 records
>> >
>> > My questions is why Ent. Manager cannot see all the records
>> >
>> > I am using SQL Server 2000 standard edition
>> > The database itselt is nearly 21gb.
>> >
>> > Any help or advice appreciated
>> >
>> > Regards,
>> > Allan
>> >|||To Uri,Dan,Greg and Tibor,
Thank for all your help. It was decided to restore from last nights backup
and redo todays activities.
After restore queries from vb6 and Ent. Manager equalled
I am printing your advice for future reference
Regards,
Allan
"Allan" wrote:
> Hi all,
> Using vb6 ado connection I can see the record
> e.g. SELECT * FROM table where id=1234
> I will see the record
> but
> Using the same query on Enterprise Manager
> the same query results to nothing.
> Also
> SELECT COUNT(*) AS Expr1
> FROM table
> Result:
> vb6 query:1726 records
> Ent. Manager view: 18 records
> My questions is why Ent. Manager cannot see all the records
> I am using SQL Server 2000 standard edition
> The database itselt is nearly 21gb.
> Any help or advice appreciated
> Regards,
> Allan
>|||> Not only does this avoid ambiguous references, it helps improve
> performance too.
Is the performance gain documented any where?
/Sjang|||On Sep 3, 10:46 am, "Henrik Davidsen" <n...@.none.dk> wrote:
> > Not only does this avoid ambiguous references, it helps improve
> > performance too.
> Is the performance gain documented any where?
> /Sjang
You can see for yourself easily:
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'CREATE PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10)) + ' AS RETURN 0'
EXEC sp_executesql @.SQL
END
GO
DECLARE @.i INT, @.j INT, @.d DATETIME, @.SQL NVARCHAR(300), @.SQL_to_run
NVARCHAR(300)
SELECT @.i = 0, @.j = 0, @.d = getdate()
WHILE @.j < 3 BEGIN
SET @.j = @.j + 1
IF @.j = 1 BEGIN
SET @.SQL = 'EXEC DummyProc'
END ELSE BEGIN
SET @.SQL = 'EXEC dbo.DummyProc'
END
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL_to_run = @.SQL + CAST(@.i AS NVARCHAR(10))
--PRINT @.SQL_to_run
IF @.j <3 BEGIN
EXEC sp_executesql @.SQL_to_run
END
END
SELECT DATEDIFF(ms, @.d, GEtdate()) as ms
SELECT @.i = 0, @.d = getdate()
END
GO
DECLARE @.i INT, @.SQL NVARCHAR(300)
SELECT @.i = 0
WHILE @.i < 10000 BEGIN
SET @.i = @.i + 1
SET @.SQL = 'DROP PROCEDURE dbo.DummyProc' + CAST(@.i AS
NVARCHAR(10))
EXEC sp_executesql @.SQL
END
GO
-- without dbo.
ms
--
3860
(1 row(s) affected)
-- with dbo
ms
--
640
(1 row(s) affected)
-- the overhead of running the loop
ms
--
46|||On Mon, 3 Sep 2007 17:46:30 +0200, "Henrik Davidsen" <none@.none.dk>
wrote:
>> Not only does this avoid ambiguous references, it helps improve
>> performance too.
>Is the performance gain documented any where?
Don't have a link handy, but it's mentioned in various white papers,
also just saw it mentioned in Kalen's "Storage Engine 2005" book, near
the middle, I'm too lazy to look up the page!
Per Kalen, in SQL2005 apparently it helps even a little more, if you
don't specify then there is more that SQL2005 does before it decides
you meant dbo. all along.
OK it's a small effect, but on a high-transaction system it might get
you a few TPS, and also there may be something of an increase in the
use of namespaces generally with 2005, so it might be a good practice
just on that basis.
Josh|||You *could* have had an open transaction in snapshot mode on 2005, ...
but it does sound more like corruption. Did you have any crashes
recently? Did you try running DBCC CHECKDB? Was this on a production
system? It's really a table and not a view with some kind of weird
user-sensitive parameters on it? Were you using the same login in
both cases? Was it repeatable - you could log off and log back in
again in both situations and see the same conflicting results?
I know it's moot now, but nobody likes that sort of anomaly.
J.
On Mon, 3 Sep 2007 07:00:00 -0700, Allan
<Allan@.discussions.microsoft.com> wrote:
>Hi all,
>Using vb6 ado connection I can see the record
>e.g. SELECT * FROM table where id=1234
>I will see the record
>but
>Using the same query on Enterprise Manager
>the same query results to nothing.
>Also
>SELECT COUNT(*) AS Expr1
>FROM table
>Result:
>vb6 query:1726 records
>Ent. Manager view: 18 records
>My questions is why Ent. Manager cannot see all the records
>I am using SQL Server 2000 standard edition
>The database itselt is nearly 21gb.
>Any help or advice appreciated
>Regards,
>Allan|||Hi J,
"JXStern" wrote:
> You *could* have had an open transaction in snapshot mode on 2005, ...
We are using SQL Server 2000 standard edition
> but it does sound more like corruption. Did you have any crashes
> recently?
No crashes
Did you try running DBCC CHECKDB?
No.
Was this on a production system?
We are very small company. Production and test system is the same.
It's really a table and not a view with some kind of weird user-sensitive
parameters on it?
Yes it is a table. The query was taken (copied and pasted) directly from vb6
source code that hasn't been changed for 3 years
Were you using the same login in both cases?
There is only one login "sa"
Was it repeatable - you could log off and log back in again in both
situations and see the same conflicting results?
We restarted the SQL server 2000. Same result.
Restarted Windows Server 2003 standard edition. Same result
> I know it's moot now, but nobody likes that sort of anomaly.
> J.
>
> On Mon, 3 Sep 2007 07:00:00 -0700, Allan
> <Allan@.discussions.microsoft.com> wrote:
> >Hi all,
> >
> >Using vb6 ado connection I can see the record
> >e.g. SELECT * FROM table where id=1234
> >I will see the record
> >
> >but
> >Using the same query on Enterprise Manager
> >the same query results to nothing.
> >
> >Also
> >SELECT COUNT(*) AS Expr1
> >FROM table
> >
> >Result:
> >vb6 query:1726 records
> >Ent. Manager view: 18 records
> >
> >My questions is why Ent. Manager cannot see all the records
> >
> >I am using SQL Server 2000 standard edition
> >The database itselt is nearly 21gb.
> >
> >Any help or advice appreciated
> >
> >Regards,
> >Allan
>

Hidden Property - "Deserialization failed: is not a Valid Value" E

If I add an expression to the visibility property on a text box, table row,
etc.. and then remove that same expression, I get the following error message
in preview:
"deserialization failed: is not a valid value..."
I get an option to edit the XML code. When I look at the code, I find the
<Hidden /> tag. But get this, there is no </Hidden> tag. Apparently, VS is
removing only one side of the Hidden tag when I delete the expression.
I used to be able to edit the code, but now I can't even edit the code to
take the remaining tag out. With this error my rdl file became, for all
intents and purposes, corrupt. Luckily I had posted it to a dev Report
Manager site earlier and was able to recover it.
Has anyone seen this?
Also, not being able to close VS2005 because of the the "Modal Dialog
Open..." box is killing me. Anyone have a solution to that yet besides
killing the devenv process?
Thanks in Advance,
bsod55I just had a similar error - I had to open the file and remove the Hidden
tags. In the IDE I right clicked on the file and selected Open from the menu
- this gave me the XML version and allowed me to remove the tags.
--
mal
"bsod55" wrote:
> If I add an expression to the visibility property on a text box, table row,
> etc.. and then remove that same expression, I get the following error message
> in preview:
> "deserialization failed: is not a valid value..."
> I get an option to edit the XML code. When I look at the code, I find the
> <Hidden /> tag. But get this, there is no </Hidden> tag. Apparently, VS is
> removing only one side of the Hidden tag when I delete the expression.
> I used to be able to edit the code, but now I can't even edit the code to
> take the remaining tag out. With this error my rdl file became, for all
> intents and purposes, corrupt. Luckily I had posted it to a dev Report
> Manager site earlier and was able to recover it.
> Has anyone seen this?
> Also, not being able to close VS2005 because of the the "Modal Dialog
> Open..." box is killing me. Anyone have a solution to that yet besides
> killing the devenv process?
> Thanks in Advance,
> bsod55

Hidden parameter using userid

I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.I have since discovered the problem is not as stated but is due to the fact that I have hidden parameters dependent on the input of an earlier parameter. If the input parameter is a drop down then all is OK but if you have to interactively enter a value then you have to click the View Report button twice. Removing the userid parameter meant that the dependent parameter was last in the list and again all was then OK; however, I still have some reports where I have more than one hidden parameter dependent on an earlier value so the problem still occurs.
"JohnH" wrote:
> I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.

Hidden max number of columns in Management Studio?

Hi,
The following is observed in the SQLServer 2005 Management Studio Visual
Editor:
Open table (Right Click option on a table) fails when I try it on a table
with lots of columns in it > 820 columns! Currently the table has no rows
(empty table). The same table opens fine with sql 2000 enterprise manager.
This table has been migrated/imported from sql 2000 without any errors! Other
imported tables with less number of columns can be opened in the visual
editor!
Wondering is there a hidden limit in terms of number of columns with
SQLserver 2005 Management Studio (Grid) Visual Editor?
I get SQL Executaion error
Error Source: Microsoft.VisualStudio.DataTools
Exception has been thrown by the target of an invocation
Is this a known behavior/limitation/bug? Is there a workaround?
Thanks
Kan,
See
http://www.sql-server-performance.co...ID=9016鵬
You may wish to report this as a bug at the suggested URL
http://lab.msdn.microsoft.com/produc...k/default.aspx
(also in the thread).
Steve Kass
Drew University
Kan wrote:

>Hi,
>The following is observed in the SQLServer 2005 Management Studio Visual
>Editor:
>Open table (Right Click option on a table) fails when I try it on a table
>with lots of columns in it > 820 columns! Currently the table has no rows
>(empty table). The same table opens fine with sql 2000 enterprise manager.
>This table has been migrated/imported from sql 2000 without any errors! Other
>imported tables with less number of columns can be opened in the visual
>editor!
>Wondering is there a hidden limit in terms of number of columns with
>SQLserver 2005 Management Studio (Grid) Visual Editor?
>I get SQL Executaion error
>Error Source: Microsoft.VisualStudio.DataTools
>Exception has been thrown by the target of an invocation
>Is this a known behavior/limitation/bug? Is there a workaround?
>Thanks
>
>

Hidden items, whitespace

Hi,

i have an issue with hidden items causing whitespace on the output of a report. The last page of the report contains a table, which may be hidden if certain conditions are met. However the final page is still rendered even though it contains nothing. The page does have a header and footer, but the page is still rendered if i also conditionally hide all the elements in the header or footer.

Ideally i would like the last page to not be rendered if that table is hidden. Is there any way to achieve this?

Thanks,

sluggy

Do you have a pagebreak before the table? If you hide the contents of the table, you would still get the last (blank) page. But if you hide the table (by setting table visibility property), you shouldn't get the page.

Hidden Expression for Group Header

I am trying to hide a group header (table row) based on the value of that group, but have not been able to get it working.

Let's say I have 2 groups in my table:

Group 1: State

Group 2: City

I want to hide the group header (tablerow2) where State = "Unknown" but I want the Group 2 cities to still display - just w/o a header.

I tried putting the following expressions on the hidden property of tablerow2 with no luck:

First I tried referencing the field that it's grouped on:

=Iif((Fields!state.Value = "Unknown", true, false)

I also tried refereing the name of the group:

=Iif(("State_GRP" = "Unknown"), true, false)

In both cases, nothing gets hidden - all rows, including the group header that says "Unknown", are visible.

Any help would be greatly appreciated!

Kathleen

You are doing this correctly, and I was not able to reporduce the behavior--it works as you are thinking. I would make sure that the case of "Unknown" is consistent with the field value. I would do this in the expression (or change the query to do the trimming), so that case and extranious white space is not a factor.

=IIF(Fields!state.Value.Trim().ToLower()="unknown", true, false)

Ian

|||Thanks Ian - the trim/tolower seemed to resolve whatever issue I was having.

hi, i need ur urgent help immediately(!)

i have a sql data table which icludes names and surnames

as like as that:

george read

hasan hujtr

deem greg

ade ad

edy es

-----

i need a "select" query to receive only people whom surnames composed merely two letters(ade ad, edy es).

thanks,

best regards.

Do you mean two letters or two words?

|||

I don't think the length() function is supported in sql server... but a hack for that if you're just looking for 2 letters or less would be:

select *
from names
where substring(surname,0,2) = surname

|||^^^ I do believe that will return surnames of length 1 too though... so try it.|||

Don't mean to spam, but I was wrong... there is a length function...

select *
from names
where len(surname) = 2

|||

thanks for ur helps

best regards.

Hi enterprise manager prob.

Hi. I got Enterprise Manager to work finally.
However, the Columns are backwards when I retrieve all rows, and when i go
to design a table.
Meaning to say that the ID starts on right side of screen, and scrollbar is
on left side (everything is backwards)
in options of enterprise manager, it says english is default language.
thanksThis sounds more like a Windows setting to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Michael" <Michael@.nospamplease.com> wrote in message
news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
> Hi. I got Enterprise Manager to work finally.
> However, the Columns are backwards when I retrieve all rows, and when i go
> to design a table.
> Meaning to say that the ID starts on right side of screen, and scrollbar
is
> on left side (everything is backwards)
> in options of enterprise manager, it says english is default language.
> thanks
>
>|||Hi Tibor:
I had it working with enterprise manager 7.0. When I upgraded to 8.0 it's
like that. every other application on my machine is left to right -> only
that is right to left. Everything is English on my machine. Very weird.
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ertxVMqCEHA.1588@.tk2msftngp13.phx.gbl...
> This sounds more like a Windows setting to me...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Michael" <Michael@.nospamplease.com> wrote in message
> news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
go
> is
>|||Got it to work - It was Windows Settings, thanks Tibor!!!
"Michael" <Michael@.nospamplease.com> wrote in message
news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
> Hi. I got Enterprise Manager to work finally.
> However, the Columns are backwards when I retrieve all rows, and when i go
> to design a table.
> Meaning to say that the ID starts on right side of screen, and scrollbar
is
> on left side (everything is backwards)
> in options of enterprise manager, it says english is default language.
> thanks
>
>

hi , query for displaying data

i want to show selected data from my database table in the text boxes using select statment, can anyone help me out pl for displayin selected data from database directly in text boxes..It depends on what language you are programming (C#, VB, Java).
So, please post the question on the specific forum, to get the right answer quicker.|||sorry m new to this site, so didnt knew..
i m using vb.net language in my asp.net project.. can u help me or show me code that can shows the database rows into textboxes in asp.net page with select query..