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 access the @@RowCount from a Sproc in a report

Hi ,

I am trying to access the @.@.RowCount that is been returned by my stored procedure in a report. Can you please tell me how can i access it, This is my Stored procedure

USE [ICCStatements]GO/****** Object: StoredProcedure [dbo].[rpt_SelectInvestments] Script Date: 08/03/2007 11:54:47 ******/SET ANSI_NULLSOFFGOSET QUOTED_IDENTIFIEROFFGOALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanIdAS integer)ASBEGIN-- History-- 08/17/2004svanpatter/JSWCOinitial version created-- 08/30/2004svanpatter/JSWCOadd-- Select available fundsDeclare @.CountintSELECT [ClientPlan].PlanId, [ClientPlan].PlanName,-- Fund.[FundName] AS InvestmentName,CASEWHEN PlanFund.PlanFundDisplayNameISNULLTHEN Fund.ShortNameELSE PlanFund.PlanFundDisplayNameEND InvestmentName,'F'AS InvestmentType,--PlanFund.PlanId As InvestmentID PlanFund.IsPortfolioFundOnly, PlanFund.FundDisplayOrderAs InvestmentIDFROM [ClientPlan]--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdINNERJOIN PlanFundON [ClientPlan].PlanId = PlanFund.PlanIdAnd IsPortfolioFundOnly = "0"INNERJOIN FundON PlanFund.FundId = Fund.FundId--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperIDWHERE[ClientPlan].PlanId = @.PlanIdUNION-- Select PortfoliosSELECT [ClientPlan].PlanId, [ClientPlan].PlanName, PlanPortfolio.PortfolioNameAS InvestmentName,'P'AS InvestmentType,NULL,PlanPortfolio.PortfolioIdAs InvestmentIDFROM [ClientPlan]INNERJOIN PlanPortfolioON [ClientPlan].PlanId = PlanPortfolio.PlanId--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanIdWHERE[ClientPlan].PlanId = @.PlanIdORDER BYInvestmentType, InvestmentIDSet @.Count =@.@.RowCountReturn @.CountEND

Can some one pls tell me how can i access @.Count from the report.

Any help will be appreciated..

Regards,

Karen

If you meant Crystal Reports then I don't understand why you need this as in CR you already have such function that returns number of rows returned by SP.

|||

I meant in SQl server Reporting Services.

|||

=Count(Field!somefieldname.Value)

|||

lavanya,

Is it possible to access the no. of rows returned by the stored procedure....

Regards

Karen

|||

You shouldn't need to return the RowCount from the stored procedure. In SQL Reporting Services there is a CountRows() function that can be used to return the number of rows in the datasource. To use it you would call it like so: =CountRows("DatasourceName"). The "DatasourceName" obviously being the name of the datasource that calls your stored procedure.

Also, the = sign is only needed if it is the only item, or first item, in a text field in the report.

Is IDENTITY_INSERT OFF safe?

Say I have a sproc that is occasionally used to restore deleted records to a
table.
--If I use
SET IDENTITY_INSERT myTable ON
--Then insert the records...
INSERT INTO myTable(ID, AnyField)
VALUES (2,'hello);
--Then I return table to original state
SET IDENTITY_INSERT myTable OFF
During that process, what would happen if another user was attempting to
insert records into the same table?
Even if my transaction only takes .2 milliseconds, is it possible another
user will get an error, or is SQL Server smart enough to delay their
transaction(s) [lock the table] until mine is complete?
Thanks,
ChrisOther updaters are not blocked by SET IDENTITY_INSERT ON. But this is not a
problem since the IDENTITY_INSERT ON applies only to the connection that
runs it. The identity column is handled normally in all other connections
(unless, of course, that connection has also set IDENTITY_INSERT ON.
So, yes, it is safe to use.
Tom
"Chris" <rooster575@.hotmail.com> wrote in message
news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
> Say I have a sproc that is occasionally used to restore deleted records to
> a table.
> --If I use
> SET IDENTITY_INSERT myTable ON
> --Then insert the records...
> INSERT INTO myTable(ID, AnyField)
> VALUES (2,'hello);
> --Then I return table to original state
> SET IDENTITY_INSERT myTable OFF
> During that process, what would happen if another user was attempting to
> insert records into the same table?
> Even if my transaction only takes .2 milliseconds, is it possible another
> user will get an error, or is SQL Server smart enough to delay their
> transaction(s) [lock the table] until mine is complete?
> Thanks,
> Chris
>|||Thanks Tom!
"Tom Cooper" <tom.no.spam.please.cooper@.comcast.net> wrote in message
news:WsmdnSkiXZ4uqBvZnZ2dnUVZ_vqdnZ2d@.co
mcast.com...
> Other updaters are not blocked by SET IDENTITY_INSERT ON. But this is not
> a problem since the IDENTITY_INSERT ON applies only to the connection that
> runs it. The identity column is handled normally in all other connections
> (unless, of course, that connection has also set IDENTITY_INSERT ON.
> So, yes, it is safe to use.
> Tom
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:OsTb2VdiGHA.4512@.TK2MSFTNGP02.phx.gbl...
>|||ACID
Isolation