In SSRS populate table querying from two different servers without using Linked servers

Have you ever happened to query from  two different servers to fill report table in SSRS? Here is a trick that do not need to use Linked Server but using two data sources.

First you need to create two separate data sources for each different server.

Then create two datasets (write the query) and link each for the created datasources.

The trick is to use the LOOKUP function.
 

=LookUp(Fields!emp_no.Value,Fields!emp_no.Value,Fieldsleave_hrs.Value,"DataSet2")

If I check the tablix properties it has been set to "DataSet1". In the Expr I use is the above function passing Dataset1 value and then pass Dataset2 value. Third parameter is to bring values from Dataset 2. Last parameter is the name of the other dataset. LOOKUP function returns the

first matching row.  

 Lookup(source_expression, destination_expression, result_expression, dataset)

 

How to write to/update Active Directory entry using C#

In my previous posts I have discussed how to read from the Active Directory(AD). Here I talk about how to update a record in the AD.

First you need to establish the connectivity with the AD same as the way I have explained in one of  my previous posts.

 

Using below method I pass the 'testname' which is the username.  Inside the method I go through all the AD users to compare whether the 'testname' and 'samaccountnames' are similar.

In the below method I check whether titles are matching for the AD user passed from above method, if not new 'position'  is assigned to user principal description.

To save the changes you need to use below command.

public void SaveADRecords(UserPrincipal prinUser)
{
    prinUser.Save();
} 

SSRS – How Report manager shows DATE TIME format in datetime box

Usually Report Manager prompts the date time box using browser language, it doesn't matter how the parameter date displays in the report designer. The reason why you are prompted with a date time format which is not same for some other is because your browser language is not same as other person. To use same date time format in the report manager, the date format in the prompt box displays correctly when you change the browser language. To change the culture of Internet Explorer (IE), you can follow below steps:

1.       Open IE, click Tools->Internet Options.

2.       In the “General” tab, click the button “Language".

3.       Add the required language to the list and move the language to top.

In SSRS, how to change the Background colour depend on Expr value

Sometimes we get requirements to highlight a specific cell of a table depend on the final value. In my example below, I calculate the value falls under “Weeks” column and need to make the cell Bodycolour to Red if the value is greater than 10. Here are the steps.

Go to Text Box properties and use the IIF condition.

Ex:

=IIF((<<Expr>>) >=10 ,”Name(code) of the colour if the condition is TRUE”, “Name(code) of the colour if the condition is FALSE”)

How to format the date format in SSRS reports (SQL 2008 R2)

Date can be format to what to different formats. Below I talk about a common requirement to format date/time. In my report I have Open and Closed dates as mm/dd/yyyyy hh:mm:ss (am/pm). Here I’m going to change the format.

I set the expression as below:

=Format(Fields!clo_dte.Value,"dd/MM/yyyy hh:mm:ss tt")

You may have noted that only MM is in upper case. (month)
If you set the HH in upper case then it use 24 hr time format. (hour)

How to set first day of current month as the default date in SSRS report (SQL 2008 R2)

First you need to create a parameter to set the date. In my project I have two parameters as @startDate and @endDate. I need to set the @startDate as the first day of current month and @endDate as today as the default values. Here are the steps to do it.

1.       Go to @startDate parameter properties - > Default Values -> Select Specify Values -> =DateSerial(Year(Now), Month(Now), 1)

 

 2.       To set the current date for @endDate follow the same steps as above and use following expression.  =Today()

 

In C# how to read and print from stored procedure

First establish the DB connection string in app config. In the c# code you can simply pass the Stored Procedure to sql command with the connection string, in my sample method it is "au_ad_sync" (In this stored procedure I have used a simple select statement which brings out few records from the database). We'll next see how we can  read the data and print in VS Output window.

References:
using System.Data.SqlClient;
using System.Configuration;



Make sure that you close the sql connection after using the SqlReader. (Simple but may miss out.). In above code I have used reader[0]. This will bring the values of the first column that Stored Procedure brings up.

Create Connection String using database name with a space

Below is a simple point but can be easily missed out. To stop wasting time on troubleshooting, I thought of adding this to my blog.                      

As an example here is my database name ‘Production Test’. If I need to create a connection string calling this DB, I need to use &quot; surrounding the DB name in web config or app config file in Visual Studio in order to avoid connection string errors.

&quot;  represents  “ quotation mark (U+0022)

<connectionStrings>

<add name="Name.Properties.Settings.TESTConnectionString"

connectionString="Data Source=server\test;
Initial Catalog=&quot;Production Test&quot;;Integrated Security=True" providerName="System.Data.SqlClient" />

</connectionStrings>

Enjoy escape “ quotation mark (U+0022)