One of the new features in Reporting Services 2008 that debuted in the SQL Server 2008 RC0 build is the enhanced textbox report item. In previous releases, if you wanted to mix static and dynamic expression-based text, you either needed multiple textboxes or a Visual Basic expression to concatenate strings together. The first approach led to textbox “explosion”. The disadvantage of the second approach was that you couldn’t format string fragments inside the same textbox independently.
In Reporting Services 2008, the textbox report item has been redesigned to support multiple bands of text. The screenshot shows a report title of a sales order report. In the past, you would need two textboxes (or three if you wanted different formatting for the sales order number). You may be surprised to find that the entire title is implemented as a single textbox with two paragraphs. The second paragraph combines static text (Order #:) with dynamic text ([SalesOrderNumber]), which defines a placeholder for a dataset field value. Each fragment can have its own format settings. Thanks to these enhancements, you’ll find that by moving to Reporting Services 2008, you need fewer textboxes and you need to write less often expressions that concatenate text. Moreover, the new textbox lets you implement report solutions, such as mail merge, that were difficult or impossible to implement with previous releases.
Many report authors will rejoice learning that the textbox report item now supports a subset of HTML tags for formatting the text content. This is also known as rich formatting (not be confused with RTF which is not supported). You can import static HTML text or bind the textbox to a dataset field. For instance, if the dataset field includes HTML tags, such as <b>SO50750</b>, you can configure the textbox to interpret these tags and display the sales order number in bold.
Microsoft declared build #1442.32 to be the official SQL 2008 RC0 build and made it publicly available.
Key SSRS improvements in this build:
Rich text formatting. You can now format the text inside a textbox with different styles.
Report Wizard to let the user auto-generate the report definition.
Fully functional MDX Query Designer.
An updated build of the Report Builder 2.0 (aka Report Designer Preview) will be available via a web download. The URL link is not known at this point.
Chris Webb, a fellow MVP and MDX guru, who’s blog is a must-read for all SSAS junkies, was kind enough to let me take a look at the Intelligencia Query product he’s been working on for a while with Andrew Wiles. Chris announced the product back in April which is now selling as a commercial offering.
Now, lots of things have been said about the current state of the SSRS-SSAS integration, which is to say the least wanting. In a previous blog, I wrote “In my opinion, the biggest challenge the Microsoft BI initiative faces today is the inadequate support for Analysis Services.” Alas, we won’t witness an improved SSRS-SSAS integration in SQL Server 2008 as Microsoft decided to focus on other areas and priorities.
Rest assured though that Microsoft understands the importance of this integration scenario. I personally have voiced my concerns on a few occasions and have put this on the top of my wish list which I shared with the Reporting Services team. There are good things happening already which make me believe that SQL Server.NEXT (post-SQL Server 2008) will materialize this wish. What’s really needed is Excel-like support of SSAS in Reporting Services, plus calculated members which are already supported.
Then, the question is what to do meanwhile if you target Analysis Services (and you should). One approach is to take the “I’ll stick with Microsoft” approach. This may require you find workarounds for the static schema and other limitations, such as using the Microsoft OLE DB Provider for Analysis Services instead of the built-in SSAS provider. This is not as bad as it seems as little out-of-box thinking usually gets the job done. By the way, I wrote some 50 pages in my next book to cover integrating SSRS with SSAS in as much detail as possible.
The second approach is to look at third-party offerings, such as Radius Producer and Intelligenca Query. What I liked about Intelligenca Query is that it doesn’t require throwing the baby with the water. You can still keep the Microsoft Report Designer but use Intelligenca Query to replace the built-in SSAS provider. This works because just like the built-in provider, Intelligenca Query is implemented as a data extension. I tested Intelligenca Query with SQL Server 2008 and it worked without any issues with the BIDS Report Designer and Report Builder 2.0 (aka Report Designer Preview).
The good thing about Intelligenca Query is that it lets you plug in an arbitrary MDX query. Behind the scenes, it pulls out a little trick where it rewrites the report dataset by transposing columns to rows. Consequently, you have to use the matrix region to rotate the rows back to columns to recreate the original query results. Now, my ideal provider would support a dual mode where I can tell it not to rewrite anything but just to give me the results. Of course, if another dimension member is added, it won’t show up on the report columns (assuming a table with fixed columns) but I can see this being useful for notice users that create ad hoc reports or when the columns are fairly static.
Another thing I liked, of course, about Intelligenca Query is that it eliminates the nagging issue of server aggregates which are the aggregates for the dimension All members. With the built-in provider, you have to explicitly request them by using the Aggregate function but they won’t be retuned if you hand-code your query. Intelligenca Query simply brings them as additional rows. What if you don’t want them, such as when you need a group footer? No problem, just exclude them from the query and group on the rest of the rows.
Another cool thing is working with parent-child hierarchies. The MS built-in provider takes this strange design pattern where it dumps all members in a single column, thereby making it virtually impossible to create subtotals per level. With Intelligence Query this issue disappears:
I also liked the graphical query designer which is pretty much in par with the Microsoft graphical MDX Query Designer. On the downside, some outstanding work is required to handle parameters and synchronize the text query with the graphical designer.
If the SSAS built-in provider is driving you nuts, I encourage you to take a look and evaluate Intelligenca Query.
You need to configure a SharePoint site integrated with Reporting Services for Internet access. In our case, the end users would authenticate with Windows security by using local Windows account created on the web server.
Issues
Report URLs reference the machine NetBIOS name and fail to execute with “The path of the item ‘<path’ is not valid. The full path must be less than 260 characters long; other restrictions apply”. Same happens when Report Builder tries to load models.
Solution
This one took a few hours to solve:
Since Reporting Services 2008 only supports access from SharePoint URLs in the default zone, change the public URL of the default zone (SharePoint 3.0 Central Administration -> Operations -> Alternate Access Mappings) to the web server Internet URL, such as http://www.adventure-works.com/.
This will take care of the report URLs which now should include the server Internet address. However, requesting reports would result in rsItemNotFound errors although the report URLs look perfectly normal.
Open the report project in BIDS. In the project properties, change the TargetDataSources and TargetReportFolder settings to include the server Internet URL instead of NetBIOS names, such as http://www.adventure-works.com/Report Library/Shared Documents/Data Sources for the TargetDataSource setting.
Redeploy the report definition.
At this point, standards reports deployed to SharePoint should work. If you use local server accounts for your users, however, previewing reports in Report Builder may fail with mysterious errors about invalid semantic queries. You can solve this issue by making sure that the user logon and server accounts are identical, that is, have the same name and password.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2008-05-10 00:55:522016-02-17 11:27:32Sharing Lost Reports with Web Users
Q: How to make the darn parameter drop-down list wider in HTMLViewer? HTMLViewer is the Report Manager control that displays the report when you view the report.
A: HTMLViewer supports limited customization via a CSS stylesheet.
Copy HTMLViewer.css to MyHTMLViewer.css in the same folder (Reporting Services\ReportServer\Styles). Note that the actual folder path varies between SQL Server versions.
Add the following CSS style to MyHTMLViewer.css
SELECT {
font-size: 8pt;
font-family: Verdana;
width:200px
}
As a result, all SELECT elements on the page (all dropdowns, such as parameter prompts, zoom, export) will pick up these settings.
Configure the server to use the custom stylesheet by one of these options:
If you want to apply the custom styles to all reports, add a new HTMLViewerStyleSheet element to the rsreportserver.config file, such as:
Note that due to an unfortunate bug, the HTMLViewerStyleSheet setting doesn’t work with February CTP of SQL Server 2008 but it will be fixed in the final release.
Use the rs:Stylesheet device setting to apply the custom style to given report only, such as:
http://localhost/ReportServer?/AdventureWorks Sample Reports/Product Line Sales&rs:Command=Render&rc:Stylesheet=MyStylesheet
February CTP is out. It brings the much anticipated integration with Visual Studio 2008 and the new BIDS Report Designer.
Download the February CTP here: http://www.microsoft.com/sql/2008/prodinfo/download.mspx
Updated Books Online for February CTP are available here: http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en
What’s new in Reporting Services February CTP (compared to the previous November 2007 CTP):
Support for Microsoft Word Rendering This allows users to render reports as Word documents that are compatible with Microsoft Word 2000 and greater.
Data Visualization Enhancements Data Visualization Enhancements provides significantly improved support for Chart and adds support for Gauge controls directly within reports.
SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies The SQL Server 2008 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2008 report rendering, processing and management capabilities in SharePoint Integrated mode. This version includes Data-driven subscriptions.
Report Design Enhancements in Business Intelligence Development Studio (BIDS) BIDS overall has been migrated from a Visual Studio 2005 to Visual Studio 2008 platform. In addition, the report design tools found in BIDS were significantly updated for Reporting Services. Updates include: – the more powerful Tablix based Tables, Lists, Matrices – the Report Data window for easier interaction with data fields – the integration of data visualization enhancements (Chart, Gauge) – new dialogs throughout the design experience
Throughput Enhancements Further improvements in report rendering throughput, the number of reports rendered in a given amount of time for several types of workloads.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2008-02-22 12:41:512016-02-17 11:35:02SQL Server 2008 February CTP Is Out
SharePoint always has a trick up its sleeve to surprise the innocent. I have to admit that I continue to be baffled by this technology. Not that I know too much about it, of course. But this thing is like the Pacific Ocean – web applications, site collections, sites, oh my. The further you go, the deeper it gets. Things that still escape my mind include how to get the default Office Server web site (with the Report Center, etc.) after upgrading from WSS 3.0, why I cannot implement custom SharePoint filter provider web parts in WSS given that the interfaces are in WSS, and the mother of all questions, why WSS supports Windows Server OS only.
One thing, however, I managed to figure out (of course, after hours of experimenting, installing, and head-scratching since documentation is, shall we say, wanting) is how to get the Report Server content types to show in Microsoft Office SharePoint Server 2007. If you installing a new MOSS installation, after installing the Reporting Services Add-in, the Report Server content types should show up in the site settings. However, if you are upgrading from WSS, they may not show up. The issue could be that the Report Server Integration Feature is not activated. To check:
Open the site, expand the Site Actions and go to Site Settings, Modify All Site Settings.
Under Site Collection Administration, choose Site Collection Features. If this is not a top-level site, you need to select the link for the Top Level Site Collection Features.
Check the Report Server Integration Feature. If it is deactivated, click Activate. Isn’t this tricksy?
That should do it. You should be able to add the Report Server content types to the site.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2008-01-13 02:55:002016-02-17 11:42:23Where Are Report Server Content Types Gone?
Visual Studio 2008 embraces the exciting new world of Windows Communication Foundation (WCF) for communicating with services. However, pitfalls await the unwary. I’ve recently tackled invoking the Reporting Services Web service with WCF and I want to share my findings.
The Visual Studio Add Web Reference menu has been renamed to Add Service Reference to denote that WCF can communicate with much more than Web services, including probably my Zune device. Although the dialog has somewhat changed, you will be find your way to generate the proxy.
What’s more surprising is that the auto-generated proxy methods now have somewhat different signatures.
For example, the SQL Server Books Online has the following signature of the Reporting Services GetExecution Options API.
public ExecutionSettingEnum GetExecutionOptions (string Report,out ScheduleDefinitionOrReference Item);
Yet, WCF generates the following signature:
public ServerInfoHeader GetExecutionOptions(string Report, out ExecutionSettingEnum executionOption, out ScheduleDefinitionOrReference Item);
So, the returned value becomes an out parameter while ServerInfoHeader becomes a returned value. I am not sure how WCF figures this out. Does it mean that now the documentation should show both the 2.0 and WCF signatures?
The second surprise wave hit me when I was trying to figure out a way to pass my credentials to the Web service. This, of course, will probably be one of the first things you need to do to invoke an Intranet service.
In the good ol’ 2.0 days, impersonating the user takes a single line of code.
How do we this in the shiny new WCF world? Strangely, the Visual Studio help says little about this. I came across some bizarre examples of declaring HTTP transports that made my head spin. In a sheer stroke of luck, I managed to figure out the right changes in the application config file (yes, now we have declarative settings).
At this point, I felt like upgrading my house only to find that I have to enter through the chimney. Upgrading to a new technology shouldn’t complicate things unnecessarily. I promptly switched back to the 2.0 style of programming. Luckily, they kept the old Add Web Reference button from the advanced settings of the Add Service Reference dialog.
I guess they were right. You can’t teach an old dog new tricks…
As he posted here, Brian Welcker (Group Program Manager of Reporting Services) is leaving the Reporting Services team and moving to the Microsoft Healthcare Solutions Group. Brian did so much to build and promote Reporting Services. SSRS wouldn’t have been the same if it wasn’t for Brian. The technical community (myself included) will surely miss him.
ould need two textboxes (or three if you wanted different formatting for the sales order number). You may be surprised to find that the entire title is implemented as a single textbox with two paragraphs. The second paragraph combines static text (Order #:) with dynamic text ([SalesOrderNumber]), which defines a placeholder for a dataset field value. Each fragment can have its own format settings. Thanks to these enhancements, you’ll find that by moving to Reporting Services 2008, you need fewer textboxes and you need to write less often expressions that concatenate text. Moreover, the new textbox lets you implement report solutions, such as mail merge, that were difficult or impossible to implement with previous releases.
