Tag: SQL Server

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

Dynamic Sorting Using Parameters in SSRS

The other day, someone requested that a report in SSRS be sorted differently by default. While that might make sense if everyone wants it that way, more than likely you might have people that want a report sorted differently by default. How to do it?

There are probably a few ways, but this is what I did.

First, I added two parameters. “SortByDefault” and “SortOrder”

The “SortByDefault” will be a drop down of your columns you want to sort by for your dataset (or group, or table/tablix)

The “SortOrder” is simply Asc (1 to N, A to Z) and Desc (N to 1, Z to A)

Now, here is how mine look:

SortByDefault (I have two columns I want to allow sorting by, PointsLeft and StackRank):

SortOrder:

Now comes the fun stuff: Making it work.

Make sure you remove any “ORDER BY” in your dataset (you don’t have to but this makes it easier).

I also have every column in the report set up for interactive sorting based on the column header/column it shows, but not sure that is necessary here, I just wanted to put that out there just in case

You want to get to your sorting options. So in my case, I have a tablix, so get to your tablix property window and the sorting option:

Now you can see, my “Sort By” and “Then By” are expressions. It is kind of weird here. Also you can’t set expressions for “Asc” or “Desc” so what I had to do was trick it somewhat.

the first is to handle the asc option:

=IIF(Parameters!SortOrder.Value=”Asc”,Fields(Parameters!SortByDefault.Value).Value,0)

the second is to handle the desc option

=IIF(Parameters!SortOrder.Value=”Desc”,Fields(Parameters!SortByDefault.Value).Value,0)

You can see, some magic. If the order by is XYZ then use the field, otherwise 0. If you notice from the screenshot, first one is A to Z (Asc) and the second one is Z to A (Desc). So we are basically telling SSRS to sort by the param or not based on the order by option and it chooses the right order by (ASC/DESC). I think this was easier in SQL 2000 SSRS 🙂

Well now you should be able to test your report and try to sort orders. What I did next is make my params hidden. The defaults are what I wanted for the existing report (Order By PointsLeft DESC), and what I did next is create a linked report and set the hidden parameters int he report options in Report Manager to (Order By Stack Rack, ASC)

Now I have one report, with hidden sorting params and I can create linked reports with different sort options without having to create a new report. I could add all columns to the choices, or even let users choose as parameters (but they already have interactive sorting in this case).

Happy Report Buildin’!

Categories
Business Intelligence SQLServerPedia Syndication

SQL Server 2012: Data Quality Services

With the release of SQL Server 2012, I am looking more into Master Data Services (MDS) and Data Quality Services (DQS). A brief overview of DQS.

You install DQS with SQL, and you have to configure it. The server configuration is a cmd line process that runs to create some databases on your server (DQS_MAIN, DQS_PROJECTS, DQS_STAGING_DATA).

I ran into one issue with the running of the configuration, not sure if this happens everywhere, I am running Windows 8, but nonetheless, I ran into. After running the tool and getting error after error, and trying as admin, etc. I dug deeper into the error message and found that I there were some security/permission issues I had to resolve. It ended up being that I had to change permissions on

C:WindowsMicrosoft.NETFramework64v4.0.30319Configmachine.config

to allow write access. Once I did that, the configuration tool worked and I could get into DQS.

DQS gives you a “Data Quality Services Client” to work with. When you open it, connect to the database where you configured the three databases I talk about above. Once you, you have 3 panes.

You have Knowledge Bases, Data Quality Projects, and Administration.

Knowledge Bases: datasets of known data that you can use in your Data Quality Projects. You get a default Knowledge Base – state names and some other data similar to that.

Data Quality Projects: Here is where the magic happens. You can choose some source data (Excel xls – xlsx wouldn’t work or SQL table) and then apply your knowledge base on it. Then you can reimport your data at the end back into SQL or export it, and update your Knowledge Base with learned values.

Administration: Not a ton of options, but you can set some thresholds, and also setup your Azure data market settings.

Azure Data Market https://datamarket.azure.com/browse/Data?Category=dqs – Lots of data you can use to combine with your Knowledge Bases. Much more here and I won’t go into detail – it could be its own post in itself.

As a test, I took an excel file, added a few records with columns first, last, city, state (I actually imported into a staging SQL table to work with it) – But in the state field I put different variations of state, WI, Wis, Wisconsin, MN, Minn, Minn., etc.

I then ran the file through creating a new data quality project and ran it against the default Knowledge Base, and it corrected the values it could. Got a weird error clicking next on the project, it seems the button is touchy. Hopefully they come out with a fix soon.

