is it possible to pass a report table column in sql reporting serivces

Hi,
I am having a storeprocedure which will return 10 columns data and i designed a table with 10 columns and i made 2 columns, visible = false.
Is it possible to show those two columns visible = true at runtime?
Please give me how to do.
Thanks & Regards
Lokesh

Select the column in the report designer, then set the Hidden property under Visibility in the Properties window to an expression. Set that expression to contain the logic you desire.

Hope that helps,

-Lukasz

is it possible to pass a report table column in sql reporting serivces

Hi,
I am having a storeprocedure which will return 10 columns data and i designed a table with 10 columns and i made 2 columns, visible = false.
Is it possible to show those two columns visible = true at runtime?
Please give me how to do.
Thanks & Regards
Lokesh

Select the column in the report designer, then set the Hidden property under Visibility in the Properties window to an expression. Set that expression to contain the logic you desire.

Hope that helps,

-Lukasz

sql

is it possible to merge 2 columns into 1 in SQL server

Hi..

is it possible to merge 2 columns into 1 to hold data like this... When the user imports the file in particular file they will be

ACT_ID1 Tot_ACT1 ACT_ID2 TOT_ACT2 ..... until 15

BB 1245.45 CT some amount ....

The 2 letter character may change prob for each file.. at leat i know of somethem may change

So while i transfer the data to the production database can is it possible to do some thing like

COL1 BB 1245.45

COL2 CT 12456.12 etc..

Any help will be appreciated.

Regards

Karen

Hi Karen

You could try two inserts. Do the first one on column 1 and 2 and then do the second one on 3 and 4 in the same destination table. Put them in a stored proc that gets called on the import. You may also want to add a column that identifies which set of columns they are from.

|||

Charles,

Thanks for your answer... I am getting these acronymns from another dbf file which the user imports... so does it makes sense to create a table called acronymns (may change the name later) and have the following fields..

AcryID(int identity) Name Description

so when i am importing the information into the production database i can reference the acronymn from this table by inner joining it and then insert the data into the production database... like the way you suggested...

Is this approach good or would it slow down things...

Any other suggestions are welcome too.

Regards

Karen

|||

Anything you can do to normalize the data is a good thing, in terms of performance, maintenance and scalability.

|||

so do u think the approach is good or bad ?

|||

Yes. Here's an article about normalization:

http://en.wikipedia.org/wiki/Database_normalization

is it possible to freeze column or row in a report?

I am using sql 2000 reporting service. One of the matrix report which has so
many columns and rows and it stretches beyond the width and height of the
paper size specified and the first row and column of the matrix report
showing the description of the data like the column/row header and I wonder
if it is possible to freeze this row and column in the report so that it is
visible when scrolling down or to the right of the report. Thanks.That is possible in RS 2005, but not in RS 2000
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul" <paul_mak@.shaw.ca> wrote in message
news:%23O6$fs7EGHA.3700@.TK2MSFTNGP15.phx.gbl...
>I am using sql 2000 reporting service. One of the matrix report which has
>so many columns and rows and it stretches beyond the width and height of
>the paper size specified and the first row and column of the matrix report
>showing the description of the data like the column/row header and I wonder
>if it is possible to freeze this row and column in the report so that it is
>visible when scrolling down or to the right of the report. Thanks.
>

Is it possible to dynamically create columns in a table in SSRS

Hi,

I have a sproc that returns somevalues and everything is working fine... and in my reports i am assigning the header data (in a detail column) based on the some feilds in the sproc... and there around 20 feilds that i want to show... but at a given time i am pretty sure that there wont be more than 10 fields that will have data.

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

any help is appreciated..

Regards

Karen

Hi,

So is it possible that show only the columns that have data in it and sometimes if there is less that 5 - 6 fields.. i want to realign the widths of the column in those tables without shrinking the size of the Table...

If there's no record in one of your row, you can use =IsNothing(Fields!productname.Value) filter your record. But if you want to hide the column that has no data, I suggest you to handle these works in your data accessing modular. For example, if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Thanks.

|||

Jin,

Thanks for your response.. what do u mean by remove the column from the recordset... cause i am populating the Reports using a stored procedure and sometimes... there may be some data or not..

Can u pls give me code snipet or an example

Regards

Karen

|||

Hi,

if you check one of your column is empty, just remove the column in your record set, so the column would not show in the report.

Here's the sample code, suppose you have two fields, Sp and Hd. If there's no data in Hd field of your return set, then only Sp field would been selected.

DECLARE @.NULLCOUNTINTSELECT @.NULLCOUNT =COUNT(*)FROM MatrixCapitalWHERE MatrixCapital.Spisnot nullif @.NULLCOUNT=0BEGIN SELECT MatrixCapital.HdFROM MatrixCapitalEND ELSEBEGIN SELECT MatrixCapital.Hd,MatrixCapital.SpFROM MatrixCapitalEND

Thanks.|||

Jin,

Thanks a lot for your answer so this mean that if i have more 5 - 6 columns NULL... i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

I have another question too.. if i have a table variable like

Declare @.tbl table

(

tblid int indentity(1,1),

Col 1,

Col 2,

..,

Col n

)

Is it possible to have a variable column size depending on the number of entries in my select column like for example.. my if i have 10 columns in my select statement and in that 5 are null... so can just insert 5 rows to the table and then remove the remaining columns out.

Regards

Karen

|||

Hi,

i have check for each column in the NULL count and then prob union for each feild so that my end resultset will be columns that has data in them?

Yes, that's right.

Is it possible to have a variable column size depending on the number of entries in my select column like for example..

Based on my knowledge, another way i can see is to use CASE WHEN clause in your SQL, but it still requires you to give differrent sql statments accoring to the "isNUll" result of a column.

Thanks.

sql

is it possible to do this?

Hi,

I have a table with 2 columns. If the condition for that text box evaluates to true i want to hide the second column and make the first columns width equal to the First and the second... for example this.

Company Match

Years of service Vesting %

its gonna display like this if the condition is false..

if true i want it to display

Company Match

Immediately Eligible (this should be in the middle) right now i can display it like this

Company Match

Immediately elgigible..

Any help will be appreciated.

Regards

Karen

Instead of hiding a column, why dont you use two asp:label and Hide the other if your condition matches. That makes your life more easier.

|||

i want to do this in the report itself?

Regards

Karen

|||

Each colum is act as a textbox

Go to its properties -> Visiblity-> Hidden

The value for Hidden can be an expression like " = iif(CDec(Parameters!xxxVal.Value) > 0.00,false,true)"

|||

Instead of hiding a column, why dont you use two asp:label and Hide theother if your condition matches. That makes your life more easier.

|||

how can i use asp:Label in SSRS? Never mind guys i solved it

Karen

sql

Is it possible to dispaly all columns for a record in a dropdownlist

Say a record has four columns in { Emp#, firstName, secondName, thirdName, spouseName }.

Now if you configure a sql datasource for a dropdownlist to return all columns, only Emp# will be displayed in the dropdownlist, the other columns wont be listed.

Is there a way to list the values of all columns in the dropdownlist using the sql datasource or in ADO.net code.

Thanks.

You have 5 columns there... but I think I see what you mean.

Anyway, the easiest way to do this is on your SQL.

"SELECT Emp#, firstname + ' ' + secondname + ' ' + thirdname + ' ' + spouseName AS TheName FROM table". Then set Emp# as the DataValueField value and TheName as the DataTextField value

|||

Mikesdotnetting:

"SELECT Emp#, firstname + ' ' + secondname + ' ' + thirdname + ' ' + spouseName AS TheName FROM table". Then set Emp# as the DataValueField value and TheName as the DataTextField value

But this would be listed as one dropdownlist value. I want each column to be listed in the dropdownlist as a new value. Since they are five columns, then i want them as five dropdownlist values.|||

Nope - sorry. Don't follow you. Do you mean you want them like this:

<item>firstname1</item>
<item>secondanme1</item>
<item>thirdname1</item>
<item>spousename1</item>
<item>firstname2</item>
<item>secondname2</item>

etc or do you mean like this:http://www.codeproject.com/aspnet/MultiColDdList.asp ?

|||

Mikesdotnetting:

Do you mean you want them like this:

<item>firstname1</item>
<item>secondanme1</item>
<item>thirdname1</item>
<item>spousename1</item>
<item>firstname2</item>
<item>secondname2</item>

Exactly.

But we would have to leave out firstname2 and secondname2. It would be like so

EMPLOYEE_1

<item>firstname1</item>
<item>secondanme1</item>
<item>thirdname1</item>
<item>spousename1</item>

EMPLOYEE_2

<item>firstname2</item>
<item>secondanme2</item>
<item>thirdname2</item>
<item>spousename2</item>

EMPLOYEE_3

<item>firstname3</item>
<item>secondanme3</item>
<item>thirdname3</item>
<item>spousename3</item>

Now notice that only one employee firstbname, secondname, thirdname and spousename would be listed in droopdownlist at a time and that is why i said we will leave out we would have to leave out firstname2 and secondname2 in your quoted example code above. But infact you have got the exact logic i want.

|||

Ok. You can get the record for each employee in your code-behind and loop through the fields in the DataReader, appending items to your dropdownlist as you go.

[Not complete or tested - just intended to convey the general idea]

while(rdr.Read())
{
for(int i = 0;i < rdr.FieldCount;i++)
{
MyDropdownlist.Items.Add(rdr[i].ToString());
}
}

|||

SELECT FirstName AS Value FROM MyTable WHEREEmpID=@.EmpID

UNION

SELECT SecondName AS Value FROM MyTable WHEREEmpID=@.EmpID

UNION

SELECT SpouseName AS Value FROM MyTable WHEREEmpID=@.EmpID

|||

Almost all the replies so far work in regard to displaying the values in the dropdownlist but not in retaining the selectedindex on page postback.

Now i have discovered why the selectedindex is always being read as 0 but i do not know how to resolve the problem, but atleast i now know why.

This behavour is being caused by the presence of a multivew and its child views. I say so because if i move my databound controls outside of the multiview views, every thing runs great, when i put them back in the view, selected index is always 0. I have tested and confirmed that this is the cause. I however have no idea how to fix this even after googling for some days.

As more proof, if i go to my page's markup and look at multiview tag, it is underlined in red meaning some error and in the tooltip, the error says

"Validation(ASP.NET): Text can not be allowed between opening and closing tags of element multiview" .

Using a multiview and its child views made my application look smart and easy to navigate, but now here is the pain at last.

sql

Is it possible to create the columns of a #Temp table base on a query result?

I’ve been trying without success something like this…

CREATE TABLE #table

(

(SELECT MAX(Column) FROM Table) varchar(50)

)

I'm working with SQL 2005; thank you for any help in advance.

Not quite like that.

You can SELECT ... INTO.

In the process, you need to provide a column Name for any computed or derived columns, and you can change the datatype.

Something like this:

Code Snippet

USE Northwind
GO

SELECT cast( max( EmployeeID ) AS decimal(6,2)) AS MaxEmp
INTO #MyTable
FROM Employees

|||

There are 2 options,

Create Table #Table (

ColumnValue Varchar(50)

);

