You have a SQL Server reporting services (SSRS) report that has a table which displays some records — but sometimes it can have NO rows; In that case, how to display “There are No rows” message so that it doesn’t confuse the consumer.
Solution:
Open the report in SQL Server Data Tools and go to the “design” tab of your SSRS report
Select your table (do NOT select a cell inside a table. Make sure that the table is selected)
While the “table” is selected, Go the Properties section OR you can use F4
Inside the Properties section, find “No Rows” section and you should see a NoRowsMessage property:
Go to the preview tab to make sure it’s working and you should be ready to deploy the change!
For SSRS reports hosted on SharePoint, the Data Alerts & Subscribe are grayed out or disabled.
What do you have to do?
You need to upload a SSRS and for the data sources, you’ll have to store the credentials or no credentials.
It’s not ideal for user-level security (e.g. via Windows Authentication) setup on the data source side but the requirement of data alerts and email subscription dictate that you need to stored the credentials or not have credential requirements.
If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials. – http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx
Real-world story: what we ended up doing at a client’s was to create a SharePoint library for “report subscriptions” which is hidden from end-users. We added a service account to the data source & we store the credentials of the service in the report used for report subscription. And IT “controls” who receives the email. So after a user submits a request to get emails, IT goes in the security database & see’s if a user is fit to receive the email or not. So not all users may get approval to receive the email. That was a solution that we had to take to stay compliant.
New Journal Article: First article of 2, where you will be able to see how you can use SQL Server 2012’s DQS to solve common data quality problems. http://bit.ly/172Kh5L
Topics covered: – Data standardization – Identifying and correcting unrealistic or invalid values – Validation and correcting records using Regular Expressions
While I was working with DQS, I got an error that didn’t allow me to export the cleansing results to an excel file. I searched to find that it was a known issue for the 64 bit version of Excel. So based on what I found, this is how I was able to solve the error:
As I mentioned, these are post-sql-server-installation tasks and so I assume you have your BI environment using SQL Server 2012 already up. The official note can be found here: Install Master Data Services
For the purpose of this blog-post, we’ll break the steps into three main sections:
1. Create MDS Database
2. Create Master Data Management Web Application
3. Associate MDS database w/ web Application
So, let’s get started:
Got to: Start > SQL Server 2012 > Master Data Services > Master Data services Configuration
1. Create MDS Database
1a: click on Create Database:
1b: Enter the instance name and credentials:
1c: specify the MDS database name and collation:
1d: Specify the Administrator Account and review the summary. After it installs successfully – you’ll see a message:
2. Create Master Data management Web Application
2a. Switch to Master Data Services configurations > web configuration > select Create new website
2b. Specify configuration details for the website:
Here’s what I entered:
Website Name: MDS
Port: 81
Host Header: Left it blank
Entered username and password
2c. Click on OK and in the web configuration dialog box, click on Create Application
3. Associate MDS database w/ web Application
3a. Under the Associate Application with Database click on Select
3b. Select the database that will get associated to the application selected.
* I selected the database that I had created in Step 1
3c. After successful installation, you’ll see a message:
The web application is then launched in the browser, here you’ll see getting started guide, videos and MDS Samples. Go Explore!
Conclusion
In the blog post, we saw the three post installation tasks for Master Data Services w/ SQL Server 2012.
One of the task while building a PowerPivot Model is to create/Edit/Delete Relationships between tables. With SQL Server 2012 release of PowerPivot for Excel, we now have a Diagram view and this makes our task of defining relationships easier! Great addition. Any-who when you view your model in Diagram Model, This is how it looks:
To Edit a Relationship, You can click on the Relationship represented by the arrow and it would pop up an “Edit Relationship” Dialog Box.
But what if you wanted to Manage ALL relationships of this Model? You can do this!
GO TO Design Tab > Click On Manage Relationships.
Now here you can manage (create/edit/delete) relationships among tables. Very easy!
That’s about it for this post. Your feedback is welcome!
I got a chance to play with power-view which seems like great feature of SQL Server 2012! And I liked it that we can create Hans Rosling styled BI dashboard’s with Powerview. For those who do not know about Hans Rosling presentations, check these collection: All Hans Roslings Ted Videos.
And by studying modules in SQL server Training kit 2012, I was able to create a similar Dashboard, It was fun!
Now, Listing resources for you, If you are Interested to learn about Powerview:
