Need part of standard report in a report

[David_Skov]

The topic is not really good, but I didn't know how to express it then

My last thread was a question about creating the correct table to be able to create a report. I can now create the report and have almost all the info I need except for one minor detail.

My report is an overview of the market in Denmark, and it shows a total DVH (almost) of the danish retailmarket and then the top 5 biggest retailers in Denmark. Part of the report Should have a percentage of each of the top 5 retailers marketshare in Denmark. If the total market is given by 'DVH', the marketshare of the top retailer is:

Marketshare=(Top retailer revenue)/DVH

Since I have the total DVH I would like to know if I can make a reference to DVH in a report?

 

[David_Skov]

I can see I need this function:

=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = DAGLIGVAREHANDLEN EX. COOP")

DVH_krydstabel has these coloumns:

Market, SegmentID, DMLY, DMTY, LYFY, YTDLY, YTDTY

The Dlookup function should give me the value of [LYFY] in the tabel 'DVH_krydstabel' where [Market] = 'DAGLIGVAREHANDLEN EX. COOP' and SegmentID equal the current Segment shown in the report.

 

[Andrew Fergus]

Hi David,

I don't understand the 2nd post so I will answer your first post.

If the "DVH" value is in your query, then you can include that value on your report. And if that value is on the report then "yes" you can refer to it in another calculation in your report.

So if the value "DVH" is already there then you don't need to calculate the market share in the query (which is what I think you were doing with your 2nd post) - you can calculate the market share on the report instead. In the detail section of the report beside the "value" create a new unbound text box and enter your market share formula from your first post. Provided you use the correct variable names and format the box to % then it should work. Post again if it doesn't work.

HTH, Andrew.

 

[David_Skov]

Problem is that I have the following market:

DVH
Chain-store1
Chain-store2
Chain-store3
Chain-store4

DVH is part of data and the marketshare is calculated for each chain-stores for different facts. So marketshare will be:

Chain-store1/DVH
Chain-store2/DVH
Chain-store3/DVH
Chain-store4/DVH

So I need to be able to retrieve the same revenue 4 times. And unless I'm wrong (might be it ) then a report is a structured setup were I build header/footer and a detailsection. The detailsection is only one line out of all the lines retrieved in the database. So how do I call upon the same number 4 times (DVH)?

 

[Andrew Fergus]

Hi David,

Ignoring the variable names for the moment ( I will come back to your variable names soon), you are right with this statement :

a report is a structured setup were I build header/footer and a detail section

You are right.

In the header you put the report heading.

In the detail you put the detail - let's call it "value". This will pull through each "value" from the underlying table or query.

In the footer we put summary things such as "total" for which the control source might be =Sum([value]), and for this example give the total the name "total" in the properties box.

However, you can refer to this "total" in the detail part of your report. If you create a new unbound text box in the detail section of the report and set the control source to =[value]/[total] with a percentage format, then you will get the value divided by the report total as a percentage for each and every line in the detail section.

Alternatively, instead of using =[value]/[total] you can use =[value]/Sum([value]) and it works exactly the same. Try it and you will see.

Getting back to your variables, if the value "DVH" is the total market and if "DVH" is in the underlying table or query then you can use your market share formula in the detail section of the report (with reference to "DVH") - even though there are 4 lines it will work it out for every line. You could have 100 lines of data and it will still work it out for each line. You can have the DVH in the same detail section four times if you want and it will still work. Provided your variable names are correct then the formulae you mentioned...

Chain-store1/DVH
Chain-store2/DVH
Chain-store3/DVH
Chain-store4/DVH

...should work. If you try a small example first you will see what I am referring to. Unless I am misunderstanding you then this should work.

Andrew.

 

[David_Skov]

Problem is that the DVH is not! equal to Chain-store1+Chain-store2+
Chain-store3+Chain-store4

So I can't just take the total, that's why I need to get the value from DVH datapart

 

[Andrew Fergus]

Where I have used "total" in my example you can use "DVH".

Is DVH in the table or query that you have used for your report?

 

[David_Skov]

Yes DVH is in the Query since it's part of the whole report

 

[Andrew Fergus]

Hi David,

You have already written the solution here :

Chain-store1/DVH
Chain-store2/DVH
Chain-store3/DVH
Chain-store4/DVH

If you are using 4 different columns of data in the report then the formulae you wrote above are your answer.

If you have one variable name (i.e. the 4 stores are shown on 4 rows instead of 4 columns) then the formula will be :

=[your store sales variable name]/[DVH]

Have you tried entering the formula into the detail section of the report?

Andrew

 

[David_Skov]

I found this way to get by:

=DLookUp("[LYFY]";"DVH_krydstabel";"[Market] = ""DAGLIGVAREHANDLEN EX. COOP"" AND [SegmentID] =""" & Reports!DVH_incl_COOP_test!Segment & """")

It looks up the value needed depending on the segment given in the report

But tx for taking Your time