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 and Security Filter

I have a single database which will be shared by many customers.
So the idea was to design a "Report Model" and use Security Filters to make
sure that customers only see data belonging to them. Each tabel has a field
called ClientID.
How can one design a single report model which asures that each customer
only sees those records, belonging to him.
br, MichaelMaybe I shoul be more specific:
Is the concept of security filters the right way to go?
Is it better to design a special Report Model for each customer, where the
entities are based on queries?
thx, for any help
"Michael Schwabl" wrote:
> I have a single database which will be shared by many customers.
> So the idea was to design a "Report Model" and use Security Filters to make
> sure that customers only see data belonging to them. Each tabel has a field
> called ClientID.
> How can one design a single report model which asures that each customer
> only sees those records, belonging to him.
> br, Michael
>

Report Layout Troubles

I have created a tabular report that has four columns. Using the ruler at the top of the design page I have placed all columns inside my margins. When I view this report or print it the last column spills over to another page. I have designed this report to have portrait orientation. Is this an issue or am I doing something wrong. Does the ruler at the top signify a location on the page? Any help or ideas is greatly appreciated.
Thanks
-JWTake a look at my blog entry: http://blogs.msdn.com/bwelcker/archive/2005/08/19/454043.aspx. It covers logic and physical pagination rules.

Report Layout Troubles

I have created a tabular report that has four columns. Using the ruler at the top of the design page I have placed all columns inside my margins. When I view this report or print it the last column spills over to another page. I have designed this report to have portrait orientation. Is this an issue or am I doing something wrong. Does the ruler at the top signify a location on the page? Any help or ideas is greatly appreciated.
Thanks
-JWTake a look at my blog entry: http://blogs.msdn.com/bwelcker/archive/2005/08/19/454043.aspx. It covers logic and physical pagination rules.

Report Headers vs Page Headers

Hi, this is probably a simple question but I just can't figure it out. I'm using SQL Server Reporting Services 2005 sp1.

I'm trying to design a report that displays a particular embedded image on the first page, and then a different one on every subsequent page. I.e full, fancy company logo on the first page, and a trimmed down version on every other. In Crystal, I would have simply used the Report Header and Page Header sections to achieve this - easy.

However, in SQL Reporting Services, there is only a Page Header section - therefore whatever I place in the Page Header shows on every page. Now, I know how to stop it displaying on the first page, but I don't know how to display the other image instead.

I read in a support forum that it is possible to do this by "placing report items above or below your data regions" but I can't get it to work.

Grateful for any assistance.

Matt

Hello Matt,

Here's what I did to accomplish this task.

In your 'Page Header' section, place your trimmed down image (you want to display on all pages but the first), and set the PrintOnFirstPage property to False. Then, in the 'Body' section, move everything down enough to place your fancy company image at the top. Now, your fancy logo will show on the first page and none after, and your trimmed image will display on all other pages.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks very much, that was helpful. I think I'm nearly there - one last Q though! Now that the Page Header is set not to print on the first page it leaves a white blank space where it will appear on the other pages. Therefore, the image placed in the 'Body' section appears about 1/3 of the way down the first page. Do you know any way to make it appear at the top - at the same level as the Page Header one?

Thanks again

Matt

|||

Here’s another way you could do this:

Remove all the images you created earlier

Change PrintOnFirstPage back to true for the Page Header

In the BackgroudImage.Value property of the Page Header, type this: =IIf(Globals!PageNumber = 1, "Link to image that should show on Page 1", "Link to image that should show on the rest of the pages")

Resize your Page Header section to fit the images.

Unless your fancy and trimmed images are the same height, there will be blank space below the shorter of the two.Since you can’t dynamically change the size of a section, I think that’s as good as you’re going to get.

Hope this helps.

Jarret

|||Thanks - I'll give that a try. SSRS is great, but there are some features which Crystal has had for years which it is sadly lacking |||I'm having the same issue. I've found questions about this dating back to July of 2004, but no answers. Is it simply impossible to make a Page Header not print (and not take up space) on the first page?

Report Headers vs Page Headers

Hi, this is probably a simple question but I just can't figure it out. I'm using SQL Server Reporting Services 2005 sp1.

I'm trying to design a report that displays a particular embedded image on the first page, and then a different one on every subsequent page. I.e full, fancy company logo on the first page, and a trimmed down version on every other. In Crystal, I would have simply used the Report Header and Page Header sections to achieve this - easy.

However, in SQL Reporting Services, there is only a Page Header section - therefore whatever I place in the Page Header shows on every page. Now, I know how to stop it displaying on the first page, but I don't know how to display the other image instead.

I read in a support forum that it is possible to do this by "placing report items above or below your data regions" but I can't get it to work.

Grateful for any assistance.

Matt

Hello Matt,

Here's what I did to accomplish this task.

In your 'Page Header' section, place your trimmed down image (you want to display on all pages but the first), and set the PrintOnFirstPage property to False. Then, in the 'Body' section, move everything down enough to place your fancy company image at the top. Now, your fancy logo will show on the first page and none after, and your trimmed image will display on all other pages.

Hope this helps.

Jarret

|||

Hello Jarret,

Thanks very much, that was helpful. I think I'm nearly there - one last Q though! Now that the Page Header is set not to print on the first page it leaves a white blank space where it will appear on the other pages. Therefore, the image placed in the 'Body' section appears about 1/3 of the way down the first page. Do you know any way to make it appear at the top - at the same level as the Page Header one?

Thanks again

Matt

|||

Here’s another way you could do this:

Remove all the images you created earlier

Change PrintOnFirstPage back to true for the Page Header

In the BackgroudImage.Value property of the Page Header, type this: =IIf(Globals!PageNumber = 1, "Link to image that should show on Page 1", "Link to image that should show on the rest of the pages")

Resize your Page Header section to fit the images.

Unless your fancy and trimmed images are the same height, there will be blank space below the shorter of the two.Since you can’t dynamically change the size of a section, I think that’s as good as you’re going to get.

Hope this helps.

Jarret

|||Thanks - I'll give that a try. SSRS is great, but there are some features which Crystal has had for years which it is sadly lacking |||I'm having the same issue. I've found questions about this dating back to July of 2004, but no answers. Is it simply impossible to make a Page Header not print (and not take up space) on the first page?