I have problem when formatting a crosstab report. Attached is the .rep file. My crosstab have to show all (eventhough there are no data associated to it). So, I’ve made an object query with to act like master table. (Table 1)
Then I do another query to get the actual information. (Table 2).
Then I link these 2 queries based on object and create the table format wanted (Table 3) but there is an empty/additional column which exists. When I filter on the column, it will reduce my but I need to have a list of all . Is there any other way to filter this and yet shows all the ? Any formula, etc?
Thanks in advance
Shirley
Email : sang@csam.com.my or shirleyang@maxis.net.my
Instead of doing two queries, you may want to use a union query. In the first query, select all objects you need for your crosstab. In the second unioned query, select the resort object and either create user-defined objects or add new objects to your universe that are defined as blanks, zeroes, and dates to match up the data types of your objects in the first query. A union query has to have the same number and data type of columns in each SELECT statement. When you run your report, you will get at least one record back for every resort, since the second query doesn’t reference any table other than where the resorts are defined. You can get creative with your crosstab formatting to handle the dummy rows. Bringing all the records back in one data provider makes the formatting a lot easier.
I use union queries frequently for complex reports, and I have found it useful to define a class of objects comprised of blanks, zeroes, and dates (normally based off the system date) in the universes I work with. These objects don’t parse in Designer since they do not reference a table, but work fine in queries as long as at least one table-based object is selected.
On Thu, 15 Jun 2000 17:10:37 +0800, Shirley Ang sang@CSAM.COM.MY wrote:
I have problem when formatting a crosstab report. Attached is the .rep
file.
My crosstab have to show all (eventhough there are no data associated to it). So, I’ve made an object query with to act like master table. (Table 1)…
Thank you for the reply. I have tried using union but I am not able get to the format that is needed. My crosstab should not show the dummy figures but I am not able to filter it out. Attached is the sample I’ve tried with Union. When I filter the dummy figures, the corresponding went missing. I hope that you can look into this.
Thanks
Shirley
Email : sang@csam.com.my or shirleyang@maxis.net.my
Another trick is to combine the dummy row with a value that will normally be on your report, such as =If = " " Then “France” Else . The dummy row will then be aggregated in with the real data for France, which shouldn’t affect the results if the dummy measure value is zero.