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?

Hidden Subreport Executing Queries

I'm currently developing a report in which the customer has requested a
drill-down type of interface that starts at a regional level to a
district level to a territory level and finally to the individual
customers within the territory. The drill-down interface was easy to
create by using Groups in conjunction with the ToggleItem and
Visibility.Hidden properties at the different Grouping levels.
The problem I have run into is that there are around 1 Million
customers across the country and the report was attempting to retrieve
all of the records at all levels at once. This turns out to be
unworkable from a performance standpoint.
To try to work around this I proposed using drill-through at the
territory level to open a new report with that territories customer
list. The customer was unwilling to accept that approach since they
wanted to keep the flexibility of navigating through the tree without
the disruption of opening a separate report.
Next I tried to work around the problem by implementing a subreport at
the customer level thinking that the subreport would only execute once
the user drilled down into the territory level. Following is a general
overview of this solution:
+ North East
- South East
- Georgia
- Atlanta
- subreport displays here with territory customer list
+ Savannah
+ Athens
+ Florida
+ Kentucky
- Midwest
+ Kansas
+ Iowa
+ Minnesota
+ West
By using SQL Profiler I found that the report was actually executing
the query for each individual territory's corresponding subreport
even though those levels were hidden. This was essentially the same
scenario that I had with my initial attempt.
To try to work around this I created a parameter within the subreport
called "show_subreport" and if that parameter is set to 1 the dataset
will return the customer list and if it is set to 0 the dataset will
not return any rows. By manually setting the parameter to 0 through
the parameter mapping in the parent report this worked well. However,
I have not been able to figure out how to dynamically change that
parameter by using an expression. I have been trying to set the
parameter expression to something like:
= IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
However, I have not been able to get to that property or any other
property which would be relevant to the "show_subreport" parameter.
This seems like it would be a very common type of report request so I
assume there is a way to do this. Any help would be appreciated.I've got the same problem.
--
/Mattias
"fred_mumble@.yahoo.com" wrote:
> I'm currently developing a report in which the customer has requested a
> drill-down type of interface that starts at a regional level to a
> district level to a territory level and finally to the individual
> customers within the territory. The drill-down interface was easy to
> create by using Groups in conjunction with the ToggleItem and
> Visibility.Hidden properties at the different Grouping levels.
> The problem I have run into is that there are around 1 Million
> customers across the country and the report was attempting to retrieve
> all of the records at all levels at once. This turns out to be
> unworkable from a performance standpoint.
> To try to work around this I proposed using drill-through at the
> territory level to open a new report with that territories customer
> list. The customer was unwilling to accept that approach since they
> wanted to keep the flexibility of navigating through the tree without
> the disruption of opening a separate report.
> Next I tried to work around the problem by implementing a subreport at
> the customer level thinking that the subreport would only execute once
> the user drilled down into the territory level. Following is a general
> overview of this solution:
> + North East
> - South East
> - Georgia
> - Atlanta
> - subreport displays here with territory customer list
> + Savannah
> + Athens
> + Florida
> + Kentucky
> - Midwest
> + Kansas
> + Iowa
> + Minnesota
> + West
> By using SQL Profiler I found that the report was actually executing
> the query for each individual territory's corresponding subreport
> even though those levels were hidden. This was essentially the same
> scenario that I had with my initial attempt.
> To try to work around this I created a parameter within the subreport
> called "show_subreport" and if that parameter is set to 1 the dataset
> will return the customer list and if it is set to 0 the dataset will
> not return any rows. By manually setting the parameter to 0 through
> the parameter mapping in the parent report this worked well. However,
> I have not been able to figure out how to dynamically change that
> parameter by using an expression. I have been trying to set the
> parameter expression to something like:
> = IIf(Customer_DetailRow.Visible.Hidden = True, 0, 1)
> However, I have not been able to get to that property or any other
> property which would be relevant to the "show_subreport" parameter.
> This seems like it would be a very common type of report request so I
> assume there is a way to do this. Any help would be appreciated.
>sql

hi need help

hi all......,

in mssql 2000 there's this code
Declare LockObj_Cur CURSOR Fast_FORWARD FOR
Select id, dbId, type
FROM master..SysLocks (NOLOCK)
WHERE spId = @.spId
AND type in (5, 8, 10, 11)

how do i proceed in mssql 2005 so that i can get the same info as given by above piece of code,
i have found out that sys.dm_tran_locks has to be used but i wasnot able to find equivalent of "type" column of syslocks table of mssql 2000 in sys.dm_tran_locks
plz help

According the 2000 BOL, syslocks was renamed after 6.5 to syslockinfo. And the rsc_type column types only went up to 10. Can you state what you are actually looking for?

On my blog, I have the query that I use all of the time (http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!906.entry#postcns!80677FB08B3162E4!906):

select login_name,
case des.transaction_isolation_level
when 0 then 'Unspecified' when 1 then 'ReadUncomitted'
when 2 then 'ReadCommitted' when 3 then 'Repeatable'
when 4 then 'Serializable' when 5 then 'Snapshot'
end as transaction_isolation_level,
request_session_id, resource_type, resource_subtype, request_mode,

request_type, request_status, request_owner_type,
case when resource_type = 'object' then object_name(resource_associated_entity_id)
when resource_type = 'database' then db_name(resource_associated_entity_id)
when resource_type in ('key','page') then

(select object_name(object_id) from sys.partitions

where hobt_id = resource_associated_entity_id)
else cast(resource_associated_entity_id as varchar(20))
end
from sys.dm_tran_locks dtl
left outer join sys.dm_exec_sessions des
on dtl.request_session_id = des.session_id
where request_session_id <> @.@.spid

But I haven't documented what every column of the dmv's have yet (I am actually working on a project to do so.)

Louis

sql

Hi I would like to import 1,000.00 price in DB - type??

Hi I would like to import into DB prices in format 1,000.00

I am using type money (mssql2004) but it doesnt let mi import this format.

Any ides. thx rek

Try DECIMAL(10,2)|||

Hi thank you for answer but it -

Error converting data type nvarchar to numeric.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

|||

You'll need to convert the value first:

my_decimal_value = Decimal.Parse(my_nvarchar_value);

|||

thats sucks : I am using

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

UpdateCommand="Update HSRSeason SET Price=@.Price > </asp:SqlDataSource>

Do I have to then work it through aspx.cs?

|||

Use this instead:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

UpdateCommand="Update HSRSeason SET Price=CONVERT(DECIMAL(10,2), @.Price)"> </asp:SqlDataSource>

|||

UpdateCommand="Update HSRSeason SET
Price=CONVERT(DECIMAL(10,2), @.Price)

And same error (it works without ",")

Error converting data type nvarchar to numeric.

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Error converting data type nvarchar to numeric.]

|||

I'll bet you have the literal commas in the numbers. Use this instead:

UpdateCommand="Update HSRSeason SET
Price=CONVERT(DECIMAL(10,2), REPLACE(@.Price, ',', ''))

to replace the commas with a zero-length string before parsing.

Hope this helps...

|||

you were right. I am sorry I didnt tell you. Because I will make a person to write it with commas so it will be more secure to input prices.

What do you think about this idea? I will valide it so it will have to have a 00,000.00 format. But not there yet :-)

|||

The REPLACE function above will work regardless of whether there are commas in the string. If they are there, they will be removed, and if not the string will be passed as is.

Let me know if that works.

|||

the replace function (both) works fine as you suggested -

CONVERT(DECIMAL(10,2), REPLACE(@.Price, ',', ''))

I didnt know I can write functions in aspx. What kind of coding is this? It suprised me!

|||The REPLACE() function is actually an SQL function. I'm not sure if it's specific to T-SQL or if it is ANSI standard, but it's a very handy tool.|||lovely thx-for helpingsql

HELP: Text Field data type in SQL Server

I have a text field in a txt file.
It has multi-line.
Id like to copy / paste this text in a SQL Server table field.
I guess the field must be of a Text Data Type.
I tried it and it retains only the first line.
How can I paste that multimeline text from the SQL Server tools?
I tried from Management Studio but it does not work.
txs for your answer
In past I have used bulk insert to copy text file data into SQL server
text field. Are you specifying any delimiter or terminator for text
file?

HELP: Text Field data type in SQL Server

I have a text field in a txt file.
It has multi-line.
Id like to copy / paste this text in a SQL Server table field.
I guess the field must be of a Text Data Type.
I tried it and it retains only the first line.
How can I paste that multimeline text from the SQL Server tools?
I tried from Management Studio but it does not work.
txs for your answerIn past I have used bulk insert to copy text file data into SQL server
text field. Are you specifying any delimiter or terminator for text
file?

HELP: Text Field data type in SQL Server

I have a text field in a txt file.
It has multi-line.
Id like to copy / paste this text in a SQL Server table field.
I guess the field must be of a Text Data Type.
I tried it and it retains only the first line.
How can I paste that multimeline text from the SQL Server tools?
I tried from Management Studio but it does not work.
txs for your answerIn past I have used bulk insert to copy text file data into SQL server
text field. Are you specifying any delimiter or terminator for text
file?

HELP: MFC ODBC SQL Server problems

I got a problem where in SQL Server a table has a column of type REAL & size
(precision) 4. It's meant to store double types for C++ variable values.
In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
translates to wrong numbers. Such as:
1.1E+10 => 10000000512
1.00001E+10 => 10000001024
1.01E+10 => 10099999744
1.0000001E+10 => 10000100352
Strange huh? Has anybody experienced this before? Does anyone know what is
wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.6
to SP2. Still no success.You should not use "Real" to handle a big number like "1E+10" since real
data type does not have enough precisions to do the job. If you change
the data type to "Float", you will have better chance to get the number
right. Or if you want to store "Exact" numbers, you should use "decimal"
as your data type.
Charles Zhang
http://www.speedydb.com
SpeedyDB ADO.NET is the fastest, most secure, and most flexible ADO.NET
Provider over Wide Area Netword (WAN).
Andrew Wan wrote:
> I got a problem where in SQL Server a table has a column of type REAL & si
ze
> (precision) 4. It's meant to store double types for C++ variable values.
> In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
> MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
> translates to wrong numbers. Such as:
> 1.1E+10 => 10000000512
> 1.00001E+10 => 10000001024
> 1.01E+10 => 10099999744
> 1.0000001E+10 => 10000100352
> Strange huh? Has anybody experienced this before? Does anyone know what is
> wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.
6
> to SP2. Still no success.
>
>