AdventureWorks2012 - Sales Order Detail Query

Every time we used to join different tables to get complete Sales information. 

The below query returns complete sale order details based on below tables

Sales.SalesOrderDetail. 

Production.Product

Production.ProductCategory

Sales.SalesTerritory

Person.CountryRegion

SELECT PC.Name AS Category ,SC.Name AS SubCategory ,P.Name AS Product ,SO.SalesOrderID ,SO.UnitPrice ,SO.LineTotal ,SO.OrderQty ,SOH.OrderDate ,CR.Name AS CountryOrRegion ,ST.[Group] AS Region FROM Sales.SalesOrderDetail AS SO LEFT OUTER JOIN Sales.SalesOrderHeader AS SOH ON SOH.SalesOrderID = SO.SalesOrderID LEFT OUTER JOIN Production.Product AS P ON P.ProductID = SO.ProductID LEFT OUTER JOIN Production.ProductSubcategory AS SC ON SC.ProductSubcategoryID = P.ProductSubcategoryID LEFT OUTER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = SC.ProductCategoryID LEFT OUTER JOIN Sales.SalesTerritory AS ST ON ST.TerritoryID = SOH.TerritoryID LEFT OUTER JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = ST.CountryRegionCode

SQL Server - Access TEMP table created dynamically

Sql server provides an option to create the query dynamically and execute it as below.

DECLARE @SqlStmt VARCHAR(2000)

SET @SqlStmt='SELECT * FROM HumanResources.Employee'

EXEC (@SqlStmt)

Even temporary table also can be created dynamically as follows.

DECLARE @SqlStmt VARCHAR(2000)

SET @SqlStmt='CREATE TABLE #Class(ID INT, Name VARCHAR(200))'

EXEC (@SqlStmt)

But when you insert or Select statements on #Class table immediately next to above query, Sql Server throws an error “Invalid object name '#Class'.”

To Avoid that you have to write insert/select statements also inside dynamic query string.
As #Class temporary table created dynamically that ends it's scope after EXEC statement. So you should write any further queries inside dynamic query string itself.

SSRS - Showing Multi valued Parameter selected values as CSV in Report

SSRS provides flexibility to add a parameter as single valued or multi valued parameter for a report. We may need to pass this parameter selected values to a query or need to show it on the report.

Here the requirement is to show the multi valued parameter values as comma separated values (CSV) in report. This let end your to see the selected values in the report.

SSRS provided built in function JOIN, use this to combine the selected values.

Example:

In report we have a Parameter “Country” which is multi valued. We can write an expression like below for a textbox value to combine the values with “Spaces”.

=JOIN(Parameters!Country.Value)

 Select the values from country parameter and click on view report button, then you can see the combined values in report

If you want to show the values as comma separated, then you should pass the delimited as second parameter to JOIN function as below.

=JOIN(Parameters!Country.Value,", ")

Like this you can pass &, |, ;  characters to join function to show the values as separated by given delimited on the report.