Optional grouping of a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way I can create a flexible report whereby the grouping for it
is optional? I have created a sales report that is sourced from a crosstab
query and I now want to provide users with the option (via a form) to group
the data on the report by store. I have found a way of amending the query's
SQL for this as appropriate, however, I now need a way to firstly amend the
grouping options for the report, and then to prevent the report from
requesting a parameter value for the 'StoreName' field each time that it is
not required.

Any help will be much appreciated.

Regards,

Jedster
 
you can add a "GroupByThis" field to your report source query, aliasing some
default grouping field, then group report using it. now you can just change
report recordsource SQL, setting alias GroupByThis to necessary field - and
report will be grouped accordingly
 
Alex,

Thanks for your reply, however, I'm not quite sure I fully understand your
solution. It seems that this would only allow me to switch a single grouping
option to any field of my choice.

The problem with this method is that, within my report, I already have a
number of other grouping levels which I wish to retain, and so it is only the
StoreName grouping level that I wish to set as optional. However, I'm not
sure how I might be able to 'optionally' remove all traces of 'StoreName'
from both the display and grouping levels of the report.

Regards,

Jedster
 
Hi Jedster,
as i understand you - i think you can do the same with optional grouping.
When you dont need it - just set GroupByThis to null, and in format event of
this group level check for null and set visible property to false. so you
will "remove" grouping
HTH
 
Alex,

Thanks so much for your help; it's really appreciated.

I think I can understand the general theory of how this works, however, the
problem is that there some lines of your code that I am unfamiliar with and I
can't really understand their purpose. There are also some modules in there
too which, due to my sheer inexperience, I am really struggling to comprehend.

I don't want to appear cheeky here, but would it be possible to include a
little more annotation in your coding so that I could get a better idea of
what's going on at each stage? That would really help me a lot, as I think
your solution seems to be exactly what I'm looking for.

Thanks again for your time and trouble.

Jedster
 
