Error message in Financial reporting with wildcards

Fisherman

When we try to run the Account Schedule - Landscape report, we get the following error message -

---------------------------
Microsoft Business Solutions-Navision
---------------------------
The sum of the values in the Amount field cannot be calculated because the filter contains question marks (?), asterisks (*) or 'at' signs (@).

Filters: G/L Account No.: 4000..4095, Global Dimension 1 Code: 0*, Global Dimension 2 Code: W*, Posting Date
Table: G/L Entry

---------------------------
OK   
---------------------------

Is there a way to change these reports to allow them to use wildcards? We've spent a lot of time redesigning our dimension setup, and this is a massive headache on the back end.

Thanks.

matttrax

Why not set your filters differently. Something like this.

Global Dimension 1 Code: < 10000 (everything that starts with a 0 should come before this)
Global Dimension 2 Code: W..Wz (everything that starts with a W)

I think those filters end up being the same as the ones with the wildcards.

Fisherman

This is probably a bad example.

here's the thing. Our corporate strategic planning committee has decided that they want to capture 4 pieces of information related to all transactions (sales/purchase, etc...). They want our line of business, our division, our customer (who we're servicing), and their industry. To accomplish this, and to keep data entry simple, we condensed the four pieces of information into the two global dimensions. So, our first dimension is Division & Line of Business, and the second one is Customer and Industry. For example, if we serviced Sony's Consumer Electronics out of our Division 01 Retail area, then our dimensions would be ...

Global Dimension 1 -
01 Retail
Global Dimension 2 -
Sony - Consumer Elec.

So, if I wanted to run a report for all Sony business in any division, I would need to filter for Sony*... for all Retail would be *Retail*, etc... It's not a simple matter of > or < some value. It should be context specific.

kine

1) There is used CALCSUMS somewhere in the report. And this function cannot calc sums if filter like XXX* is used. Just simple filter or range can be used (it is because SIFT)
2) filter "Sony*" is same like "Sony..Sonyz" because all other combinations will be inside this range (if no special characters are used).
3) Worst is the filter "*something*" because you cannot create appropriate range filter.

If you still need this filtering, you will need to change the report and replace the calcsums (implicit (properties of dataitem) or explicit (by code)) by loops calculating the sums of selected fields. But this can have big impact to the processing time...

modric

One more...

You may add to yor Dim Values list:

Dim Value Code = 0X
Dim Value Type = Total
Totaling = 01|04..07|0BLABLA or whatever you need, feel free to experiment...

and then in AccShed in Dim Code Totaling column use this DimValueCode 0X - its allowed here (and works for me in v3.7) to use Dim Value of Type=Totaling, too...

Fisherman

wow... that is a massive limitation to SIFT. To only have partial implementation of the filters available in CALCSUMS... that's... almost criminal.

Modric - we have tried this, and I posted it in a previous thread, and never got a good answer. When we tried using a totalling dimension on this particular report, the report printed out with absolutely nothing on it. Are you sure about this?

Fisherman

kine - is this behavior of Calcsums documented somewhere? I've never seen it...

kine

If the "error message" is documentation...

I didn't saw anything else...

modric

Modric - we have tried this, and I posted it in a previous thread, and never got a good answer. When we tried using a totalling dimension on this particular report, the report printed out with absolutely nothing on it. Are you sure about this?

Fisherman, we use Total rather extensively, haven't tried BegTotal & EndTotal, so can't say if you can use the last two...

In fact, I was too lazy to write long Formula's in AccSchedules many times - we have several hundreds of small Depts, each having a positional 7-digit DimCode. As for you, 012* or 0123* didn't work for me either, so we created these Total Dims, where only once the long summing Formula must be entered

Fisherman

Mod -

We've tried both ways - with Begin..End Totals and just the straight Total. Neither Way is effective for us. We're in 4.2, by the way.

Can you give me an example of how you've set this up? Did you use ranges (dim1..dimx), or did you concatenate values (dim1|dimx)? We're also using a text-based dimension scheme, rather than numerics, although we've made the names consistent. So, if we're handling consumer goods, microprocessors, raw materials, and chemicals for CompanyX, then our dimension values look like this...

CompanyX - Cons Goods
CompanyX - MicroProcs
CompanyX - Raw
CompanyX - Chem

What we'd like to do is be able to get all of CompanyX to total (if we can use the total, then that's great). But, we might also perform services in consumer goods and chemicals for other customers, and we'd want to have a Consumer Goods total for all customer/consumer goods combinations, and the same for chemicals.

Will using a text-based dimension value like this prevent that, or will it still work so long as our convention is consistent? ideas?

modric

Ranges are rather tricky - you can never be sure, what NAV will include in the range, especially, if you have many values of different length - but concatenating thru | works for sure.

Besides, you can just add some more Dimesions - in one AccShedule there may be max 4 of them, but it must be based on Analysis View if more than 2 Globals (which historically sit directly in G/L Entry) are used.

In example mentioned, you may add Dimension named Partner, but, an Analysis View with Dims Region, Partner, GoodsType exported to Excel Pivot table will give you more freedom to slice & dice your data...

Fisherman

This still isn't working for us. I've tried tons of different iterations on this.

You are talking about the Account Schedule Reports, not the Analysis views, right?

Does anyone else have a problem with this?? I simply don't understand what these stupid dimension totals are supposed to do if you can't use them in this way, and I can't find any documentation from Microsoft that covers this topic.

Fisherman

Sorry, all - this is just really frustrating. MS has put this functionality out there, but has not documented it well-enough (that I can find) to adequately explain what it is for, or how to use it. I've found a financials manual that talks about it, but the examples that it uses don't even work in my DB. Does anyone have any more detailed info on this?