Insert Into #Table

SELECT MAX(Column) FROM TableName;

--OR

SELECT Cast(MAX(Column) as Varchar(50)) ColumnValue Into #Table FROM TableName;

|||Thank you for the help provided so far. Another question on the same subject. How can the Column name be assigned dynamically after a query result? Thanks.
|||

You should use the aliase name. if you failed to give the aliase name for the expression sql server thow an error says that "No column was specified for column n on tablename".

Code Snippet

Select Max(Column) as MaxColumn Into NewTable From OldTable

Select Max(Column) MaxColumn into Newtable From OldTable

|||

As indicated above, No.

My apologies, I guess this statement was not clear enough.

In the process, you need to provide a column Name for any computed or derived columns...

sql

Is it possible to create dynamic reports using reporting services

I have a requirement to create dynamic reports for my client.

once i create these reports then the user will choose columns of there choice.

so the columns may belong to multiple tables.

Now the report should get generated with the layout etc. is it possible.

since our project is totally on the webserver(webbased.)

please if you can provide me with any links with dynamic report creation wizards.

and also we only use Stored procedures via database.

which is best is writing the entire queries right behind the layouit or calling the entire logic via Stored procedure. i am a bit confused. this is my first project working on reports itself.

Thank you all for the helpful information.

Sounds like you need to generate the report definitions programatically. The RDL Object Model code sample in this download should help.sql

Is it Possible to Concatenate a Text Column of a Table

Hi All,
I have a table in which one of the columns is Text.
I need to concatenate a hard coded text with the value of the text column
for each and every row of the table. Is this possible.
Sevugan.CI don't understand why you need to concatenate anything, *especially* the
same value for *every row.*
Can't the consuming application do that?
If you aren't storing > 8000 characters, then you could say:
DECLARE @.constant VARCHAR(12);
SET @.constant = 'some prefix';
SELECT @.constant + CONVERT(VARCHAR(7988), textColumn) FROM table;
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:A1A3FE64-4D11-4217-97AC-A8A6A380F355@.microsoft.com...
> Hi All,
> I have a table in which one of the columns is Text.
> I need to concatenate a hard coded text with the value of the text column
> for each and every row of the table. Is this possible.
>
> --
> Sevugan.C|||Hi
Thanks for your reply.
I am storing more than 8000 chars in the text column. Then, how this could
be resolved.
Sevugan.C
"Aaron Bertrand [SQL Server MVP]" wrote:

> I don't understand why you need to concatenate anything, *especially* the
> same value for *every row.*
> Can't the consuming application do that?
> If you aren't storing > 8000 characters, then you could say:
> DECLARE @.constant VARCHAR(12);
> SET @.constant = 'some prefix';
> SELECT @.constant + CONVERT(VARCHAR(7988), textColumn) FROM table;
>
>
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:A1A3FE64-4D11-4217-97AC-A8A6A380F355@.microsoft.com...
>
>|||> I am storing more than 8000 chars in the text column. Then, how this could
> be resolved.
Some options:
(a) have the consuming application perform the concatenation
(b) move to SQL Server 2005, where you can use VARCHAR(MAX)
(c) store your redundant prefix with the data (you can use UPDATETEXT for
that)|||Hi
I am using the third option suggested by You. But, I am getting the
following error.
What could be the reason. How it should be resolved.
Server: Msg 7123, Level 16, State 1, Procedure sp_SendStockRebalanceMails,
Line 100
Invalid text, ntext, or image pointer value
0x0100010000000000331C2F0C00000000.
The statement has been terminated.
--
Sevugan.C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Some options:
> (a) have the consuming application perform the concatenation
> (b) move to SQL Server 2005, where you can use VARCHAR(MAX)
> (c) store your redundant prefix with the data (you can use UPDATETEXT for
> that)
>
>|||> I am using the third option suggested by You. But, I am getting the
> following error.
> What could be the reason. How it should be resolved.
Who knows? You forgot to include DDL, sample data, and the code in your
procedure.
http://www.aspfaq.com/5006|||> I am using the third option suggested by You. But, I am getting the
> following error.
> What could be the reason. How it should be resolved.
Who knows? You forgot to include DDL, sample data, and the code in your
procedure.
http://www.aspfaq.com/5006

Is Index useful?

Hi
In a table A (currently holding approx. 4700 rows but will
expand during use) the primary key is a combination of
three columns col1, col2, col3.
The primary key is indexed with a clustered index
(FillFactor 90).
On the columns col1, col2 an nonclustred index have been
added (FillFactor 90) too, this because I guess these two
columns together form a foreign key to another table B
(currently holding approx 1000 rows - number will raise).
The two indexes are both sorted ascending for all values.
My assumption is that the nonclustered index are of no use
and only adds overhead to inserts etc., since both col1
and col2 allready are indexed (in same sequence and sort
order) in the clustered index, so a query on col1 and/or
col2, and a join between table A and B can already use the
clustered index values for the col1, col2 to speed up the
query and the join using a merge join.
Infact the DBCC SHOWSTATISTIC returns the exact same
result (density etc.) for both the clusterd and
nonclustered index (propably because the nonclustered
index after having scanned its only structure will point
to the clustered index anyway and use this afterwards - to
my recollection).
But does the extra col3 of the clustered index make it
useless for queries/order by/joins on only col1, col2?
or
Can I - with a good nights sleep in mind - just delete the
nonclustered index as it is superflous?
Kind regards
Jakob PerssonJacob
On which column(s) do you have clustered index?
What is mostly WHERE caluse in your queries?
If all three columns are participated in your query you probably want to
consider using COVERING index.
A clustered index is more useful where you try to retrive a set of data ( a
lot of rows) on other hand a non clustered index will be more useful where
you retrieve a single row.
Again it is depends upon you requriments, you will have to monitor your
query to find out an appropritate indexes
SET STATISTICS IO may be useful ,also look at excution plan of the query.
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>|||Jakob,
>But does the extra col3 of the clustered index make it
>useless for queries/order by/joins on only col1, col2?
No. The index with col1, col2, col3 is just as useful as the one with col1, col2. So your thinking
is spot on. One thing is of course if col1 and col2 are very narrow and col3 is very wide, but that
is obvious.
Another, more important issue, is that the index on col1, col2 is an NC index which has other
characteristics than a clustered index. The optimizer might use this index to cover queries (or
partially cover), which can have significant performance gains compared to a clustered index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jakob Persson" <jakobpersson@.yahoo.dk> wrote in message
news:084601c3b8bc$173b71c0$a501280a@.phx.gbl...
> Hi
> In a table A (currently holding approx. 4700 rows but will
> expand during use) the primary key is a combination of
> three columns col1, col2, col3.
> The primary key is indexed with a clustered index
> (FillFactor 90).
> On the columns col1, col2 an nonclustred index have been
> added (FillFactor 90) too, this because I guess these two
> columns together form a foreign key to another table B
> (currently holding approx 1000 rows - number will raise).
> The two indexes are both sorted ascending for all values.
> My assumption is that the nonclustered index are of no use
> and only adds overhead to inserts etc., since both col1
> and col2 allready are indexed (in same sequence and sort
> order) in the clustered index, so a query on col1 and/or
> col2, and a join between table A and B can already use the
> clustered index values for the col1, col2 to speed up the
> query and the join using a merge join.
> Infact the DBCC SHOWSTATISTIC returns the exact same
> result (density etc.) for both the clusterd and
> nonclustered index (propably because the nonclustered
> index after having scanned its only structure will point
> to the clustered index anyway and use this afterwards - to
> my recollection).
> But does the extra col3 of the clustered index make it
> useless for queries/order by/joins on only col1, col2?
> or
> Can I - with a good nights sleep in mind - just delete the
> nonclustered index as it is superflous?
> Kind regards
> Jakob Persson
>