How To Create An SSRS Report

This document walks through the steps required to deliver the results of an SQL Server query which itself delivers results via our linked Pulse servers to an SSRS output accessible by a CBAM user.

You are at liberty to set up an SSRS solution using placeholder SQL and update it once you have built the actual SQL. Solution Creation

Open Visual Studio on your machine under your Dev account (e.g. "AMDev") and create a new Report Server Project:

Why Can't I See My Favourite Dev Folder Under My Dev Account?

---

You DEV account doesn’t have the same folder permissions as your standard Windows account. To share a folder with your Dev account make sure you’re in your standard Windows account then right click on your folder, choose Properties and then the Security tab:

Click Edit, then type e.g. "AMDev" into the object name box and click check names. Then click OK:

Update as appropriate:

Reopen VS under AMDev, should be able to see that folder.

Alternatively, just save everything into a Dev folder on the U:\ drive which does appear visible under Dev accounts.

Right click on "Shared Data Source" and select "Add New Data Source":

We’re going to use a standardised data connection to our SQL Server Database (the one with the Pulse database links):

Name: "CBAM_DATAFEEDS":

Data Source=DC1LNAPSQLUAT01;Initial Catalog=CBAM_DATAFEEDS or… Data Source=DC1LNAPSQLUAT01;Initial Catalog=CBAM_APPS

GOTCHA: Note CBAM_DATAFEEDS. Over in SQL Server your stored procedures will need to be in here to be accessible back in SSRS:

Set the credentials. Since we're under our Dev account we can use Windows authentication:

GOTCHA: go back and Build the data connection:

…so you can test you have connectivity:

The result should be this:

Now we can build a report. Add a new item to the Reports folder:

("Add New Report" triggers a wizard)

The result should be this:

(Alter the height of the “document” pane so you can see the workspace more clearly). Add any parameters you want to offer the user by right-clicking on Parameters in the Report pane:

Update the parameters with the appropriate datatypes etc:

Think of the Name of the parameter as the name of a textbox control in a VBA solution. We will cite this name when we're grabbed its value later and piping it into our parameterised query. The user input controls for the parameters will automatically be added to the report:

Now we tell the report to use our data connection:

Give your connection an appropriate name for the report and cite the shared data source:

Use Windows Authentication credentials:

The result should be this:

For our report we have thus leveraged the shared Data Source CBAM_DATAFEEDS_SCH with a connection here called "SIPPsInTransfer". Now we can slice a dataset for the report itself. In the Report pane right click on Datasets and choose “Add Dataset”:

Give the dataset a name, choose the data source which we just created and then choose the appropriate option for the query/stored procedure you want to use:

If you want to bolt your SQL straight in using the text Query type option rather than citing a stored procedure be aware of the following. These both do the same thing: SELECT * FROM OPENQUERY (PULSECOPY,'select * from CPAM.PORTFOLIO ') SELECT * FROM PULSELIVE..CPAM.PORTFOLIO …but only the latter gets you your list of fields under “Dataset” in the SSRS report designer. If you do use the latter make sure you correctly cite either PULSELIVE or PULSECOPY and your table names need to be in ALL CAPS In addition, this is what you need to feed a date from SSRS across to Oracle via your T-SQL: to_date( + CONVERT(varchar, @fromDate, 20) + ,'yyyy-mm-dd HH24:MI:SS') …or, to reformat: TO_CHAR (TRUNC(TO_DATE( + CONVERT(varchar(10), @ValuationDate) + ,'yyyy-mm-dd')), 'dd-mm-yyyy') If you get this at any stage:

…head back up to the main CBAM DATAFEEDS_SCH connection and in Properties click Build again. Match the query parameter name with where we're getting the input values from:

(You can also hard-code Parameter values here if the situation requires it.) https://blogs.perficient.com/2014/03/10/sales-report-with-date-picker/ Click OK and the result should be this:

What if the fields of your report aren't showing? It's almost certainly because you’re leveraging a Stored Procedure and you haven’t set it up to deliver results to a temp table. See [TBC]. U:\Departments\ITAppsLondon\DEV\App Dev Tools\SSRS Tools Now we can build the structure of the report. Right click on the design page and chose Insert and Table:

Drag the fields into the table:

Right click on individual fields and choose “Textbox properties” to change the data formatting…:

…in here:

Segoe UI 8pt is usually good for Excel outputs:

Note there is no column autofit in SSRS, you have to manually alter column widths by trial and error. Move your table to the very top left of the page to ensure any extracts to Excel populate from cell A1:

Now click "Preview", your query will run in the background:

…and you may find you need to apply more visual formatting:

You could head back to Design and right click on each individual field value placeholder and choose Text Box Properties, but that is very tedious. Try selecting the Tablix row and use the formatting toolbar:

Tips And Tricks How To Create An SSRS Report: Tips And Tricks Deployment Remember you’ll need to create a new CI for your SSRS report for when you raise your RFC.

Example CI For SSRS Reports 

How To Create And Add A CI To An RFC Use the same naming convention (YOURTOOLNAME_SSRS - or _SSIS) and details as here:

Do I need to change the Shared Data Source connection string? 

We are talking about this:

You don’t have to update this if you’ve named the connection after a Data Source already on the report server (e.g. CBAM_DATAFEEDS or CBAM_APPS). The deployment tool will detect the connection on the server and will not overwrite it with yours:

Switch to Release mode:

(If you forget this step it will try to deploy to your localhost and trigger an error) Right click on the individual Solution project and choose "Deploy":

We’re going to need to update this:

Change TargetServerURL to one of: http://DC1LNAPSQLDEV01/reportserver (currently out of use) http://DC1LNAPSQLUAT01/reportserver http://DC1LNAPSQLPRD01/reportserver

Reminder: you need to do all this under your Dev account. This is what happens when you try to deploy under your standard Windows ID:

…and this is what a successful deployment looks like:

Open Chrome under your Dev account and head to the server you deployed to, in this case: http://dc1lnapsqluat01/reports/browse/ You should see something like this:

If you see something like this you’re in the wrong account:

Securitisation (Work in progress) Once you've deployed your SSRS solution to the report server from within Visual Studio, open Chrome under your Dev account and, when in UAT, head here: http://dc1lnapsqluat01/reports/browse/ When going live head here: http://dc1lnapsqlprd01/reports/browse/ App Dev staff have admin access to the reports server, so we can assign permissions to the deployed SSRS solutions. Click on the ellipsis of your report and choose “Manage”:

…and in the Security tab you can manage permissions (assign individual users – which we shouldn't do – or groups/roles):

One final thing. Make sure your report solution is using the CBAM_DATAFEEDS connection (which logs into SQL Server under CReporting in the Report Manager). If it isn’t your user is likely to report the following even though you have added their name to the security list:

…because the CBAM_DATAFEEDS_SCH connection is set to log in using Windows Authentication so it is rejecting individual user accounts. To fix this, click on the ellipsis on your solution and choose Manage:

Click on Data Sources and then the ellipsis, browsing to and selecting CBAM_DATAFEEDS:

Because CBAM_DATAFEEDS logs into the database as CReporting:

That should fix the above error Troubleshooting And Report Logging (Work in progress) Integration Services Catalogue See ssis_job_log_info (CR's reporting log) Ssisdb – Autorek – Projects – execture_autork – right click Reports – Standard Reports - All Executions (see ID number and drill in)