Create a SSRS Report based on a Cube and Deploy it
http://www.sqlservercurry.com/2009/11/create-ssrs-report-based-on-cube-and.html
Showing posts with label SQL Server Reporting Services. Show all posts
Showing posts with label SQL Server Reporting Services. Show all posts
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.
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.
Optional Parameters In SQL Server Reporting Services
Optional parameters In SSRS means if parameters have been selected, it would be passed back to SQL query as condition in WHERE clause, and when no parameter(s) have been selected, the WHERE condition should be not in use. Dynamic SQL is not the best solution. Following solution is much better:
SELECT Col_A
, Col_B
FROM Tab
WHERE (Col_A=@para_A OR @para_A IS NULL)
AND (Col_B=@para_B OR @para_B IS NULL)
What make life harder is when parameters are selected, but due to lack of records, the result is nil? These would be happened often when multiple parameters get involved. Depends on situations, you may wish to abandon that condition completely. So, the real issue is not if you have option to select a particular parameter, it is if you have selected that parameter but it turns out nil result and you need to abandon it after this discovery.
There is no programmatic solution so far. However, following approach may help:
1, Limit the number of parameters, because you will need to combine them in matrix. Too many parameters will increase the task load exponentially.
2, Classify parameters into hierarchy.
3, Write queries for each combination case of the parameters. Hierarchical structure of parameters will help a lot.
4, From low position in hierarchy to pick up the best available result amongst the results generated from above queries. Following is an example to do this procedure:
; SET @Stopper = 0
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_1' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_2' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
...
What you need to do next is in SSRS to enable the optional parameters. While you have made it possible to accept the NULL value in your back-end queries, by default the report interface of SSRS cannot be flagged out the parameters. The users would have no choice but to select parameters. In this case, include NULL value record for your parameter data set:
SELECT NULL AS Id
, 'Unknown' AS Name
UNION
SELECT DISTINCT Id
, Name
FROM Tab
If you have problem on how to position this NULL value record, please refer to following article:
Special Function of ORDER BY Clause in T-SQL
Next, in Report Parameters window of SSRS, make sure the "Allow null value" option has been selected for this particular parameter.
Done.
http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx
SELECT Col_A
, Col_B
FROM Tab
WHERE (Col_A=@para_A OR @para_A IS NULL)
AND (Col_B=@para_B OR @para_B IS NULL)
What make life harder is when parameters are selected, but due to lack of records, the result is nil? These would be happened often when multiple parameters get involved. Depends on situations, you may wish to abandon that condition completely. So, the real issue is not if you have option to select a particular parameter, it is if you have selected that parameter but it turns out nil result and you need to abandon it after this discovery.
There is no programmatic solution so far. However, following approach may help:
1, Limit the number of parameters, because you will need to combine them in matrix. Too many parameters will increase the task load exponentially.
2, Classify parameters into hierarchy.
3, Write queries for each combination case of the parameters. Hierarchical structure of parameters will help a lot.
4, From low position in hierarchy to pick up the best available result amongst the results generated from above queries. Following is an example to do this procedure:
; SET @Stopper = 0
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_1' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_2' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
...
What you need to do next is in SSRS to enable the optional parameters. While you have made it possible to accept the NULL value in your back-end queries, by default the report interface of SSRS cannot be flagged out the parameters. The users would have no choice but to select parameters. In this case, include NULL value record for your parameter data set:
SELECT NULL AS Id
, 'Unknown' AS Name
UNION
SELECT DISTINCT Id
, Name
FROM Tab
If you have problem on how to position this NULL value record, please refer to following article:
Special Function of ORDER BY Clause in T-SQL
Next, in Report Parameters window of SSRS, make sure the "Allow null value" option has been selected for this particular parameter.
Done.
http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx
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.
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
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
Error Message: [rsInvalidDataSetName] The table ‘table1’ refers to an invalid DataSetName...
In SQL Server Reporting Services 2005, when you changed a dataset name or try to point to another dataset name, this error message is often generated as a result. That is right, you have referred to wrong dataset. The problem is it is hard to find where you can change this reference.
First, you need to remember your report may consist of several tables. Each of them usually refers to a different dataset. So, you need to click the table which does cause the trouble, then in Properties panel located at bottom right corner of your screen, to select that table, which is located right below caption “Properties”. In Data section, there is DataSetName. Select what you want.
First, you need to remember your report may consist of several tables. Each of them usually refers to a different dataset. So, you need to click the table which does cause the trouble, then in Properties panel located at bottom right corner of your screen, to select that table, which is located right below caption “Properties”. In Data section, there is DataSetName. Select what you want.
Subscribe to:
Posts (Atom)
Posts
