Feeds:
Posts
Comments

LAST_VALUE does not always return with the same value. The workaround is to use FIRST_VALUE but order the records descendingly.

 

Advertisements

I want to show a “80 out of 100” below the “80%” in a single textbox within a tablix in SSRS. To do it, create two placeholders on the same textbox and then place a break (“<br>”) at the end of the expression in the first placeholder. Format the font of the expression in the second placeholder to a smaller font.

 

I have a dataset as shown below. When the OP_CODE is null and PRIORITY = 1, I need to shift the OP_CODE of the Priority = 2 to become 1 and 3 to become 2 and 4 to become 3.

The dataset also has the history of the changes made to the records.

At first I ranked the records by using the RANK function but it didn’t work at all. It set the ranking incorrectly as shown below. It didn’t work because it ranks every record instead of every distinct record in the partition.

I then used DENSE_RANK function as it ranks the records within the partition of a result set, without any gaps in the ranking. When there is a tie in the partition, it ranks it to the same number. In this example, there are 3 records with the Priority_Shifted of 30000. These 3 records should all be ranked to 4. The DENSE_RANK function has done it! ūüôā

 

 

Apparently Microsoft has redesigned the UI of the SSRS Report Manager in particular the Data Sources bit.¬† In SSRS 2016, you have a dropdown list to select the credential type i.e. Windows user name and password or Database user name and¬† password. In the previous version, you need to tick a box that says “Use as Windows credentials when connecting to the data source”.

When I first set up my data source on SSRS 2016 report manager, I ticked the box that says “Log in using these credentials, but then try to impersonate the user viewing the report”. It was a habit that as soon as I put my credential in,¬† I would tick the box.

Previous version:

SSRS 2016 version:

 

I received this error even though I was able to connect to the data source. This is rather odd. I am on SSAS tabular server 2016 and Visual Studio 2015. VS complaint about the mismatched compatibility mode. I then checked my workspace and found out that my workspace was still pointing to SSAS tabular server 2014. So this error may not say what it means.

To change the workspace server:

Right click on the Model.bim and then choose “Properties”. In the “Properties” window, locate the Workspace Server and then change it to the SSAS 2016 tabular server.

To change to compatibility level:

In the “Properties” window, locate the Compatibility Level and then choose SQL Server 2016 RTM (1200).

capture

To change the Workspace Database (note that it’s greyed out):

Refer to https://saysmymind.wordpress.com/2012/11/23/changing-workspace-database-name-in-ssas-2012-tabular/

1. Starts SSTD (obviously!!) and create a new Analysis Services Tabular Project. At this point I would also recommend that you rename the default¬†‚ÄėModel.bim‚Äô¬†to something more meaningful to your project.

2. Right click on bim file and click properties.

3. Apart from all the other properties, you will find Workspace Database property in the bottom portion. The description of the properties is here. If you open SSMS and connect to SSAS Tabular instance, you should find a database with the same name as this property value. As you would notice, Workspace Database cannot be changed. It is read-only and that’s the one we want to change.

4. Now right click on project ,click Open Folder in Windows Explorer and close SSDT.

5. In windows explorer, there would be *.settings file. The format of this file seems to be ModelName.bim_username.settings. It might be hidden so change the folder settings to show hidden files.

6. Open this file in notepad. It is an xml although everything will come on one line. I would also recommend that you don’t try to format the file.

7. Now look for tag. The value between this tag is name of the database. Change this value to something more meaningful, save and close notepad.

8. Open the project/solution in SSDT. Check the properties of bim file. The value of property Workspace Database would be what you set in Step 7.

9. You can also open SSMS and verify this.

I am having issue with setting a date to 1002-01-01 when null in SQL Server because it’s out of range.

To get around it, use the DATEFROMPARTS function (available from SQL Server 2012) and it must¬†use datetime2 data type (datetime won’t work). An example is shown below.

CAST(DATEFROMPARTS(1002, 01, 01) AS DATETIME2)

 

I reprocessed an existing tabular model in order to refresh data but it came up with connection related errors as shown below.

Tabular model processing error

I do have permission to the views, cube¬†and everything else but I still can’t reprocess the model. How odd!

The fix is to change the connection manager provider from SQL Server Native Client 11.0 to Microsoft OLE DB Provider for SQL Server.

Tabular model processing error FIX

 

 

 

 

 

%d bloggers like this: