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 is a good practice to use autoincrement in access

Hi just new here in the forum i have a question

is it a good practice to use autoincrement fields in an access database for master and detail? and for unique record identification. if not please tell why..
im currently using access and with autoincrement for unique record identification and for master and detail.. then there is a possibilities to transfer from access to mssql. will i encounter problems when migrating since i am using an autoincrement fields

in mssql is there an autoincrement fields like access?

dont be harsh ok.. just anoob trying to learn. :eek:The equivalent of autoincrement in SQL server is the IDENTITY property.
It is possible to port existing ID values into a SQL Server table with an identity property set, but to do so you will need to temporarily turn off the identity property, and then reset it when the data load is complete.
The upsizing wizard for Access -> SQL Server may handle this for you.|||The equivalent of autoincrement in SQL server is the IDENTITY property.
It is possible to port existing ID values into a SQL Server table with an identity property set, but to do so you will need to temporarily turn off the identity property, and then reset it when the data load is complete.
The upsizing wizard for Access -> SQL Server may handle this for you.

thanks for the fast reply blindman.. but, is it a good practice to use autoincrement in a master detail ?? my master table will have an autoincrement fields, my detail will have a longint field to accomodate for the master's autoincrement field.
i will be using this method on a library program ..|||What is going to be the primary key of the detail table? A natural composite key including the master ID, or are you going to create a new surrogate key (possibly autoincremented)?|||What is going to be the primary key of the detail table? A natural composite key including the master ID, or are you going to create a new surrogate key (possibly autoincremented)?

the primary key of the detail table will be another autoincrement fields but with no relation whatsoever on the master field.|||An surrogate key on a detail table is not required, though it may facilitate SQL coding and programming the interface. You can leave it off and still have functional database design using the natural key of the detail table. Note that the natural key of the detail table is usually a composite of the surrogate key of the master table and some detail element that is unique within each master record.
One other thing; you keep asking whether it is good practice to use an autoincrementing column for a key. In SQL Server you basically have two choices for surrogate keys, and those are auto-incrementing identity values and GUIDs (Globally Unique Identifiers). Stick to using either of these. By definition surrogate keys should bear no relation to the underlying data, so you shouldn't be spending time mucking arround with methods of generating them. Hence, auto-generated surrogate keys such as Idenity columns and GUIDs are preferred.|||An surrogate key on a detail table is not required, though it may facilitate SQL coding and programming the interface. You can leave it off and still have functional database design using the natural key of the detail table. Note that the natural key of the detail table is usually a composite of the surrogate key of the master table and some detail element that is unique within each master record.
One other thing; you keep asking whether it is good practice to use an autoincrementing column for a key. In SQL Server you basically have two choices for surrogate keys, and those are auto-incrementing identity values and GUIDs (Globally Unique Identifiers). Stick to using either of these. By definition surrogate keys should bear no relation to the underlying data, so you shouldn't be spending time mucking arround with methods of generating them. Hence, auto-generated surrogate keys such as Idenity columns and GUIDs are preferred.

thanks.. i thought that i am at a lost.. thanks for clarifying those things. at least now i can sleep well coz i wont change the autoincrement fields into my own generated nos.. thanks again blindman. your a great help..