Once you build up and get your Knowledge Base stable, you can use from SSIS packages or in Master Data Services. I see many useful applications for DQS. Either around your corporate data or pulling in data from Azure data market to cleanse existing data you might have (think: looking up gender from first/last name).

This post is a brief look at DQS and how it works, but there is so much more. I hope to get more in depth in the near future.

Categories
Geeky/Programming Life

Cool Things I Have Been Doing On the Computer Lately

In the past couple of months, weeks, whatever (time flies) I have been doing some pretty cool things on the computer, in a wide range of areas. Just want to get them down on paper (you know what I mean)…

1. Yammer – working hard on growing Yammer community, external networks, just getting engagement and showing the benefits. It’s fun.

2. Kinect – did some Kinect hacking. On my own then with the group, got some cool stuff to show. It is crazy how easy it is to get something up and running with Kinect and the SDK. I see this stuff taking off in the coming months/years.

3. Azure – dorking around with Azure, looking at what it can and can’t do, what it could do well, how it would fit in with everything.

4. SQL 2012/Power View – been playing around with SQL 2012 since “Denali”, but now its got an official launch date (March 7th) and things are getting real. Power View demos online, trying to figure out how SQL 2012 is going to fit into our infrastructure and just learning as much as I can about it.

5. Ruby – been getting into Ruby and Ruby on Rails on my Mac, git, sqlite3, heroku, etc. Trying to learn more things that just the .NET ecosystem.

6. Ubuntu – same here, set up a VM, been trying to use it consistently, trying to get the other viewpoints from Windows and Mac and where things are at. Keep up with the joneses so to speak.

7. Android – I picked up a Samsung Galaxy (Verizon 4G) a few weeks ago and have been using it. I still love my iPhone, but getting more into Android. Ice Cream Sandwich (ICS) is a pretty good OS, there are still quirks, but its better. Verizon sucks around where I live btw.

8. Google+/Picasa Web Albums – been getting this into my photo workflow, for sharing and backup. Liking it so far.

9. SharePoint 2010/FAST – been researching and reading FAST server like crazy trying to see how it will fit in with a potential project. I think it could be amazing. more to come.

10. SMS – been playing around with different frameworks, and seeing how they compare, trying things out. Using Voice and SMS is all the rage these days. (Hall and Oates thing anyone?)

Bonus: Nothing with computers, but I have been really getting into brewing beer/homebrewing. I think we have made 5 batches now, and the ones I have tasted so far are really good. It is a fun hobby and breaks up the constant technology I am involved in. More to come here too.

And much much more. Time is limited, time to post is limited. Getting out there and doing cool things is fun, and sharing them is fun too. Gotta find the right balance. I hope everyone is having a cool 2012 so far.

Categories
Business Intelligence

#sqlpass 2011 Summit Thoughts

Want to get a quick post out before I forget everything cool I have been thinking about the 2011 SQL Pass Summit in Seattle WA.

The conference started out great with a first timer reception (this was my second year), and I knew a few first timers so that would great.

The first day keynote was good, and then the sessions I hit were on a full range of things, new semantic search stuff with file table, and more.

I missed a couple of sessions on day 1 because I was in a Microsoft led focus group on “BI in the cloud” some very cool ideas thrown out and excited to see what is coming up in the future.

More good keynote on day two, a deeper dive into the stuff in SQL Server 2012 (formerly Denali). Went to some good sessions on Data Quality Services (DQS) and Vertipaq vs OLAP, and Power View (remember the space!) SharePoint and Power View integration, etc. Some very cool stuff coming soon with the 2012 release.

Tabular cubes vs Multidimensional cubes. OLAP vs Vertipaq, etc. Big debates and questions here, when to put things in Tabular vs MDX, etc. DAX solves some hard problems we run into with MDX.

Another thing I went to a couple sessions on was StreamInsight. This is some very cool technology for complex event processing, using .NET and LINQ. The StreamInsight guys are crazy smart. Also integrating StreamInsight with Azure, and doing some crazy processing and analysis of tons of data in near real time. I see this technology making a difference somewhere down the road.

Power View was another big winner from the summit. This integrates with SharePoint 2010 as a Silverlight (SL still isn’t dead?) report builder and viewer. Not even sure they are called “reports” anymore, but “views”. You create views and you can interact/edit them in real time. Export to PowerPoint too. These views work off “tabular” vertipaq BISM (BI semantic model) cubes. This technology is going to change how we deliver information to the business.

Being able to take a PowerPivot (no space!) and reverse engineer it into SQL Server Data Tools (formerly BIDS – in VS2010 shell) to create a BISM tabular cube and publish to SSAS Tabular instance looks great. Solving problems like crazy snapshotting and many to many and time analysis using DAX looks very useful.

