SQL 2005 Reporting Services - Report Model

Joe_Mathis

I've just bought the SQL 2005 Reporting Services for Dummies book, which is usually enough to get my feet wet and then with some playing around I figure things out.

The question is...

Has anyone made a Report Model for Navision?

and then following on, did you have to create views or use OLAP from Analysis Services?

I tried to let the Wizard do it but it was looking for foreign keys in the database and the model just didn't look right after it was finished.
(and yes I know that not all Wizards are good Wizards some are evil Wizards )

Not looking for someone to do the work for me just kinda need a nudge in the right direction

Thanks,

Joe

kriki

[Topic moved from Upcoming version 5 (AKA Dynamics NAV) forum to SQL General forum]

DenSter

There's a SQL Server 2005 report pack for NAV that should give you a good idea. I think it's on msdn online, or in the SQL Server pages on microsoft.com. I'm pretty sure it's not hidden in partnersource.

Joe_Mathis

Crap... cross posted. :oops:

Thanks Denster,

It is located at :

http://www.microsoft.com/downloads/details.aspx?FamilyId=D81722CE-408C-4FB6-A429-2A7ECD62F674&displaylang=en

But I have not been successfully able to do anything with them.

I am really just starting the discovery phase of using this tool.
(naturally in my spare time :? )

I will continue to post more infomation and hopefully answer someone elses questions too.

If anyone has anything to contribute, I appreciate it as always.

Joe

kine

It is not possible to create the Nav data model in this tool, because Nav is using miltiple tablerelations for one field (conditioned by another field) and it is not able to create that. But if you want to be prepared for NAV 5.1, just try to create some simple reports based on one table with all data without using some special code (play with the design, not with the data processing).

Joe_Mathis

Hi Kine,

Yes. I noticed that the Wizards are looking for an E-R diagram. I also understand that Navision has multiple conditional table relations.

A prospective client wanted an E-R diagram of the database and our Sales Person found one somewhere.... never saw anything quite like it before

Maybe I will try to just make a simple model at first like a sales report linking to Customer table. I know it's available in Navision already, but still trying to learn the tool. :?

It looks as though you can create a datasource view and then create a model from there. I'll try to make a view and manually create the relations between the tables.

I'll edit this post to let you know how it went.



Joe
Edit:

I experienced a problem with the IIS. Apparently if you have an older version of the .NET framework when you install reporting services you need to register the newer version as it will keep the old one.

The error was "Failed to access IIS metabase".

I ran "aspnet_regiis -i" (located in WINDOWS\Microsoft.NET\Framework\{your version number}) and that fixed it.

This allowed me to use the canned reports that I downloaded from MS. (made by Tectura Denmark).
You can change the datasource and then the company name on the table and in the SQL statements on the design view (use replace all Ctrl + H ).

If you have the data in your company they work, but you still need a datamodel to let the users design their own reports in the report builder.

SO...


I planned on just using a few tables at first to get my feet wet, so I chose Salesheader, Salesline, Item and Customer.

I created a new report model and was going through the wizards to create a new datasource, then the dataview and finally a datamodel. This is where it started to go bad. The datamodel was completely useless. So I deleted it and checked the design of the data view. It allowed me to edit the relationships (although no filters could be added that I could see.) This allowed me to make a kind-of useful model, and some crappy ad-hoc reports once I deployed the model to the report server.

The model could be made a lot better by making more relations and by spending more time on it. But overall I'm pleased with the results for such a short amount of time spent (after the initial setup).

I would post the code for a sample, but there really isn't much to it and I don't know how transferable the model would be. Maybe if I get a lot of detail it would be worth while.

Joe