Since nobody else on my bloglist reported this (at least I didn't notice)... Here you find the KB article with the fixed issues: http://support.microsoft.com/kb/913090/en-us. And here is the download: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc
Wow, I really expected to see that download later... But I got some hints that it will we available in April since every vendor has to ship the version TPC benchmarks are based on a defined time after publishing the results. I'm not aware of that timeframe, but from that timeframe you could expect to see SP1 before May...
Let's see how things developed...
Thomas
Quite a while ago Brian Welker talked about a delivery extension to Reporting Services to directly send reports to SharePoint. Well, it’s not there and it will still take some time (Office 12 will change the situation). However I was at a customer and he asked for exactly this functionality. So I thought about different options…
The easiest solution would be just to use web folders and data driven subscriptions (or just subscriptions). You just send the reports to a network share which is a SharePoint document library. Well, that’s really easy. But unfortunately you need the “Web Client” service for that and this is disabled at the customer’s site. No idea why but somebody told me that this is quite common…
So the other option would be to leverage web services. Too bad, Microsoft “forgot” to include a “document upload” web service. There’s an example for a “workaround” in MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_sp2003_ta/html/odc_writingcustomwebservicesforsppt.asp describes how to add a document upload web service to SharePoint) but since the customer has quite a lot of SharePoint servers, deployment would be a problem.
Since Reporting Services 2000 SP2 you have that nice WebParts you can use. But the customer didn’t like to do the administration for all the users accessing reports. He just wanted some static reports in SharePoint which are refreshed automatically.
So the standard subscriptions were an option, too. But if you deliver rendered reports the mail data volume increases quickly, so that wasn’t the perfect way, too.
With the help of some colleagues I decided to go with WebDAV. And that was not to hard… WebDAV is quite easy to understand (at least the basics). You simply do a HTTP GET/PUT/DELETE to download, upload or delete documents from an URL. WebDAV doesn’t know about SharePoint’s metadata, that’s the downside. But this didn’t matter in this case. If you enable versioning you also can just post changed documents using the same name and appear as new versions of an existing document.
So you need just some little scripts to get the report from reporting services, delete it from SharePoint (if you don’t like to have a new version) and send it to the document library.
Since I’m an old DTS hacker (and the customer didn’t upgrade to SQL 2005, yet) I used a DTS package as a “container” for my scripts.
This is an example of a report services download script:
Const adTypeBinary = 1
Const adModeReadWrite = 3
Const adSaveCreateOverWrite = 2
set xmlHTTP=createobject("msxml2.xmlhttp")
Set adoStream = CreateObject("ADODB.Stream")
xmlHTTP.Open "GET", “myReportURL”,bGetAsAsync,”myUser”, “my Password”
xmlHTTP.Send
With adoStream
.Type = adTypeBinary
.Mode = adModeReadWrite
.Open
.Write xmlHTTP.responseBody
.SaveToFile “myFile”, adSaveCreateOverwrite
.Close
end with
set xmlHTTP=nothing
set adoStream=nothing
You can easily wrap it in a DTS ActiveX script task or whatever scripting you prefer. Then you have to exchange myURL with the URL of the report you want to export (i.e. as PDF). A good idea is to open the report in report manager and export it. Just before the “save as” dialog appears copy the URL of the IE window starting up. This is the URL you want…
You can also use “myUser” and “myPassword” to pass credentials for the report manager. I didn’t try a direct “download/upload” scenario, so I first save the exported report to a file “myFile”. Then I need another script to upload the file to SharePoint. This might look like that:
set xmlHTTP=createobject("MSXML2.XMLHTTP.3.0")
Set objStream = CreateObject("ADODB.Stream")
objStream.Type = 1 ' adTypeBinary
objStream.Open
objStream.LoadFromFile “myFile”.Value
xmlHTTP.Open "PUT", “myURL”,false,”myUser”,”myPWD”
xmlHTTP.Send objStream.Read
set objStream=nothing
set xmlHTTP=nothing
“myFile” is the temp file, “myURL” is the URL of the file in the SharePoint document library (just open it in SharePoint and look at he URL, you can extract the path from there, then add the filename you want to use), “myUser” and “myPassword” are credentials to log in SharePoint.
If you want to delete the old version of a file before posting a new one, just use this script:
set xmlHTTP=createobject("MSXML2.XMLHTTP.3.0")
xmlHTTP.Open "DELETE", “myURL”,false,”myUser”,”myPassword”
xmlHTTP.send
set xmlHTTP=nothing
You can leave out the credentials in all the scripts if you have a “clean” AD environment and the user running the script has rights to Report Server / SharePoint.
So this does the trick. Nothing fancy, just some lines of code… I tried that with SQL 2000 (also Reporting Services 2000), but that should work with 2005 as well.
I hope this gives you a little “inspiration” how to use Reporting Services and SharePoint together. And perhaps I helped to heal the pain while you’re waiting for Office 12…
Thomas
It’s quite a while since my last blog and I’m not quite sure how the frequency of new entries will develop in the next time… Some things changed in the last weeks and all are connected to my new employer www.avanade.com. It’s really a great company, a joined venture of www.accenture.com and www.microsoft.com bringing together Accenture’s business expertise with the Microsoft technology we (hopefully) all love…
My job is still closely connected to Business Intelligence, perhaps even more than before. It’s also still very technology focused while my colleague Markus, who moved to Avanade with me, is concentrating on the business side. So we’re now a small little team focusing on BI here in Germany (worldwide we have quite a number of people experienced in Business Intelligence projects) and there are some indicators which make me feel confident that there will be a significant growth in the future. SQL Server 2005 is knocking on our doors and I would be very surprised if not quite a number of companies will let it in.
Currently Avanade is working on an ETL framework bringing together the best practices of many BI projects around the world with the very new things coming up with SQL Server 2005. We can use the experience from SSIS implementations we just finished or which are getting ready for the customer soon.
Together with some other toolsets Avanade offers a very solid foundation for delivering Microsoft based IT projects. So if you’re looking for a partner to develop an enterprise BI solution I have a very good suggestion for you ;-))
And I’ll still try to keep you up-to-date with interesting things I find out. You’re also welcome to give me feedback and if you have any suggestions what you would like to read about at this place, please let me know!
Thomas
Did you ever try to setup international reporting with SSRS? It will be a very hard job or better, it doesn’t work. I hope that this will change (perhaps with SP1) but I don’t know for sure.
So why isn’t multilingual reporting easy? Microsoft introduced the Unified Dimensional Model (UDM) as a common data source for reporting. This is a cool thing, I like it very much. The UDM also has “translations” for each member/dimension/level name and you can define different sources (fields) for each language you define.
So what? This seams to be perfect, what am I complaining about? From the standpoint of the UDM everything is fine. But now we try to use these features in a report.
Which language to use?
So first you have to know which language you want to display in the report. How do you tell the UDM which language you’re currently working with? That happens with a parameter in the connection string. IDW15 has a bug in it, so this will not work perfectly. Brian has a blog about that… http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx
Translating Names
OK, so you dropped your tables in the report, you have column headers and footers, everything seams to be fine. And now you expect that these header/footer are translated automatically!? No, this is not possible (out of the box). The only way I know is by creating a new cube (or a separate UDM) where you store all the report/dimension/level/attribute names and issue a query (well, you need one query per name you want to have!) and exchange each multilingual text with a link to the query result… That’s a bunch of work and it’s not easy to manage.
Perhaps now you say: Why a new UDM, the names are all already translated in the UDM so why setup a new database/UDM to store them? The answer is easy: Maybe you simply need other labels in the report than you have already in the UDM. I.e. “Transactions per Second” might be a good measure name but on the report you only want “TPS” because it’s shorter… Where do you want to store this information? Perhaps in a different language “TPS” is not the correct abbreviation? So you need a different table to store this information because the UDM simply doesn’t have any place to store it!
Translating Report Manager
Microsoft did a good job to translate the Report Manager. It switches automatically the language depending on the system’s language settings. That’s great. But the report/folder names don’t switch! So your report “Sales Report” appears with the same name for German users, too. That’s not good… The only workaround we found out is that you create folders per language and place each report in with localized names in the corresponding folders. Much work, hard to support…
Translating Parameters
If you work with parameters you want different parameter captions for each language. Guess what you can do? Correct: Nothing. It’s not possible; at least we didn’t find any workaround for that but to create a report per language and well, that’s not what we/you want…?!
Free-Text Parameters
Well, a little off-topic… But did you ever try to pass free-text parameters to an UDM? Did you ever try to tell your users that they have to enter “[Time].[Year].[2005]” instead of “2005” when they want only 2005’s data? Then you have to deal with strtomember() functions and so you loose the functionality of the query designer… Or do you see any alternative for that? I don’t…
So thanks for my colleagues at Software4You (http://www.software4you.com) for providing input for this post. We are all waiting for some feedback (from Microsoft?) about what is planned in the future to address these issues…
Thomas