Who is online?  217 guests and 0 members
Home  »  Blogs  »  MikeDavis  »  SSRS 2008 Report Model with no Foreign Key and/or primary Key

SSRS 2008 Report Model with no Foreign Key and/or primary Key

change text size: A A A

posted  2/26/2010 9:57:11 AM  by MikeDavis

When creating report models you might run into bad database designs where the tables have no foreign keys and/or no primary keys. This can be resolved in the report model DSV. I have created two tables, one with first names, the second with last names. Each has an ID column which is the row that matches the row on the opposite table. There are no primary keys and no foreign keys on either table.

SSRS 2008 Report Model with no Foreign Key

In visual studio I created a report model project and selected the database with these two tables. Then I created a DSV with these two tables.

SSRS 2008 Report Model with no Foreign Key

 

After you create the DSV (Data Source View), right click on the DSV and select open.You will see the two tables in the DSV view.

SSRS 2008 Report Model with no Foreign Key

Then I right clicked on the row to set a primary key. Remember this row must be unique.

SSRS 2008 Report Model with no Foreign Key

Then I dragged the first ID to the second ID to create the foreign key. Remember, these must be matching data types.

SSRS 2008 Report Model with no Foreign Key

Then I create the model and it has the relationships needed to create reports.

SSRS 2008 Report Model with no Foreign Key

I created a report in report builder 2.0, here you can see how the names match up correctly.

SSRS 2008 Report Model with no Foreign Key

tags :   SSRS 2008 Report Model NO Foreign Key NO primary Key
  Rate This Blog Entry:  register  or  login

Author

MikeDavis MikeDavis (Member since: 10/27/2009 8:52:44 AM)
Mike Davis, MCTS, MCITP, Senior Business Intelligence Consultant at Pragmatic Works. He is an author on two Business Intelligence books. Mike is an experienced speaker and has presented at many events such as several SQL Server User Groups across the US, Code Camps, SQL Server Launches, and SQL Saturday events. Mike is an active member at his local user group (JSSUG) in Jacksonville, FL.

View MikeDavis 's profile

Comments (no comments yet)

Leave a comment


 
Type the characters:
 *
 
   

Join Business Intelligence Developer Network for FREE Today!

It's fast, easy and free! Submit articles, get your own blog, ask questions & give answers in the forums, and become a better developer, faster.

enter your email address:

Blogs RSS Feed