Jedster,
i think you can make it following:
- Make your report grouped on GroupOn Alias
- Open form frmReportGroup and replace list of fields with your ones, and
SQL there with your one (based on report's record source)
- copy/paste code behind provided report into your report
- import all module to your application also

After this i think your report will start to work

HTH
 
Hi again,

I've looked at your method in more detail, however, I'm not sure that it
could work for my report.

For the report to work properly, I think I would have to remove the
'StoreName' field from the crosstab query, as I'm not sure how I could
successfully replace this with a null value.

I have listed the SQL code for both my base query and crosstab query. If
you have the time, please have a look at them for me and tell me what you
think I might be able to do.

qryEPOSSalesByProduct (base query):

SELECT tblEPOSSales.[D/S/S Department Code], tblEPOSSales.[D/S/S Department
Description], tblEPOSSales.[D/S/S Section Code], tblEPOSSales.[D/S/S Section
Description], tblEPOSSales.[D/S/S Sub Section Code], tblEPOSSales.[Sub
Section Description], tblEPOSSales.[CU Ean Code], tblEPOSSales.[CU
Description Food Database], tblEPOSSales.FullDescription, tblEPOSSales.[BS
Transaction Date], tblEPOSSales.[BIS Sales Quantity], tblEPOSSales.[BIS Sales
Value], tblEPOSSales.[BIS Sales Vat], tblEPOSSales.[NET Sales £],
tblDates.WeekNo, tblEPOSSales.Week, Format(Weekday([Date]),'dddd') AS [Day],
tblEPOSSales.Buyer, tblEPOSSales.NSL, tblEPOSSales.[Store Ana Code],
tblStores.StoreName
FROM (tblDates INNER JOIN tblEPOSSales ON tblDates.Date = tblEPOSSales.[BS
Transaction Date]) INNER JOIN tblStores ON tblEPOSSales.[Store Ana Code] =
tblStores.StoreANA
WHERE (((tblEPOSSales.Buyer) Is Not Null));

qctbEPOSSalesByProductByWeekNo (crosstab query):

PARAMETERS Forms!frmEPOSReportDialogue!txtEndWeek Long;
TRANSFORM Sum(qryEPOSSalesByProduct.[BIS Sales Quantity]) AS [SumOfBIS Sales
Quantity]
SELECT qryEPOSSalesByProduct.[D/S/S Department Code],
qryEPOSSalesByProduct.[D/S/S Section Description], qryEPOSSalesByProduct.[Sub
Section Description], qryEPOSSalesByProduct.[D/S/S Section Code],
qryEPOSSalesByProduct.[D/S/S Sub Section Code], qryEPOSSalesByProduct.[D/S/S
Department Description], qryEPOSSalesByProduct.Buyer,
qryEPOSSalesByProduct.[CU Ean Code], qryEPOSSalesByProduct.FullDescription,
qryEPOSSalesByProduct.NSL, qryEPOSSalesByProduct.StoreName,
Sum(qryEPOSSalesByProduct.[NET Sales £]) AS Cost,
Sum(qryEPOSSalesByProduct.[BIS Sales Quantity]) AS total,
Sum(qryEPOSSalesByProduct.[BIS Sales Value]) AS [Value]
FROM qryEPOSSalesByProduct
WHERE (((qryEPOSSalesByProduct.Week) Between
([Forms]![frmEPOSReportDialogue]![txtEndWeek]-6) And
[Forms]![frmEPOSReportDialogue]![txtEndWeek]))
GROUP BY qryEPOSSalesByProduct.[D/S/S Department Code],
qryEPOSSalesByProduct.[D/S/S Section Description], qryEPOSSalesByProduct.[Sub
Section Description], qryEPOSSalesByProduct.[D/S/S Section Code],
qryEPOSSalesByProduct.[D/S/S Sub Section Code], qryEPOSSalesByProduct.[D/S/S
Department Description], qryEPOSSalesByProduct.Buyer,
qryEPOSSalesByProduct.[CU Ean Code], qryEPOSSalesByProduct.FullDescription,
qryEPOSSalesByProduct.NSL, qryEPOSSalesByProduct.StoreName
PIVOT "Wk" & ([Week]-[Forms]![frmEPOSReportDialogue]![txtEndWeek]) In
("Wk0","Wk-1","Wk-2","Wk-3","Wk-4","Wk-5");

Thanks again.

Regards,

Jedster
 
Hi Jedster,
bit difficult to findout what it wrong with queries if i dont have whole
picture.
I think you can try to first make a simple report with grouping feature,
just use a simple query, not a crosstab one, this will help you to
understand the idea and to decide if this method work for you


--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



Jedster said:
Hi again,

I've looked at your method in more detail, however, I'm not sure that it
could work for my report.

For the report to work properly, I think I would have to remove the
'StoreName' field from the crosstab query, as I'm not sure how I could
successfully replace this with a null value.

I have listed the SQL code for both my base query and crosstab query. If
you have the time, please have a look at them for me and tell me what you
think I might be able to do.

qryEPOSSalesByProduct (base query):

SELECT tblEPOSSales.[D/S/S Department Code], tblEPOSSales.[D/S/S
Department
Description], tblEPOSSales.[D/S/S Section Code], tblEPOSSales.[D/S/S
Section
Description], tblEPOSSales.[D/S/S Sub Section Code], tblEPOSSales.[Sub
Section Description], tblEPOSSales.[CU Ean Code], tblEPOSSales.[CU
Description Food Database], tblEPOSSales.FullDescription, tblEPOSSales.[BS
Transaction Date], tblEPOSSales.[BIS Sales Quantity], tblEPOSSales.[BIS
Sales
Value], tblEPOSSales.[BIS Sales Vat], tblEPOSSales.[NET Sales ?],
tblDates.WeekNo, tblEPOSSales.Week, Format(Weekday([Date]),'dddd') AS
[Day],
tblEPOSSales.Buyer, tblEPOSSales.NSL, tblEPOSSales.[Store Ana Code],
tblStores.StoreName
FROM (tblDates INNER JOIN tblEPOSSales ON tblDates.Date = tblEPOSSales.[BS
Transaction Date]) INNER JOIN tblStores ON tblEPOSSales.[Store Ana Code] =
tblStores.StoreANA
WHERE (((tblEPOSSales.Buyer) Is Not Null));

qctbEPOSSalesByProductByWeekNo (crosstab query):

PARAMETERS Forms!frmEPOSReportDialogue!txtEndWeek Long;
TRANSFORM Sum(qryEPOSSalesByProduct.[BIS Sales Quantity]) AS [SumOfBIS
Sales
Quantity]
SELECT qryEPOSSalesByProduct.[D/S/S Department Code],
qryEPOSSalesByProduct.[D/S/S Section Description],
qryEPOSSalesByProduct.[Sub
Section Description], qryEPOSSalesByProduct.[D/S/S Section Code],
qryEPOSSalesByProduct.[D/S/S Sub Section Code],
qryEPOSSalesByProduct.[D/S/S
Department Description], qryEPOSSalesByProduct.Buyer,
qryEPOSSalesByProduct.[CU Ean Code],
qryEPOSSalesByProduct.FullDescription,
qryEPOSSalesByProduct.NSL, qryEPOSSalesByProduct.StoreName,
Sum(qryEPOSSalesByProduct.[NET Sales ?]) AS Cost,
Sum(qryEPOSSalesByProduct.[BIS Sales Quantity]) AS total,
Sum(qryEPOSSalesByProduct.[BIS Sales Value]) AS [Value]
FROM qryEPOSSalesByProduct
WHERE (((qryEPOSSalesByProduct.Week) Between
([Forms]![frmEPOSReportDialogue]![txtEndWeek]-6) And
[Forms]![frmEPOSReportDialogue]![txtEndWeek]))
GROUP BY qryEPOSSalesByProduct.[D/S/S Department Code],
qryEPOSSalesByProduct.[D/S/S Section Description],
qryEPOSSalesByProduct.[Sub
Section Description], qryEPOSSalesByProduct.[D/S/S Section Code],
qryEPOSSalesByProduct.[D/S/S Sub Section Code],
qryEPOSSalesByProduct.[D/S/S
Department Description], qryEPOSSalesByProduct.Buyer,
qryEPOSSalesByProduct.[CU Ean Code],
qryEPOSSalesByProduct.FullDescription,
qryEPOSSalesByProduct.NSL, qryEPOSSalesByProduct.StoreName
PIVOT "Wk" & ([Week]-[Forms]![frmEPOSReportDialogue]![txtEndWeek]) In
("Wk0","Wk-1","Wk-2","Wk-3","Wk-4","Wk-5");

Thanks again.

Regards,

Jedster


Alex Dybenko said:
Hi again,
i made a sample which shows technique i described:
http://www.pointltd.com/Downloads/Details.asp?dlID=45
Click to expand...
Click to expand...
 
Back
Top