How to Create a New Report by Copy from Existing One in SSRS

First of all, if the Solution and Project for which the new report is supposed to be there is not exist yet, create the Solution and/or Project first.

1. In Solution Explorer, find existing report, right click and select COPY.
2. Click the Project where you want your new report be, right click and select PASTE.

Done.

Special Function of ORDER BY Clause in T-SQL

Compare to MS Access Jet Engine SQL, T-SQL's ORDER BY clause is flexible, which can point either the column name or alias of the column. Which special function can be used for special purpose. Here is any example. Let us say, you need a data set in order:

SELECT DISTINCT Name
FROM Tab
ORDER BY Name

Then you need to add a record called "Unknown" at the top:

SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab

There is problem, you won't able to add ORDER BY clause in T-SQL because you used UNION. Following is next step:

SELECT Name
FROM
( SELECT 'Unknown' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

You won't be able to get the right result, because "Unknown" is not on the top. Solution is:

SELECT CASE WHEN Name = '0000' THEN 'Unknown' ELSE Name END AS New_Name
FROM
( SELECT '0000' AS Name
UNION
SELECT DISTINCT Name
FROM Tab
) AS Inner_Q
ORDER BY Name

Why use '0000'? Because it would be on the top after sort. In this case, if you use

ORDER BY New_Name

it won't work.


http://koncordpartners.blogspot.com/2009/11/contain-data-layout-in-sql-part-for.html

Turning SSRS Report Into Form

The difference between report and form is report pulls data out from database and the form is used to feed data into database. SQL Server Reporting Services is of the report. However, sometimes the two-way interactions are really needed. Here is an example. A parameter need to be input by user; however, if the user had input previously, it should be stored in database and no longer require user to input again.

The logic of the solution is:

1. Get value A of the subject from database according to other parameters.
2. If (Null!=A) then show report based on A, and exit.
3. Else if user input parameter B, then insert B into database, then show report based on B, and exit.
4. Else show the blank report with notice that user needs to key-in the B.

However, this logic does not allow front end to alter the what was already in database. Any change will be manully operated by back end. Alternatively, following logic can be applied:

1. If user input parameter B, then insert or alter B in database, then show report based on B, and exit.
2. Else get value A of the subject from database according to other parameters.
3. If (Null!=A) then show report based on A, and exit.
4. Else show the blank report with notice that user needs to key-in the B.

As a standard procedure, the user therefore is not required to key-in the parameter unless is notified.

INSERT and SET query can be as text or as stored procedure. However, INSERT query should not be stand along as text, or an error message will be generated. At the end, SSRS is of report.

Can JavaScript Codes Be Added To SSRS Report?

The SQL Server Reporting Services HTML is poorly constructed so that the tags you'd most want to customize don't have Id's or Classes assigned. In addition, the ASPX pages just reference compiled assemblies, so the only real way to modify them is via CSS. Someone tried to edit the ReportingServices.js file, but it is neither the concept of customization nor standard functionality.

Conclusion: you could not add JavaScript block onto SSRS.



http://geekswithblogs.net/mnf/archive/2007/11/25/sql-server-reporting-services-notes.aspx
http://geekswithblogs.net/mnf/archive/2007/11/25/sql-server-reporting-services-notes.aspx
http://stackoverflow.com/questions/789303/is-it-possible-to-embed-javascript-into-an-ssrs-report

Contain Data Layout in SQL Part for Reporting Services

Business Intelligence in both ORACLE and SQL Server has offered standard tools and settings, such as cube and dimensions. However, for complicated reports, these tools may not be very useful. The orthodox approach to this issue is to have a customized OLAP database built. In some case, even with OLAP database, complicated queries still needed. This issue does also cause the further problem of data layout in report. Here is an approach to deal with these issues. Basically, this approach does contain the task of data layout in SQL part to avoid complex programming in Stored Procedures.

Let us say a report is required to present a daily summary of patient movement within a State, with certain criteria. It requires 5 columns: Date, Sending Facility, Receiving Facility, total transports between these two facilities in that day [Transports], and the Daily Subtotal for whole State. Indeed, Daily Subtotal and Transports can share same column. However, for better visual effect, it is required to have separate columns. GROUP BY and other analytic functions can be used to generate numbers for Transports and Daily Subtotal. However, for the sake of data layout in report, it is suggested only GROUP BY is used. As at today, almost all other analytic functions just generate undesired data layout, for instance, extra but unneeded columns from PIVOT.

The data was not located in a single table, so joins are necessary. Since the selection criteria (WHERE Conditions) are not strictly have one-to-one relationship, the joins therefore are Outer Joins to ensure needed records won’t be excluded. Here is FROM clause:

FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN ……

The sending facility and receiving facility indeed is in same table. So, it had been outer joined twice with flag as s and r.

Because of one-to-more relationship, it is for sure multiple rows will be generated for some records. So, mechanism to distinct records is needed. Unfortunately, DISTINCT function does not work with GROUP BY. So, an inner query is used strictly only selecting the essential fields together with DISTINCT:

( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
) AS Distinct_Q

Do not forget to add “AS [name_of_inner_query]” at the end of inner query, which is required by Transact-SQL. DATEDIFF(DD, 0, t.add_datetime) is used here to uniform the time during the date enabling the use of GROUP BY. DATEADD(HH, 12, …) will be explained late. If Distinct_Q contains less fields needed by next step, it is neccessary to join back to retrieve the missing fields.

Next step is to use GROUP BY:

( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM
(…) AS Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q

So far, we generate all essential parts of the report except the Daily Subtotal. We can use same structure for Daily Subtotal. Since inner query Distinct_Q used twice, it is worthwhile to move this temporary resultset to the head with WITH:

WITH Distinct_Q
AS
(…
)

(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q

Note, DATEADD(HH, -1, Entry_Date) is used again here.

We can use UNION to put two temporary resultsets Detail_Q and Daily_Subtotal_Q together:

SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
(…) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(…) AS Daily_Subtotal_Q

The purpose of CAST(… AS VARCHAR(3)) used here is to avoid the empty string '' automatically turns to 0 after UNION. Field [Daily Subtotal] in Detail_Q is string data type and Daily_Subtotal_Q is number. So does for field [Transports].

The final step to to add ORDER BY at the end:

ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO

It is time to explain why using DATEADD() function above. Since [Entry Date] is to be sorted DESCENT-ly, one hour difference as result of two DATEADD() functions could place row of ‘Daily Subtotal’ at the end of every rows for that day.

Here is completed SQL script:

WITH Distinct_Q
AS
( SELECT DISTINCT DATEADD(HH, 12, DATEDIFF(DD, 0, t.add_datetime)) Entry_Date
, m.mt_number [MT#]
, s.healthcare_facility_name Sending_Facility
, r.healthcare_facility_name Receiving_Facility
FROM dbo.transfer t
LEFT JOIN dbo.healthcare_facility s ON s.healthcare_facility_id = t.sending_facility_id
LEFT JOIN dbo.healthcare_facility r ON r.healthcare_facility_id = t.receiving_facility_id
LEFT JOIN …
WHERE …
)
SELECT Entry_Date [Entry Date]
, Transfer_Status [Transfer Status]
, Sending_Facility [Sending Facility]
, Receiving_Facility [Receiving Facility]
, CAST(Daily_Transports AS VARCHAR(3)) Transports
, '' [Daily Subtotal]
FROM
( SELECT Entry_Date
, Sending_Facility
, Receiving_Facility
, COUNT(*) Daily_Transports
FROM Distinct_Q
GROUP BY Entry_Date
, Sending_Facility
, Receiving_Facility
) AS Detail_Q
UNION
SELECT E_Date [Entry Date]
, '' [Transfer Status]
, 'Daily Subtotal' [Sending Facility]
, '' [Receiving Facility]
, '' Transports
, CAST(Daily_Subtotal AS VARCHAR(3)) [Daily Subtotal]
FROM
(SELECT DATEADD(HH, -1, Entry_Date) E_Date
, COUNT(*) Daily_Subtotal
FROM Distinct_Q
GROUP BY Entry_Date
) AS Daily_Subtotal_Q
ORDER BY [Entry Date] DESC
, [Transfer Status]
, [Sending Facility]
, [Receiving Facility]
, [Transports]
, [Daily Subtotal]
GO

This blog is to illustrate how to manipulate the data layout in report simply by use SQL. You can also refer to http://koncordpartners.blogspot.com/2009/08/practical-case-of-transpose-query.html.


http://koncordpartners.blogspot.com/2010/12/special-function-of-order-by-clause-in.html

A Practical Case of Transpose Query

Let us say a pension fund wants to generate a summary consists of total contributions had been made so far for each member, by their employers. The fund does not care about exact contribution made by each employers for each member. However, the fund wants to know the three employers paid the contributions most for each member. As the result, the summary table should have 5 columns: Member_Id (unique), Total_Contributions, Employer_1, Employer_2, Employer_3. If a member has less than three employers, leave it blank. Each member should have occupied one row only.

Simple SQL query of SELECT GROUP BY won't be able to accomplish this task, since it is a transpose requirement, namely, turning the row into column. However, CASE or DECODE in PL/SQL will do. The equivalent of CASE in Microsoft Office Access is IIF. Alternatively, TRANSFORM in Access and SELECT PIVOT in PL/SQL can be used. By use CASE or DECODE, it is possible to avoid the complex programming. There are five steps:

1. To get dataset first which should consist of all necessary information. Let us use Dataset_All to describe it:

SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contribution
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id

ORDER BY is important since it would make sure the ROWNUM is in align with the Member_Id.

2. Get a dataset which consists of the Member_Id and their last ROWNUM in Dataset_All. Let us name it Dataset_Uni:

SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id

Using GROUP BY to get largest ROWNUM if same Member_Id having multiple rows.

3. To flag each employer according their rank, start from 1. Let us name it Dataset_Rank:

SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id

4. Transpose process. We also use the numerical feather of Employer_Id which was generated from sequence:

SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM Dataset_Rank
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Put everything together:

WITH Dataset_All AS
( SELECT Member_Id
, Employer_Id
, SUM(Contribution) Total_Contributio
FROM Tab
GROUP BY Member_Id
, Employer_Id
ORDER BY Member_Id
)
SELECT Member_Id
, Total_Contribution
, SUM(CASE Employer_Serial (WHEN 1 Employer_Id, ELSE 0)) Employer_1
, SUM(CASE Employer_Serial (WHEN 2 Employer_Id, ELSE 0)) Employer_2
, SUM(CASE Employer_Serial (WHEN 3 Employer_Id, ELSE 0)) Employer_3
FROM
( SELECT Dataset_Uni.Member_Id
, Total_Contribution
, (Serial_No - Dataset_All.ROWNUM + 1) Employer_Serial
, Employer_Id
FROM
( SELECT Member_Id
, MAX(ROWNUM) Serial_No
FROM Dataset_All
GROUP BY Member_Id
) Dataset_Uni
JOIN Dataset_All ON Dataset_Uni.Member_Id = Dataset_All.Member_Id
)
GROUP BY Member_Id
, Total_Contribution
ORDER BY Member_Id

Done.