Ad hoc Reports

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16

    
    SSRS comes with a built-in modeling tool called Report Model Designer which is used for developing ad-hoc reports. The main purpose of the report model is to shred the dependency on the coding and give a semantic model to the user to  develop their own reports on the fly without consulting the developer.

Example:

• Open BIDS
• Create a new "Report Model Project", the solution name is "ReportModelProject".
• Give the required path for the new solution.

• Create 'Data Source and Data Source View'.

• Solution Explorer --> Right click on "Report Models" and select "Add New Report Model".

• Report Model Wizard window will open.

• Click on "Next >" Button. Then "Select Data Source View" page will open.

•  Select the required "Data Source View" from the "Available data source views". After that "Next >" button then "Select report model generation rules" page will open.

• Click on "Next >" button. Then "Collect Model Statistics" page will open.

• Click on "Next >" button then "Completing the Wizard" page will open.

• Give the required name (EmployeeReport) and then click on "Run" button.

• Click on "Finish" button.

• In 'Solution Explorer' right click on solution "ReportModelProject" and select 'Build'.

• Make sure the 'Build' is success.

• Again right click on solution "ReportModelProject" and select 'Deploy'.

• Make sure 'Deploy' process completed without errors.

• Open 'Internet Explorer' type/past the URL: http://localhost/Reports/
• Then "SQL Server Reporting Services" will open.

• Click on 'Report Builder'. If you are opening first time then it will ask to run the Report builder exe file. Then click on "Run" button.

• After that report builder will open.

• Click on "Blank Report".

• Right click on "Data Sources".

• Select the "Add Data Source..", then the "Data Source Properties" will open.

• Click on "Browse.." button. then browse the required report.

• Click on "Open" button.

• Click on "OK" button.
• Right click on "Dataset".

• Select "Add Dataset..", then the "Dataset Properties" will open.

• Select the "Use a dataset embedded in my report" check box. Select the "Data Source" from the drop down list. Then after click on "Query Designer.." button, then Query Designer will open.

 

• Select the required table and double click on required columns, then after click on "OK" button.

• Click on "OK" button.

• Right click on designer window and insert table (Highlighted in the above screen.

• Add report header and required columns. Click on Run (Highlighted in the above screen top left side).

• Report is generated.

Data Source Views

1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16

     Cascading parameters provide a way of managing large amounts of report data. With cascading parameters, the list of values for one parameter depends on the value chosen in preceding parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list.


    A data source view is a logical data model based on one or more data sources.

•  Connect to SQL Server and create employee and Department below tables (Click her for table creation script).

• Open required solution.
• Right click on "Data Source Views" and select "Add New Data Source View".

•  "Data Source View Wizard" window will open.

• Click on "Next >" button. Then "Select a Data Source" page will open.

• Click on "Next >" button.Then "Name Matching" page will open.

• Click on "Next >" button. Then "Select Tables and Views" page will open.

• Click on "Next >" button. Then "Completing the Wizard" page will open.

• Give the required name (Employee) and click on "Finish" button.
• Data source view is created.

SSRS Report Deploy/Localhost URL Error

    Below are the few frequently facing SSRS Report Deploy errors.

• Could not connect to the report server http://localhost/ReportServer
• The permissions granted to user 'XXXX\ykiran' are insufficient for performing this operation.

    Below are the few frequently facing "localhost URL access errors.

• Service Unavailable
• The permissions granted to user 'XXXX\ykiran' are insufficient for performing this operation. (rsAccessDenied)

The permissions granted to user 'XXXX\ykiran' are insufficient for performing this operation. (rsAccessDenied)

    During Localhost URL (http://localhost/ReportServer) access, if you are facing below error.

Then the user does not have permission to perform an action.

• Close the "Internet Explorer".
• Go to start button.
• Right click on "Internet Explorer" and select "Run as administrator".

• Then the Internet Explorer will open.
• Type/past the URL "http://localhost/ReportServer"

For More Details: Click here

Service Unavailable

   While accessing the URL "http://localhost/ReportServer", if you are facing the error "Service Unavailable" then we need to start the SSRS service.

• Click on Start button
• Click on All Programs
• Click on "Microsoft SQL Server" Folder
• Click on "Configuration Tools"
• Click on "Reporting Services Configuration Manager"

• Then "Reporting Services Configuration Manager" Window will open.

  

•  Click on "Connect" button.

• In "Report Server Status" click on "Start" button.
• Once the "Report Server" is started, then click on "Exit" button.
• Now we can access the URL  "http://localhost/ReportServer".