Wednesday, March 28, 2012
Report Design Question
I am developing a Work Order report similar to the "Adventure Works Cycles"
sample provided with Reporting Services. What I would like to do is repeat
the "Header" information on each page. I am looking at the samples for an
example on how to do this but no luck. I even reduced the height of the
"Adventure Works Cycles" sample report to see if it's Header data is carried
over and it isn't.
How do I get my "Header" data to carry over to the next page. I tried adding
it to the page Header but it doesn't like field data in it.
Thanks in advance,
YoshiLook at the Header attibute for the Table. It can be set to repeat on each
page.
--
| From: "Yoshi" <yoshi@.home.com>
| Subject: Report Design Question
| Date: Wed, 17 Nov 2004 10:06:03 -0600
| Lines: 17
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#YmgY9LzEHA.1292@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: cs24174142-174.satx.rr.com 24.174.142.174
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10|||My header data is not in the table as I am confined to the column width. I
placed the header data in the body of the report above the table. I used the
"Adventure Works Cycles" sample provided by Microsoft, as my guide.
My problem is when the detail lines carry over to the next page. I would
like page 2 to have the same header data that page 1 has. In fact, I want
subsequent pages to look like page 1 with the header data.
Make sense?
Thanks,
Yoshi
""Brad Syputa - MS"" <bradsy@.Online.Microsoft.com> wrote in message
news:wbVFvhNzEHA.2916@.cpmsftngxa10.phx.gbl...
> Look at the Header attibute for the Table. It can be set to repeat on each
> page.
> --
> | From: "Yoshi" <yoshi@.home.com>
> | Subject: Report Design Question
> | Date: Wed, 17 Nov 2004 10:06:03 -0600
> | Lines: 17
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#YmgY9LzEHA.1292@.TK2MSFTNGP10.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: cs24174142-174.satx.rr.com 24.174.142.174
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10
> phx.gbl
> | Xref: cpmsftngxa10.phx.gbl
> microsoft.public.sqlserver.reportingsvcs:35173
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi everyone!
> |
> | I am developing a Work Order report similar to the "Adventure Works
> Cycles"
> | sample provided with Reporting Services. What I would like to do is
> repeat
> | the "Header" information on each page. I am looking at the samples for
> an
> | example on how to do this but no luck. I even reduced the height of the
> | "Adventure Works Cycles" sample report to see if it's Header data is
> carried
> | over and it isn't.
> |
> | How do I get my "Header" data to carry over to the next page. I tried
> adding
> | it to the page Header but it doesn't like field data in it.
> |
> | Thanks in advance,
> |
> | Yoshi
> |
> |
> |
>|||Place the header in a table group - go to Edit Group and select the
"Repeat Header" option.|||Unfortunately, my header will not work in the "Table" header row. The
columns do not allow for the correct width.
Thanks for the response.
"papaboom" <smith.jamie@.gmail.com> wrote in message
news:1100725306.172333.164530@.z14g2000cwz.googlegroups.com...
> Place the header in a table group - go to Edit Group and select the
> "Repeat Header" option.
>|||Techinically - Yoshi - you are not confined to keeping your header
outside the table. If this is the problem - then place the header back
in the header (or group header) section of the table. If for example
this is a single header in the header section of the table then you can
put the header in the left-most cell then select the adjacent cells in
the header row (by pressing Ctrl-click.) Once all the cells from that
row are selected - right-click the slected cells and select the "Merge
Cells" option. This will merger the cels an that row only - and will
leave the Details section rows intact.
Lastly if this is a stand alone text-object over a table - then right
click the textbox you want to repeat and select Properties. Then select
the Advanced button at the bottom left corner - on the General tab
there should be an option selected called "Repeat report item..." and
beneath it is a drop-down - select the table you wish the object to
repoeat with in each page and you should be in business. If your table
is named Table1 - then select Table1.
Let us know how it turns out.|||I really appreciate everyone's input. Thank you!
Hi. I tried the 2nd suggestion where I selected my stand alone text-object
that is over my table.
I right clicked the textbox and selected Properties. I then clicked the the
Advanced button at the bottom left corner on the General tab. I then clicked
the "Repeat report item with data region on every page" and selected the
"table 1" Data region in the drop down.
I run the report and it doesn't repeat the header textbox on the subsequent
pages. I believe this is all I need to do. This should repeat the text box
for the data region when it spans multiple pages, correct?
Basically, I want my header text boxes to repeat on each page. Just as if I
placed then in the Group header row in the table. Unfortunately, the header
data is formatted in such a way that the table does not lend itself for this
design.
Thanks again!
Yosh
"papaboom" <smith.jamie@.gmail.com> wrote in message
news:1100731569.214512.82360@.z14g2000cwz.googlegroups.com...
> Techinically - Yoshi - you are not confined to keeping your header
> outside the table. If this is the problem - then place the header back
> in the header (or group header) section of the table. If for example
> this is a single header in the header section of the table then you can
> put the header in the left-most cell then select the adjacent cells in
> the header row (by pressing Ctrl-click.) Once all the cells from that
> row are selected - right-click the slected cells and select the "Merge
> Cells" option. This will merger the cels an that row only - and will
> leave the Details section rows intact.
> Lastly if this is a stand alone text-object over a table - then right
> click the textbox you want to repeat and select Properties. Then select
> the Advanced button at the bottom left corner - on the General tab
> there should be an option selected called "Repeat report item..." and
> beneath it is a drop-down - select the table you wish the object to
> repoeat with in each page and you should be in business. If your table
> is named Table1 - then select Table1.
> Let us know how it turns out.
>|||The textbox will only repeat each time the table repeats - so if it's
one table spanning multiple pages then you may be SOL with option 2.
How is the header formatted in such a way that it cannot be displayed
in a table header? Unfortunately I think you are going to have to put
that header in the table if you want to see it repeat across multiple
pages - if option 2 didnt work.|||It seems that if the table itself repeats then the text box will repeat
- but since it is a single table spanning mulitple pages that it will
not repeat. What is special about the header that it can't be placed in
the table itself? Because it seems at this point that you will have to
go with option A (header in the table) if you want this to work.|||Option 2 was a great suggestion but your right, it's not working because of
what you said.
Option 1 will have to do. I will need to change the format of the header so
that it works and looks nice. I hope the client says it's ok.
Thank you for being so patient and persistant with my questions/posts. I
really appreciate it!
Take care,
Yosh
"papaboom" <smith.jamie@.gmail.com> wrote in message
news:1100733672.095005.163320@.z14g2000cwz.googlegroups.com...
> The textbox will only repeat each time the table repeats - so if it's
> one table spanning multiple pages then you may be SOL with option 2.
> How is the header formatted in such a way that it cannot be displayed
> in a table header? Unfortunately I think you are going to have to put
> that header in the table if you want to see it repeat across multiple
> pages - if option 2 didnt work.
>|||I believe part of the problem is the original "report" was done in Excel. I
come from the Crystal Report side of development where I could put data
fields pretty much anywhere using "free-form" placement.
I am not used to inserting column after column and merging cells to get a
very wide data field to look right in a table? That make sense?
Learning curve I guess. =0}
"papaboom" <smith.jamie@.gmail.com> wrote in message
news:1100734006.823128.176120@.z14g2000cwz.googlegroups.com...
> It seems that if the table itself repeats then the text box will repeat
> - but since it is a single table spanning mulitple pages that it will
> not repeat. What is special about the header that it can't be placed in
> the table itself? Because it seems at this point that you will have to
> go with option A (header in the table) if you want this to work.
>|||Absolutely - I come from Crystal Reports side also. I have just started
with this Reporting Services stuff about a month ago. The learning
curve can be steep here. All I can say is get ready for some more pain.
This table header business you will find to be the least of your
problems.|||Hey... Thanks Papaboom! =0}
Reporting Services makes me appreciate Crystal alot more. However, I do see
how powerful Reporting Services can become. I am very excited about the ease
of use.
Maybe you can answer this question. I have a page footer that has 1 textbox
with today's date. When I export the report, the page footer is left out.
It's not exported.
I don't know why this is happening.
Thanks so much!
Y
"papaboom" <smith.jamie@.gmail.com> wrote in message
news:1100795491.349175.78160@.f14g2000cwb.googlegroups.com...
> Absolutely - I come from Crystal Reports side also. I have just started
> with this Reporting Services stuff about a month ago. The learning
> curve can be steep here. All I can say is get ready for some more pain.
> This table header business you will find to be the least of your
> problems.
>|||Sorry Yosh - on this one I havent the slightest. The most I've done
with exporting is export a couple of reports to PDF.
Friday, March 23, 2012
Report Builder, SSAS, and Many-to-Many Relationships
Does Report Builder handle many-to-many relationships within an SSAS cube correctly? I can't seem to get it to. Using Adventure Works, here's an example of the problem I'm seeing.
- Create a new report in RB, opening the Adventure Works DW model
- Select one of the perspectives that includes the Sales Reason dimension (like Direct Sales)
- Select the Sales Reason entity in the Entities list and put the Sales Reason Type attribute into the report (making Sales Reason the primary entity)
- The Entities list will be reduced to show the Adventure Works Sales Reasons relationship (which maps to that underlying measure group)
- If you select the Adventure Works Sales Reasons relationship, you’ll see the Internet Sales Order Details entity below it
- If you select the Sales Order Details entity, however, you’ll only see the attributes that are in that dimension; you won’t see any of the measures that exist in the measure groups that are related to it
- Based on that fact, you can’t get to any of the measures in the cube (since the Sales Reason dimension is related to the other measure groups only through M-to-M relationships)
Of course, it may be that I just don't understand how the auto-generated report model for the SSAS cube is exposing the relationships and the entities that correspond to the Sales Reason dimension, the Sales Reasons measure group (the intermediate measure group for the M-to-M relationships to the other measure groups), etc.
Anyone seen this before or know that this is defined behavior? Seems that for any SSAS cube that includes M-to-M relationships, Report Builder just won't work...
Thanks!
Dave Fackler
Unfortunately, the SMDL model in the Report Builder sometimes gets in the way when SSAS is used as a data source. To get around similar limitations, I added calculated members in the cube which mapped to the original measures and I assigned them to the first measure group. The net result is that all measures were mapped to only one measure group. Then, I had to manually change the SMDL model to hide the measure groups/measures I don't need.|||
Hi Teo,
I understand most of the limitations with RB against an SSAS-based SMDL model. However, I've never tested trying to build a report that includes dimensions/measure groups that are related via a many-to-many relationship. The fact that this doesn't seem to work is troubling, so I'm trying to confirm that it is indeed an issue (or bug?) with RB and not just me doing something silly... Have you ever tried to create a report using RB that hits the Sales Reason dimension in the AW cube? If not, give it a try and let me know what your experience is...
Thanks!
Dave F.
|||The fact that this doesn't seem to work is troubling, so I'm trying to confirm that it is indeed an issue (or bug?) with RB and not just me doing something silly.
Not the latter for sure . M-M relationships is not the only case. The same thing happens if you have a multi-grain relationship, e.g. Sales Target in the Adventure Works. Then the model drops the Date relationship so you can't slice by date. I'd recommend you file a bug report with product services for all Report Builder issues that you encounter (I've done my fair share doing so). The more people complain about the SSAS integration scenario the more likely is to get fixed/improved. Meanwhile, consider opening the SMDL model and adding the dropped relationships manually. This isn't a supported scenario but it will probably get you going.