theclosh

I have a large report that is normally printed as a PDF. It has a coverpage, disclaimer and margins. Several of the subreports take up more than one page. Recently it has been asked of me to provide an Excel version of this report but to not create a separate report if possible. I have done this before but with not very clean results. I need to be able to have the coverpage, disclaimer and margins go away while allowing the page size to be larger. The first three are easy. The last I am completely stuck on. What I'd like to do is have subreports within subreports so that I can have a container report as my main report with two subreports - one for the PDF and one for the Excel. The subreports for each would be linked accordingly. What I need is a way to be able to keep the page height for the PDF as 792 pixels while being able to have the Excel page height be much larger. Is there a way I can do this? Am I over thinking this? The main report height, in my experience, controls the height of the entire report. So if I make it the PDF size then there will be spaces in my Excel (which I could clean up except that I also plan on having custom locations for the one sheet per page property). If I make it the Excel size the PDF will be much much larger than standard Portrait (or Landscape) layout. Any thoughts would be appreciated. Thanks.

You cannot go backwards with the sharing. If the first subreport literally comes first on the report then it is evaluated prior to the second subreport. If there is a reason it is first and can't be moved after the second then your only real option is to provide another subreport before the first that is only used to run the same query as the second and is blank otherwise. Then you can pass the values from the new subreport to the main and then into the previously labeled first subreport. The second will only be used to display the data that was also queried in the new subreport.

Create a subreport and setup the parameters to pass into it. Make sure the subreport is setup as high on the report bands as possible to be sure it runs before the bands to be suppressed are generated. Have the subreport do whatever calculations you are needing. Pass this information to a variable on the subreport. Create a matching variable that is blank on the main report. On the main report under the subreport link properties there is an option to choose your return variables. Set this up with the name of the subreport variable and the name of the main report variable it will apply the value to. Base your Print When Expression on the variable value.

I'm not sure why your field is adding a space and making the end drop off. However, I may have a better way if you are able to do this. We use Oracle and run multiselect parameters to provide data to the report. The parameter is provided to the query as a comma delimited string. We use a common table expression with CONNECT BY LEVEL to split the value into their on rows and then access that data from the main query. Doing this you would not have to do the split on the field. Each value would be its own row and could be treated as such. WITH USER_LIST AS (SELECT NVL(TOKEN, '0') FROM (SELECT TRIM(SUBSTR(USER_LIST, INSTR(USER_LIST, ',', 1, LEVEL) + 1, INSTR(USER_LIST, ',', 1, LEVEL + 1) - INSTR (USER_LIST, ',', 1, LEVEL) - 1 ) ) AS TOKEN FROM (SELECT ','|| '$P!{USER_NAME}' ||',' USER_LIST FROM DUAL) CONNECT BY LEVEL <= LENGTH('$P!{USER_NAME}') - LENGTH (REPLACE ('$P!{USER_NAME}', ',', '') ) + 1 ) ) SELECT * FROM USER_LIST

Under Group properties there is a checkbox labeled "Reset page number".

What you really will need in that situation is a subreport.

If the years are variable then, yes, a crosstab is your best bet. You would want Num and Module in the rows and Year in the columns. It will try to order by Num though so if you want it to order by Module you'd need another row in from of Num that you add in and then hide by removing the field and minimizing the cells. What you have IS doable though.

The format you provided looks like it would be better suited for a table rather than crosstab.

I do not think it would be usable the way you want it to be usable. That blank space is made up of three different Group Headers. If you put any info there it will be repeated every time the group cycles. If not static the data would still be dependant of the Group Expression. If, however, all that information were in a subreport against on band you could theoretically put another subreport next to it or other data next to it and use the space.

To further answer your question you can also create a variable that would add up the values as you go.

Are you using the same dataset for the line chart as you are the crosstab? If not, is the query the same between the two?

Have you considered using a Join? Based on the need for the counts to be added up I would think that would be better than a UNION. Select T1.NWID, T1.Name, (T1.Amount + T2.CWAmount) FinalAmount From (Select NWID, Name, Amount From table1) T1 Inner Join (Select NWID, Name, CWAmount From table2) T2 On T1.NWID = T2.NWID

First, let me compliment you on your design. Very clean and nice to look at. I would suggest creating a new Group over the first that is based on a static value shared by all the data. That way it will pass through this group once but through the next Group that you already have defined every time you would expect. The Header does not need a band. The Group footer would contain your Grand Total information. Use the Keep Together option on the Group. I can't guarantee that will work but I have found in some of my reports that if you use Keep Together on the higher Group it applies to everything within the Group including (sub)Groups. Good luck!

Unless you want your lists to return multiple times (once for each row of the main query) I wouldn't put your lists in a detail line. Plus, yes, putting them both on the same band is probably not a good idea. I would suggest using a group footer or the summary band. Plus it appears you may actually want to use the table tool rather than list based on the data format you appear to want.

The snippet provided is using the Field "transcript" and passing it to a Java Class that runs specific methods to return data. Without the class file I can't tell you more.