Link to home
Start Free TrialLog in
Avatar of BobRosas
BobRosas

asked on

Use string as SSRS table name

I have a data source that is a folder filled with daily txt files.  Everyday I want to be able to automatically read yesterday's file.  In Report Services if I use the following code I am able to see the contents of a file.  
SELECT        *
FROM            12272012.txt

In SSMS if I use this code...
SELECT replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt' as ErrLog

It produces the file name I need (Ex: 12272012.txt) but it doesn't work in the FROM statement of SSRS.   Please help me with how I need to change this to make it work in SSRS?
SELECT *
FROM replace(convert(varchar, DATEADD(d, -1,getdate()), 101), '/', '') + '.txt'
SOLUTION
Avatar of venk_r
venk_r
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BobRosas
BobRosas

ASKER

Thank you for your help but if I do that I'm not sure how to access the sp.  Currently the data I need to access is a text file so my DataSource is an ODBC connection.  I don't know how to use my ODBC Data source connection and then connect to the sp.  Should I be doing this an entirely different way?
Avatar of Howard Cantrell
One thing you may want to look at; is made your txt files into XML (layout) file.
since SSRS are just readable XML files.
Thank you for your input!  Is there a place I can go to read more about how to do this?  Or can you provide me with more details?  I would still need to be able to automatically update the txt file to an xml file and I don't know how to do that.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've increased points so I can award to both since I'm not sure how I will proceed.  Thank you for your help!