Audit or QA report 1

[Fadius]

I have a report I am working on. It is has a primary group (GH1) on store name. Secondary (GH2) grouping under that of Location type.

I am making a report to display missing location types and data. What I am getting stuck on is how to have the report display missing location types for a store.

I have 8 different location types that each store shoud have. each site can have a possability of having multiple of 2 types. Primary and Billing. All other 7 they will have only 1 of each.

 

[Turkbear]

Hi,
What is the data structure you are using?
If just 1 table then you may have to create a lookup table with all Store and Location Types .
Then you link it ( Left-Outer maintable-->lookup by Store Name), so that a formula can be created the handles the missing types, like:

If IsNULL({LookupTable.LocType})Then Missing
Else
{LookupTable.LocType}

Use that formula to Group on for G2 instead of the main table's type field.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Fadius]

I am unable to do this as I do not have access to the database directly. I need to run a query a from within the application and then build the report based off that.

Is there some way to build a formula that would check the existing location types and then have it display which ones are missing?

This is a QA report for the managers of the staff to use to verify that data is being entered correctly and data integrity.

I have thought about assigning a value to each type and then have a summary field adding all of them up. If I give anough of a range in value, I am thinking I should be able to right a formula to deduce which ones are missing.

 

[lbass]

Please identify the tables you are working with, and identify fields like this: {table.field}--so we can see the relationship.

I was following you until you mention primary and billing. You said you have 8 location types--what are they? How do primary and billing relate to these? You also refer to them as "types".

-LB

 

[Fadius]

As I stated, this is a query which is run from within the application which creates a single table with all of the data and results. Table name is MissingData.

The results will display all of the location type is in a field called: MissingData.addresstype. In this I will have the following values: Practice Location(can have multiple), Billing Address(can have multiple), Manager(Only will have 1), etc with the others types having only one.

The first group (GH1) is MissingData.groupname as this is how they need to be sorted.

The second grouping is on MissingData.addresstype

What I need to do it have the report (can be a subreport) display the missing address types.

I am not having a problem display missing data elements for each addresstype that is in the query results table (MissingData), just the ability to display which addresstypes are missing as all 8 are required under the groupname (GH1).

I apologize if I am not explaining this very clearly. I appraciate the help I get here more than anyone knows.

 

[IanWaterman]

Sounds like a limitation of yor system. As Turkbear said in opening reply you need to include the addresstype table and connect this to your dataset to indicate which address types are missing.

But I gather you can not add tables to your main report.

Can you build a subreport which queries the address type table.

Using Shared arrays you will be able to build a list of addresstypes included in main report and then use that in a subreport to list missing address types.

Ian

 

[Fadius]

Thanks. I can create a secondary query of the ref table which contains the address type values and will do that. Was hoping there was a nother way around it.

 

[Fadius]

I now have a table that contains all the location types. One field in the table:

LocationType.addresstype

How do I link this with the main table?

LocationType.addresstype--->MissingData.addresstype

I can get it to show only address type that match, not the missing types.

 

[lbass]

You need to link using a left outer join FROM location type TO MissingData.

-LB

 

[Fadius]

For some reason I am not getting it to work. I group it on missingdata.groupname

then I group it on the formula

If IsNULL({locationtype.addresstype})Then "Missing"
Else
{locationtype.addresstype}


All I get back is the addresses that the groupname has, not the ones they are missing. I do have the left outer join from locationtype.addresstype---->missingdata.addresstype

 

[Turkbear]

Try reversing the link

TableWithMissingData --> addresstypeTable

Still Left Outer join only.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Fadius]

Same thing when I reverse it.

What I have is one table with all the possible addresstypes.

Another table that contains groupname, address type with the corrosponding address info. (This table is just the groups with the address types they. A missing addresstype will not show in this table.)

Left Outer join from locationtype.addresstype --> missingdata.addresstype

Have tried reversing this left outer join.

GH1 = missingdata.groupname
gh2 = formula as above

All I get for each Groupname is the address types they each have, not the ones they are missing.

 

[Turkbear]

Hi,
Is the link on the groupname field and no other?
Do you have ANY selection criteria that involves the addresstype table when linked in the order I posted?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Fadius]

The only place groupname appears is in the missingdata table. The locationtype table only has 1 field called addresstype and this is the list of all addresstypes that each group should have.

So I am linking the tables on the addresstype. And no selecvtion criteria.

 

[Turkbear]

Hi,
In order to associate addresstype with the correct groupname the addresstype table needs to have a groupname field and be linked by that.

Your original post indicated that Store Name was the first group ( is that the same as groupname?).

For each store you want all possible address types and if none are in the missingdata table, show Missing as the type.

To do that you need to link on Store name ( or groupname) in order for the correct associaltion to be made and for the If NULL formula to work.

revise your addresstype table to include a Store ( or groupname) for each addresstype ( same record, 2 fields)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

If you want your report to show the store name and then show ONLY the list of missing addresstypes, you could first insert only one group--on store--in the main report, and then create these formulas:

//{@reset} to be placed in the store group header:
whileprintingrecords;
shared stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the suppressed detail section:
whileprintingrecords;
shared stringvar x;
if not({locationtype.addresstype} in x) then
x := x + {locationtype.addresstype}+",";

Then insert a subreport in the store group footer that is not linked to the main report and that only shows {locationtype.addresstype} in the detail section. Still in the subreport, you can click on database->select distinct records so that only eight records appear. Then go into the section expert in the sub->details->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar x;
{locationtype.addresstype} in x

-LB

 

[IanWaterman]

What is your select statement?

If you are filtering on data from missingdata table you will over ride the Left Outer Join.

Change filter eg

Missingdata.field = x

should be

(isnull(Missingdata.field ) or Missingdata.field = x)

need to wrap in () so that it can be included with other conditions.

Ian

 

[Fadius]

lbass, that worked great. Than you so very much. This one gave me such a headache.