Recently a colleague of mine needed to access Oracle to build a report but did not have the rights to create a stored procedure, which would be the obvious method of choice. We therefore need to pass parameters from Reporting Services to Oracle using the “Query Type” of text. To do this in SQL Server it is relatively intuitive.
Create a shared Data Source – I have used Adventure Works.
Then add a DataSet:
This query will return all records from the Adventure Works ‘people’ table, showing the following fields; FirstName, MiddleName, LastName and Email Address. If you wanted to restrict this list using a parameter, all you would need to do is add a parameter to the report.
In this instance I have added a parameter titled “EndsWith”, so we can use a WHERE with any character or string to filter by the last characters of the FirstName. I have also added a parameter called “BeginsWith”. Now you can edit the DataSet to add the where statement as follows:
SELECT FirstName, MiddleName, LastName, EmailAddress
FROM Person.Contact
WHERE (FirstName LIKE @BeginsWith +’%’ + @EndsWith)
Now this will work perfectly with SQL Server, however should you wish to connect to Oracle, you will need to make a few simple, but not obvious, changes. These are as follows:
SELECT FirstName, MiddleName, LastName, EmailAddress
FROM Person.Contact
WHERE (FirstName LIKE :BeginsWith +’%’ + :EndsWith)
So in short all you have to do is change the @ sign to a : (colon), easy when you know how, a real pain when you don’t!
This report will appear as follows when connected to Oracle, or SQL (as long as there are the same databases).
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr