I've created a new BIC Report in Evolution using the Sales Master Container. I format my pivot table to group my dates into Years and Months as I want to see customers total sales by month.
I also collapse the field to show the totals by month which can be expanded by clicking on the + button.
When I save the Template to my report and re-run it, it loses the Grouped Dates and it Expands the Field automatically.
Does anyone have a solution to this?

Views: 186

Reply to This

Replies to This Discussion

hi, what u are experiencing is normal behavior as the grouping is based on the data, and when you save excel template, you are clearing the data out of the template.  

Try opening your template in design mode and adding a dummy row of data to your data sheet, then redo the grouping and save.  

Thanks very much Nigel.

I don't like to revive old posts, but I am curious... Did this solve your problem, Nadine?  I am having the same issue with Sage Intelligence for Sage 100 and although I have tried this, I am still having the same issue.  It gives me dates, not groups when I run the report.

Hello Benton

Is that specific column in excel in your pivot-table formatted as "date"

Format it as date (the entire column in the sheet)

Then try grouping those dates once more (clearly defining the month and year start and end)

Save the template and try it once more

Let me know how this goes


Hi Benton,

Apologies, I only saw this post now.

I did get it right eventually by adding two columns to the end of my raw data and inserting the following formulas:

1st Column

Heading: MonthE

Formula: =TEXT(X2,"MMMM")

Replace the X2 with whichever cell your Tx Date is in

2nd Column

Heading: YearE

Formula: =TEXT(X2,"YYYY")

Replace the X2 with whichever cell your Tx Date is in

This way I could group by the year followed by the month in my Pivot by using these two fields I created.

Make sure to rename your Data Source Named Range to include your two extra columns by going to Formulas | Name Manager. Find the RawData (or whatever you named the range as) Named Range and edit the selection by highlighting all the columns (in case the number of rows varies each time you pull the report) If you make all the columns your RawData named range, you'll just have to filer "BLANK" out of your Date Groups to exclude the Blank Rows.

Hope that helps:)

FANTASTIC!  I added them in the connector module and it works perfectly!  Thank you.  :)

It's a pleasure:)


The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service