Of course, GameWorks appreciation night was fun, and I found a Mcmenamins (Six Arms) up the road from the convention center, so I got get a taste of Ruby again. Good conversation and times at the Taphouse and around the Sheraton and Convention center was good too. Seattle is a nice city and the weather was pretty good I thought. Another great summit.

Categories
Uncategorized

MADPASS Monthly Meeting April 27th (SSRS!)

The third meeting for the Madison Area SQL Server Users Group (MADPASS) is tonight. The talk is about SQL Server Clustering for High Availability.

Meetings are always FREE and food is provided.  Details about the next meeting can be found below.
In an ongoing attempt to make the content presented useful to our members we start each meeting with a short Q&A session.  Unless noted in the meeting notes the schedule for each meeting will be:
5:30 – 5:45 – General Q&A session & Announcements
5:45 – 6:15 – Short Presentation
6:15 – 6:45 – Food & Networking
6:45 – 7:45 – Main Presentation
7:45 – 8:00 – Door Prizes & Q&A Session

 

Reporting Services 201: The Next Level

You have mastered the art of linking a dataset to a table in SQL Server Reporting Services. You have solid, reliable reports that you and others depend on. Learn how to take them from "meh" to "wow", using features such as tablix, lists, images, and charts.

Date:

Wednesday, April 27th, 2011  5:30 PM

Speaker:

Jes Schultz BorlandJes Schultz Borland is a Database Administrator at Kimberly Clark. She holds an AAS – Programmer/Analyst degree. She has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration. She is an active member of PASS, the PASS WIT virtual chapter, and MADPASS. She is also an avid tweeter, blogger and runner.

Location:

2310 Crossroads Drive
Madison, WI 53718

Park behind the building and walk in main entrance.
Our room will be straight ahead on the first floor.

Live Meeting:
Click here to enter the meeting.

Add Meeting to Calendar:

Click here to add the meeting to your calendar.

We will also be talking about upcoming elections. Cool prizes. Beer. Pizza, and more.

Categories
Uncategorized

MADPASS Monthly Meeting Tonight!

The second meeting for the Madison Area SQL Server Users Group is tonight. The talk is about SQL Server Clustering for High Availability.

Meetings are always FREE and food is provided.  Details about the next meeting can be found below.
In an ongoing attempt to make the content presented useful to our members we start each meeting with a short Q&A session.  Unless noted in the meeting notes the schedule for each meeting will be:
5:30 – 5:45 – General Q&A session & Announcements
5:45 – 6:15 – Short Presentation
6:15 – 6:45 – Food & Networking
6:45 – 7:45 – Main Presentation
7:45 – 8:00 – Door Prizes & Q&A Session

Topic:   

SQL Server Clustering for High Availability

Date:

Wednesday, March 23rd, 2011  5:30 PM

Speaker:

Michael SteinekeMichael Steineke is the Vice President of Information Technology for Edgenet – Big Hammer.  He has been working with NLBS and Windows Clustering on Windows NT 4 Server through 2008R2, creating clusters with IIS, File System, SQL, ISA, and Hyper-V, including geographically dispersed clusters.  Michael has designed and implemented multiple systems for scalability, high availability and disaster recovery.

Location:

2310 Crossroads Drive
Madison, WI 53718

Park behind the building and walk in main entrance.
Our room will be straight ahead on the first floor.

Live Meeting:
Click here to enter the meeting.

Add Meeting to Calendar:

Click here to add the meeting to your calendar.

Categories
Business Intelligence

MADPASS (Madison Area SQL Server User Group) Kickoff Meeting Tonight!

We are kicking off the MADPASS SQL Server Users group tonight in Madison. I am in the initial “Director of Membership”, keeping track of minutes and members, etc. If you are available tonight, come check it out. Overview of BI from Dave DuVarney. Pizza and Beer provided, should be good info and networking event as well.

Chapter Kickoff & Microsoft BI Overview

Many of today’s organizations rely on SQL Server and SharePoint to run their businesses.  Included with these products is a tremendous ability to also derive valuable insight from an organizations data.  In this session, Dave will walk through where the Microsoft BI solutions have come from, where they are today and where Microsoft is headed in the future.  After leaving this session, you should have a better understanding of what Microsoft offers and how best to apply it in your environment.
 

Date:
Wednesday, February 23rd, 2011  5:30 PM
 
Speaker:
Dave DuVarney is an independent BI consultant and trainer. He has broad technical knowledge and  has been involved in multiple software development projects ranging from contract management systems to human rights auditing.  He is proficient in numerous development languages as well as Microsoft Business Intelligence technologies.  Most recently he has been consulting and developing with SQL Server 2008 Analysis Services, Reporting Services and Integration Services. Dave is the co-author of Professional SQL Server Reporting Services 2000, Professional SQL Server Reporting Services 2005 and SQL Server 2008 Analysis Services Step by Step.
 
