Report Parameter List from Field

How can I have a report parameter show a list of employee names from the
employee table fields, (lastname, firstname) is this possible?
Thank you
FrankDo you use Business Intelligence studio to update reports, if so, create
query for employees table (Select lastname + ', ' + firstname as EmployeeName
from tblemployees order by lastname, firtsname", then create report parameter
(menu bar <Report><Report Parameters> ) that uses the qry for its "available
values" data.
"Frank" wrote:
> How can I have a report parameter show a list of employee names from the
> employee table fields, (lastname, firstname) is this possible?
> Thank you
> Frank|||loulou,
I am using Business Intelligence and did develop a second dataset called
Employees. I have the following written:
SELECT LastName, FirstName, Active_Employee, Employee_ID
FROM Employees
ORDER BY LastName, FirstName
In the Report Parameter I am using Available Values and have selected the
Employees Dataset, Employee_ID Value Field and Employee_ID Label Field.
This brings back a list of the Employee ID Numbers when I go to Preview. I
would also like to include last name and first name except that it errors
out.
Thank you
"loulou" wrote:
> Do you use Business Intelligence studio to update reports, if so, create
> query for employees table (Select lastname + ', ' + firstname as EmployeeName
> from tblemployees order by lastname, firtsname", then create report parameter
> (menu bar <Report><Report Parameters> ) that uses the qry for its "available
> values" data.
> "Frank" wrote:
> > How can I have a report parameter show a list of employee names from the
> > employee table fields, (lastname, firstname) is this possible?
> >
> > Thank you
> >
> > Frank|||loulou,
I set everything correctly now and it works great, thank you!
SELECT LastName + ',' + FirstName AS Expr1, Active_Employee, Employee_ID
FROM Employees
ORDER BY LastName, FirstName
"loulou" wrote:
> Do you use Business Intelligence studio to update reports, if so, create
> query for employees table (Select lastname + ', ' + firstname as EmployeeName
> from tblemployees order by lastname, firtsname", then create report parameter
> (menu bar <Report><Report Parameters> ) that uses the qry for its "available
> values" data.
> "Frank" wrote:
> > How can I have a report parameter show a list of employee names from the
> > employee table fields, (lastname, firstname) is this possible?
> >
> > Thank you
> >
> > Franksql

Report paging problem

I'm having a problem with a report i recently built. I use a table
with three colums and one issue I have is there is a lot of white
space left all over the report because a table group will not span
onto the next column.
With the exception of one group that is larger than 1 entire column.
That one actually fills up one column and then starts agian at the top
of the next column.
Has any one else ran into this?
Also if you know how to potentially fix this let me know.
Thanks,
GregHave you tried Merging several cells to span the columns?
"Greg" <greg.cheadle@.gmail.com> wrote in message
news:1183647511.082603.121240@.q69g2000hsb.googlegroups.com...
> I'm having a problem with a report i recently built. I use a table
> with three colums and one issue I have is there is a lot of white
> space left all over the report because a table group will not span
> onto the next column.
> With the exception of one group that is larger than 1 entire column.
> That one actually fills up one column and then starts agian at the top
> of the next column.
> Has any one else ran into this?
> Also if you know how to potentially fix this let me know.
> Thanks,
> Greg
>

Report Paging

We are converting from Crystal Reports to Reporting Services. We are
encountering what appears to be an issue with the Report Viewer. If Table
rows in the body of the report are conditionally made Hidden (depending on
parameter input), the number of pages differs between the viewer and the
printed (either printer or pdf). In fact, some reports show a single page in
the viewer, but many pages when printed or exported to pdf. If the Table rows
are always visible, the pagination is consistent between viewer and printing.
Is there any way to work around this?Hello HK,
I would like to know the following things:
1. What's the SQL server version did you use? 2000 or 2005?
2. Since this issue only occured for the conditional Hidden, it is a known
issue in SQL 2005. And the product team is researching this issue. If I get
any update, I will let you know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you for your response.
We are using sql server 2005 sp2, visual studio 2005.
It appears to be occuring only with the conditional Hidden. If I remove the
conditional Hidden, we appear to get correct pagination. I (and our many
report users) would greatly appreciate any solution to this.
"Wei Lu [MSFT]" wrote:
> Hello HK,
> I would like to know the following things:
> 1. What's the SQL server version did you use? 2000 or 2005?
> 2. Since this issue only occured for the conditional Hidden, it is a known
> issue in SQL 2005. And the product team is researching this issue. If I get
> any update, I will let you know.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello HK,
Product Team confirmed this is a known issue and will be addressed in the
next version of Reporting Services.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I'm glad to hear that - thanks for your response.
"Wei Lu [MSFT]" wrote:
> Hello HK,
> Product Team confirmed this is a known issue and will be addressed in the
> next version of Reporting Services.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

Report only showing a single record

I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
records in Table. However, the report only shows a single record/page. I
placed the fields on the report in the "Body". What am I missing here?Nevermind... Got it. It's the List item.
"Dan" wrote:
> I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?|||When you place the fields on the blank layout surface you are not using any
of the controls that know about multiple rows. You need to place either a
table control or a list control and then drag and drop onto them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:7F61EA6A-DB25-43FC-9117-84A2E7E037AA@.microsoft.com...
>I have a DataSet which is defined as "SELECT * FROM Table". There are MANY
> records in Table. However, the report only shows a single record/page. I
> placed the fields on the report in the "Body". What am I missing here?sql

Report on a Sharepoint table in SRS

I'm not sure this is possible, but... I was asked to write a SQL Reporting Services Report that reports on a SQL table that is joined to a Sharepoint Table. In trying to look up how to do this, I'm not finding any how-to posts. Is this possible and if so, could you point me in the direction of a step-by-step how -to white paper?

Thanks,

Jennifer

WSS exposes its feature set as a web service. I think the one that returns the data in a list is called Lists.asmx. From there, you can use the XML data provider in SSRS 2005 to connect to the service and consume the data in XML. This post should help you to get started with the SSRS XML data provider. If you need to create a report that joins data from two lists on the report, you could try a third-party provider such as Enesys or build your own custom data extension.

As a side note, a native provider that reports from WSS is on top of the wish list for a next release as discussed in Brian Welcker's blog.

Report on a Sharepoint table in SRS

I'm not sure this is possible, but... I was asked to write a SQL Reporting Services Report that reports on a SQL table that is joined to a Sharepoint Table. In trying to look up how to do this, I'm not finding any how-to posts. Is this possible and if so, could you point me in the direction of a step-by-step how -to white paper?

Thanks,

Jennifer

WSS exposes its feature set as a web service. I think the one that returns the data in a list is called Lists.asmx. From there, you can use the XML data provider in SSRS 2005 to connect to the service and consume the data in XML. This post should help you to get started with the SSRS XML data provider. If you need to create a report that joins data from two lists on the report, you could try a third-party provider such as Enesys or build your own custom data extension.

As a side note, a native provider that reports from WSS is on top of the wish list for a next release as discussed in Brian Welcker's blog.

sql

Report not using pagination properly

I have a portrait report that has 1 table with 1 hidden group header (for the Page Header to reference fields from) and 36 detail rows. My problem is that pagination is not working properly. Depending on the length of data in the detail rows, the report tends to push majority of data on the next page(s) which leaves a lot of blank lines or blank page at the beginning of each group of data. I want the data to complete the first page before rolling over to the next page. That's why I have 36 individual detail rows instead of all the data in 1 detail row. I've tried adjusting the page length, but it doesn't seem to work all that well. I either get all the report on one page (which is fine when viewing online, but data is cut off when printing) or inadequate pagination. I do not have any white space at the bottom of the table and the page footer. Anyone have any ideas?

Thanks,

T

Have you tried setting KeepTogether=false on your table? The behavior your describing sounds like it's set to true, which would mean that if the table can't fit completely on the current page, but will fit on the next page, it will move the table to the next page.

You shouldn't need to specify the detail rows individually if you're populating from a data source!|||Yes, KeepTogether is false and yes I am using a shared data source and data is queried using Stored Procedure.|||Well it seems the problem lies within the Group Header row. I removed that row and pagination works properly now, but I lose the ability to call fields in my page header when the report spans more than one page.|||

Ok, I found a solution for showing Field values in Page Header even when the report spans more than one page(but you don't know at what point the report spans to the next page). It's a combination of several solutions:

1) Create hidden textbox(es) with the field value you're wanting on every line where the page might span to the next page.

textbox1

textbox2

textbox3

2) Create a function in code that will reference each hidden textbox(es) by name

Shared Function Header(reportItems as ReportItems) as string
Dim final as string
If ReportItems!textbox1.value <> "" Then
final = ReportItems!textbox1.value
Else If ReportItems!textbox2.value <> "" Then
final = ReportItems!textbox2.value
Else If ReportItems!textbox3.value <> "" Then
final = ReportItems!textbox3.value
End If
Return final
End Function

3) In Page Header call function, pass (ReportItems)

=Code.Header(ReportItems)

The reason for the function is because you can't reference multiple ReportItems in Page Header textbox, so you have to determine which hidden textbox has a value on that page and return that value to the calling Header textbox. Hope this helps....

sql

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 Model Timeout

I am trying to create a report model consisting of one main table with
several views connecting to the main table. The main table is rather large
(20-30 million records). I keep getting the following error message below.
Any ideas?
An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.i have same problem,
there must be a timeout setting somewhere
"retkow" wrote:
> I am trying to create a report model consisting of one main table with
> several views connecting to the main table. The main table is rather large
> (20-30 million records). I keep getting the following error message below.
> Any ideas?
> An error occurred while executing a command.
> Message: Timeout expired. The timeout period elapsed prior to completion of
> the operation or the server is not responding.
>|||I am getting the same error message after 30 minutes into my report
execution. The execution timeout in site settings is set to 3 hours.
SO dont seem to understand what can be causing this timeout.
Any ideas on where to look is greatly appreciated.
Thanks
"Tango" wrote:
> i have same problem,
> there must be a timeout setting somewhere
> "retkow" wrote:
> > I am trying to create a report model consisting of one main table with
> > several views connecting to the main table. The main table is rather large
> > (20-30 million records). I keep getting the following error message below.
> > Any ideas?
> >
> > An error occurred while executing a command.
> > Message: Timeout expired. The timeout period elapsed prior to completion of
> > the operation or the server is not responding.
> >

Report Model Question

Anyone know if it is possible to do a UNION between objects in a report model. I have 3 table that need to be accessed individually, but on occasion I need to do a UNION of the 3.

R

You could try doing the union in a view and adding that to the model.|||

I did that in the first cut, but it gave me two different paths for the end user to choose to get to the same data. I was trying to avoid that. Thanks anyway.

R

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 on tables without primary key

I am trying to create a report model on some tables that do not have a
primary key. The wizard keeps failing with the message "Table does not have a
primary key". Is a primary key mandatory?You could define logic primary key in your dsv.
"Dan Varozza" wrote:
> I am trying to create a report model on some tables that do not have a
> primary key. The wizard keeps failing with the message "Table does not have a
> primary key". Is a primary key mandatory?|||WHen I run the DSV wizard it tells me that "no foreign keys were found. You
can create logical relationships on matching columns."
Then it gives me the choice to "Create logical relationships by matching
columns" and 3 Foreign Key Matches to choose from:
"Same as Primary key"
or
"Same name as destination table name"
or
"Destination table name + primary key name"
I have tried all 3 of the choices above and still the wizard fails with the
same error. "Table does not have a primary key".
Any ideas?
"yongli" wrote:
> You could define logic primary key in your dsv.
> "Dan Varozza" wrote:
> > I am trying to create a report model on some tables that do not have a
> > primary key. The wizard keeps failing with the message "Table does not have a
> > primary key". Is a primary key mandatory?sql

Report Model missing some fields

I hope someone can clarify what I observe below.

When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.

This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).

Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?

Perhaps I'm doing something wrong, or there is a workaround?

If anyone can shed any light in the issue, I'd greatly appreciate their comment.

Thanks in advance and kindest regards

Craig

What is the datatype of the column that is not added? Text fields are not supported. Also, are you saying that the field IS added as a role? This is not clear.

-Carolyn [MSFT]

|||

Carolyn,

Thanks for your reply.

I hope will make what I'm trying to say slightly clearer.

CREATE TABLE [dbo].[MBB010](
[PRE_B01] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARTNO] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT15] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMMCODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HAZARD1] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
<...SNIP... (total of 77 fields) >
[ITSTACODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MBB010__ITSTACOD__03681F15] DEFAULT (' '),
CONSTRAINT [MBB010_1] UNIQUE CLUSTERED
(
[PRE_B01] ASC,
[PARTNO] ASC,
[ACCOUNT15] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Indexes as follows

index_name index_description index_keys
-- --
MBB010_1 clustered, unique, unique key located on PRIMARY PRE_B01, PARTNO, ACCOUNT15
MBB010_2 nonclustered located on PRIMARY PRODGROUP, PRE_B01, PARTNO, ACCOUNT15
MBB010_3 nonclustered located on PRIMARY ACCOUNT15, HSYSCODE_ITEM

So PARTNO column points off to other tables within the DSV.

PRE_B01 and ACCOUNT15 get added by the wizard when I add this table to the report model, but PARTNO gets skipped. Only thing I can see different about this one field is that its included as a role.

I'm keen to understand and avoid having to manually edit the model to fix this as I have 500+ tables :-(

Thanks in advance

Craig

|||

Sorry to reply to me own question but I think I made a mistake. ACCOUNT15 does NOT get added either. So can someone clarify the rules that the SQL wizard (when adding a new table to a report model) follows?

Do all fields that become roles not get included (to end users) when they select this table in Report Builder?

|||

I too am struggling with the same problem. There are about 277 tables in my model and each table one or more such fields that a part of primary key go missing and appear as roles. So when I am trying to build a report I cannot find it under the parent table I have to go the related child table and pick it from there. This is not necessarily obvious to the end users of the model who are building reports.

It will be great to hear if anyone know how to work around this problem. It is not feasible to add all of the manually again.

|||

Sorry for not updating people.

I'm since got this working as you would expect (for new test fields I added into the model), by which I mean the "field" remains as an Attribute but is also created as a Role. I am giving SSRS the benefit of the doubt that I had corrupted the report model as I created the entire thing programmatically by reverse engineering the XML from other examples. Mind you, many times during this excersise Visual Studio would report the model as being unloadable or corrupt in some way (so I'm saying its validation is usually very good), but my current model definately loads without complaint.

The only related thing that I find annoying is that it renames the fields. So as I have many tables that link on PARTNO, the roles gets renamed PARTNO2, 3, 4, 5, etc. I guess this is because the underlying format is XML which is case-sensitive and so SSRS can't allow to "items" to have the same name.

Report Model missing some fields

I hope someone can clarify what I observe below.

When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.

This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).

Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?

Perhaps I'm doing something wrong, or there is a workaround?

If anyone can shed any light in the issue, I'd greatly appreciate their comment.

Thanks in advance and kindest regards

Craig

What is the datatype of the column that is not added? Text fields are not supported. Also, are you saying that the field IS added as a role? This is not clear.

-Carolyn [MSFT]

|||

Carolyn,

Thanks for your reply.

I hope will make what I'm trying to say slightly clearer.

CREATE TABLE [dbo].[MBB010](
[PRE_B01] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARTNO] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT15] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMMCODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HAZARD1] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
<...SNIP... (total of 77 fields) >
[ITSTACODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MBB010__ITSTACOD__03681F15] DEFAULT (' '),
CONSTRAINT [MBB010_1] UNIQUE CLUSTERED
(
[PRE_B01] ASC,
[PARTNO] ASC,
[ACCOUNT15] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Indexes as follows

index_name index_description index_keys
-- --
MBB010_1 clustered, unique, unique key located on PRIMARY PRE_B01, PARTNO, ACCOUNT15
MBB010_2 nonclustered located on PRIMARY PRODGROUP, PRE_B01, PARTNO, ACCOUNT15
MBB010_3 nonclustered located on PRIMARY ACCOUNT15, HSYSCODE_ITEM

So PARTNO column points off to other tables within the DSV.

PRE_B01 and ACCOUNT15 get added by the wizard when I add this table to the report model, but PARTNO gets skipped. Only thing I can see different about this one field is that its included as a role.

I'm keen to understand and avoid having to manually edit the model to fix this as I have 500+ tables :-(

Thanks in advance

Craig

|||

Sorry to reply to me own question but I think I made a mistake. ACCOUNT15 does NOT get added either. So can someone clarify the rules that the SQL wizard (when adding a new table to a report model) follows?

Do all fields that become roles not get included (to end users) when they select this table in Report Builder?

|||

I too am struggling with the same problem. There are about 277 tables in my model and each table one or more such fields that a part of primary key go missing and appear as roles. So when I am trying to build a report I cannot find it under the parent table I have to go the related child table and pick it from there. This is not necessarily obvious to the end users of the model who are building reports.

It will be great to hear if anyone know how to work around this problem. It is not feasible to add all of the manually again.

|||

Sorry for not updating people.

I'm since got this working as you would expect (for new test fields I added into the model), by which I mean the "field" remains as an Attribute but is also created as a Role. I am giving SSRS the benefit of the doubt that I had corrupted the report model as I created the entire thing programmatically by reverse engineering the XML from other examples. Mind you, many times during this excersise Visual Studio would report the model as being unloadable or corrupt in some way (so I'm saying its validation is usually very good), but my current model definately loads without complaint.

The only related thing that I find annoying is that it renames the fields. So as I have many tables that link on PARTNO, the roles gets renamed PARTNO2, 3, 4, 5, etc. I guess this is because the underlying format is XML which is case-sensitive and so SSRS can't allow to "items" to have the same name.

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 Design Question...

The senerio:

Table: Voucher Fields: Voucher Id, Dollars, other fields...

Table: Payment Fields: Payment Id, Dollars, other fields...

Table: VoucherPaymentXRef Fields: Id, Voucher Id, Payment Id

The relationship is Voucher many-to-many VoucherPaymentXRef many-to-many Payment.

I originally brought these 3 tables into the DSV and setup 3 entities in the model (Voucher, Payment, XRef (hidden except for roles)). It allows me to do reports in Report Builder just on Voucher or just on Payment, but it didn't let me create a report to contain fields from both Voucher and Payment. I'm guessing because of the lack of support for many-to-many relationships.

I then went back to the DSV and started from scratch creating 2 named queries; one for Voucher that brings back everything from Voucher as well as the ID field from XRef by way of left outer join. Did the same for Payment so I could eliminate the XRef table from the join between the 2 named queries. This approached help with the reports containing fields from both Voucher and Payment, but causes problems when I just want to do a report just from Voucher or Payment because the left outer join in each named query creates duplicate rows based on the many-to-many relationship with XRef which makes any aggregates wrong.

Is there another way to do this where I can run any of these report types from one model rather than creating 2 different model / perspectives for two different type of reports?

Thanks.

I'm not sure why you weren't able to display fields from Voucher and Payment in the same report. For instance, with the AdventureWorks sample model, you can create a report that shows Sales by Product and Order Year, which leverages a many-to-many relationship between Product and Order (through Sale).

This post on my blog may be helpful in simplifying the user experience for many-to-many relationships:

http://blogs.msdn.com/bobmeyers/archive/2006/03/24/560255.aspx

Hope that helps!

|||

Hi Bob,

I tried implementing the steps in your post but it still doesn't let me drag and drop fields from both Voucher and Payment. I set up a cardinality of Many to Many from roles Voucher to XRef and Many to Many for roles XRef to Payment. After I drag a voucher fields and try to drag a payment field, it just doesn't allow me to drop it. I can see how if I set the cardinality from 1 to M for Voucher to XRef and M to 1 for XRef to Payment would work, because if I do that it lets me drop it, but doesn't bring the data back correctly. I not sure if I'm doing something wrong.

|||Setting the cardinality to 1:* and *:1 is the correct approach. Can you give more detail about the data you are getting back, and why you believe it is incorrect?

Report Model Design Question...

The senerio:

Table: Voucher Fields: Voucher Id, Dollars, other fields...

Table: Payment Fields: Payment Id, Dollars, other fields...

Table: VoucherPaymentXRef Fields: Id, Voucher Id, Payment Id

The relationship is Voucher many-to-many VoucherPaymentXRef many-to-many Payment.

I originally brought these 3 tables into the DSV and setup 3 entities in the model (Voucher, Payment, XRef (hidden except for roles)). It allows me to do reports in Report Builder just on Voucher or just on Payment, but it didn't let me create a report to contain fields from both Voucher and Payment. I'm guessing because of the lack of support for many-to-many relationships.

I then went back to the DSV and started from scratch creating 2 named queries; one for Voucher that brings back everything from Voucher as well as the ID field from XRef by way of left outer join. Did the same for Payment so I could eliminate the XRef table from the join between the 2 named queries. This approached help with the reports containing fields from both Voucher and Payment, but causes problems when I just want to do a report just from Voucher or Payment because the left outer join in each named query creates duplicate rows based on the many-to-many relationship with XRef which makes any aggregates wrong.

Is there another way to do this where I can run any of these report types from one model rather than creating 2 different model / perspectives for two different type of reports?

Thanks.

I'm not sure why you weren't able to display fields from Voucher and Payment in the same report. For instance, with the AdventureWorks sample model, you can create a report that shows Sales by Product and Order Year, which leverages a many-to-many relationship between Product and Order (through Sale).

This post on my blog may be helpful in simplifying the user experience for many-to-many relationships:

http://blogs.msdn.com/bobmeyers/archive/2006/03/24/560255.aspx

Hope that helps!

|||

Hi Bob,

I tried implementing the steps in your post but it still doesn't let me drag and drop fields from both Voucher and Payment. I set up a cardinality of Many to Many from roles Voucher to XRef and Many to Many for roles XRef to Payment. After I drag a voucher fields and try to drag a payment field, it just doesn't allow me to drop it. I can see how if I set the cardinality from 1 to M for Voucher to XRef and M to 1 for XRef to Payment would work, because if I do that it lets me drop it, but doesn't bring the data back correctly. I not sure if I'm doing something wrong.

|||Setting the cardinality to 1:* and *:1 is the correct approach. Can you give more detail about the data you are getting back, and why you believe it is incorrect?

Report Model Design Question...

The senerio:

Table: Voucher Fields: Voucher Id, Dollars, other fields...

Table: Payment Fields: Payment Id, Dollars, other fields...

Table: VoucherPaymentXRef Fields: Id, Voucher Id, Payment Id

The relationship is Voucher many-to-many VoucherPaymentXRef many-to-many Payment.

I originally brought these 3 tables into the DSV and setup 3 entities in the model (Voucher, Payment, XRef (hidden except for roles)). It allows me to do reports in Report Builder just on Voucher or just on Payment, but it didn't let me create a report to contain fields from both Voucher and Payment. I'm guessing because of the lack of support for many-to-many relationships.

I then went back to the DSV and started from scratch creating 2 named queries; one for Voucher that brings back everything from Voucher as well as the ID field from XRef by way of left outer join. Did the same for Payment so I could eliminate the XRef table from the join between the 2 named queries. This approached help with the reports containing fields from both Voucher and Payment, but causes problems when I just want to do a report just from Voucher or Payment because the left outer join in each named query creates duplicate rows based on the many-to-many relationship with XRef which makes any aggregates wrong.

Is there another way to do this where I can run any of these report types from one model rather than creating 2 different model / perspectives for two different type of reports?

Thanks.

I'm not sure why you weren't able to display fields from Voucher and Payment in the same report. For instance, with the AdventureWorks sample model, you can create a report that shows Sales by Product and Order Year, which leverages a many-to-many relationship between Product and Order (through Sale).

This post on my blog may be helpful in simplifying the user experience for many-to-many relationships:

http://blogs.msdn.com/bobmeyers/archive/2006/03/24/560255.aspx

Hope that helps!

|||

Hi Bob,

I tried implementing the steps in your post but it still doesn't let me drag and drop fields from both Voucher and Payment. I set up a cardinality of Many to Many from roles Voucher to XRef and Many to Many for roles XRef to Payment. After I drag a voucher fields and try to drag a payment field, it just doesn't allow me to drop it. I can see how if I set the cardinality from 1 to M for Voucher to XRef and M to 1 for XRef to Payment would work, because if I do that it lets me drop it, but doesn't bring the data back correctly. I not sure if I'm doing something wrong.

|||Setting the cardinality to 1:* and *:1 is the correct approach. Can you give more detail about the data you are getting back, and why you believe it is incorrect?

Report Model - Custom Code or Assembly Reference

Hi, I'm trying to create a new Report Model Project, but I have a problem. The problem is that I have a one custom column in the database table, lets say column's name is 'Test', with encoded data, so I need to add a new field to the generated model, lets say 'Test Decoded', and use a some method from my custom assembly to decode the data in the expression designer. I don't see how to use a method from my added custom assembly in the expression designer, also I was browsing Google many hours without an result. Also Data - > Add New Datasources -> Object throws an exception in the VS2005 (Report Model Project). Maybe someone can help me. Thanks

HI,Maryan:

Not possible as far as i know. The Report Model used for the Report Builder to generate ad-hoc report.

I don't think you could use custom assembly in the Report Model. I suggest the you to do the decode job in the SQL Server side.

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi Rex Lin, thank you for a reply. Exactly - I need to create a Models to enable my customers to create own specific reports by the Report Builder (nice feature of the SSRS 2005 from the first view). That is why we have selected this solution from Microsoft.

Actually this column is implementation of the Custom Fields of the some model, so in this column are decoded X custom columns, as result from the real column in the table - ColumnA, we need to create dynamic columns ColumnA1, ColumnA2, ColumnA3... based on the decoded content of the column ColumnA. So Model on the server side (SSRS) should contain all these dynamic columns (if some doesn't exist in the decoded column it will be Null). Unfortunaly we received this DB from the other team and we can't change the architecture of database, because there are real projects that already use it. It is possible to implement Custom Fields in the more accurate way, but it can't be a subject of discussion...

Ok, you wrote that it is not possible. But, do you know that if we will create Report Server Project, create some datasource based on a some query, then add Reference to the custom assembly, then we can add custom fields to the datasource and use methods from the custom assembly in the Expression and it works! It also requires to deploy (copy) custom assembly to the both folders in 'C:\Program Files\...', one for the Visual Studio and the next one for the SSRS 2005. So, the SSRS support it - it can execute a some set of the predefined functions in the Expression or to call a custom assembly without problems. So, why it doesn't work in the Report Model - I don't know. It is more - Report Model project contains context menu "New Assembly Reference..." in the solution explorer, also there is menu at the top Data->Add Datasource->Object Datasource (this is exactly what I need), but it always crashes after the first click on the Next button. I'm sure - this is a bug. An any report enginemust supports custom code - it is clear. Maybe development team already working on the Sql Server 2008, 2011... and do not have a time to complete existing services...

Please let me know if you have a some ideas, you have more detailed description of my problem now. Also maybe you are related to the development team and you can ask them directly ;) Please note, the database server uses Sql Server 2000, so we can not use a some features like Table-Valued Functions or Managed Code on the server side. Also a custom Data Processing Extension isn't good solution for me (theoretically it is possible to parse command query, and if it is related to the table with decoded column..., but I do not think it is good). Thank you again

|||

Also I have a new question. We moved our database to the SQL Server 2005. We can not use Table-Valued Function (TVF), because our origin table contains more than 200000 records, and TVF haven't access to the Select/Query Parameters (it can be much more powerful in this case), so to create a dynamic table with 200k records and then select 10-20 records for report will be really very bad idea. I created CLR based stored procedure and deployed it to the server, it works, but I don't know how to use it in the Report Model Project, because Data Source View asks only for tables and views... You suggested me to decode columns on the Server Side, my quesion is - how? I already have lost one week and I'm trying to to break an impasse.

|||

Ok, I found solution by myself. As usually it is not possible to receive an answer for non standard/simple question on these forums, even from Microsoft guys. If someone have the same problem - my solution is based on the one CLR based Table-Valued Function (TVF) that accept one parameter (ID of the row) and do all required custom logic, one additional Transact-SQL TVF that use first TVF and feature of the SQL server '... CROSS APPLY ...', actually it selects ID from the real DB table and pass it as the input parameter to the first TVF through CROSS APPLY, and one DB View that selects data from the second Transac-SQL TVF. In the model designer I used this view - it works ;)

Report model

Hi,

In my database I have a table with user rights. For example:

User 1 may see region 1

User 2 may see region 2 and 3

Is it possible to use this in my report model. When user 1 makes a report with the report builder he must not be able to see region 2 and 3.

thanks in advance

You can use the SecurityFilters property on the corresponding model entity to grant permission to specific rows. In addition to the online documentation, look for a post on my blog in the near future for more details on the many options for securing the data exposed by a report model.