Optional grouping of a report

[Jedster]

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

 

[Alex Dybenko]

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

 

[Jedster]

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

 

[Alex Dybenko]

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 Dybenko]

Hi again,
i made a sample which shows technique i described:
http://www.pointltd.com/Downloads/Details.asp?dlID=45

 

[Jedster]

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

 

[Alex Dybenko]

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

 

[Jedster]

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]

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

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 again,
i made a sample which shows technique i described:
http://www.pointltd.com/Downloads/Details.asp?dlID=45