MikeDavis's latest blog posts

  • "Drive" a great book
    8/31/2010 1:23:59 PM by MikeDavis  - Views: 2300  - Comments: 0
    If you only get a chance to read one book this year, it should be Drive , The Surprising Truth about what Motivates Us. This book has made me rethink how I am motivated and how I can motivate others. ...
  • My New Business Intelligence Book
    8/24/2010 12:59:48 PM by MikeDavis  - Views: 155  - Comments: 0
    My new book on Business Intelligence is available on amazon. You can pre order now and save. http://www.amazon.com/Knights-Microsoft-Business-Intelligence-24-Hour/dp/0470889632
  • Getting Previous Row in SSIS Data Flow
    8/20/2010 5:06:40 PM by MikeDavis  - Views: 326  - Comments: 6
    There is no native Transform built in to SSIS to get the previous row in the data flow. But with a little work you can do this and make it perform much better than a SQL cursor and you don’t have to u...
  • SQL R2 Map Change map type, Road, Aerial, Hybrid
    8/6/2010 8:50:52 PM by MikeDavis  - Views: 149  - Comments: 0
    With the new Map control in SQL 2008 R2 there are three different map types, Road, Aerial, and Hybrid. If you want to allow your users to change the map type you would think you could map a parameter ...
  • MDX Scope with an If Then Exception or Filter
    8/5/2010 7:35:41 PM by MikeDavis  - Views: 345  - Comments: 0
    The scope statement in MDX is great for applying calculations to a certain area of a cube, also called a subcube. Dustin Ryan wrote a great article on the scope statement here . The issue I am going t...
  • MDX YTD not working due to format String
    8/2/2010 2:13:55 PM by MikeDavis  - Views: 210  - Comments: 1
    If you are using the templates for MDX calculations built into SSAS 2008 you might run into some strange issues. In the image below you can see the calculation is working for all years except for 2008...
  • SSIS Execute SQL task Error: No disconnected record set is available
    7/26/2010 2:07:19 PM by MikeDavis  - Views: 261  - Comments: 0
    SSIS Execute SQL Task Error : No disconnected record set is available for the specified SQL statement. If you get the error in SSIS that says: [Execute SQL Task] Error: Executing the query "INSERT INT...
  • Microsoft Business Intelligence Certification Pro Test 70-452
    7/23/2010 8:56:20 PM by MikeDavis  - Views: 249  - Comments: 1
    I just took the Microsoft Business Intelligence Certification Test 70-452. This is the pro test and the second one after the MCTS 70-448. I have a lot of experinece with the BI world so the test was n...
  • Going to SQL Saturday 40 South Florida
    7/20/2010 3:43:55 PM by MikeDavis  - Views: 167  - Comments: 0
    I will be at SQL Saturday in South Florida on July 31st, 2010 Hope to see you there. http://www.sqlsaturday.com/eventhome.aspx?eventid=47 The South Florida SQL Saturday will be help on July 31st, 2010...
  • SSRS Nulls in a Matrix
    7/14/2010 2:02:00 PM by MikeDavis  - Views: 171  - Comments: 0
    In a recent SSIS class one my students ask me, if you are reporting off a table and using it in a matrix, does the matrix show null rows. The answer is yes. Here is an example table and the resulting ...
  • SSRS Hide or Show, Some or All Columns on a Report
    7/7/2010 6:34:34 PM by MikeDavis  - Views: 923  - Comments: 2
    Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns show on a report. You will need to create a multiple value parameter and place an expression on...
  • SSIS Performance with Multicast
    6/28/2010 8:16:32 PM by MikeDavis  - Views: 389  - Comments: 0
    If you have two or more records on the same row, and need to write each record on its own row in a destination, you have two choices. You can do this in series or parallel in a single data flow in SSI...
  • SSAS Different Dimension Granularity
    5/28/2010 3:12:54 PM by MikeDavis  - Views: 3049  - Comments: 0
    In an SSAS cube you sometimes need to connect a dimension at a different granularity than other dimensions. For example, if you have budget measures and actual numbers. If you are trying to budget you...
  • Call AS400 program from SSIS
    5/20/2010 12:56:23 PM by MikeDavis  - Views: 418  - Comments: 0
    If you need to call an AS 400 program from an SSIS package this can easily be accomplished with an Execute SQL Task. This can be made even easier with two variables and an expression too. The AS400 pr...
  • Convert Address to Spatial Data - Geocoding for SQL Server R2
    5/5/2010 2:25:13 AM by MikeDavis  - Views: 569  - Comments: 0
    If you want to use the new mapping feature in SQL server R2. You may want to get the Spatial data for the address data you have. There are several websites that can do this for you. Here are a few: ht...
  • Loop Through Excel File in SSIS
    5/5/2010 1:22:49 AM by MikeDavis  - Views: 1623  - Comments: 2
    You can loop through excel files using SSIS. This will use the For Each Loop container and a Data Flow task. First create a variable named strExcelfile as a string variable; you can leave the value bl...
  • SQL Saturday Jacksonville May 8 2010
    4/27/2010 1:16:12 PM by MikeDavis  - Views: 382  - Comments: 0
    I will be speaking at SQL Satruday in Jacksonville on May 8 2010. Hope to see you there. http://sqlsaturday.com/38/eventhome.aspx
  • SSIS Bulk Insert Task Error File Not Found
    4/14/2010 5:11:23 PM by MikeDavis  - Views: 921  - Comments: 0
    When using the Bulk insert task in SSIS, you might encounter an error stating that The File Does Not Exist or The Path Specified Cannot be Found. This is usually due to the fact that the file must be ...
  • SSIS Web Service Task
    4/6/2010 6:49:44 PM by MikeDavis  - Views: 1364  - Comments: 0
    The Web Service task in SSIS can be used to call a web service command to perform a needed operation in your package. The results of most web services will be in XML Format. You can save these results...
  • XML Task in SSIS
    4/6/2010 6:54:51 PM by MikeDavis  - Views: 1144  - Comments: 0
    The XML Task in SSIS allows you to parse through an XML file and read the nodes in the XML. In a previous blog I showed how to use a web service task to get the city and state when entering a zip code...
  • SSIS Skip Certain Files in a For Each Loop
    3/26/2010 6:31:53 PM by MikeDavis  - Views: 1720  - Comments: 2
    When running a For Each Loop through a set of files, sometimes you will have specific files that you do not want to load. For example, I have a set of files named: Abc.txt Mno.txt Rts.txt Wln.txt Xyz....
  • SSIS Renaming and Moving Tasks
    3/4/2010 4:11:19 PM by MikeDavis  - Views: 664  - Comments: 3
    If you are like me you click the mouse a lot, and fast. I drive my mouse to the breaking point clicking around so fast on items, especially in SSIS, which is a graphical interface. But when clicking o...
  • SSIS Excute SQL Task Fails with Executing the Query " " Failed
    3/2/2010 5:57:21 PM by MikeDavis  - Views: 600  - Comments: 0
    When running a package with an execute SQL Task it is easy to get syntax errors. If the tasks turns red, but still accomplished its work, like an insert statement and the error you recieve is: Executi...
  • SSRS 2008 Report Model with no Foreign Key and/or primary Key
    2/26/2010 9:57:11 AM by MikeDavis  - Views: 799  - Comments: 0
    When creating report models you might run into bad database designs where the tables have no foreign keys and/or no primary keys. This can be resolved in the report model DSV. I have created two table...
  • SSRS 2008 Change the Series Colors for Chart and Legend
    2/25/2010 12:48:27 PM by MikeDavis  - Views: 1644  - Comments: 0
    In my last SSRS 2008 class I was showing the students the chart tools and used color as a series on a chart. It was the product color column from the AdventureWorks 2008 Production.Product table. The ...
Blogs RSS Feed

Latest community blog posts

  • PowerPivot Installation failure
    5 hours, 35 minutes ago by MarkGStacey  - Views: 60  - Comments: 0
    Sometimes, when installing PowerPivot Services, you may get the message "sharepoint availability check for existing farm" FAIL Or "user account requirement for farm administrator" Even though your use...
  • Alternating Colors in your Sql Svr Report
    19 hours, 2 minutes ago by SMcDonald  - Views: 27  - Comments: 0
    When creating a report, it's nice to add color to it, especially when attempting to separate the rows. Here is a quick how to for you: 1. After you create your report, highlight one of your rows: 2. G...
  • Ten interview question you should'nt ask
    22 hours, 21 minutes ago by sqlscottgleason  - Views: 297  - Comments: 6
    So a good friend of my is leaving is current role, I wanted to write down a few things He should'nt ask during the interview of his next job. 15. How frequently do your accountants audit petty cash? 1...
  • MDX - Calculating the Percent of a Named Set
    9/8/2010 12:14:06 PM by DevinKnight  - Views: 39  - Comments: 0
    Getting the percent of something in MDX seems like a pretty easy task but can be complicated when extra logic is needed to be built in. In this example a named set has been added and the requirement i...
  • Restarting SQL Server using a Batch Script
    8/29/2010 5:17:21 PM by briankmcdonald  - Views: 34  - Comments: 0
    In a previous post, I showed you how one could Restart, Stop and Start the services for an instance of SQL Server using the SSMS GUI. Again, please do not execute this on your PRODUCTION server, but h...
  • Where do you get your motivation and drive?
    9/6/2010 7:04:58 PM by PatrickLeBlanc  - Views: 58  - Comments: 3
    I was asked by someone the other day, where do you get your drive and motivation? For a second, I was stumped. After giving it a little thought I realized that my drive has several sources. The first ...
  • SQLMotivation for your Monday
    9/6/2010 3:39:24 PM by AdamJorgensen  - Views: 40  - Comments: 0
    Motivational time again! Today I want to talk about being lazy with your data. More likely than not, it’s not your data, it’s your firm’s data. Make sure you’re taking precautions. There are so many f...
  • Breaking in (Entry level advice)
    9/3/2010 1:44:17 PM by sqlscottgleason  - Views: 34940  - Comments: 3
    How does one get started in this field? One thing that I see from time to time is someone who just keeps taking class after class hoping that a resume full of classes will make their resume took impre...
  • MDX Puzzle #7 - Solution
    9/3/2010 1:23:35 PM by PatrickLeBlanc  - Views: 10764  - Comments: 4
    Puzzle #7 had an interesting twist. You were required to add a column to the result set that dynamically displayed a currency type based on the existing currency code. As always, there are a couple of...
  • Script Indexes With Your Table
    9/3/2010 11:34:51 AM by BradSchacht  - Views: 2761  - Comments: 0
    Scripting tables inside of SQL Server Management Studio is a very simple task. Just right click and select script table. The problem is that, but default, the indexes on a table are not part of the sc...
  • Retrieving Report Names for SSRS Subscription Jobs
    9/1/2010 6:00:10 PM by briankmcdonald  - Views: 1420  - Comments: 0
    The way Reporting Services creates subscriptions is through generating a SQL Server Agent job. This is apparent when you look at the jobs on the database server that Reporting Services is configured t...
  • Pirates of PASS: Curse of the Elections Process
    9/2/2010 6:52:12 PM by sqlchicken  - Views: 10124  - Comments: 3
    Pirate Code: They're more like guidelines really... The last few weeks we've watched the drama unfold in regards to the PASS BOD elections. We've seen people attack criticize the NomCom, the board, PA...
  • What are plan guides?
    9/2/2010 1:34:14 AM by indupriya  - Views: 6306  - Comments: 0
    Just a while ago I was watching a video on sqlshare Creating a Plan Guide for a Parameterized Statement By Andy Warren . Only then I heard about plan guides. I didn't know what plan guides were. Here ...