When you name your folders and fields in the Catalog.
Put the name of the folder in brackets after the name of the
field.
i.e. The folder name is Invoice Information. The field names are Invoice Date
and Invoice Number.
Invoice Date (Invoice Details)
Invoice ID (Invoice Details)
This helps you to locate the correct field when analysing later. It also helps confusion if fields have the same name, but are in a different folder.
i.e.
Invoice Date (Order Details)
Invoice ID (Order Details)
If you don't have a DBA or Schema for your database and you need to find fields fast
Make a content overview and convert to Word then use the find facility to find the field you are looking for quickly.
It may be worth creating a catalog with all fields from the whole database and create a content overview from that first.
Sometimes users want to export their reports to Excel and this can present problems. When writing reports to export to Excel remember you will lose your formatting. Also the data exports in Query order not in report column order. The most common problem here is with duplications, caused by incorrect associations which can cause real headaches..
Some solutions to make it easier.
Make sure eliminate duplicate rows is checked.
Make sure the report is written to return a unique line and make sure the
value is at the lowest level of this uniqueness in your associations. Associating
at a higher level will cause problems.
In this example invoice 12345 has two lines 1 and 2.
line 1 has a value of 70 and line 2 a value of 30 therefore the total value
of invoice 12345 is 100
If the value is associated with the invoice number and you include data from line 1 and line 2 in your report you will export to Excel
12345 line 1 100
12345 line 2 100
This is obviously incorrect. The value has been associated incorrectly in wants to be associated with the line number to return
12345 line 1 70
12345 line 2 30
You can then total up in excel to the correct sum value of 100.
Wherever possible keep your query table in hierarchical order. Highest levels at the top multiple levels below, put calculations next and then finally summaries. This will help with future analysis and strangely enough speed up the query time for your report.
Putting your filter in hierarchical level can also speed up your query time.
I have found this housecleaning has really paid dividends especially when you are using existing reports as templates for others.
Cascading prompts are really useful in helping your users get to their information really quickly.
Say you have a database with millions of names.
Create a report that takes the first letter of the name.
Create a second report that takes the first two letters of the name and filter
it on the first report.
Create a third report that takes the first three letters of the name and filter
it on the second report
finally create a report that gives the full name and filter it on the third
report.
Filtering a report against this final report will give you the ability to cascade down the initial letters of the name until you get a small picklist of the names you want. This is especially useful if you have different spellings of the name use the in function to give you more flexibility.
Remember you picklist is limited to 99 rows of data. In this
example your first picklist cannot exceed 26 and it will eliminate roughly
90% of your names in the first pass. As this is a geometric progression each
step will eliminate 90% of the remaining names until you get down to a suitable
picklist. Really large databases may need 4 or even 5 cascades.
Remember to use the UPPER function to flatten out any data entry inconsistencies.
A really powerful use of cascades is to jump across categories once you've
got down to a certain level. i.e. once you have selected your Client name
i.e. SMITH you can then cascade down subdivisions of that client, and the
prompts will only return data that is in the database saving wasted time with
typo errors.
Reports will link to the Catalog via the mapped drive
if you navigate to them that way. However if you can navigate to the Catalog
via Network Neighbourhood this is better as other users can use your report
who don't have their PC's mapped in the same way as you.
To convert reports already written change the file path
in the Page Set up to
\\name of computer\file path\file path\report.imr.
Remember to put the \\ at the front.( This replaces the mapped path
s:\file path\file path\report.imr. where you have mapped
the computer name to your s drive.
Put a buffer Catalog between you and the end users. Create an administration catalog and make this distributed then make the distributed catalog a distributed catalog.
Admin Catalog to Buffer Catalog to Personal Copy.
The reason for this is that users, if they open a report attached to the buffer catalog will lock off the Catalog until they have logged on and saved their personal Catalog. Any user who leaves the login prompt open jams the Catalog for other users until they have logged in. The buffer Catalog allows you to make changes to the Admin Catalog without being restricted by these jams.
Use PowerPlay cubes for Summaries and Impromptu for detail.
Too much detail in Cubes makes them cumbersome to navigate and find what you want. It also takes longer for the cube to be refreshed.
Summary reports in Impromptu may be too simplistic and may disguise errors and they do not have the extra facility to slice, dice. and drill through.
Use the cube to analyse your summary data and when you spot something you want to investigate further use the conditions to run detailed Impromptu reports.
 |
|
 |
|
 |
|
 |
|