Feeds:
Posts
Comments

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

 

 

 

 

 

I have an SSRS report with a parameter named Responsible Department, that allows for multiple selection of values. For example: electrical, mechanical, shutdown.  These values  need to be split into  as individual records in one column as shown below.

dept

How do we do this in Oracle? The answer is to use the regexp_substr function, introduced in Oracle 10g.

The query below shows how the Responsible Department parameter values are split. Note that there is no space in between the comma and Shutdown.

DECLARE @ResponsibleDepartment VARCHAR(MAX) = ‘Electrical,Shutdown’

DECLARE @Statement VARCHAR(MAX)
= ‘
SELECT regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) as RESPONSIBLE_DEPARTMENT from dual
CONNECT BY regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) is not null
‘;

EXEC (@Statement) AT BABEL

 

The query below shows how to pass them into a table. Note that there is a space in between the comma and Shutdown, hence the need to use the LTRIM function to remove the white space returned from the regexp_substr function.

DECLARE @ResponsibleDepartment VARCHAR(MAX) = ‘Electrical, Shutdown’

DECLARE @Statement VARCHAR(MAX)
= ‘
SELECT *
FROM EVENT_CODE
WHERE RESPONSIBLE_DEPARTMENT IN ( SELECT LTRIM(regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level)) from dual
CONNECT BY regexp_substr(‘ + ”” + @ResponsibleDepartment + ”” + ‘,’ + ”” + ‘[^,]+’ + ”” + ‘, 1, level) is not null
)
‘;

EXEC (@Statement) AT BABEL

 

To pass the multiple values of the parameter from SSRS into the SQL query, these values need to be concatenated with a space and comma. The parameter in the dataset containing the query needs to be set to below, otherwise the dataset will fail to run.

= Join(Parameters!ResponsibleDepartment.Value, ” , “)

 

%d bloggers like this: