custom sort

[kristal9]

CR 2008

I have a report with Fiscaldate/Year, Account Name and Measure.

the report will contain multiple fiscalmonths at a time. I need to sort the report first by fiscal month -ascending them by measure decending only for the first or earliest fiscal date.

for all dates afterwards, i need to sort ascending by the account name.

i have tried the formula
//sort

if min({fiscaldate/Year})={fiscaldate/year} then -{measure} else
0


then i could sort by @sort asc / 2nd sort on account name asc.

It however wont let me sort on this. @sort formula

suggestions?

 

[hilfy]

You can't sort on a formula that uses a summary - so the call to Min() is causing this. Do you have something like a start date parameter in the report? If not, how do you determine the start date?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kristal9]

Hi Dell,

Thanks for your response. That is correct. There are no parameters, nor start / end date. the query is simple fed the data and it will change weekly.

 

[hilfy]

One possible solution would be to create a command (SQL Select statement) that returns the min of the start date based on the query for the rest of the data.

If you're already using a command for the report, you could even include this data as a sub-select in your existing command.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kristal9]

Hi Dell,

Yea. I thought of that. Datasource is SAP so I don't have add command option. Boo.

 

[hilfy]

The only other option I can think of would be to use shared variables and a subreport. In your main report you would include only the data from the sales table. The subreport would include the data from the rejected parts table and would return the sum in a shared variable. Link to the subreport based on the item number.

This is going to slow things down quite a bit, though. When you use a subreport, it re-queries the data every time it runs and you'll need to run it for each item.

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kristal9]

sub reports wont be an option because i already have multiple sub reports in the report and this would require each subreport have embedded within them.

I found a work around in Excel by concentating values and doing a vlookup. However now i am running into too many rows issue. So back to Crystal.. it is possible to identify the top 10 records from the first period and only retrieve those records (by conditional suppression for example) into to only show those customers

For example... my data is Fiscal | Period Customer | Measure i have it sorted first by Fiscal Period ASC then by Meausre Decending. i have a running total to rank based on this with reset at change of fiscal period.

Where my detail data shows the Minimum fiscal period and rank of 1 to 10 i only want to show the first 10 for the first fiscal period then the corresponding accounts for subsequent fiscal period.

I tried to identify each of the top 10 with a formula @cust1 = if min[fiscalperiod]=[fisalperiod] and {#rank customer}=1 then [customer] else ""... etc with 10 formula for each of the top 10 then conditionally suppress each detail with this formula
({@MinFP}=[fiscalperiod{#Rank Revenue Customer}
or not({@Customer}
[{@Cust1}, {Cust2}, {Cust3},
{@Cust4},{@Cust5},{@Cust6},
{@Cust7},{@Cust8},{@Cust9},
{@Cust10}])

this however supprsses everything but the first 10 records of course.

Is there a way that is not a command and not a subreport to try to return 10 records per fiscal period that correspond to the top 10 records for the first period?

 

[kristal9]

Hi... still trying to get to this... to recap I have 3 fields in my report. FiscalPeriod, Account, Measure.

the requirement is to determine the top 10 accounts for the first fiscal period based on measure and then return ONLY those accounts for each FiscalPeriod a summary of their measure. BTW... cross tab isn't an option as i need to return details in rows not a grid. Again, my query simply is loaded on the back end retrieving 18 fiscal periods of data. there is no parameter on the back end to tell what is the start of the fiscal period.

I have created a sub report that determines teh minimum fiscal period using this formula
//@MFP
whileprintingrecords;
shared stringvar MFP;
MFP := minimum({FiscalPeriod})

Placed this into the report header of the main report. Then using this formula in the main report to retrieve the variable
//@MFP

whileprintingrecords;
shared stringvar MFP;

Using this formula i am making a determination of the measure based on a match to fiscal period. Essentially saying if the detail records fiscal period = the minimum return the measure else return a 0 value
//@MinMonthCalc

if {FiscalPeriod} = {@MFP} then {[Measures]} else 0

Then I inserted a group based on Account as my plan was to do at Top N (10) summary by Account to retrieve my results.

However, I am unable to do a group sort expert based on this or to summarize this field based the group. Is there a way? Alternative?

 

[IanWaterman]

Have you tried replacing Subreport with a SQL Expression?

Take SQL from subreport and then convert it to give you a summary showing min fiscal period.

You should then be able to use expression as you have

if {FiscalPeriod} = {%SQLExp} then {[Measures]} else 0

NB
When creating a SQL expression in the editor make sure all code is wrapped in ()

Ian

 

[kristal9]

Hi Ian,

thanks so much for your response. My source is SAP and I don't see SQL expression as an option. Is it available for SAP data source?

 

[IanWaterman]

Sorry never used SAP.

Have a look in File -> Options -> database tab and see if you can enable anything there.

Ian

 

[kristal9]

Within that tab the only things not checked under Data Explorer were System Tables and Synonyms. I check both of those but did not see SQL Expression become avaialable afterwards. Or any changes for that matter.

Any other options?

 

[IanWaterman]

Sorry nothing I can think of.

Can you create stored procedures on SAP. You can then do all this in SQL and and just run your report off that.

Looks like SAP is trying to make your life difficult ;-(

Ian

 

[kristal9]

Looks like SAP is trying to make your life difficult ;-(

You can say that again! Can't do stored procs with SAP. OMG

 

[kristal9]

I got it!

i took the orginal report and first grouped asc by fiscal period so my earliest fiscal period would be first. then 2nd group by Account showing the top 10 accounts by month.
Then with a sub report showing only the detail the fiscal period, account name, measure placed this in Group Header 2 B and linked this sub rpeort via the Account field to the main report

Then, within the section expert
Group 1 suppressed
Group 2 a suppressed
Group 2 b conditionally suppressed if the variable from the first subreport {@MFP}<>the Group 1 fiscal period
Detail suppressed
group 2 footer suppressed
etc.. and so on. ...



Thanks everyone for the thoughts and time and energy spent thinking it through with me.