Modify Sort/Grouping options dinamically

[Duvy]

Hello,
I have an application working with market, product, etc ...
with 5 levels. I would like to produce reports depending on user's choice and let them choice if they want by Market/Product or Product/Market etc... I would prefer to generate the report by modifying the sort/grouping, and also control if they need totals or not.
In all possible combinations of the 5 levels, I would like to drop some of them ... in other words, all 5 levels are (should) not be required. Is it doable?

Is there any way to modify the sort/grouping dynamically when I start the report ?

Thanks in advance for any help
Yvon Duvivier

 

[BSman]

I think that at minimum you're going to have to have a separate report defined for the number of levels used.

Given that requirement, you might try using a query as the data source for each report where the grouping levels are "generic" (i.e., Level1:[columnx], Level2:[columny], and so on). Once the user has selected the number of and order of columns to use for the report, build a query based on the user's request, but be sure to include the renamed level columns so you might have Level1:[columnd], Level2:[columnz], Level3:[columnx] in the query rather that what was in it when you designed the report.

By doing that you will only need a separate report for each number of levels that you permit the user to select for use in the final report. Just count the number of levels requested, build the SQL statment, and replace the query used to define the report (qryTwoLevels, qryThreeLevels, qryFourLevels) with the new SQL code before opening the report.

 

[Duvy]

Many thanks BSman for your response. Indeed I've worked in that direction.
What I have done : I generate 1 query with the first five fields (col) are what the user select and in the correct sequence. Never used to print but only for sort purpose. All others fields are what I need to print. The column contains the name I use for my level.

Now for my report, it's a bit more complicated : first I did not find any way to "modifiy" the levelHeader or footer but well how to generate it. So I wrote a module to completely generate the report and I check what they request. I endup with the correct report : if they need the major level on item (whatever market it is) I produce it, etc....

The next step where I am busy today is to transfert the detail from another report that I called "template". Doing that, I can continue to design any detail I want and when I generate my report I make a choice in one of my "templates".
I've worked in that direction because it is not easy to produce a "nice" detail when you do it programatically. Another advantage is the fact I can prepare the summary user need (totals and/or Min/Max date).

Once again, thanks for your help,
Yvon Duvivier