Report of Reports

Are there any example of SSRS reports using an rdl file as the source for the report. I.e. a Report of Reports?

Hi Freddie,

could you please elaborate a bit more?

thanks, 99

|||

One way of doing this in RS 2005 is to render a report with the xml data rendering extension as xml document. This is then used as input for the xml data extension. However, getting the query setup correctly and working is a bit tricky. A specific sample and explanation for this approach will be available in an upcoming MSDN article about the RS 2005 Xml data extension later this summer.

-- Robert

|||

Thanks Robert,

I did find an article "Integrate XML into Your Reporting Environment" by Bill Wolff at http://www.ftponline.com/special/sqlserver/bwolff/

I will be looking for the upcoming MSDN Article.

To elaborate more on my question...I would like to document my reporting service reports using reporting services. For example which property has custom code (e.g. IIF).

Report of Reports

Are there any example of SSRS reports using an rdl file as the source for the report. I.e. a Report of Reports?

Hi Freddie,

could you please elaborate a bit more?

thanks, 99

|||

One way of doing this in RS 2005 is to render a report with the xml data rendering extension as xml document. This is then used as input for the xml data extension. However, getting the query setup correctly and working is a bit tricky. A specific sample and explanation for this approach will be available in an upcoming MSDN article about the RS 2005 Xml data extension later this summer.

-- Robert

|||

Thanks Robert,

I did find an article "Integrate XML into Your Reporting Environment" by Bill Wolff at http://www.ftponline.com/special/sqlserver/bwolff/

I will be looking for the upcoming MSDN Article.

To elaborate more on my question...I would like to document my reporting service reports using reporting services. For example which property has custom code (e.g. IIF).

Report not refreshing

The data in the report is different from the data in source table (SQL Server). How can I refresh the data? Pressing F5 is not refreshing the data.
Thanks!I've not known F5 to not refresh before! Could the report possibly be using saved data (check report options)? Could you possibly be looking in two different databases?|||Which version if crystal are you using?|||Is crystal embedded -like within an erp system, or is it pulling directly from a database table

Report Models Data Source View

How does the report model know what data source view to use? I could not find it defined anywhere in the .smdl file.

My problem is this. I have a Report Model project with two data sources, two data source views and multiple report models. When I try and bind a data source to an entity in the report model I do not get to choose which data source view to use to choose what table/view I want to bind the entity too and only the tables in one of my DSV's shows up. When I first created it, it worked fine. It automatically selected the correct view and table and was successfully created but now when I open the project, that correlation is lost.

Any suggestions or help is appreciated, thanks.

I found out that if I close my solution and reopen it, the mappings are fixed. Does anyone know if this is a bug or if I could be doing something wrong? I can not find any documentation anywhere describing how data models are mapped to data source views. I really wish you can manually specify what datasource view you want to be using for each model. The autogenerate is nice to initially create the model but if you have already altered the elements or attributes in the model it can be a hassle. Any thoughts?

Report Model/Datasource View, Primary Data Source Problem

I have a datasource view DSV1. It points to a datasource DS1 that is considered the "primary".

I have created a Report Model that uses DSV1 (and thus uses DS1)

I created a new datasource, DS2 that I would like to use instead of DS1. (I can't just modify DS1 because if I modify it, it will overwrite it when we go to our Production environment and break that datasource)

So, I can go into DSV1 and change all the references from DS1 to DS2.

But that's where the problem lies.

When I try to build, I get the following error:

"The Table property of the Entity "E1" refers to the Table "dbo_View", which is not in the primary data source."

Somehow, the entity is tied to the "primary" datasource. When I change it back to DS1, everything works fine. Any thoughts? What can I do?

Here is what I've noticed:

If I look at the code for the Data Source DS1, the XML shows an <ID> tag and a <NAME> tag.

In SSRS, it is requiring that the ID be named "DS1", but the name can be whatever I want.

So if I change the name, it looks like I can deploy no problem without overwriting my old Datasource, but I just wish I could have these two pieces updated without a problem.

Any idea why this is?

Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

sql

Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

Report Model Project: Multiple Data Sources

I have setup a "Report Model Project" with multiple data sources, and
imported tables from each of these into my data source view. When I try to
use the wizard to create a report model I get an error on the "Completing the
Wizard" stage when I click "Run".
An error occurred while executing a command.
Message: Invalid object name 'dbo.<table name>'.
Command:
SELECT COUNT(*) FROM [dbo].[<table name>] t
The <table name> will always relate to the first table it attempts to
process from the secondary (non-primary) data source. I figured this out by
setting up the project from scratch with the opposite data source as primary.
It seems like it is looking in the primary data source for the table that it
should be trying to use the secondary data source to query.
Any help is appreciated. :)A report model can only reference one data source in this release. Data
source views are more flexible than supported by report models.
-Carolyn
"Greg T. Smith" wrote:
> I have setup a "Report Model Project" with multiple data sources, and
> imported tables from each of these into my data source view. When I try to
> use the wizard to create a report model I get an error on the "Completing the
> Wizard" stage when I click "Run".
> An error occurred while executing a command.
> Message: Invalid object name 'dbo.<table name>'.
> Command:
> SELECT COUNT(*) FROM [dbo].[<table name>] t
> The <table name> will always relate to the first table it attempts to
> process from the secondary (non-primary) data source. I figured this out by
> setting up the project from scratch with the opposite data source as primary.
> It seems like it is looking in the primary data source for the table that it
> should be trying to use the secondary data source to query.
> Any help is appreciated. :)

report model project problem - entity not showing up

I have a client table and a clientdoctors table.
In the data source view i see all the needed tables in the diagram organizer
pane. I also see the relationship between the clientdoctors table (which
holds clientid and doctorid), as well as client table and doctor table.
That all looks fine. The problem however is that when in my report model i
do not see those relationships at all between the client - client doctor -
doctor tables. There are a few situations just like this that i cannot
explain.
I am new to the report model project but i dont know why i dont see these
relationships inthe report model or while using report builder.
Any ideas please'Have you deployed properly? I mean you didn;t get any error. After deploying
you get to see in Report Builder. Just check.
Amarnath
"dave" wrote:
> I have a client table and a clientdoctors table.
> In the data source view i see all the needed tables in the diagram organizer
> pane. I also see the relationship between the clientdoctors table (which
> holds clientid and doctorid), as well as client table and doctor table.
> That all looks fine. The problem however is that when in my report model i
> do not see those relationships at all between the client - client doctor -
> doctor tables. There are a few situations just like this that i cannot
> explain.
> I am new to the report model project but i dont know why i dont see these
> relationships inthe report model or while using report builder.
> Any ideas please'|||Yes everything deployed properly. It seems like the problem was related to
keys on the tables. i.e. i had to place keys on the middle table.
"Amarnath" wrote:
> Have you deployed properly? I mean you didn;t get any error. After deploying
> you get to see in Report Builder. Just check.
> Amarnath
>
> "dave" wrote:
> > I have a client table and a clientdoctors table.
> >
> > In the data source view i see all the needed tables in the diagram organizer
> > pane. I also see the relationship between the clientdoctors table (which
> > holds clientid and doctorid), as well as client table and doctor table.
> >
> > That all looks fine. The problem however is that when in my report model i
> > do not see those relationships at all between the client - client doctor -
> > doctor tables. There are a few situations just like this that i cannot
> > explain.
> >
> > I am new to the report model project but i dont know why i dont see these
> > relationships inthe report model or while using report builder.
> >
> > Any ideas please'|||You need to have key relationship otherwise it wont select tables in the
report model itself. If you have completed the wizard of creating report
model. then it would have asked for keys when you select tables for
relations. So just check if all the keys are in place and deploy.
Amarnath
"dave" wrote:
> Yes everything deployed properly. It seems like the problem was related to
> keys on the tables. i.e. i had to place keys on the middle table.
>
> "Amarnath" wrote:
> > Have you deployed properly? I mean you didn;t get any error. After deploying
> > you get to see in Report Builder. Just check.
> >
> > Amarnath
> >
> >
> > "dave" wrote:
> >
> > > I have a client table and a clientdoctors table.
> > >
> > > In the data source view i see all the needed tables in the diagram organizer
> > > pane. I also see the relationship between the clientdoctors table (which
> > > holds clientid and doctorid), as well as client table and doctor table.
> > >
> > > That all looks fine. The problem however is that when in my report model i
> > > do not see those relationships at all between the client - client doctor -
> > > doctor tables. There are a few situations just like this that i cannot
> > > explain.
> > >
> > > I am new to the report model project but i dont know why i dont see these
> > > relationships inthe report model or while using report builder.
> > >
> > > Any ideas please'

Report Model Generator freezes VS2005

Hi,

I'm trying to generate data source model from appropriate relational

data source view with Report Model Wizard. But at the end of "Step 2:

processing rules on relations" Visual Studio devenv process aquires all

CPU resources and freezes VS.

What's wrong with my view? May be it contains some features that aren't supported by model generation process.

Regards,

Anatoly.

что произойдет если оставить машину в таком состоянии на какое-то время?
какая версия репорт сервера?
попробуй приаттачить DSV, кстати какой размер DSV файла?

Report Model from multiple data source

Creating a Data Source View from multiple Data Sources is no problem but what happens when you try to create the actuall Report Model. Using the primary Data Source doesn't give access to other databases, since each table name starts with dbo. Is there a option/flag to set to include database name?

Report Model DSVs only support one database, to pull in data from other databases you'll need to create a Named Query.

Analysis Services, on the other hand, don't have that problem, except for being limited to one database per DSV.

Note: I'm pretty sure I'm correct, but not 100%

sql

Report Model Filtering

When creating a filter with Report Model against my cube (or perhaps any
source) - it seems to force me to free form type how the filter will work.
For example -
Attribute: Does_Client_Smoke
Values: Yes, No, N/A, Won't Answer, Cannot Answer
When I create the filter it makes me type in one of the above values instead
of letting me select it from a pull-down such as how BIDS works - what am I
doing wrong here?Can someone help me on this question below. When using the report viewer,
there is a BACK button but when using browser, it won't page back correctly.
I'm using latest IE beta - maybe that's my issue?
"Joe" <hortoristic@.gmail dot com> wrote in message
news:%23ZxRAzSzGHA.772@.TK2MSFTNGP05.phx.gbl...
> When creating a filter with Report Model against my cube (or perhaps any
> source) - it seems to force me to free form type how the filter will work.
> For example -
> Attribute: Does_Client_Smoke
> Values: Yes, No, N/A, Won't Answer, Cannot Answer
> When I create the filter it makes me type in one of the above values
> instead of letting me select it from a pull-down such as how BIDS works -
> what am I doing wrong here?
>

Report Model DSV Data Source change.

I have a Data Source View that is based on DS_Dev and all the table/named queries are based on this Data Set. I want to point the DSV to DS_Prod, but when I refresh the DSV it is still trying to look at DS_Dev. I know I must be overlooking something. How can I change all the tables in the DSV to point to the new data source?

I'm assuming I have to replace each table with the corresponding table in the new data source. I hope not.sql

Report model data sources

Is it possible to have a custom data processing extension as the data source in a report model project?

No, this is currently not supported.

-- Robert

sql

Report Model Data Source other than SQL Server?

Hello, is this a true statement: "The only Data Sources available to a Report Model are SQL Server 2000, SQL Server 2005, and SQL Server Analysis Services 2005".

I had read somewhere that there was going to be an ability to specify a "custom" query processors so that you could use data sources in addition to the items listed above.

Thanks!

It is true statement in this release.
MS people may know about future releases.
Philippe

Report Model as datasource in ordinary report

I think the report model concept is great and it works well for the report
builder. Can a report model or a data source view be used as a datasource for
ordinary reports? We have a large number of reports built upon complex
relational queries (join join join join...), but pretty much the same query
for all the reports. One way of solving this is by building an olap cube, but
the thing is that all this reports are automaticly genererated from a
"backroom" database for quality checking and then all the data is loaded into
a final datawarehouse. But maybe it's possibel to do this without a cube in
the backroom.OK, I've figured it out myself. A report model can definitly be the data
source for a report and it works very well, actually!
"Erik Hedlund" wrote:
> I think the report model concept is great and it works well for the report
> builder. Can a report model or a data source view be used as a datasource for
> ordinary reports? We have a large number of reports built upon complex
> relational queries (join join join join...), but pretty much the same query
> for all the reports. One way of solving this is by building an olap cube, but
> the thing is that all this reports are automaticly genererated from a
> "backroom" database for quality checking and then all the data is loaded into
> a final datawarehouse. But maybe it's possibel to do this without a cube in
> the backroom.|||I didn't think that it could be, can you clue me in?
"Erik Hedlund" <ErikHedlund@.discussions.microsoft.com> wrote in message
news:EB83FAA1-FCE7-4A89-8125-18FEB3C71489@.microsoft.com...
> OK, I've figured it out myself. A report model can definitly be the data
> source for a report and it works very well, actually!
> "Erik Hedlund" wrote:
>> I think the report model concept is great and it works well for the
>> report
>> builder. Can a report model or a data source view be used as a datasource
>> for
>> ordinary reports? We have a large number of reports built upon complex
>> relational queries (join join join join...), but pretty much the same
>> query
>> for all the reports. One way of solving this is by building an olap cube,
>> but
>> the thing is that all this reports are automaticly genererated from a
>> "backroom" database for quality checking and then all the data is loaded
>> into
>> a final datawarehouse. But maybe it's possibel to do this without a cube
>> in
>> the backroom.|||Create a data source, a datasource view and a report model and deply it to
the report server.
Create a report and select report model as the data source and specify a
connectionstring for it. Example:
server=http://localhost/reportserver;datasource=/Models/name of report model
And then your on!
"Steve MunLeeuw" wrote:
> I didn't think that it could be, can you clue me in?
> "Erik Hedlund" <ErikHedlund@.discussions.microsoft.com> wrote in message
> news:EB83FAA1-FCE7-4A89-8125-18FEB3C71489@.microsoft.com...
> > OK, I've figured it out myself. A report model can definitly be the data
> > source for a report and it works very well, actually!
> >
> > "Erik Hedlund" wrote:
> >
> >> I think the report model concept is great and it works well for the
> >> report
> >> builder. Can a report model or a data source view be used as a datasource
> >> for
> >> ordinary reports? We have a large number of reports built upon complex
> >> relational queries (join join join join...), but pretty much the same
> >> query
> >> for all the reports. One way of solving this is by building an olap cube,
> >> but
> >> the thing is that all this reports are automaticly genererated from a
> >> "backroom" database for quality checking and then all the data is loaded
> >> into
> >> a final datawarehouse. But maybe it's possibel to do this without a cube
> >> in
> >> the backroom.
>
>sql

report model and it's datasource

hi everyone,

I created a report model and deployed it. When I open the rpt builder on web I get the following error.

'The selected data source does not have and content available'

Seemed like it would be a easy issue, but I cannot retrieve any data in the rpt model. The datasource I'm using does work for rpts not associated with the rpt model. Any suggestions? It seems like I've missed a step. Thanks, Lisa

If there is no primary key defined, you can build the model, but no contents. Do you have primary key defined?sql

Report Model against Cube data source

We have successfully generated a Report Model against a Cube data source. My question is I can't seem to figure out how to modify the model. The wizard organized and named entities in an odd way - and even finding the measures is in some obscure place. I know how to create models in BIDS, but it doesn't seem to give me that same flexibility against a cube source.
unfortunately you can't edit it using VS.
maybe you can manually edit the XML file behind the model... but its a huge job...
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:7FE0118E-8DD0-4D77-B39A-024254A0C10E@.microsoft.com...
We have successfully generated a Report Model against a Cube data source. My question is I can't seem to figure out how to modify the model. The wizard organized and named entities in an odd way - and even finding the measures is in some obscure place. I know how to create models in BIDS, but it doesn't seem to give me that same flexibility against a cube source.

Report Model against Cube data source

We have successfully generated a Report Model against a Cube data source. M
y question is I can't seem to figure out how to modify the model. The wizar
d organized and named entities in an odd way - and even finding the measures
is in some obscure place. I know how to create models in BIDS, but it does
n't seem to give me that same flexibility against a cube source.unfortunately you can't edit it using VS.
maybe you can manually edit the XML file behind the model... but its a huge
job...
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:7FE0118E-8DD0-4D77-B
39A-024254A0C10E@.microsoft.com...
We have successfully generated a Report Model against a Cube data source. M
y question is I can't seem to figure out how to modify the model. The wizar
d organized and named entities in an odd way - and even finding the measures
is in some obscure place. I know how to create models in BIDS, but it does
n't seem to give me that same flexibility against a cube source.

Report Model (Data Source View) issue

We've built an oltp that uses nothing but synonyms that reach out to various DB's in the organization thru linked servers.

The issue that we are having is Data Source Views can only see tables and views. Is it possible to use synonyms? If not what would be a work around.

Please advise. Thanks

Steve

I use linked servers in reports (actually, let's be honest, I use linked servers pretty much ALL THE TIME ).

I haven't done this with Report Models that I can recall. But I am pretty sure that what works for me would work in Report Models.

There are a couple of things you can do as a workaround:

1. If you don't have other uses for the synonyms, eliminate the synonym from the equation entirely and just use fully-qualified syntax in views, for example (and of course you don't have to use * here!) :

CREATE VIEW vw_Synonym1 AS SELECT * FROM MyLinkedServer.MyDB1.dbo.MyTable

2. I'm not entirely sure that the models can't use synonyms! It's likely that they can but the wizards and interfaces that you're given in the designer cannot. If I'm right, you just need to write something to handle the model XML directly to get what you want.

I am not going to mess with this to test it out, because I don't spend a lot of time with report models, but start with the XSD (which must be published) and figure out whether the appropriate values should go. Then alter a copy of an existing model that you have to test -- I bet it works. If you want to go this route and get stuck on the details, holler. <g>

3. If you do have other uses for the synonyms, as apparently you do, and if you don't want to mess with the model xml, you can create a view that represents each synonym in a one-to-one relationship. IOW, pretty much CREATE VIEW vw_Synonym1 AS SELECT * FROM Synonym1. Again I know that * is evil but you get the picture <s>. Extend this properly as follows:

You can avoid the evil "*" by interrogating the properties of the thing underneath. You can automate all current synonyms to create new views (and leverage the interrogation code you just wrote) by examining SELECT * FROM sys.synonyms and writing code that works through the list. You can automate all future synonyms you create to do the same thing automatically by using a database trigger. Here is a sketch: