Adding columns at runtime

I have a procedure that will return a dataset with an unknown number of columns (the user chooses a date range, and there will be one column per day). Since the columns are not always the same, the report designer doesn't want to help me with this. How can I make this work?

Thanks

Hello my friend,

For performance and ease-of-use reasons, I strongly recommend you take a different approach than using columns in this way, especially for reporting services. Please give details on what you are trying to do (the table structure and the query, etc) and I will try to suggest an alternative to achieving the same result.

Kind regards

Scotty

|||

Currently, I have this table:

CriticalUnitHistory
(
CritcalUnitHistory int (PK),
MarketID int,
UnitLCN int,
CriticalDate datetime,
CriticalReason varchar(50)
)

Every day, I look through a list of computers (each with a UnitLCN that is unique to its city) in different cities (MarketID corresponds to each city), and if its current status satisfies certain criteria, I add a record to this table with the MarketID, UnitLCN, current date and a short description of the criteria that it met to be included on the critical list.

I have been asked to create a report that will take a list of UnitLCNs and MarketIDs, and a date range, and show a table with the UnitLCNs down the left side, the dates across the top, and, if the computer was critical on a certain day, show the CriticalReason in the corresponding cell.

It would look something like this:

MarketID UnitLCN 1/20/2007 1/21/2007 1/22/2007 1/23/2007
1 519 No Contact No Contact
1 234 DL Error DL Error
1 219 GPS Fail GPS Fail

Hope that helps. Thanks for your assistance

|||

Hello my friend,

I take it you are having problems generating the data in this way from the original query. Refer to the following url: -

http://www.sqlteam.com/item.asp?ItemID=2955

It is really good. It shows you how to do a cross tab pivot to make data come out in this way. I tested the code myself with my own database tables and it works.

Kind regards

Scotty