0

Report Builder with a View that doesn't have a Primary Key

Jet Reports Historic Posts

January 16, 2013 14:29

None

Follow

Hi

I've built a report using the Report Builder function however when I run it I get the "Primary Key is not set" error.

The data is being pulled from a View in SQL so Primary Key setting is not possible at the DB level.

I used the Data View Creator/Table Configuration tool to define a Primary key, then used the Categories/DataView to set the fields for the template.

Even with this set the error occurs when the report runs.

Any ideas?

Thanks

Dave

• Facebook
• Twitter
• LinkedIn

3 comments

• 
  Jet Reports Historic Posts January 17, 2013 18:42 Official comment

  Hi Dave,
  
  I think you missed a comma in your NL(Rows) formula. Your current formula would probably looks like this:
  
  =NL("Rows","Calls_Analysis_Oak_All",,"Filters=",$D$4:$E$5)
  Notice the extra comma between the table and the Filters= signifying an empty Field argument in the formula. All you need to do is add an Excel array to this argument with the field names used in your NF functions. You can either put those field names in cells and just reference the array of cells like this:
  
  =NL("Rows","Calls_Analysis_Oak_All",$C$3:$I$3,"Filters=",$D$4:$E$5)
  Or you can actually write the names directly into the formula as an array like this:
  
  =NL("Rows","Calls_Analysis_Oak_All",{"CALLID","Caller","TALK","Day","Month","Year","SDate"},"Filters=",$D$4:$E$5)
  Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 16, 2013 23:17

  Hi Dave,
  
  So since the View does not actually have a primary key, but the Report Builder *thinks* it does due to you defining the primary key in the Data View Creator, it's probably not creating a field cache in the NL(Rows) function. In Jet, in order to pull data from a table/view with no primary key, you have to use a field cache (where you specify all the fields you want to return in the Field parameter of the NL function). You can probably fix the the error in the finished report by going to the NL(Rows) function that's pulling data from the view and specifying all the fields you are returning with NF functions in the field parameter of the NL function. Does that help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts January 17, 2013 13:02

  Hughes
  
  Thanks for helping, what you have decribed makes sense to me but I'm a little unsure of the delivery.
  
  The NL function is as follows currently (position C8 in the spreadsheet):
  
  =NL("Rows","Calls_Analysis_Oak_All","Filters=",$D$4:$E$5)
  
  The columns are populated where
  
  Cell Formula
  D8 =NF(C8,"CALLID")
  E8 =NF(C8,"Caller")
  F8 =NF(C8,"TALK")
  G8 =NF(C8,"Day")
  H8 =NF(C8,"Month")
  I8 =NF(C8,"Year")
  J8 =NF(C8,"SDate")
  
  I understand the concept of inserting an NF function into the NL function however I'm not 100% on how to make it work. I've tried one and it created a circualar reference (though it did get rid of the primary key error :))
  
  Could you explain what you mean about spcifying the fields in the field parameter?
  
  Ta