SSRS: How to create embedded data source in SSRS
- Details
- Written by Ranjeet
We have already covered what is a data source and its different types like shared and embedded. And have also covered how to create a shared data source. Now its times to know how to create an embedded data source. I would strongly recommend you to go through “what is data source and its types” article to know about it.
As we all know a shared data source resides on report project level where as an embedded data source resides on individual report level. When we open/create a new report project we gets the list of three folders in right hand side under solution explorer. Here you can see named “shared data source”. So, the data source created under this folder is called shared data source and can be shared with all the reports which is fall under this report project. See below screen shot for your reference.
SSRS: A log on error occurred when attempting to access the file share. The user account or password is not valid in SSRS Report
- Details
- Written by Ranjeet
Recently I created a subscription in SSRS reports. I noticed it was failing. When I saw its status, there was an error reported as below.
Failure writing file \\SharedLocaiton\ReportName.xlsx : A log on error occurred when attempting to access the file share. The user account or password is not valid.
It means report server is good and even subscription we created is also good. Somehow the account which is running subscription and dropping the file doesn’t have permission to drop file. There two way to resolve the issue. I will talk one first here which I did to resolve It by providing user account credential in subscription which has access permission. Follow the steps
- Go to http://<servername:80>/reposts
- Open the My Subscriptions on top right
- Click on Edit button
SSRS: How to create a duplicate copy of a report in SSRS
- Details
- Written by Ranjeet
Sometime it is required to create a similar report with little change. For that it is not a good move to do same thing once again and waste your valuable time and get irritated. It is very simple but sometime people waste unnecessary time to do this. There might be some other way to do the same thing but here I am going to illustrate here to do this way.
- Right click on the report project and copy the location of project
- Browse the project location and find the report with extension .rdl
- Create a copy of this file by just copy and paste.
SSRS: How to create your first report in SSRS
- Details
- Written by Ranjeet
We already seen how to create your first blank report in SSRS. You can go with my previous article to get basics about how to create a basis report. In this article I will take you to create your first report in SSRS which use shared data source and shared datasets. If you know about shared data source and shared datasets it is very good else you can visit my different articles on data source and data sets. Now, without spending time on its different components descriptions let me start the step by s step executions of creating my first report. Please follow these steps:
- Open Solution Explorer
- Create Shared Data Source
- Create Shared Datasets
SSRS: How to create shared data set in SSRS
- Details
- Written by Ranjeet
As we already know what is data set and what are its different types. Now it’s time to know how to create a shared data set in SSRS. We are also familiar with project solution also. Before talking about data set and other stuff let go directly to see step by step execution of creating a shared data set:
- Browse project solution in Solution Explorer
- Right click on Shared Data set folder and click “Add new Datasets”
- It will open a Shared Datasets Properties dialog box. Please enter below
SSRS: How to create shared data source in SSRS
- Details
- Written by Ranjeet
As we already know what is data source and what are its different types. Now it’s time to know how to create a shared data source in SSRS. We are also familiar with project solution also. Before talking about data source and other stuff let go directly to see step by step execution of creating a shared data source:
- Browse project solution in Solution Explorer
- Right click on Shared Data Source folder and click “Add new Data Source”
- It will open a Shared Data Source Properties dialog box. Please enter below
SSRS: What is Cache Shared Datasets in SSRS
- Details
- Written by Ranjeet
As we already went through what is shared and embedded dataset. What is this new dataset type then? Is it different than what we learnt in last article? No, it is same as what shared dataset is but it differs in the way it keep the dataset result set in cache for future use. When we use cache dataset it query result, it is use to save in cache which can be used by different reports. It help optimizing the query result time. It highly recommended when dataset is big and taking too much of time. Below is the few scenario when cache shared dataset is recommended:
- Query take longer time to execute
- Requires always most updated data
- Dataset query takes parameter
- If dataset query run on specified time in week or so
- Populate list
- Etc.
SSRS: What is Data Set in SSRS
- Details
- Written by Ranjeet
As its name “data set”, it consist/define set of data. Data set is an object which define the query and fetch/return the data from database to report. Data source help data set to connect with dataset. In my understanding it has different name in different language, like record set, cursor, data set, etc. Dataset is very important in building a SSRS report as it actually contains query, which contains the data filter, sort order, parameter, expressions etc.
SSRS supports two types of dataset
- Shared dataset and
- Embedded dataset
Shared Dataset: shared data is use to create on project solution which can be used to return data for any report in that report project. It is often recommended to use shared dataset when same data set is being used by different reports. Shared dataset can only use data source. Shared datasets resides on report project level.
Embedded Dataset: As it name, this of dataset is being define and used within a report. You need to define embedded dataset for this but that data source can reference to shared data source. It managed within report only and not shared with any of the reports in that project solutions. Embedded datasets resides on report level.
My next article will cover how to create Shared and Embedded data set
SSRS: What is Data Source in SSRS
- Details
- Written by Ranjeet
In SSRS, It shows the data from database which can be of any source like SQL Server, Oracle, and text file etc. So, we can define data source as it is an object containing sufficient information to connect to the database. SSRS can connect to different data source type as below
- Microsoft SQL Server
- Microsoft SSAS
- Microsoft Azure database
- PDW (Parallel database house)
- Oracle
- SAP
- Teradata
- XML
- Etc
SSRS: How to create your first blank report in SSRS
- Details
- Written by Ranjeet
Creating first report is always an exciting. Here in this block I will take you to the journey to create your first blank report. This report will not use any Data Source or any Data Set. It will be pretty simple which will give you confidence of creating project. Later in separate article I will go through creating a simple tabular report which will use Data Source and Data Set.
- Create a new project solution and report project
- Right click on Reports skipping “Shared Data Source” and “Shared DataSet”
- Right click on Reports-> Add -> New Item
- Select Report and give appropriate name in appeared dialog box