Data we are moving around, querying every day has stories to tell. Communicating effectively with business leaders largely depend on how we present the data effectively.
I especially liked the example from a consultant about caring for her nephew and relating that story to how data should be “on time”, “as promised”, and should allow people to “follow up”.
I recently started to use the SQL 2008 Reporting Services in BIDS 2008. There are many nice enhancements in the new version. The first enhancement I noticed is the new Report Data window. Report Data can be viewed by going to View menu then select View Data option at the bottom of the option list.
Report parameters, and Images, and Datasets are nicely organized into their own folders, and no longer hidden.
The second change you would notice right away is that everything is a Tablix now. Although both Table and Matrix are available as Report Items in the Toolbox, they are both a type of Tablix with configurable Row Groups and Column Groups. This enhancement makes it a lot easier for data grouping/summarizing.
But the feature I like right away is the ability to set properties for texts, instead of being forced to set properties for the entire textbox as in BIDS 2005.
I am able to set part of the text in a textbox to blue, underlined, and jumping to a URL.
I rarely use case statements in SQL where clause. But in SSRS reports, I find myself use it occasionally. It’s not hard to code it, it’s only the syntax that is tricky. I am very much against writing complex SQL code for any SSRS reports. A stored procedure will be a better solution for complex logics. Using staging is also common for data you need to bring over from other sources.
There are times in SSRS reports where a different field will be used in the where clause, depending on user’s selection of one or two report parameters. In this example, when user selects “Power On” as the Report parameter, the [PowerOnDate] will be used in the where clause; with the selection of “Power Off” as the Report parameter, the [PowerOffDate] will be used in the where clause.
You can write dynamic SQL. I am strongly against dynamic SQL, unless it’s your only solution. You can write a stored procedure with IF statement to construct different SQL statements depending on the parameter. I find a stored procedure is a little overkill in this situation. So I chose to write a straight SQL code with a case statement in the where clause.
Here is the code with a case statement in the where clause.
Here is the part that shows the syntax.
Here is an in-depth discussion about how to make SSIS work with 64-bit Oracle client.
Here is an extract from the above article. I’ll leave all these investigation/installation to our future DBA. For now, I’ll just use the linked server to work around this issue.
There is no problem with the linked server to the Oracle database on the 32-bit or 64-bit SQL Server. However, on the 64-bit server, SSIS connection to the 64-bit Oracle database failed.
Notice that the Log on to the server section is grayed out.
Here is the “oraoledb.oracle.1 is not registered” error from SSIS.
1) Mismatch between 32-bit and 64-bit. I doubt this one. Because the SQL Server is 64-bit on this machine.
2) The port is not open on the firewall. What is the port number for SSIS to connect to an Oracle database?
3) Truly missing the driver.
Since the SSIS connection to the Oracle on the 32-bit SQL Server is working, I’ll start investigation from there.
Here are all the Providers installed on the 32-bit SQL Server.
MSDAORA: Microsoft OLEDB provider for Oracle
MSDASQL: Microsoft ODBC provider
OraOLEDB: Oracle Native OLEDB provider for Oracle
SQLOLEDB: Microsoft Native OLEDB provider
On the 64-bit SQL Server, we are missing MSDAORA. I don’t think this is the problem. Since we are not going to use the Microsoft OLEDB provider for Oracle anyway.
On the 32-bit server, using either Microsoft OLEDB provider for Oracle or Oracle Native OLEDB provider for Oracle worked fine in SSIS.
Using either Microsoft OLEDB provider for Oracle
Using Oracle Native OLEDB provider for Oracle
No solution so far. But since the linked server is working, I’ll use the linked server to extract raw data from the Oracle database.
On one of our servers, the application shortcuts to BIDS and the Microsoft SQL Management Studio are missing for some reason. I finally found them.
SQL Server 2005 SSMS = SqlWb.exe
"D:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"
SQL Server 2005 BIDS = devenv.exe
"D:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"
SQL Server 2008 SSMS = Ssms.exe
"D:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
SQL Server 2008 BIDS = devenv.exe
"D:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe"