Create report from RDL file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Create report from RDL file

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-10 : 10:26:12

How do I create a report from an RDL file? I have a report that I created for one of my companies (one of twelve companies). I now need to use this same report to create the same report with the other 11 companies. They each have their own databases. I wanted to take the .rdl file and do a find and replace on the database name. Can I take the existing .rdl file and easily create the other reports? Does anyone know how to accomplish this. The only thing that needs to change is the report title and the database names. Example:

mas_databaseXYZcompany

mas_databaseABCcompany

All other columns/fields stay the same. I'm stumped. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-10 : 12:15:08

If its such a small change, i think find & replace would do the trick for u.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 12:41:25

You can easily import your rdl file via Report Manager (http://.../Reports (default name))

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-10 : 15:16:12

In the Report Manager I can import the .rdl file and then create the other reports?

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-10 : 15:18:41

I'm sorry, but if I do just the find and replace then I'll still have to format in the Layout Design all the things I wouldn't have to do if I could just use my RDL file correct? I'm asking because I haven't done any of this yet. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 15:26:56

What you can do is create multiple rdl files, each with the parameter that you need, then import all of them into Report Manager.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-11 : 16:32:16

Ok. Maybe I have not explained this very well. First off, I don't have access to Report Manager or Report Builder.

If anyone has used Crystal Reports before and has had a 'base' report where you can go in and change the Datasource locations. This allows you to use different tables that have the same fields/columns. This is what I'm trying to accomplish in SSRS.

I have a base report. I have one database that has 12 company tables on it. All columns are the same just a different company table needs to be interrogated. How can I do this? It seems like I can't just do find and replace in the sql query and then 'save as' a new file. It won't let me do it. Any help on this would be greatly appreciated because I have no clue. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-11 : 18:42:15

If you've got SSRS, then you've got Report Manager.

Perhaps you should show us your query that needs to be modified.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osupratt
Posting Yak Master

238 Posts

Posted - 2008-03-12 : 10:48:49

I have 4 occasions on the FROM statement that would need to be changed for each company (dbo.MAS_BSC........ to dbo.MAS_CCS, etc.)

SELECT

CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 11 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEleven,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 10 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessTen,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 9 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessNine,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 8 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessEight,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 7 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessSeven,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 6 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessSix,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 5 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessFive,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 4 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessFour,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 3 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessThree,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 2 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessTwo,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END AS MonthLessOne,
CASE WHEN DATEDIFF([MONTH], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END AS CurrentMonth,
CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 0 THEN id.SOExtChargeAmount ELSE 0 END AS CurrentYearTotal,
CASE WHEN DATEDIFF([YEAR], ih.SOTransDate, GetDate()) = 1 THEN id.SOExtChargeAmount ELSE 0 END AS LastYearTotal,
CASE WHEN DATEADD([year], - 1, GetDate()) > ih.SOTransDate AND DATEDIFF([YEAR], ih.SOTransDate, GetDate())
= 1 THEN id.SOExtChargeAmount ELSE 0 END AS LastYearYTD,

case
when substring(a.Account,5,3)='900' then 'ALE'
when substring(a.Account,5,3)='902' then 'ATO'
when substring(a.Account,5,3)='904' then 'BOW'
when substring(a.Account,5,3)='906' then 'BRY'
when substring(a.Account,5,3)='908' then 'BPT'
when substring(a.Account,5,3)='910' then 'BYD'
when substring(a.Account,5,3)='912' then 'BUF'
when substring(a.Account,5,3)='914' then 'CLE'
when substring(a.Account,5,3)='916' then 'GRN'
when substring(a.Account,5,3)='920' then 'DXN'
when substring(a.Account,5,3)='924' then 'CTH'
when substring(a.Account,5,3)='926' then 'ELC'
when substring(a.Account,5,3)='928' then 'FTL'
when substring(a.Account,5,3)='930' then 'FTW'
when substring(a.Account,5,3)='932' then 'I35'
when substring(a.Account,5,3) IN ('936','000') then 'GAI'
when substring(a.Account,5,3)='939' then 'STW'
when substring(a.Account,5,3)='940' then 'GRE'
when substring(a.Account,5,3)='942' then 'HEN'
when substring(a.Account,5,3)='944' then 'FTS'
when substring(a.Account,5,3)='948' then 'JAC'
when substring(a.Account,5,3)='952' then 'JEN'
when substring(a.Account,5,3)='956' then 'KIL'
when substring(a.Account,5,3)='957' then 'MCA'
when substring(a.Account,5,3)='958' then 'MIN'
when substring(a.Account,5,3)='960' then 'NOC'
when substring(a.Account,5,3)='962' then 'ODE'
when substring(a.Account,5,3)='964' then 'BTP'
when substring(a.Account,5,3)='966' then 'RA'
when substring(a.Account,5,3)='968' then 'RIF'
when substring(a.Account,5,3)='970' then 'SWD'
when substring(a.Account,5,3)='971' then '3PS'
when substring(a.Account,5,3)='972' then 'ROC'
when substring(a.Account,5,3)='976' then 'SJO'
when substring(a.Account,5,3)='978' then 'SMB'
when substring(a.Account,5,3)='980' then 'STO'
when substring(a.Account,5,3)='982' then 'TOL'
when substring(a.Account,5,3)='984' then 'VEL'
when substring(a.Account,5,3)='985' then 'CFP'
when substring(a.Account,5,3)='986' then 'CLM'
when substring(a.Account,5,3)='988' then 'WHI'
when substring(a.Account,5,3)='992' then 'WRA'
when substring(a.Account,5,3)='995' then 'ADM'

ELSE 'Unknown'END AS Location,

case
when substring(a.Account,5,3) IN ('000','900','904','908','910','914','920','930','932','936','939','948','960','964','966','970','971','976','978','980','982','985','986','988','995') then 'North Region'
when substring(a.Account,5,3) IN ('902','957','984') then 'Woodford Region'
when substring(a.Account,5,3) IN ('906','926') then 'South Region'
when substring(a.Account,5,3) IN ('912','924','942','952','956','958') then 'East Region'
when substring(a.Account,5,3) IN ('916','944') then 'Fayetteville Region'
when substring(a.Account,5,3) IN ('928','940','968','972','992') then 'Rockies Region'
when substring(a.Account,5,3) = '962' then 'West Region'

ELSE ' 'END AS Region,

ih.SOTransDate, ih.InvoiceNumber, cm.CustomerName, id.DetailSeqNumber, id.LineType, a.Account, id.SOExtChargeAmount
FROM dbo.MAS_BSC_AR1_CustomerMaster cm, dbo.MAS_BSC_ARN_InvHistoryHeader ih, dbo.MAS_BSC_ARO_InvHistoryDetail id,
dbo.MAS_BSC_GL_Account a
WHERE cm.CustomerNumber = ih.CustomerNumber AND ih.InvoiceNumber = id.InvoiceNumber AND id.SOGLSalesAcct = a.AccountKey AND year(ih.SOTransDate) >= year(dateadd(year,-1, getdate()))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-12 : 14:12:49

What I would do is wrap it into a stored procedure, one stored procedure for each company. Then have an rdl file per company and each of those would point to the matching stored procedure.

But this is a database design issue too. You shouldn't have similar tables like this, but rather one table with all of the companies in it and a column or columns that signifies which company is which in each row. Then you'd just need to have a WHERE clause in your queries to specify which company to get. And then you could also use a report parameter.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources