Pages

Showing posts with label SSRS. Show all posts
Showing posts with label SSRS. Show all posts

Saturday 2 February 2013

Dynamic Column Report In SSRS Step By Step

Dynamic Column Report In SSRS


I am showing you how to create Dynamic Column Report. It means when a user select two column the report will display only two columns and if they select five columns then it will display five columns. So we can say that the report will display the field on the basis of user selection.
For this we have to create two datasets.
Here is an example from Northwind Database.
Create first dataset named Hussain_Dataset.


SQL Scripts:

SELECT        CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone FROM  Customers

Insert a Table in design area and create a Table Like that: 


Now go to the dataset and create another dataset  Hussain_Dataset1

SQL Scripts:

SELECT        1 AS ID, 'Customer Id' AS CustomerID
UNION
SELECT        2 AS ID, 'Company Name' AS CompanyName
UNION
SELECT        3 AS ID, 'Contact Name' AS ContactName
UNION
SELECT        4 AS ID, 'Contact Title' AS ContactTitle
UNION
SELECT        5 AS ID, 'Address' AS Address
UNION
SELECT        6 AS ID, 'Postal Code' AS PostalCode
UNION
SELECT        7 AS ID, 'Country' AS Country
UNION
SELECT        8 AS ID, 'City' AS City
UNION
SELECT        9 AS ID, 'Phone' AS Phone
FROM            Customers
Now create a parameter DCParamaeter and click the Allow Multiple value.

Go to the Available Values and choose Get values from query and set dataset to Hussain_Dataset1 , Value Field CustomerID, Label Field CustomerID.

And Now the time to set the Column Visibility. For this we go to first column of the Tablix and right click after that we click on Column Visibility option.





A new popup box will appear. Here we choose Show Or Hide Based On Expression and click on fx button.
A new popup box will appear here we write the expression.


Expression :

=IIF(InStr(JOIN(Parameters!DCParameter.Value,","),"Customer Id")>0,False,True)

We have to perform this step to each column of Tablix.

(Note:Don’t forget to modify the expression for the respective column name accordingly.)

Your Dynamic Column report has completed. Now run the Report. Here is the some preview of your Dynamic Column Report.










Sunday 2 September 2012

MOUSE HOVER REPORT OR TOOL TIP REPORT STEP BY STEP



MOUSE HOVER REPORT OR TOOL TIP REPORT IN SSRS

I am showing you that how to create Mouse Hover or Tool Tip report in this article.

Here is an example from  AdventureWorks Database.



SQL Scripts : 

SELECT     Person.Contact.ContactID, (Person.Contact.FirstName+' '+ Person.Contact.MiddleName+' '+Person.Contact.LastName) As FullName,
                      Sales.ContactCreditCard.CreditCardID, Sales.CreditCard.CardType, Sales.CreditCard.CardNumber, convert(varchar(10),Sales.CreditCard.ExpMonth) as months, Sales.CreditCard.ExpYear
FROM         Person.Contact INNER JOIN
                      Sales.ContactCreditCard ON Person.Contact.ContactID = Sales.ContactCreditCard.ContactID AND
                      Person.Contact.ContactID = Sales.ContactCreditCard.ContactID INNER JOIN
                      Sales.CreditCard ON Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND
                      Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID AND
                      Sales.ContactCreditCard.CreditCardID = Sales.CreditCard.CreditCardID
                     where Person.Contact.MiddleName <> 'NULL'
                     order by ContactID


Select the Header Row of the table in the report.
Go to Properties Window.
Under the BackgroundColor property, Select color from drop down.




Repeat previous step for data row to set color for better look




Now select the ContactID column and go to the properties window.

Select Expression from Tool Tip





Set the expression for Tool Tip





Expression :

=("FULL NAME :  " & Fields!FullName.Value & chr(10)
 &"EXPIRY MONTH :  " & Fields!months.Value & chr (10)
 & "EXPIRY YEAR :  " & Fields!ExpYear.Value & chr (10))

Click Ok
Now Run the report




Above the preview of report.


Monday 6 August 2012

Drillthrough report in SSRS Step by Step

Drillthrough report in SSRS

A Drillthrough is a kind of report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. The data in the drillthrough report is not retrieved until the user clicks the link in the main report.

Here I am showing you how to create Drillthrough Report.
For this I am creating two Report  i.e. Parent and Child Report.

Here is an example from  AdventureWorks Database for Parent Report.




SQL Scripts
SELECT        CustomerID, ContactID, Demographics, ModifiedDate
FROM            Sales.Individual

Now insert a table and  Format the column


And Now create a Child Report
Here I am also using  AdventureWorks Database for Child Report.




SQL Scripts
SELECT        CustomerID, ContactID, Demographics, ModifiedDate
FROM            Sales.Individual
WHERE        (CustomerID = @CustID)

Here I am creating a Parameter Custid.

Here both report created and now its time to map the parent report to child report. For this
We go to the parent report [CustomerID] column.

Right click on [CustomerID] column and choose Text Box Properties


Go to Action tab and map these:


Then Click on OK.

And now run the report. Below the preview of Drillthrough report.





Sunday 29 July 2012

Page Break at Nth row in SSRS Report Step by Step


We can design a SSRS report with a constraint of only Nth rows per page.

I am showing you that how to display only Nth rows in the report in this article.

Here is an example from  AdventureWorks Database.

SQL Scripts.

SELECT TOP 1000 [StateProvinceID]
      ,[StateProvinceCode]
      ,[CountryRegionCode]
      ,[Name]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.
Go to Properties Window.
 Under the BackgroundColor property, Select color from drop down.


Now create a group.



After creating the group go to the group properties and click on fx button.



And set the expression for group properties.



Expression :
=Ceiling((RowNumber(Nothing)) / 15)

( Note : You can put Nth number in place of 15. For example if you want to your page break affect after each 25th row than you have to put 25 in place of 15 )  

 Now, report will show 15 rows in a group. Next step is to define page break for the group. Go to page breaks tab and select page break option.



And now run the report. Below the preview of report.








Tuesday 24 July 2012

Create SSRS report using Excel Data Source Step by Step

By creating a report using Excel as a Data Source we will follow the following steps ...



Right click on Shared Data Source folder and choose Add New Data Source. A new popup screen will open. Give the Data Source Name and Choose ODBC from the dropdown box.


Then Click on Edit button. A new screen will open. From the new screen i.e. Connection Properties go to the Use user or system data source name and from the drop down choose Excel Files .




Then Choose the Use Connection String and click on Build button. A new pop up screen will open . Click on New button.






Then click on Next button



Choose the Microsoft Excel Driver and click on Next button and then click on Finish button.



Then click on Select Workbook button and choose the file and click on OK button.





Click on OK.




Test the connection and press OK.







Click on OK.
Click on Reports folder and choose add new items and choose Report. Map your Data Source and click OK.



Right click on Dataset Folder and Write the query for the table. (I have used a very simple query .For ex.)



This is Your Report from Excel data source.







Sunday 17 June 2012

Alternating Table Row Color In SSRS Step by Step


Alternating Table Row Color  In SSRS Step by Step

Here is an example from AdventureWorks Database.


Sql Script.

SELECT TOP 1000 [StateProvinceID]
      ,[StateProvinceCode]
      ,[CountryRegionCode]
      ,[Name]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks].[Person].[StateProvince]

Select the Header Row of the table in the report.
Go to Properties Window.
 Under the BackgroundColor property, Select color from drop down.

Now, Select the Data Row of the table in the report.
Go to Properties Window.

Under the BackgroundColor property, select expression from the drop down.
Enter the expression "=IIF(RowNumber(Nothing) mod 2 = 0, "SeaGreen","Pink") " in the Expression Window.
Click OK.
Below is a Preview of a SSRS report with two different colors for alternate rows.






Monday 9 April 2012

How do you create share schedule in SSRS


Q: How do you create share schedule in SSRS ?
To create shared schedules
  1. Click Site Actions.
  2. Click Site Settings.
  3. In the Reporting Services section, click Manage Shared Schedules.
  4. Click Add Schedule to open the Schedule Properties page.
  5. Enter a descriptive name for the schedule. On the application pages used to work with Reporting Services reports, this name will appear in drop-down lists in schedule definition pages throughout the site. Avoid long names that are hard to read. Do follow a naming convention that puts the most description information at the beginning of the name.
  6. Choose a frequency. Depending on the frequency you choose, the schedule options that appear on the page might change to support that frequency (for example, if you choose Month, the name of each month will appear on the page).
  7. Define the schedule. Not all schedule combinations can be supported in a single schedule.
  8. Set a start and end date.
  9. Click OK.  Read more ...

Sunday 8 April 2012

What is difference between table and metrix report in SSRS


Q: What is difference between table and metrix report in SSRS ?

Table : Table report is just like a grid view.

Matrix : Matrix report gives us cross tab view it is also known as pivot report . 

What are data regions


Q: What are data regions ?

Data regions are report items that display repeated rows of summarized information from the data set. For 

ex. Table, Matrix, Chart etc.