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 page hanging in browser

Hi all,
using SQL2000 sp4
How can I see why a report is not loading in browser.
Just hangs with hour glass. not even the paremeter input fields are loading?
I haven't even clicked view report?
other reports are fine.
thanks,
gvOn Apr 26, 1:37 pm, "gv" <viator.ge...@.gmail.com> wrote:
> Hi all,
> using SQL2000 sp4
> How can I see why a report is not loading in browser.
> Just hangs with hour glass. not even the paremeter input fields are loading?
> I haven't even clicked view report?
> other reports are fine.
> thanks,
> gv
Do you have default(s) on your report parameter(s) set to query? It
sounds like the report parameter'(s) dataset/query is hanging. How
complex is the query for the report parameters? Also, make sure that
you do not have Auto-refresh set on the report (via Layout tab ->
Report drop-down -> Report Properties... -> General tab) or set too
frequently. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks! I will look at what you suggested.
gv
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1177639744.633440.121190@.n35g2000prd.googlegroups.com...
> On Apr 26, 1:37 pm, "gv" <viator.ge...@.gmail.com> wrote:
>> Hi all,
>> using SQL2000 sp4
>> How can I see why a report is not loading in browser.
>> Just hangs with hour glass. not even the paremeter input fields are
>> loading?
>> I haven't even clicked view report?
>> other reports are fine.
>> thanks,
>> gv
>
> Do you have default(s) on your report parameter(s) set to query? It
> sounds like the report parameter'(s) dataset/query is hanging. How
> complex is the query for the report parameters? Also, make sure that
> you do not have Auto-refresh set on the report (via Layout tab ->
> Report drop-down -> Report Properties... -> General tab) or set too
> frequently. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

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 Aggregates

Is there anyway to do a simple line chart that compares 5 fields? For
example, X axis shows fieldA, fieldB, fieldC, etc where Y axis is the value.
I'm having such a hard time with this, the report keeps wanting to take the
Sum or Count (aggregate) of fields. The values don't need summed, they're
already contained in the fields. Please help.
Thanks,
RyanHello Ryan,
I would like to know how you use the SQL Statement to get the dataset.
Also, please give me some sample data.
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.|||2 Tables
table_items
item_id (PK)
item_name
table_values
value_id (PK)
year
actual_amount
item_id (FK)
SQL statement used for the dataset (for years 2002-2004 for example)
(the embedded selects are because some items will not have a row for years,
this puts a 0 value in for those years)
SELECT A.item_id, A.item_name, ISNULL
((SELECT actual_amount
FROM dbo.amounts
WHERE (item_id = A.id) AND (year = 2002)),
0) AS [2002 Actual], ISNULL
((SELECT actual_amount
FROM dbo.amounts AS amounts_5
WHERE (item_id = A.id) AND (year = 2003)),
0) AS [2003 Actual], ISNULL
.....
ETC.
The SQL resultset looks like this:
item_id
item_name
2002 Actual
2003 Actual
2004 Actual
Each item (detail row) is displayed on it's own page. I want each item to
have it's own graph. Just a simple line graph in this example would display
on the X-axis 2002 Actual, 2003 Actual, and 2004 Actual.
Probably too much info for a simple problem but there it is!
Thanks!
Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:HQcIY19CIHA.360@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> I would like to know how you use the SQL Statement to get the dataset.
> Also, please give me some sample data.
> 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 Ryan,
I suggest you just use another dataset for the chart.
Since the chart is based on the dataset's column and you could not put
multiple columns into the X Axis, you need to use another dataset like this:
item_id, item_name, Actual value, Year.
So that when you put the Year into the X axis, you could get the result you
want.
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.|||Hmmm, this looks good. The problem though is that the last year in the
chart is a different field. For example:
[2006 Actual], [2007 Actual], [2008 Budget].
The last year draws from a Budget field and not the Actual field.
Looks like this may not be doable. We did have the chart in Microsoft
Access. I may have them use that or see if there's any way to accomplish
this in Crystal Reports (I need something I can build into my VS project).
Thanks,
Ryan
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:7$yFzgvDIHA.5256@.TK2MSFTNGHUB02.phx.gbl...
> Hello Ryan,
> I suggest you just use another dataset for the chart.
> Since the chart is based on the dataset's column and you could not put
> multiple columns into the X Axis, you need to use another dataset like
> this:
> item_id, item_name, Actual value, Year.
> So that when you put the Year into the X axis, you could get the result
> you
> want.
> 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 Ryan,
If you are using VS 2005, you could use the ReportViewer Control to
complish this report in the WinForm or Web Form.
Reporting Services and ReportViewer Controls in Visual Studio
http://msdn2.microsoft.com/en-us/library/ms345248.aspx
Hope this helps.
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.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
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 name (XYZ.rpt) to be printed on the report without the path where its sav

Hi,
Can anyone tell me how to print the name of the report (XYZ.rpt) without the path. In special fields there is a field called "File Path and Name". I need to print only the name and not the path of the report.
If anyone has done this before please let me know.
Thanks,Soething like

select reverse(substring(reverse(path),1,charindex('/',reverse(path),1)-1))

Report moving between fields

I'm having a strange issue.
When moving between parameter fields when viewing a report, it refreshes the
report every time I move from field to field. Whether I'm moving with the
tab button or just clicking in the next field.
The refresh option is off.
Anyone have any ideas?Here's an update...
This only occurs when using a default field. I'm using the global constant
"user id". It is the last parameter in the stored procedure and the last
parameter entry field in my report.
If I move this field to the first parameter entry field, it works just fine.
When it's in any other position, it tries to run the report each time I move
to a new field.
This is extremely frustrating.
"Scott M" wrote:
> I'm having a strange issue.
> When moving between parameter fields when viewing a report, it refreshes the
> report every time I move from field to field. Whether I'm moving with the
> tab button or just clicking in the next field.
> The refresh option is off.
> Anyone have any ideas?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 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 Layout

I'm working on a report that could potentially require 13 or so datasets in
order to get the specific fields that I need for each section of the report.
The SQL table is structured as follows:
select
year,
branch,
measure,
jan,
feb,
mar
from tblBPM_BP
I was trying to bring all of the measures in on one dataset, but I need to
be able to further filter the data for each section of the report (i.e. Sales
& headcount). Is there a better technique than using a dataset for each
section of the report?What about using filters.
"DJONES" wrote:
> I'm working on a report that could potentially require 13 or so datasets in
> order to get the specific fields that I need for each section of the report.
> The SQL table is structured as follows:
> select
> year,
> branch,
> measure,
> jan,
> feb,
> mar
> from tblBPM_BP
> I was trying to bring all of the measures in on one dataset, but I need to
> be able to further filter the data for each section of the report (i.e. Sales
> & headcount). Is there a better technique than using a dataset for each
> section of the report?
>|||Can I filter out data in a table w/i the report designer?
"Victor" wrote:
> What about using filters.
>
> "DJONES" wrote:
> > I'm working on a report that could potentially require 13 or so datasets in
> > order to get the specific fields that I need for each section of the report.
> > The SQL table is structured as follows:
> >
> > select
> > year,
> > branch,
> > measure,
> > jan,
> > feb,
> > mar
> > from tblBPM_BP
> >
> > I was trying to bring all of the measures in on one dataset, but I need to
> > be able to further filter the data for each section of the report (i.e. Sales
> > & headcount). Is there a better technique than using a dataset for each
> > section of the report?
> >|||Table properties -> Filters
"DJONES" wrote:
> Can I filter out data in a table w/i the report designer?
> "Victor" wrote:
> > What about using filters.
> >
> >
> > "DJONES" wrote:
> >
> > > I'm working on a report that could potentially require 13 or so datasets in
> > > order to get the specific fields that I need for each section of the report.
> > > The SQL table is structured as follows:
> > >
> > > select
> > > year,
> > > branch,
> > > measure,
> > > jan,
> > > feb,
> > > mar
> > > from tblBPM_BP
> > >
> > > I was trying to bring all of the measures in on one dataset, but I need to
> > > be able to further filter the data for each section of the report (i.e. Sales
> > > & headcount). Is there a better technique than using a dataset for each
> > > section of the report?
> > >|||That will do the trick. Thanks!
"Victor" wrote:
> Table properties -> Filters
> "DJONES" wrote:
> > Can I filter out data in a table w/i the report designer?
> >
> > "Victor" wrote:
> >
> > > What about using filters.
> > >
> > >
> > > "DJONES" wrote:
> > >
> > > > I'm working on a report that could potentially require 13 or so datasets in
> > > > order to get the specific fields that I need for each section of the report.
> > > > The SQL table is structured as follows:
> > > >
> > > > select
> > > > year,
> > > > branch,
> > > > measure,
> > > > jan,
> > > > feb,
> > > > mar
> > > > from tblBPM_BP
> > > >
> > > > I was trying to bring all of the measures in on one dataset, but I need to
> > > > be able to further filter the data for each section of the report (i.e. Sales
> > > > & headcount). Is there a better technique than using a dataset for each
> > > > section of the report?
> > > >

Report Layout

I have a page with a List of about 12 fields with text labels.
Something like this:
Label1
Field1
Label2
Field2
Label3
Field3
Label4
Field4
IS there any way to scale this so that if, for example, Label3/Field3 are
empty, I can move Label4/Field up? Right now, it leaves a big blank line in
the report.
Any help on this would be greatly appreciated!
Chris KyleTry adding this in the visibility / hidden property, for each row pair
=IIF(IsNothing(Field!Field1.Value), True, False)
Kaisa M. Lindahl Lervik
"Chris Kye" <pixelboy@.yourdomain.com> wrote in message
news:2239d6e237054d6ea161b45bc23e60aa@.ureader.com...
>I have a page with a List of about 12 fields with text labels.
> Something like this:
> Label1
> Field1
> Label2
> Field2
> Label3
> Field3
> Label4
> Field4
> IS there any way to scale this so that if, for example, Label3/Field3 are
> empty, I can move Label4/Field up? Right now, it leaves a big blank line
> in
> the report.
> Any help on this would be greatly appreciated!
> Chris Kyle

Report item expression can only refer to fields within the current data set scope

I switched my reports datasource and made sure the new query outputs
the same fields. However i now get this error...
Report item expression can only refer to fields within the current
data set scope or, if inside an aggregate, the specified data set
scope
I've checked everywhere to confirm I'm pointing to the right dataset
and I am.On Oct 10, 9:09 pm, jobs <j...@.webdos.com> wrote:
> I switched my reports datasource and made sure the new query outputs
> the same fields. However i now get this error...
> Report item expression can only refer to fields within the current
> data set scope or, if inside an aggregate, the specified data set
> scope
> I've checked everywhere to confirm I'm pointing to the right dataset
> and I am.
You might want to select the Refresh icon in the Data view. Also, if
you are using a parameter in the dataset, you will want to make sure
that it is mapped correctly (via the Data view >> Edit Selected
Dataset [...] >> Parameters tab). Another thing to check is to make
sure that you did not accidentally misspell one of the dataset fields
since the previous dataset. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report Help..!

Hi All,

I have a set of "VB reports" binded to a wizard, where you can select fields which will appear on the report. Also you can change Group By and Order By Fields.

Initiallly this was designed and used for "A4" size papers. But now I need dynamically adjust the report for selected paper size. Anyone can guide me on this...?

I am hoping to use papers larger than "A4" only....

- Help meHow about designing seperate files for different sizes and display them accordingly?|||Dim Report As New dsrVoucher

Report.SetUserPaperSize 1400, 2200
Report.PaperSize = crPaperUser

then so on...

but.. size u 'll given is in pixel..
not in inches..cms..
well.. as i also suggest.. the better way to do so is to design different report..with different page setup...
crystal report have some very unfortunate and teasing issues.. while printing .. bcoz of printer based reporting system..

but very some time teasing....and always comfortable at any type of reporting...
neways
best of luck

Report header and footer size when using matrix

Hi,

Is there a way to dynamically make report header and footer fields change location (or size)? I have many matrix reports that grow in width and then the header and footers do not look good.

Another example is I have a line that is in the header...I would like this to grow to the width of the report, which again is not static on a matrix report.

Thanks.
I do not think dynamic sizing or relocating is a feature of SSRS 2005. If you explain your situation more explicitly then I might be able to offer a couple ideas for work-arounds.

Report gets refreshed automatically

Hi,

I created a report using SQL 2000 Reporting Services. I have 3 input fields viz., Start Date, End Date and Configuration Item. First 2 are textboxes and the last one is a dropdown. I also have a button 'View Report' clicking which the report page will be refreshed. When I deploy the Reports in report manager, when I give the Start Date then the End date, before I could select a value from the Dropdown, the report page is getting refreshed automatically before I click View Report button. i.e., the report gets refreshed on lifting the focus from the End date textbox. Why does this happen? Note that I have not set the Auto refresh property for this report.

The most likely reason the report is being refreshed is because the "Configuration Item" parameter has a dynamic value that may depend on the values of "Start Date" and/or "End Date". The report is refreshed as soon as the value for "End Date" changes to ensure that the valid values for "Configuration Item" are accurate.

For example, RS will assume that "Configuration Item" depends on "End Date" if:

"Configuration Item" is populated from a query that uses expressions in the query, filters, calculated fields, or query parameters|||

I also faced the same problem.

In my case, by mistake I set the Report Properties " AutoRefresh " as 1 insted of 0 in properties window on right hand side

so just check whether its 1 or 0

Report Format with a Group

Is there a way to have the data rows begin on the same row as the group fields. For example if I have a student with multiple tests/scores, I want the tests/score to begin on the same line as the students name.

You could place everything on a detail row...basically no grouping level. This will display the student name as a repeating element, however.

You can then add some conditional logic to the Hidden property to only display the first occurrence of that student name.

|||Do you have an example of the logic to hide all but the first occurance? The programming side of this is a little new to me.|||

Check out the HideDuplicates feature of TextBox: http://msdn2.microsoft.com/en-us/library/ms152916.aspx.

In ReportDesigner, right click on your textbox and check "Hide duplicates" in the Textbox Properties dialog.

Report Format with a Group

Is there a way to have the data rows begin on the same row as the group fields. For example if I have a student with multiple tests/scores, I want the tests/score to begin on the same line as the students name.

You could place everything on a detail row...basically no grouping level. This will display the student name as a repeating element, however.

You can then add some conditional logic to the Hidden property to only display the first occurrence of that student name.

|||Do you have an example of the logic to hide all but the first occurance? The programming side of this is a little new to me.|||

Check out the HideDuplicates feature of TextBox: http://msdn2.microsoft.com/en-us/library/ms152916.aspx.

In ReportDesigner, right click on your textbox and check "Hide duplicates" in the Textbox Properties dialog.