How to show column headers on all pages in SSRS Report

If a report contains several pages it will be useful to have column headers on each page. In tablix properties we have options as Repeat header columns on each page as shown in below figure.

But even though we put a tick on that property, sometimes it is not applied and and column header are not repeated in other pages. This is really confusing and I found useful link in the internet.

https://connect.microsoft.com/SQLServer/feedback/details/337720/katmai-reporting-services-2008-tablix-control-repeat-column-headers-does-not-work

It mentions the work around as well the reasons that may cause for it.

Work around is

• Select the Tablix that you want to repeat column headers for by clicking on it.
• At the bottom of the screen, find the "Row Groups" and "Column Groups" section.
• Click the small drop-down-arrow on the right side of that section.
• In the "Row Groups" section, locate the top-outermost "static" row and click on it.
• In the properties pane, you need to set the following TWO properties:
  -RepeatOnNewPage = True
  -KeepWithGroup = After

Extracting QuickBooks data using SSIS Package...

To extract data from QuickBooks, I used QODBC driver.
You can download a trial version of it using below link

http://qodbc.com/default.htm

When we installed the QODBC driver, if ODBC entry is not available for it, we can add it as System Data Sources. For that go to the Control Panel --> Administrative Tools -->Data Sources(ODBC)

Then you will get the ODBC Data Source Administrator window. In that click on the System DSN and you will get a screen as below

In that click on the add button and select the QODBC Driver for QuickBooks as shown in the below figure.

Then you have to configure the ODBC connection. Initial set up window is as given below.

If you want to set a different company profile, click on the Browse button and select the qbw file as shown in the below figure.

Then you can use the created ODBC connection in SSIS package.
Create a new Integration Service Project and add a Data source. In the New Data Source window, select the Provider as Odbc Data Provider as in the below figure.

Then select the relevant ODBC data source name for the QuickBooks and if any authentication is required provide those too. Then click on the Test Connection button and verify the connection is possible.

Then add a new package to the project and add a Data Flow task. Then add a ADO NET Source and provide the created data source as the ADO .NET connection manager. If you created the connection correctly, you will be able to select the required table as shown in the figure.

In this example I just added a DataReader Destination component to view the data using a Data Viewer.

Passing two Multi-Value report parameters to a MDX Query...

When we write a MDX Query to filter data, we may want to filter the data set using two multi value report parameters. As an example we may want to get the Sales value for multiple Branches and for multiple Sales Persons.
In such kind of a situation the MDX Query will be as below.

SELECT NON EMPTY { [Measures].[Sales]} ON COLUMNS, NON EMPTY { ([DimBranch].[Branch].ALLMEMBERS * [DimEmployee].[SalesPerson].ALLMEMBERS) } ON ROWS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!Branch.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM (SELECT ( STRTOSET ( '{" & Join(Parameters!SalesPerson.Value,",") & "}', CONSTRAINED) ) ON COLUMNS FROM [Sales]))

Branch and SalesPerson are the Multi-Valued report parameters and Sales is the Cube.

Also when you set the Value field for those parameters, set it as the field taken from considering UNIQUENAME. Otherwise if you have the parameter value as a string which has spaces, then it will gives an error since it only consider the first word before the space.

As an example if you send the branch value as Head Office, it only take the Head part. Therefore you have to pass the UNIQUENAME.

As an example you can take the data set for the Branch using below query.

WITH MEMBER [Measures].[ParameterCaption] AS [DimBranch].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [IDimBranch].CURRENTMEMBER.UNIQUENAME

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS , [DimBranch].[Branch].ALLMEMBERS ON ROWS FROM [Sales]

Then you can use the ParameterCaption as the Label field and ParameterValue as the Value field for the Branch parameter.

Record Count in SSAS Fact Table

We may need to take the record count for a fact table values in SSAS cube. As an example we may need to take the item count for a category. For that we do not need to write any MDX queries or create any Calculated Members.
If you just added the fact table by using New Measure Group, you can see a measure as FactTableNameCount, which was not in the data warehouse. We can use this measure to take the item count for the above example.
But sometimes when we create the fact table, initially we may not add that measure and later we may want it. Then we can add it to the relevant fact table by right clicking the fact table and selecting New Measure.

In the New Measure window default the Usage value is Sum. Click the down arrow and select Count of rows as shown in below figure.

Then click Ok and FactTableNameCount measure will be added. Use that measure to get the record count.

FROM clause have the same exposed names...

When I tried to get the matching item records from two databases, I got the below error.

The objects "Database1.dbo.Sales" and "Databse2.dbo.Sales" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

The query used was

SELECT DISTINCT Database1.dbo.Sales.ItemName FROM Database1.dbo.Sales INNER JOIN Databse2.dbo.Sales ON Databse1.dbo.Sales.Id=Databse2.dbo.Sales.Id

It is because that the table name is same in those two databases. To avoid it we have use aliases.
The query is shown below

SELECT DISTINCT Table1.ItemName FROM Databse1.dbo.Sales AS Table1 INNER JOIN Databse2.dbo.Sales ON Table1.Id=Databse2.dbo.Sales.Id

Databse1.dbo.Sales AS Table1 will fix the error.

Cube File is Corrupted...

While trying to view SSRS reports, which load the SSAS cube data I got the below error message.

File system error: The following file is corrupted: Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Sales.0.db\Customer.0.dim\5.English Education.(All).sstore. Logical file . Errors in the metadata manager. An error occurred when loading the Customer dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\ Sales.0.db\Customer.5.dim.xml'. Errors in the metadata manager. An error occurred when loading the A Sales cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\ Sales.0.db\Adventure Works DW.9.cub.xml'.

The reason for this error message is the the cube file in the file system is corrupted.

You can't this issue by just redeploying the cube. When you try to deploy the cube, you will get the same error message and deployment process will get failed.

You can fix the above issue by following below steps.

1. Stop the Analysis Service

2. Delete everything in the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\%YOUR_PROJECTNAME% folder

3. Restart the Analysis Service.

4. Connect to the Analysis Service using SQL Server Management Studio.

5. Delete the Analysis Service database which you got the error.

6. Re-deploy the SSAS project.

Those steps will fix the above issue.

How to Change the Start Up xaml file in a Silverlight Application

To change the Start Up xaml file for a Silverlight Application, open the App.xaml.cs file.

In that you will find the below code

private void Application_Startup(object sender, StartupEventArgs e)

{

this.RootVisual = new MainPage();

}

Change the "MainPage" to your new xaml file.

Run the test page and you will see the content of your new page...