Reports: Including a percent and also a chart 1

[supmrio]

I have tried just about everything, in trying to show a
percent on the report. I try to create a calculated field,
but keep getting a "parameter" dialog box.
I imagine someone must have crossed this bridge before, so,
your input would be greatly appreciated.
Also, on the Charts issue, can't find anything that tells me how to chart two fields, everything seems to be geared for 3 fields.
Your time and help are greatly appreciated.
Sincerely,

Supmrio

 

[scriverb]

The percent part: Please post your calculated information. Show us the calculation being used to create this figure. Then after we fix that all you have to do is choose from the format property of the text box the Percent format and then 0.23 will be displayed as 23%.

So get back to me on the calcualted part and let's solve that first.

Then I need a little more info on the chart to help you. Please give me the details of what you want to chart and how you want it to look. Bob Scriver

 

[supmrio]

Hi Scriverb,
i have a query that gives total of respondents - ID field -
in TABLE "members" followed by another query that breaks it down by gender from the same table "members":

GENDER COUNT
female 78
male 22

I would like to show what pct of the total (ID) is female and/or male, thus:

GENDER COUNT PCT
female 78 78% "future chart"
male 22 22% "future cbart"

I have been building an expression :

TABLE TABLE
pct : =[members]![countofgender] / [members]![countofID}

i am doing this from he expression builder dialog.
When i try to execute it, the paramter dialog comes up.
asking for members and gender.

Hope this is what you wanted...

Thanks,

Supmrio

 

[scriverb]

The error you were getting is because the first query was taking in table Members and aggregating the total count. The second query was taking in that query and you were still referring to table members when you needed to be referring to the query name. Table members was not available at that time.

But, here is the solution to your problem. I used a made up table named Members with fields ID and Gender. Make three new queries and name them as indicated below. Then execute the last query only. The first two are just setups for the last one where the percentage is calculated.

New query qryCountOfMembers
SELECT 1 AS LINK, Count(Members.ID) AS CountOfID
FROM Members
GROUP BY 1;

New query qryCountsByGender
SELECT 1 AS LINK, Members.Gender, Count([Members]![Gender]) AS CountOfGender
FROM Members
GROUP BY 1, Members.Gender;

Last query qryPercentByGender
SELECT qryCountsByGender.Gender, qryCountsByGender.CountOfGender, Format([qryCountsByGender]![CountOfGender]/[qryCountOfMembers]![CountOfID],"0.0%&quot AS [PerCent]
FROM qryCountOfMembers INNER JOIN qryCountsByGender ON qryCountOfMembers.LINK = qryCountsByGender.LINK;

Let me know if this is what you wanted.




Bob Scriver

 

[supmrio]

Bob,you hit a homerun with this one. That is exacatly what I wanted, albeit it took me all weekend to comprehend the SQL statements.
Were I to want other queries to produce similar results, do I have to use the same concept? What happened to getting Percents at report time?
And now to the charts.

Thansks,

Mario

 

[scriverb]

Yes the same concept is necessary because you need to tabulate your denominator(Total ID's) in a different fashion before you are ready to calc. the percentage.

Each situation is different but the same concept applies. I use the manufacture field LINK: 1 as a common field for all of my queries like this so that I then have a way to link these single tabulated records together.

As far as percentages at report time you can do it this way or you can perform some type of Grouping and rollup and calculations. Record counts at report time are available so that percents can be calculated in the Constrol Source of a report object textbox.

Glad that I could help. Bob Scriver

 

[supmrio]

Bob,
About the Charts?

Thx,

Mario

 

[scriverb]

Exactly wht would you like your chart to look like? Explain to me how the Chart Wizard does not help you. Bob Scriver

 

[supmrio]

None of the charts pictured are for 1 or 2 fields, they all
point to 3 parts.
Unless I am not reading it correctly. If you think the answer is in the HELP somewhere, then by all means I will go there and dig it out.
Just give me a little push, I will gain the speed!
Thank you for all your time.

Mario

 

[scriverb]

Yes, I think you are eliminating the charts that could help you because of the graphic in the wizard. Just pick one that you think will apply and proceed. You can create a just a two axis chart without the z axis.

Give it a try. Bob Scriver