Location:
2310 Crossroads Drive
Madison, WI 53718

Park behind the building and walk in main entrance.
Our room will be straight ahead on the first floor.
 

Please register for the event on EventBrite here: http://madpass.eventbrite.com/?ref=ebtn

Also, follow @MADPASS on twitter. http://twitter.com/madpass  and check out http://madpass.sqlpass.org/ for more information.

Categories
SQLServerPedia Syndication

SQL DBA: Starting Fresh, What do you do?

If you start at a new place, as a SQL Server Database Administrator (DBA), what is one of the first things you should do? In my opinion, after figuring out the key servers and instances running you need to support… is setting up alerts.

By setting up alerts you can start to get an idea of what is not working and start focusing on things that are failing, etc first. All the while you can still check on backups and getting everything else set up and working, but if you don’t have alerts, well, you are blind.

Alerts should tell you..

1) When a physical server is down (network)
2) When backups fail
3) When jobs fail
4) When logins fail
5) I/O issues
6) the “critical” 14, 15, 16, 17
7) crazy cpu and memory issues
8) services going up and down
9) if your SAN is up/down
10) Hard drive getting close to 100%

and that is just the beginning. What other alerts should DBA’s set up *right away* to make sure they are on top of things?


Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

Exporting Data from SQL Server to Excel Directly

Had a request to export data out from SQL Server to Excel directly. There are a few ways you can do this. BCP is one, another is OPENROWSET. SSIS, etc.  Here is the OPENROWSET method, using Jet (I think this only works on 32bit servers as well)

First you need to enable a setting on SQL, so.. Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration, then click on the “Surface Area Configuration for Features” at the bottom.

image
Under database engine, make sure “Enable OPENROWSET and OPENDATASOURCE support” is checked under Ad Hoc Remote Queries option.

image

Then, you need to create an empty excel workbook, with the columns of the query you want to export out. Create one on C:Testing.xls with “Name” and “Rating” columns

Then run this query:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT 'Steve','1'
Union
SELECT 'Joel','2'
GO

And if everything worked, your xls should have 2 rows in it.

 

Now, if you want output to other spreadsheets, you could dynamically set the filename in the statement, or other trickery!

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL Schema Source Control (CodePlex)

Source Control. In my eyes, one of the best inventions of development.

Software Developers have used it for years, and it allows them to easily develop in a team environment, and be less scared they will lose a change or not be able to see things they did historically.

I have blogged a few times about source control before..

SQL Server Schema Automatic Revision History using DDL Triggers and SVN
MSFT BI In a Team Environment
Visual Source Safe Sucks/
Source Control at Home with Subversion

But this was mostly for code. Developers. The SQL Community has kind of been shafted with source control. Yea you can tie in VSS to Management studio, and others. I have never found one that works, and just wanted something to work in the background.

I originally started doing this with DDL triggers as my <a href="“>post in November kind of outlined. It worked, but wasn’t reliable enough for what I wanted and was too much setup.

So I did what developers usually do, I wrote an app. SQL Schema Source Control http://sqlschemasourcectrl.codeplex.com/

At first it worked with one database, one server, everything was hardcoded. And then it progressed, and now it is all configurable for multi server/multi database, etc. I decided to put it up on CodePlex because I think it can be improved and made to work with other source control providers, like TFS.

The code itself isn’t anything crazy, some file operations and SMO operations to get the DDL and then some functions to add/update/delete and commit to source control.

For info on how to get it working, check out the documentation page on CodePlex, I can also answer any questions here, or on CodePlex.

I have been using the app for a few months now and it has saved headaches, accidental deletes, wanting to see changes over time by developers, etc.

The feature I like the most is that it logs the SQL Agent job changes, so if someone changes a job, you can see the history…

Now, there are competitiros out there. Redgate (http://www.red-gate.com/products/solutions_for_sql/database_version_control.htm) and others. But I wanted something free and open, so that is why I am putting this out there. I would be perfectly fine not putting anything out and just using it, but I think (and hope) others could benefit from using this app

So if you are looking for semi-easy way to get revision control on your SQL Schemas and SQL Agent jobs, check out the app. There is minimal setup, but once you have it working, it just runs.

Note at this time it works with SVN and SQL 2008. Also the solutions is VS2010. I originally had it working with 2005, but no need on my end anymore for that, someone could easily make a version for SQL 2005. VS2008 solution could be created pretty easily as well. Have fun!